Tutorial to access MySQL databases behind a firewall with the LibMyWitch control

1. »Introduction«
2. »Functionality of the LibMyWitch control«
3.    Install the LibMyWitch control
4.    Prepare a VB form
5.    Show status information
6.    Create PHP - Script
7.    Upload and test the PHP - script
8.    Initialize Control
9.    Load databases from server
10. »Example: Address Book«
11.    Table design
12.    Create table
13.    List records
14.    Insert new record
15.    Handle a picture/ BLOB field
16.    Backup / Restore table
17. »Shareware or not Shareware«

Introduction

In the internet (and also on this site) you find a lot of tools, controls and source code to manage MySQL databases ether via MS ADO -MyODBC interface or via the MySQL API library *libmysql.dll*.
But have you ever tried to access a remote MySQL database which is hosted in the web behind a firewall?

The most tools only work if the database is not hosted on a web server behind a firewall. The reason is, that client and database server communicate over port 3306 which is blocked by the firewalls's of the internet provider for security reasons.
Till now there was no efficient way to embed the access to the MySQL database into a VB application.

I've now developed a VB Activex control LibMyWitch, which is able to access every remote MySQL databases in the web – even if the database server is hosted behind a firewall. The control automatically creates a PHP script, which encapsulates all database queries:

The control calls the PHP script via port 80 and the PHP script calls then the database via port 3306. The database sends the results back to the PHP script which prepares the data to send it back to the Activex control.
No firewall will block these actions, because the standard HTTP port 80 is used to access the server.
The transfer of data between the web server and the client could be encrypted (blowfish or SSL) and compressed. So the transmission will be secure and fast with less traffic.

Important: This control is Shareware but free for "private" use! Please read more about this at the end of this article.

To demonstrate the functionality of the control I will first show the main functions of the LibMyWitch control and ...

• ... how to create a PHP script, which has to be upload to the web server.
• ... how to check special information's about the PHP / MySQL configuration on the server.
• ... how to initialize the LibMyWitch control.
• ... how to show for example all available databases.

Then I will demonstrate the powerful functions by developing an address book with the following features ...

• ... manage the address data (add, delete, update, search)
• ... handle pictures. A picture of a person will be loaded and saved in the database
• ... backup and restore the MySQL table of all address data inclusive the picture
• ... manipulate the MySQL table itself(create, delete)

Functionality of the LibMyWitch control

I will give you now a small overview how to use the LibMyWitch control within of a VB application.
A complete description of the LibMyWitch object you will find on the LibMyWitch web site

Install the LibMyWitch control

After you have downloaded and installed the LibMyWitch Activex control you have to add the control to your VB application.
Choose Project - Components from the menu. Select the LibMyWitch OCX control. Then choose View - Toolbox. The control () should appear in the toolbox.

Prepare VB form

In our example we have to ...

• ... open a new project
• ... add the LibMyWitch control
• ... add three Command Buttons
• ... add two List Boxes

Show status information

To have a look at the current actions and states of the control and the errors within the web connection we use the events »LibMyWitch1_Error« and »LibMyWitch1_StateChange«.
Move a list box on the form and add the following functions:

Private Sub LibMyWitch1_Error(ErrorCode As _ 
                                LibMyWitchOcx.mw_ErrorEnum, _ 
                                ByVal ErrorDesc As String) 
    List1.AddItem "Error: " & ErrorCode & " - " & ErrorDesc 
End Sub 

Private Sub LibMyWitch1_StateChange(State As _ 
                            LibMyWitchOcx.mw_StatesEnum) 
    List1.AddItem "State: " & State & " - " & _ 
                    LibMyWitch1.StateString(State) 
End Sub

(Description: »State«, »StateString«)

Create PHP - Script

To access the database behind a firewall a PHP script has to be created and uploaded to the web server.
To create the script we use the LibMyWitch method »CreateMainScript()«.
Copy the following code to your form

Private Sub Command1_Click() 
    LibMyWitch1.CreateMainScript "db.database-server.com", _ 
                                        "restrict to database-name", _ 
                                        "user-name", _ 
                                        "password", _ 
                                        3306 
End Sub

(Description: »CreateMainScript()«)

Replace your MySQL database access information in the code:

• MySQL server IP: Location of your MySQL server
• Database name: You don't need to set any database name. But sometimes your ISP disable the SQL query "SHOW DATABASES", so that you cannot list the available databases. In this case you should explicitly set a database name.
• user name: MySQL user name
• password: MySQL password

