vbMySql

vbMySql is a Visual Basic 2005 (Visual Basic .NET) class module that lets you run MySQL database statements in a way that is similar to running them from PHP or directly from the MySQL client.

I've been running MySQL statements through various tools for several years. When I tried to use the MySQL Connector/NET driver I found that the methods used in Visual Basic were aukward. To solve the problem I learned how to use the MySQL Connector/NET driver in the way published by Microsoft and MySQL AB and then I created this class as a wrapper that lets me run statements (queries) in the way that I prefer.

vbMySql.Execute("INSERT INTO sometable (column1, column2) VALUES ('Hello', 'World')")

As a side note, I may have been referring to MySQL "statements" incorrectly as "queries". One of the things I ran into was "non-queries". It seems that only some MySQL statements are queries. If you take the word literally, that makes perfect sense, but after years of habit, it had me confused for a couple minutes. And, taken litterally, you can't really use statements in both cases either, but I will in this document.

Setup

You need the MySQL Connector/NET driver in order to use this class. Download and install that first. Then do the following basic steps.

  1. Add a reference to the MySQL Connector/NET driver to your VB project.
  2. Add the vbMySql.vb class file to your project.

Now, you need to create an instance of the class and set the connection properties. This will be one of the first things you run when your program starts. The Form_Load event of your main form may be a good place for the following code.

' Setup a variable for return value error checking
Dim returnValue As Boolean

' Create an instance of the vbMySqlClass
Dim vbMySql As New vbMySqlClass

' Setup connection details
vbMySql.Server = "localhost"
vbMySql.Database = "mydb"
vbMySql.Username = "root"
vbMySql.Password = "pass"

' Initialize the database
returnValue = vbMySql.Initialize()
If returnValue = False Then
    MsgBox("An error occured when connecting to the database.")
End If

vbMySql.Execute

This function executes a MySQL statement, which VB calls a "non-query". These are MySQL statements that don't return rows. For example an Update, Insert, or Delete statement.

vbMySql.Execute("INSERT INTO sometable (column1, column2) VALUES ('Hello', 'World')")

vbMySql.ReadBase64

I created this function as an easy way to write binary data into the MySQL database. You can write binary data directly, but it's a bit more difficult. Base64 encoding adds about 33% to the file size and adds some processing overhead, but I digress. I find Base64 data easier to read, write, display, and troubleshoot.

' Setup a string variable for the data
Dim myFileString as String

' Base64 encode the data and return the result into the string
myFileString = vbMySql.ReadBase64("c:\somefile.jpg")