Asked By
Stan Wayne
470 points
N/A
Posted on - 05/26/2011
Hello,
I'm a software developer who develops both desktop applications and web applications. My preferable database when developing desktop applications would be MS SQL Server. Sometimes I use Sybase SQL Anywhere and MS Access Database.
Recently I heard that MySQL can be used for desktop applications. I only use MySQL for web applications but if it's true that MySQL can be used in desktop environment as well that would be great I can connect both web and desktop applications to MySQL and interact with each other.
I'm using Visual Basic to develop desktop application while I'm using Netbeans for web applications. I'm very excited with the prospect of developing web and desktop applications connected to MySQL database. The prospects are endless and I just can't wait to apply it for the next project if it's true. Unfortunately I can't find the information I need while Googling.
I'm currently using Windows XP 32 bit SP3 with 2 GB RAM installed. Is there any additional steps needed to make MySQL database work with desktop applications? Or any additional files needed to be installed to make it work? I'm open to any ideas and suggestions guys. Looking forward to hearing from you all.
Thanks,
Stan
 …..
Answered By
Harry
200 points
N/A
#106607
MySQL database with Desktop Applications
Hi Stan,
As you know, MySQL is the most popular free and open source Relational Database Management System in the world. Surely it can be used with both web based and standalone applications written in common programming languages. First you have to download the latest MySQL setup file from the MySQL site and install the server.
Then configure the server instance as a multifunctional database from the MySQL server instance configuration wizard. After successful configuration, MySQL will run as a windows service. MySQL is not now heavyweight as Oracle, thus it will not eat up your resources. Your computer configuration is quite enough for running MySQL server.
To connect your VB desktop application and MySQL server, we need a 3rd part tool, which commonly called as a connector. For each programming language it will be a different connector. For Visual Basic, the connector is called as MySQL ODBC driver. So you need to find it and install it on your computer. Then you have to create Microsoft Remote Data Object for your Visual Basic project.
-
Go to Project option in the menu bar, then select the references, from there select the Microsoft Remote Data Object from the list.
-
Start a new Visual Basic project and add the Microsoft Remote Data Object – Using the menus select Project > References and then select the Microsoft Remote Data Object from the list. Now then anywhere from your code you can access the MySQL database and execute queries.
-
The following sample code will help you more.
-
Private Sub connectMySQL_Click().
-
First you have to create three variables.
-
One for store the connection, one for store the query and one for store the results returned by the query.
Dim connection As New rdoConnection
Dim rs As rdoResultset
Dim query As New rdoQuery
Then you can connect to the MySQL server using connection variable with the MySQL ODBC Driver. You have to provide the username where I mentioned username and password where I mentioned password, for the server and the IP address or the name of the server and the name of the database you are connecting as well.
connection.CursorDriver = rdUseOdbc
connection.Connect = "uid=Username;pwd=Password;server=ServerIpOrName;"
& _"driver={MySQL ODBC 3.52 Driver};database=Database;dsn=;"
connection.EstablishConnection
Now we can test the connection by running a query. For that purpose we are creating a simple query for return the all records of subjects table. The results for the query will be saved in rs Result Set variable.
With query .Name = "selectSubjects"
.SQL = "select * from subjects"
Set.ActiveConnection = connection
Set rs = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With
For display the result, we have to iterate through the result set. So we would use a loop for that purpose.
Do Until rs.EOF
With rs
In here you can call the fields of a result set. You can reference a table field as !field
rs.MoveNext
End With
Loop
After using the Result Set, you have to close it. You have to close the connection to the database as well.
rs.Close
connection.Close
End Sub
Hope you will have a better idea about connecting your Visual Basic desktop application to MySQL server. It will be advisable to create a single common database connection code in your program and use the same code whenever possible.