Some other properties for encrypted and compressed transfer could be set, but in this first example we don't need to set them.
Now start the project and click the first button to create the PHP script.
A message box will be shown with the information where the PHP script has been saved.

Upload and test the PHP - script

Now upload the PHP script to the web server.
Enter the URL of the script into your internet browser to test if the script is available.
You can also check the availability  by using the LibMyWitch method »GetInformation()«.
Please replace the URL of the script in the LibMyWitch1.UrlScript property. A timeout will stop the action after 5 seconds if there are problems with the connection.

Private Sub Command2_Click() 
' Check if server is aviabable 
     Dim sInformation As String 
     ' Enter the Url of the uploaded PHP script 
    LibMyWitch1.UrlScript = "http://somewhere.in.the.web.com/libmywitch/libmywitch.php" 
    LibMyWitch1.Timeout = 5 
     If LibMyWitch1.GetInformation(mw_Test_PHPInfo_available, sInformation) Then 
        MsgBox ("Script is available!") 
     Else 
        MsgBox ("Script is not available! Check the Url") 
     End If 
End Sub

(Description: »GetInformation()«, »UrlScript«)

By using the method »GetInformation()« you can get also information about the MySQL version, the PHP version and every section in the PHP-Info file.

By testing if the PHP-Info file can be loaded from the server, we check if the URL is entered correctly and so if the server is available.

Execute the project!

Initialize Control

Some properties have to be set to access the PHP script on the web server:

• Access web server via proxy server?
• Access the PHP script via web authentication?
• Transfer should be encrypted?
• Transfer should be compressed?
• Should the access to the MySQL database via PHP script or the MySQL API wrapper *LibMySQL.DLL*. The MySQL API wrapper can only be used if the database is located in the intranet/LAN and not remote in the internet.

Please have a look at the comments within the code and set the »UrlScript« property to the url of the PHP script.

We always have to set also the »SelectDatabase()« property if we want to access a table of the MySQL database.

Private Sub SetAccessType() 
    ' The MySQL database can be accessed directly via the MySQL API 
    ' Library *LibMySql.dll* ConnectionType=MY_DLL 
    ' or can be accessed via PHP-Script. 
    ' Now we want to access the database via PHP scrip: 
    LibMyWitch1.ConnectionType = mw_Script 
    ' Set Proxy configuration: 
    ' If you are using a proxy server to access the web, 
    ' you can set the data of your proxy server 
    LibMyWitch1.ProxyUse = False 
    ' Set Web-Authentification if needed 
    LibMyWitch1.Authentification = False 
    ' Set Password for encrypted data-transfer 
    ' In this example we don't use encryption 
    LibMyWitch1.BlowfishPassword = "" 
    LibMyWitch1.BlowfishEncryption = mw_NoEncryption 
    ' It is also possible to compress data before sending to the server 
    ' or back to the client 
    LibMyWitch1.GZipUse = False 
    ' Url of PHP-Script 
    LibMyWitch1.UrlScript =  "http://somewhere.in.the.web.com/libmywitch/libmywitch.php" 
End Sub

(Description: »ConnectionType«, »ProxyUse«, »Authentification«, »BlowfishPassword«, »BlowfishEncryption«, »GZipUse«, »UrlScript«)

Load databases from server

First part of our test is finished: The PHP script is available.
Now we have to check if the database information we have entered is correct and if the MySQL database could be called.
To get record sets back from the MySQL server we have to send SQL queries to server:

example 1:

To get the available database, we have to send the
    SQL query: "SHOW DATABASES;"
to the server.

example 2:

To get the available tables of a database 'XYZ', we have to send the
    SQL query: "SHOW TABLES FROM XYZ;"
to the server.

example 3:

To get the tables content of a table 'ABC', we have to send the
    SQL query: "SELECT * FROM ABC;"
to the server.

The most important function of the LibMyWitch control is the »Execute« method.

Private Sub Command3_Click() 
     Dim MyRS As MyWitch_Recordset 
     Dim i As Integer 
    ' clear list box 
    List2.Clear 
    ' Set Proxy configuration, Web-Authentication, Url of PHP-Script 
    ' This function is defined above 
    SetAccessType 
    ' Show all databases 
    List2.AddItem "Available databases ..." 
    List2.ListIndex = List2.ListCount - 1 
    ' Execute query 
     Set MyRS = LibMyWitch1.Execute("SHOW DATABASES", 1) 
     If MyRS.RecordCount > 0 Then 
        ' List databases if records exist 
         Do 
            List2.AddItem MyRS.ValueByCol(0) 
            MyRS.MoveNext 
         Loop While Not MyRS.EOF 
     End If 
    List2.ListIndex = List2.ListCount - 1 
