Using Mobile BASIC and PHP to access MySQL databases on your Mobile Phone
This article discusses how to access, create, update and delete data held on SQL databases using your Mobile Phone. This is achieved by two pieces of software, which are described in this article. The first is the SQLClient program written in Mobile BASIC for use on the mobile phone. The second piece of software is the SQLServer program written in PHP and installed on the server. This article uses PHP and MySQL because of their wide popularity however the same techniques could be used for other scripting / CGI languages and databases.
- The mobile phone must be Java Enabled with a copy of Mobile BASIC installed. A trial version of Mobile BASIC can be downloaded from http://www.mobilebasic.com/
- The database you need to access must reside on a server connected to the Internet.
- This server must have a running Web Server supporting PHP.
- You must have access to the server in order to install the server side PHP script.
Establishing Communication with your server
One of the first things to establish before writing any form of database access functionality is that you can establish a communications channel with your server, send it some data and receive a response back.
This is essential and gives you the confidence that everything is configured and working correctly.
Although it is possible for your Mobile BASIC programs to send and receive string, floating point and integer data to the server, it is best to limit yourself to text data. Where it is essential to send integer or floating point data it should be converted to a string using the STR$() function.
The reason for this is that it avoids having to decode the individual bytes making up the integer or float number. A floating point number would be additionally difficult since the environment which your Mobile BASIC runs within doesn’t support IEEE floating point numbers – consequently floating point numbers use a proprietary format.
The program presented below simply asks the mobile phone user to enter their name. Once this has been entered the program opens a channel to HelloServer.php at the URL specified in the OPEN statement. The channel must be opened as “OUTPUT” since we are outputting data.
The entered name is sent to the server using the PRINT statement in line 1020. The INPUT statement on line 1030 is used to obtain the response from the server. Line 1040 closes the channel and line 1050 prints out the response from the server.
Note: This program uses the HTTP protocol and is subject to the limitations imposed by that protocol. Chiefly, you must send all data to the server before you attempt to read a response back. Whilst you can perform OUTPUT 1, OUTPUT 2, INPUT 1, INPUT 2 you cannot perform OUTPUT 1, INPUT 1, OUTPUT 2, INPUT 2. In most cases this is easy to achieve once you are aware of the limitation, in the event that this cannot be done you must close the channel and reopen a new channel to the script.
Mobile BASIC sends data to Internet Servers as HTTP POST data. A PHP script can access this data using the variable $HTTP_RAW_POST_DATA. Unfortunately the data needs to be processed since Mobile BASIC sends text data in Utf8 Format, likewise it also expects text data to be returned in Utf8 format. Fortunately, Utf8 format is quite simple, consisting of a two byte (16 bit) byte count (MSB first) followed by the bytes making up the text string.
This PHP script should be installed on your web server at the same URL as specified in SQLClient.bas
SQL Client / Server Programs
We now go on to illustrate how to implement a simple client program in Mobile BASIC that allows you to enter SQL commands and view there result of those commands on a mobile phone.
Before we embark on writing the program we need to decide on a mutual protocol that both the client and the server understand. Fortunately this is relatively straightforward.
Firstly, in order for the server to be able to perform an operation it needs four items:- The database username and password, the database name and the SQL command to execute. These items are simply sent to the server as four text strings.
Secondly, the server needs someway of send the results back to the client. The contents of the response depend largely on the command that was issued. Our server will send two types of results back:- A status message, and row / column data returned by the database.
The first string read from the server indicates the type of the result. If it’s numerical then it contains the number of rows returned. If it is non-numeric then it is either the string “OK” or an error message generated by the SQL command.
Assuming that the result was numeric then this is the number of rows of data returned.
The next item in the stream is a string containing the number of columns, followed by that number of strings defining the column names.
There then follows a series of text strings – one for each column in every row. For example, if there are 5 rows and 4 columns then there will be 20 (5 x 4) strings to input. This is organized as the data for all columns in row 1, followed by all columns in row 2, etc.
As an example, let’s assume that the SQL command “SELECT Username, Email from UserTable” returned the following data: –
The source for SQLClient.bas is listed below. It is also available under the applications category at http://www.mobilebasic.com/ and can be downloaded directly into a registered version of Mobile BASIC.
Lines 1000-1280 Used to set up initial values for your searches. Due to security considerations you should set USERNAME$ and PASSWORD$ to blank in production programs. Depending on your application you may also want to set DATABASE$ to blank.
If you have a frequent SQL command that you frequently use then you may like to set it as the default. Lines 1300-1440 Allows the user to edit the values before sending the request to SQLServer.php.
This is implemented as a series of dialog boxes that show the current value. The password field uses EDITFORM’s password mode. Lines 2000-2090 Sends the username, password, database and SQL command to the server for processing. Lines 2100-2200 Receove the first string back from the server.
If you recall we said that the first value would either be a numerical value (indicating rows and column data follows) or it is a text string containing either “OK” or an error message generated by the SQL command. This is handled by setting an error handler using the TRAP statement and then attempting to convert the string into a number using the VAL() function.
If the string doesn’t contain a number then the error handler will be invoked resulting in the program resuming at line 9050. In addition, if the number of rows is 0 then we invoke the “No Data” handler at line 9080. Lines 2300-2430 Reads in the number of columns / fields and then proceeds to read the returned column / field names.
These are concatenated together into a comma-separated list stored in FIELDNAMES$ Lines 2500-2660 Reads in the data for the rows and columns.
This is done by defining an array, ROWDATA$, data contains an array element for each row. Note that arrays are indexed from 0 so data for the first row is stored in ROWDATA$(0). All data values for the same row are concatenated together into a comma-separated list. Lines 3000-3060 Simply presents the user with a message containing the column / field names.
Lines 3100-3190 Allow the user to move through the data one row at a time. Note the POP statement in line 3170 this is necessary if you terminate a FOR/NEXT loop abnormally.
If the POP is missing then you will eventually get a stack overflow if you break out of the loop enough times. Lines 9000 onwards Define various message-handling routines that are invoked by the program.