Bridging the Gap: Talking to MySQL From VB.NET Through PHP and XML

No matter what I did, the server would not let me in. I had tried connecting from my web site first, but that was a no go. So I decided to break out a VB.NET app to see if that would do the trick. Well, it does, but not the way I was hoping for. I have finally finished a workaround for this security nightmare that I have had for the past couple of weeks.

I had tried talking to the hosting company but kept getting very general, very basic answers that really did not have anything to do with what I was trying to accomplish. So after banging my head on the desk and scouring Google like a madman, I decided to take matters into my own hands and learn a few new tricks while I was at it.

First, I will explain the big picture and then break it down into the individual pieces.

The Big Picture

I’ve created a small front-end in VB.NET to display a DataGrid showing messages saved in the client’s MySQL database. I cannot connect through MyODBC thanks to the wonderfully secure hosting environment, so I came up with another way…XML generated by PHP.  Don’t ask me how I came up with this, it was a late night enlightenment.

In order to limit who or what could access this information, I created a key value to pass along with the rest of my post data. Once this data has been posted, I get back a string of XML with the data I need for the DataGrid. This is similar to the actions that occur when communicating with a Web Service.

Simple enough, right?

First, I’ll start with the front-end.

An Adventure in VB.NET

All it takes is a Form with a DataGrid placed on it, along with a couple of buttons to read new and old messages. Name the form frmMsg.vb and then name the three buttons btnNew, btnRead, btnClose and the DataGrid dgMsg

You will also need to create a new class named WebBridge.vb. Since it is the most important piece of the front-end, we will start here. Refer to the comments within the code for explanations.


'Import these namespaces to make our programming life a 
'little easier
Imports System
Imports System.Net
Imports System.Text
Imports System.IO
 
Public Class WebBridge
 Private _acc As String = "12345"
 'Our test key
 Private _url As String 
 'Target URL
 Private _pdata As Object
 'Post Data
 Private _rsp As String
 'Response from php page
 
 Public ReadOnly Property Account() 
 As String 'Key to access page
  Get 'ReadOnly ensures the value
   Return _acc 'cannot be changed
  End Get
 End Property
 
 Public Property URL() As String
 'Target URL
  Get
   Return _url
  End Get
  Set(ByVal Value As String)
   _url = Value 
  End Set
 End Property
 
 Public Property PostData() As Object
 'Posting Data
  Get
   'Add Account value to post data
   Return "acc=" & Account & "&" & _pdata
  End Get
  Set(ByVal Value As Object)
   _pdata = Value
  End Set
 End Property
 
 Public ReadOnly Property 
 XMLResponse() As String
 'php page Response
  Get
  'Again ReadOnly so 
   Return _rsp
   'it cannot be changed
  End Get
  'by outside influence.
 End Property
 
 Public Sub Connect()
 'Primary procedure
 
  'Create a new WebRequest object 
  'with the specified URL
  Dim rq As WebRequest = 
  WebRequest.Create(URL)
  'Set Method and Header information
  With rq
  .Method = "POST"
  .ContentType = 
  "application/x-www-form-urlencoded"
 End With
 
  'Send the data to the php page
  SendData(rq, PostData)
 
 'Assign the response from the 
 'php page to the variable for XMLResponse
  _rsp = GetXMLResponse(rq)
 End Sub
 Private Sub SendData(ByRef req As WebRequest, 
 ByVal data As String)
 
  'Store the post data as a byte array 
  'for passing to the page
  Dim b As Byte() = 
  Encoding.ASCII.GetBytes(data)
 
  'Alert the request of the length of 
  'the post contents
  Req.ContentLength = b.Length
 
  'Create a Stream to send the 
  'RequestStream to the php page
  Dim sr As Stream = req.GetRequestStream()
 
  'Send the data
  sr.Write(b, 0, b.length)
 
  'Close the stream
  sr.Close()
 End Sub
 Private Function GetXMLResponse(ByRef 
 req As WebRequest) As String
 
 'Create a WebResponse object to get 
 'response from php page
  Dim rs As WebResponse = req.GetResponse()
 
  'Create a stream to capture the response
  Dim sr As Stream = rs.GetResponseStream()
 
  'Create a StreamReader to convert 
  'the stream to text
  Dim xr As StreamReader = New StreamReader(sr)
 
  'Return the response as text
  Return xr.ReadToEnd()
 End Function
End Class



This will handle all of the communication with the target php page as you will see shortly. There are only a few procedures needed for frmMsg. These will establish a connection to a target php page through a WebBridge object and will then display the appropriate information in our DataGrid. Our DataGrid’s table will be structured like our XML output from the target php page. You will see the connection later in the PHP section of the tutorial.


Private Sub ShowMessages(ByVal 
View As Byte)
'Create a new WebBridge Object
Dim wbg As New WebBridge  
'Set up the WebBridge object’s
'values and call Connect()
 With wbg
  .URL=http://www.xyz.com/getmessagelist.php
  .PostData = "view=" & View
  .Connect()
  'Display the XML data in dgMsg
  CreateMessageList(.XMLResponse)
 End With