End Sub

(Description: »Execute«, »LibMyWitch Record set Object«, »Recordset.RecordCount«, »Recordset.ValueByCol«, »Recordset.MoveNext«, »Recordset.EOF«)

Execute the project and all databases will be listed!

Example: Address Book

To show in detail the features of the LibMyWitch control I will now develop a address book:
- Table design
- Create table
- List records
- Insert new record
- managing address data (add, delete, update, search)
- handle also a picture of the person, load and save it in the database
- backup MySQL address table
- restore MySQL address table
- delete MySQL table

The complete application with source code you will find in the LibMyWitch/Example/PhoneBook Folder!

Table design

The table will define fields for standard address information (varchar fields) and a picture (mediumblob field):

Create table

We always have to set the »SelectDatabase()« property if we want to access a table of the MySQL database:
    LibMyWitch1.SelectDatabase="database name"
before we can send a query to the server:

Private Sub CreateTable() 
    Dim MyRS As MyWitch_Recordset 
    Dim i As Integer 
    Dim strCreate As String 
    Dim lgAffectedRows As Long 
    If lstDatabase.ListIndex = -1 Then lstDatabase.ListIndex = 0 
    ' Set Proxy configuration, Web-Authentication, Url of PHP-Script 
    SetAccessType 
    ' Create sql query 
    strCreate = "CREATE TABLE dr_addressbook (" 
    strCreate = strCreate & "PersId int(3) NOT NULL auto_increment," 
    strCreate = strCreate & "LastName varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "FirstName varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Company varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Adress varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "PostalCode varchar(10) NOT NULL default ''," 
    strCreate = strCreate & "City varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Country varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Telephone varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Cellular varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Email varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Webpage varchar(50) NOT NULL default ''," 
    strCreate = strCreate & "Info TEXT NULL," 
    strCreate = strCreate & "Photo MEDIUMBLOB NULL," 
    strCreate = strCreate & "PhotoName varchar(50) NULL," 
    strCreate = strCreate & "Primary Key(PersId)" 
    strCreate = strCreate & ") TYPE=MyISAM;" 
    Set MyRS = LibMyWitch1.Execute(strCreate, lgAffectedRows) 
    MsgBox lgAffectedRows 
    Set MyRS = Nothing 
End Sub

(Description: »SelectDatabase«, »Execute«)

List records

To fetch all record sets (except the pictures) we send a SELECT query to the server.

Private Sub LoadDataFromServer() 
    Dim i As Integer 
    ' Initilize the LibMyWitch Control 
    ' This function I have explain in a section above 
    SetAccessType 
    ' Send SELECT query 
    SetMyRS = LibMyWitch1.Execute("SELECT PersId, LastName, FirstName, " & _ 
                        "Company, Adress, PostalCode, City, Country, " & _ 
                        "Telephone, Cellular, Email, Webpage, Info, " & _ 
                        "PhotoName FROM dr_addressbook;", 0) 
    If MyRS.RecordCount > 0 Then 
        ' Records are available, 
        ' Move to the first recordset 
        MyRS.MoveFirst 
        ' Show current recordset 
        WithMyRS    'LibMyWitch1.Recordset 
            'Fill the text boxes with the record 
            For i = 1 To txtPers.Count 
                ' The tag-property of the txtPers textbobex has been set to the 
                ' field names of the table. Now the values of the current 
                ' recordset are called 
                txtPers(i - 1).Text = .ValueByName(txtPers(i - 1).Tag) 
            Next i 
      End With 
    Else 
        ' No records are available 
    End If 
End Sub

(Description: »SelectDatabase«, »Execute«)

Insert new Recordset

We set the *Tag* properties of the text boxes on the form to the Name of the table fields (PersId, LastName, FirstName, etc.).
Inside of a loop the content of the text boxes will be read and the SET part of the INSERT query will be created (example FIRSTNAME ='Dirk').
The »AddSlashes()« method is necessary to prepare all quotes, backslashes and other special characters so that the INSERT query could be executed correctly.
If a path of a picture has been entered, the picture will be loaded into a string and then also appended at the INSERT query string.
At least the query will be executed and the number of affected rows will be shown.

