pseudoMySQL

A class for REALbasic

©2004 by Floyd Resler

Do you have the Standard version of REALbasic yet need to make a mySQL database connection? Your only solution is to either purchase the Pro version of REALbasic for an additional $300 or, if you can find one, a mySQL plug-in. Well, not anymore!

Introducing pseudoMySQL,

pseudoMySQL doesn’t actually make a connection to a mySQL database itself. Instead, it communicates to a mySQL database through a PHP script. The class comes with complete instructions (including instructions on how to activate PHP on your own computer) and an example project.

Download – 44KB

Below are the instructions included with the class.

The pseudoMySQL class for REALbasic allows you to access a mySQL database without the need for the pro version of REALbasic or a mySQL plug-in. It accomplishes this by communicating with a PHP script.

Installation

To use pseudoMySQL, you will need to install two items.

1. Drag the pseudoMySQL class to your REALbasic project window.
2. Copy pseudoMySQL.php to a server that has Apache configured to run PHP scripts (see below).

Where to Put pseudoMySQL.php

pseudoMySQL.php must be placed on a server that has Apache configured to run PHP scripts. Since Mac OS X has Apache, you can place it your local system. However, a couple of modifications to the Apache configuration file need to be made. Follow these steps:

1. In the Finder, Select Go->Go to Folder and type /etc/httpd/ in the dialog box and press return.
2. You will need to open this file in BBEdit. Since you don’t own this file, BBEdit will allow you to authenticate and save it once you make the chances below. TextEdit will not.
3. Look for this line: #LoadModule php4_module libexec/httpd/libphp4.so.
4. Remove the pound sign (#).
5. Now look for the line: #AddModule mod_php4.c.
6. Remove the pound sign (#).
7. Save the file (you’ll be asked to enter your username and password).
8. Launch System Preferences and click on Sharing.
9. If Personal Web Sharing isn’t turned on, start it up. If it is turned on, stop it and then start it again.
10. Apache looks for its Web documents in /Library/Web Servere/Documents. You can copy pseudoMySQL.php there or in a sub-directory that resides in the above directory.

Remember, you can put pseudoMySQL.php on any server that as Apache confirgured to run PHP scripts.

Usage

pseudoMySQL’s super class is HTTPSocket. Therefore, you will need to:

Drag a TCP Socket control onto your window and make its Super pseudoMySQL.

You will need to set some properties before access the mySQL database. These are:

• httpHost – This is where pseudoMySQL.php resides. In this property, you must include the name of the script as well. If you have installed the script locally, you can use localhost as the address. For example, for a local install, it would be http://localhost/pseudoMySQL.php. For a remote host, it might be: http://maccrafteres.com/scripts/pseudoMySQL.php
• dbHost – This is the host where the database resides. This can be a domain name or an IP (don’t place http:// in front of it).
• username – This is the username you will be using to connect to the database.
• Password – This is the password you will be using to connect to the database.
• Database – This is the name of the database you will be accessing.

Once these properties are set, you are ready to go.

Selecting Data

One of the first things you’ll probably do is want to execute a SELECT query. This is handled with the mysql_select method. When the mysql_select method has finished, the results are placed into a records array (i.e. a record set) and the mysql_selected event occurs. To access each row in the record set, you will want to use the eof method and reference the currentRecord property.

Example:

You execute the following query:

dbase.mysql_selecte(“select * from customers where lastName=’Smith’”)

When finished, the mysql_selected event occurs. So, within that event, you could do this:

while not dbase.eof

ListBox1.AddRow dbase.currentRecord.value(“lastName”)+”, “+dbase.currentRecord.value(“firstName”)

wend

(Note there is no MoveNext method).

See the sample project for an expanded example.

Updating Data

There are two ways to update records in the database. You can do it through an UPDATE query or you can use the mysql_update method. When either one is finished, the mysql_updated event will occur.

If you wish to use the mysql_update method, you must pass to it the following parameters:

• table – This is the name of the table to update.
• fields – This is a dictionary containing the fields and values you wish to update.
• keyField – This is the field that will be checked for the update.
• keyValue – This is what value of the keyField must be in order for the update to occur.

Example:

dim fields as dictionary

fields=new dictionary

fields.value(“firstName”)=”John”

fields.value(“lastName”)=”Smith”

mysql_update(“customers”,fields,”customerID’,2)

Of course, you wouldn’t want to hard-code the values as in the example above.

Inserting Data

There are two ways to insert records into the database. You can do it through an INSERT query or you can use the mysql_insert method. When either one is finished, the mysql_inserted event will occur.

If you use the mysql_insert method, you must pass to it the following parameters:

• table – The name of the table.
• fields – This is a dictionary containing the fields and values you wish to insert.

Example:

dim fields as dictionary

fields=new dictionary

fields.value(“firstName”)=”John”

fields.value(“lastName”)=”Doe”

mysql_insert(“customers”,fields)

When the insert has finished, the mysql_inserted event will have the id of the row inserted (if the table has an auto-increment field) in the results parameter.

Properties

The properties used by this class are:

currentRecord as dictionary

currentRecord contains all the fields and values of the current record.

database as string

The name of the database to be accessed.

dbHost as string

The domain name or IP of the host where the mySQL database resides.

httpHost as string

The URL of where the pseudoMySQL.php script resides.

numRows as string

The number of rows returned from the previous select query.

pos as integer

This is where the current record is located in the records array.

pword as string

The password that is used to connect to the mySQL database.

records(0) as dictionary

An array of dictionaries that contain each row returned from the previous select query.

userename as string

The username that is used to connect to the mySQL database.

Events

Since pseudoMySQL’s super class is HTTPSocket, it is event driven. Most methods (see below) have a corresponding event. To make it easy to remember which events go with which methods, the events have the same name as the methods with “ed” on the end. For example, the mysql_select method’s event is mysql_selected.

mysql_connected

This event occurs after the mysql_connect method has finished.

mysql_deleted(result as string)

This event occurs after the mysql_delete method has finished. The result parameter will contain the number of rows deleted.

mysql_error(errorMessage as string)

If an error has occurred, errorMessage will contain the error.

mysql_executed(result as string)

This event occurs after the mysql_execute method has finished. The result parameter will contain any message returned from the sever.

mysql_inserted(result as string)

This event occurs after the mysql_insert method has finished. The result parameter will contain the id of the row just inserted if the table has an auto-increment field.

mysql_selected

This event occurs after the mysql_update method has finished.

mysql_updated(result as string)

This event occurs after the mysql_update method has finished. The result parameter will contain the number of rows affected.

Methods

pseudoMySQL has several methods you can use to access a mySQL database, These methods are:

addslashes(txt as string) as string

This method will add a backslash before each tick (‘), quote (“), and backslash.

dump(which as integer)

Dump will create a text representation of a row or rows returned from the last select query and put it on the clipboard. This is handy for debugging if you need to see the fields and values of the result of the query. You can control if you wish to see all records, the current record, or a specific record. The possible values you may pass are:

• -1 – All records. You may also use the all constant.
• 0 – Current record. You may also use the current constant.
• Any Positive Number – This will return the record that corresponds to that number.

eof() as Boolean

The eof method advances to the next row in the record set. If the last row has been reached, a true will be returned.

MoveFirst()

Moves the record set position pointer to the first record.

MoveLast()

Moves the record set position pointer to the last record.

mysql_connect

This method establishes a connection to the database. Once connected, the mysql_connected event will occur.

mysql_execute(query as string)

Executes a query. Once finished, the mysql_executed event will occur.

mysql_insert(table as string,fields as dictionary)

This method will build and perform an insert query. You pass it the table name and a dictionary containing the key/value pairs of the data you wish to insert. See Inserting Data for more information. When finished, the mysql_inserted event will occur.

mysql_select(query as string)

Executes a select query (see Selecting Data). Once finished, the mysql_selected event will occur.

mysql_update(table as string,fields as dictionary,keyField as string,keyValue as string)

See Updating Data for a complete discussion and example of this method. When finished, the mysql_updated event will occur.

Examples

See the example project for some examples on how to use this class.

Contact

I’ll be more than happy to answer any questions, receive any ideas, or hear about any problems you may have. Send an email to macmage@maccrafters.com.