End Sub
Private Sub CreateMessageList(ByVal 
XMLString As String)
'Create a data set to hold the 
'XML data
 Dim ds As New DataSet
 'Create a StringReader to store the 
 'XML data
 Dim sr As New StringReader(XMLString)
 'Create a new table in our dataset 
 'called "Message". You will see why soon.
 ds.Tables.Add("Message")
 'Add columns to the table. Again, the 
 'names will be explained soon.
 With ds.Tables(0).Columns
  .Add("MsgID")
  .Add("From")
  .Add("Email")
  .Add("MsgDate")
  .Add("MsgTime")
 End With
 'Read in the XMLString to populate the table
 Ds.ReadXml(sr, XmlReadMode.IgnoreSchema)
 'Display the Data in the DataGrid
 dgMsg.SetDataBinding(ds,"Message")
End Sub
Private Sub btnNew_Click(ByVal sender 
As System.Object, _ ByVal e As 
System.EventArgs) Handles btnNew.Click
'Show New Messages
ShowMessages(0)
End Sub
Private Sub btnRead_Click(ByVal sender 
As System.Object, _ ByVal e As 
System.EventArgs) Handles btnRead.Click
 'Show Read Messages
 ShowMessages(1)
End Sub



PHP: Talking the Universal Language

 

Now that this is out of the way, we can create our php page to generate the XML. We will assume that data has been entered into the database through a different php page, so all we are concerned about is pulling the data that already exists. The steps we need to follow to generate the XML file are:

1) Check for an Access Key.
2) Validate the Access Key. In our case, we are checking for a value of "12345".
3) Start the XML document.
4) Connect to MySQL and select the proper database.
5) Query the table for the desired information.
6) Loop through the recordset and create XML containing the retrieved data.
7) Close the database connection
8) Close the XML document.


<?
 //getmessagelist.php
 
 // Make sure that there is an 
 //Access Key in the post data
 If($_POST['acc'] != '') 
 {
  If($_POST['acc'] == "12345") 
  // Check for the correct Key
   $auth = 1;
   // Key is correct, this is a 
   //valid request
  Else
   $auth = 0;
   //Invalid request
 }
 Else
  $auth = 0;
  //Invalid request
 
  //If the request is valid, 
  //produce an XML string 
  //containing the requested information
  If($auth == 1) {
 
  // Start the xml document
  echo "<?xml version=\"1.0\" encoding=\"UTF-8\"

n";
  echo "<Messages>n";
 
  //Connect to the database and select the table
  $db = mysql_connect("host","username","password");
  mysql_select_db("database");
 
  //Query the table for details about the message
  $res = mysql_query("SELECT `fname`, `lname`, `email`, `date_submitted`, `time_submitted`,`MsgID` FROM `tblMessages` WHERE `Status` = ".$_POST['view']." ORDER BY `date_submitted`, `time_submitted`");
 
  //If information exists, write it out as XML
  If($rs = mysql_fetch_row($res)) {
   do {
    echo "<Message>n";
    //This is the table name
    echo "<MsgID>".$rs[5]."</MsgID>n";
    //Field 1
    echo "<From>".$rs[0]." ".$rs[1]."</From>n";
    //Field 2
    echo "<Email>".$rs[2]."</Email>n";
    //Field 3
    echo "<MsgDate>".$rs[3]."</MsgDate>n";
    //Field 4
    echo "</MsgTime>".$rs[4]."</MsgTime>n";
    //Field 5
    echo "</Message>n";
    } while($rs = mysql_fetch_row($res)); 
   mysql_close(); 
  }
 
  Echo "</Messages>";
 }
? >

What's Said and Done in the End

Here is what our WebBridge object will see as a response:


<?xml version=”1.0” encoding=”UTF-8”

<Messages>
 <Message>
  <MsgID>1</MsgID>
  <From>Test Dummy</From>
  <Email>testdummy@td.com</Email>
  <MsgDate>1/14/2004</MsgDate>
  <MsgTime>1073437277</MsgTime>
 </Message>
</Messages>

A clean and valid xml string containing all of your requested data that can be used to easily fill a DataSet within VB.NET. Sweet and (kind of) simple.  Now run your project, click on either the “Read New” button or “Read Old” button to view the data from the database.

Your datagrid will show you something like this:

Now you can pat yourself on the back for peacefully bringing together 3 distinct technologies into one harmonic package.

Conclusion

If you need more information about any of the discussed technologies (VB.NET, PHP, MySQL, XML) be sure to visit these resources out on the web:

VB.NET - http://msdn.microsoft.com/vbasic
PHP - http://www.php.net
MySQL - http://www.mysql.com
XML - http://www.xml.com

So, if you are ever caught with an overly secure MySQL server environment and need to pull some data remotely, you can do it will a little bit of .NET magic.

This is meant as an example, and not as a full blown, secure method for database communications. This is also a good way to become familiar with some VB.NET techniques and to play around with various other technologies in a single project. I know I have learned quite a bit from trudging through this exercise myself and hopefully this will help others out there looking to do the same, while also catching the attention of Developer Shed, who will may just deem this worthy of display. [And in fact, we have! -Editor]

blog comments powered by Disqus