Private Sub InsertRecordSet() 
    Dim strSql As String 
    Dim i As Integer 
    Dim lgAffectedRows As Long 
    Dim strFileContent As String 
        strSql = "" 
        For i = 0 To 13 
            ' Check if the content of fields 
            If Len(txtPers(i).Text) > 0 Then 
                ' To handle special characters the AddSlashes method 
                ' is called 
                strSql = strSql & txtPers(i).Tag & "='" & _ 
                    LibMyWitch2.AddSlashes(txtPers(i).Text) & "'," 
            End If 
        Next i 
        If Len(txtPers(12).Text) > 0 Then 
            ' If a file name of a picture has benn entered, a the picture 
            ' has also to be upload to the database. 
            ' Load the picture 
            strFileContent = FileContents(lblPath.Caption) 
            ' Create UPDATE query part 
            strSql = strSql & "Photo='" & _ 
                    LibMyWitch2.AddSlashes(strFileContent) & "'," 
        End If 
        If Len(strSql) > 0 Then 
            ' Complete the INSERT query 
            strSql = "INSERT INTO dr_addressbook SET " & Left(strSql, _ 
                    Len(strSql) - 1) & ";" 
            ' Send query to server 
            LibMyWitch2.Execute strSql, lgAffectedRows 
            If lgAffectedRows > 0 Then 
                Call MsgBox("Affected Rows ..." & lgAffectedRows, _ 
                vbInformation + vbDefaultButton1, "PhoneBook") 
            End If 
        End If 
End Sub

(Description: »AddSlashes()«, »Execute«)

Handle BLOB Files

Sending Data into the BLOB column:
In the last section we have seen how to load a picture into the database.
We load the picture into a string, quote it (add slashes) and create the INSERT/UPDATE query string. Then we sent the sql query to the MySQL server:

    If Len(txtPers(12).Text) > 0 Then 
    ' If a file name of a picture has benn entered, a the picture 
    ' has also to be upload to the database. 
    ' Load the picture 
    strFileContent = FileContents(lblPath.Caption) 
    ' Create UPDATE query part 
    strSql = strSql & "Photo='" & _ 
            LibMyWitch2.AddSlashes(strFileContent) & "'," 
    If Len(strSql) > 0 Then 
        ' Complete the INSERT query 
        strSql = "INSERT INTO dr_addressbook SET " & Left(strSql, _ 
                Len(strSql) - 1) & ";" 
        ' Send query to server 
        LibMyWitch2.Execute strSql, lgAffectedRows 
        If lgAffectedRows > 0 Then 
            Call MsgBox("Affected Rows ..." & lgAffectedRows, _ 
            vbInformation + vbDefaultButton1, "PhoneBook") 
        End If 
    End If

(Description: »AddSlashes()«, »Execute«)

Retrieving a picture from the MySQLserver:
To avoid big traffic we only load the picture from the server if currently tablator of the picture is visible.
We send a query with the ID of the current recordset to the server:
SELECT Photo FROM dr_addressbook WHERE PersId=12
and get the picture viaRecordset.ValueByName property.
We save the string to a tempory images file and load it then into picturebox:

Private Sub LoadImageFromDB() 
    Dim MyRSImage As MyWitch_Recordset 
    Dim i As Integer 
    Dim strImage As String 
    Dim strImageType As String 
    Dim lFileNumber As Integer 
    ' Initilize the LibMyWitch Control 
    SetAccessType 
    ' Send query to database 
    Set MyRSImage = LibMyWitch2.Execute("SELECT Photo FROM " & _ 
                        "dr_addressbook " & _ 
                        "WHERE PersId=" & txtPers(13).Text, 0) 
    If MyRSImage.RecordCount > 0 Then 
        ' Move to first recordset 
        MyRSImage.MoveFirst 
        ' Load picture 
        strImage = MyRSImage.ValueByName("Photo") 
        ' Get file extention of the picture for the VB LoadPictue 
        ' method 
        strImageType = Mid(txtPers(12).Text, _ 
                       InStrRev(txtPers(12).Text, ".") + 1) 
        ' Set temporary path and name to store the picture 
        lblPath.Caption = App.Path & "	emp." & strImageType 
        On Error Resume Next 
        ' Delete old temp picture 
        Kill lblPath.Caption 
        On Error GoTo 0 
        ' Save picture to temp file 
        lFileNumber = FreeFile 
        Open lblPath.Caption For Binary As lFileNumber 
        Put #lFileNumber, , strImage 
        Close #lFileNumber 
        On Error Resume Next 
        ' Load Picture to image box 
        Image1.Picture = LoadPicture(lblPath.Caption) 
    End If 
    Set MyRSImage = Nothing 
End Sub 

