Jason Irwin dot Net

Creating a Simple REST API With VB, PHP and MySQL

May 24, 2009 Development

The internet is an interesting place.  A few weeks ago I had posted an article showing people how to make Crystal Reports play nice with SQL Server Compact Edition.  Since then, I’ve had a few people contact me with questions about how to use SQL Server CE as well as how to make Crystal Reports access other data sources.  One question seemed to come up more often than any other, though:  How do I get VB to access a MySQL Database hosted somewhere on the internet without granting a user direct access to that database?

It’s a pretty good question, and the solution is one that I’ve had to employ several times over the last few years.

Disclaimer: As with any other solution you would find on the internet, this is not a “one size fits all” answer.  The code that I am going to show has been overly simplified and should be used as a starting point to your solution.  That said, if you have any questions about the code, I’ll do my best to answer them.

How It Works

The idea behind this solution is rather simple.  Pass some information to a PHP file on a server using a WebRequest in VB, and build a DataTable dynamically using the XML data returned from the web.  There are no security checks built into any of this code, but that can be added later.

So, with all that stuff said, let’s take a look at the PHP code that we’ll be using.

[code lang="php"]

<?php$rptID = $_POST['reportid'];
$toDate = $_POST['todate'];//Connect to the database
$db = mysql_connect("localhost","your_username","your_password");
mysql_select_db("your_database");//Query the Database
$res = mysql_query("SELECT rpt.id, rpt.LastName, rpt.FirstName, rpt.Whatever WHERE id = " . $rptID );// Start the XML Document
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>";
echo "<Report>";

//If information exists, write it out as XML
If($rs = mysql_fetch_row($res)) {
do {
echo "<Detail>";
echo "<id>".$rs[0]."</Code>";
echo "<LastName>".$rs[1]."</LastName>";
echo "<FirstName>".$rs[2]."</FirstName>";
echo "<Whatever>".$rs[3]."</Whatever>";
echo "</Detail>";
} while($rs = mysql_fetch_row($res));
mysql_close();
}
echo "</Report>";
?>
[/code]

As you can see, this code is as plain as it gets.  We have two variables, a MySQL Query, and the result set is returned in XML format.  Of course you’ll probably want to change the MySQL query to something that’ll work on your own database….

The VB Code

The next thing we’ll look at is the code in VB.net.  The example here is using VS2005 with the .NET 2.0 Framework.  It’s nothing fancy, and it should be forward compatible right up to the 4.0 Framework due out with VS2010.

The first thing we’ll do is create a super simple class that will access the PHP file.

[code lang="vb"]

Imports System
Imports System.Net
Imports System.Text
Imports System.XmlPublic Class clsAPI#Region " Class Variables "
Private xURL As String ' URL to Call
Private xPostData As Object ' Post Data to send to API
Private xResp As String ' Response from PHP API
#End Region#Region " Properties "
Friend Property URL() As String
'Target URL
Get
Return xURL
End Get
Set(ByVal Value As String)
xURL = NoNull(Value)
End Set
End PropertyFriend Property PostData() As Object
Get
Return xPostData
End Get
Set(ByVal Value As Object)
xPostData = Value
End Set
End PropertyFriend ReadOnly Property XMLResponse() As String
Get
Return xResp
End Get
End Property
#End Region#Region " Main API Code "
Public Sub Connect()
'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
xResp = 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)
req.ContentLength = b.Length

Dim sr As Stream = req.GetRequestStream()
sr.Write(b, 0, b.Length)
sr.Close()
End Sub

Private Function GetXMLResponse(ByRef req As WebRequest) As String
Dim rs As WebResponse = req.GetResponse()
Dim sr As Stream = rs.GetResponseStream()

'Create StreamReader to Convert stream to text
Dim xr As StreamReader = New StreamReader(sr)

'Return the response as text
Return xr.ReadToEnd()
End Function
#End Region

End Class

[/code]

There’s not a whole heck of a lot to this class, so feel free to add all the properties, methods, and everything else that should be in here with a proper application.

The Other Functions…

From here, we need to add two simple functions to a form (or anywhere else) to access the class.

[code lang="vb"]

Friend Function doAPIQuery(ByVal tProcName As String, ByVal tPostQuery As String, ByVal ResultName As String) As DataTable
'Function Queries the Online Database and Returns a DataTable
Dim ds As New DataSet("ds")Try
Dim wbg As New clsAPI
With wbg
.PostData = tPostQuery
.URL = "http://www.whateveryoursiteis.com/" & tProcName
.Connect()'Construct the Columns from the XML Nodes
Dim dt As New DataTable
ds = CreateDataSetFromXML(.XMLResponse, ResultName)'Create a StringReader and Pass the XML Response through it
Dim sr As New StringReader(.XMLResponse)'Read in the XMLString to populate the table
ds.ReadXml(sr, XmlReadMode.Auto)
End WithCatch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "doAPIQuery Error")
End Try

'Return the DataTable
Return ds.Tables(0)
End Function

Private Function CreateDataSetFromXML(ByVal XMLData As String, ByVal ResultName As String) As DataSet
'Function Reads an XML Field and Constructs DataTable Columns based on the results
Dim xmlDoc As New XmlDocument
Dim xmlNodes As XmlNodeList
Dim xNode As XmlNode
Dim baseDataNodes As XmlNodeList
Dim ds As New DataSet("ds")

Try
xmlDoc.LoadXml(XMLData)
ds.Tables.Add(ResultName)

xmlNodes = xmlDoc.GetElementsByTagName(ResultName)
For Each xNode In xmlNodes
baseDataNodes = xNode.ChildNodes

'Create a Column for Each Node in the XML Result
For Each basedatanode As XmlNode In baseDataNodes
'If the Menu Item hasn't been added yet, do so now
If ds.Tables(0).Columns.IndexOf(NoNull(basedatanode.Name)) < 0 Then _
ds.Tables(0).Columns.Add(NoNull(basedatanode.Name), Type.GetType("System.String"))
Next
Next

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "CreateDataSetFromXML Error")
End Try

'Return the DataTable
Return ds
End Function

[/code]

And that’s that. To have data returned from the PHP file that we created earlier we would need only to create a DataTable and populate it like so:

[code lang="vb"]

Dim dt as new DataTable("Result")
dt = doAPIQuery("whatever.php", "reportid=99&todate=2009-05-24", "Result")

[/code]

The doAPIQuery function sends and receives data through the API class, and the CreateDataSetFromXML function reads that XML response and converts it into a DataTable that can be used anywhere in the application. Heck, the way this code is presented, there would be little problem adapting this if you wanted to create 100 different PHP files to do different functions and return different result sets.

Is this the best way to have a VB-based application communicate with an online MySQL database?  Like everything else in the world, it depends.  Some would argue that this method is the lazy man’s way out of doing the job right, while others would argue this method would be sufficient for small custom applications built for a small business.  At the end of the day, it’s up to you to decide what the best solution is while designing an application.

That said, if you believe you have a better way of getting VB and MySQL to communicate without granting direct access to the database server over the web, I’d love to hear it.

Tags: .NET, API, database, PHP, REST, VB