(Description: »Recordset.ValueByName«, »AddSlashes()«, »Execute«)

Backup/Restore

We want also to integrate a backup/dump and restore function for our MySQL table.
How to backup the table "dr_addressbook"?
We dump the table within two steps.

Step 1: Create the table definition query:
Since MySQL version 3.26.26 we ca use the query
SHOW CREATE TABLE dr_addressbook;
to get the CREATE query string of a table. We use this feature to get the CREATE query of our adress table:

    ' Send SHOW CREATE String to get the table definition 
    strSql = "SHOW CREATE TABLE dr_addressbook;" 
    Set MyRSBackup = LibMyWitch2.Execute(strSql, lgAffectedRows) 
    If MyRSBackup.RecordCount > 0 Then 
        MyRSBackup.MoveFirst 
        ' Get the table definition 
        strData = MyRSBackup.ValueByCol(1) & ";" & vbLf 
    End If

Step 2: Create the INSERT queries of the dump.
Now we fetch all recordsets of the table ("SELECT * FROM dr_addressbook;") and append the data at the end of our dump string.

    ' Fetch complete table data 
    ' Comment: If the table is to big, you should split the backup 
    ' into several queries with *LIMIT  start, number records* 
    strSql = "SELECT * FROM dr_addressbook;" 
    Set MyRSBackup = LibMyWitch2.Execute(strSql, lgAffectedRows) 
    If MyRSBackup.RecordCount > 0 Then 
        MyRSBackup.MoveFirst 
        While Not MyRSBackup.EOF 
            ' Create Insert queries for dump file 
            strRecord = "INSERT INTO dr_addressbook VALUES (" 
            For i = 0 To MyRSBackup.Fields.Count - 1 
                strRecord = strRecord & "'" & LibMyWitch2.AddSlashes _ 
                            (MyRSBackup.ValueByCol(i)) & "'," 
            Next i 
            If Len(strRecord) > 0 Then strRecord = Left(strRecord, _ 
                                    Len(strRecord) - 1) & ");" & vbLf 
            strData = strData & strRecord 
            MyRSBackup.MoveNext 
        Wend 
    End If

Hint: If the table is too big, we will get a timeout of the PHP script on server side. In this case the recordsets should be loaded within a loop of several "SELECT * FROM dr_addressbook LIMIT X,100;" queries.

Now we have to save the content of the variable strData in a file:

    lFileNumber = FreeFile 
    Open App.Path & "\phonebook.sql" For Binary As lFileNumber 
    Put #lFileNumber, , strData 
    Close #lFileNumber

How to restore the table "dr_addressbook"?
The LibMyWitch control is able to send multiple SQL queries within one single string. The SQL queries have to be seperated by a colon and CRLF ";" & vbCRLF.
To restore the dump we only have to load the dump file into a string and send it to the server:

    ' Load dump fiule 
    strSql = FileContents(App.Path & "\phonebook.sql") 
    If Len(strSql) > 0 Then 
        ' To send mulitple queries to the server, the queries has to be 
        ' seperated by ';' + 'CRLF' 
        ' Comment: If the dumpfile is too big, you should split 
        ' the Dump File into several chunks and send each of them 
        ' as a single small query 
        LibMyWitch2.Execute strSql, lgAffectedRows 
        Call MsgBox("Records inserted: " & CStr(lgAffectedRows), _ 
            vbInformation + vbDefaultButton1, "PhoneBook") 
    End If

Hint: We have to take care that the string that we send to the server is not too big. Otherwise it could be that the PHP script on the server will stop by an timeout. In this case the dumpt string should be splitted into smaler chunks.

Shareware or not Shareware

The LibMyWitch control is distributed as shareware.
You can test the control without any registration.
There are several types of licensees which could be purchased. But there is also one type of license which is free of charge.

1.) Licensee, against payment, for an unlimited period:

• Developer license (or so-called "Unlimited" license) for redistribution of the software to third parties, but only within the licensee's software applications and only one developer (per license), and up to a maximum of 5.000 installations or 5.000 users in case of intranet and http based applications.
• Site license with the permission to use the software on several (or all) computers within the site of the company (location or subsidiary), but only for the mentioned postal address,
• Single license (or Workstation license) for installation and using the software on exactly one computer. The use over a network (LAN/WAN) for several users including http-based applications is explicitly excluded.

2.) Licensee,  free of charge for a limited period of three months:

• Private license for installation and using the software on exactly one computer for private use only. After three month this licensee can be renewed again for free.
  If you want to request a free key for a private project, visit the LibMyWitch website.