SQL Driver Sample Programs

From CometWiki

Latest revision as of 20:22, 10 December 2009

This page shows how to set up MySql and sample IB programs to give you a better idea how the SQL Driver works in Comet. Please refer to the SQL Driver page for a more thorough reference.

Installing MySql Community Server

In order to start using the SQL Driver in Comet, you must have access to a MySql database somewhere. It can be on your local machine, on the local network, or out on the internet somewhere. If you already have a MySql database server installed and configured, please skip forward to the Example Database and Tables section.

If you are setting up a MySql server, we recommend you download the free Community Server version of MySql 5.1. This is simply because we have been using mostly version 5.1 for testing, but the SQL driver should work with all 5.x and later versions of MySql server. Once you have downloaded the MSI installer, install it on your local machine (if you are simply testing), or on your server if you wish to host your MySql data elsewhere. We strongly recommend installation on your local workstation for testing purposes in order to cut out problems that might occur with network/internet protocol issues. Once the installation is complete, the wizard should automatically start the MySql configuration utility, which guides you through the rest of the set up process. If not, you can find it usually under Start->Programs->MySQL->MySQL Server X.X->MySQL Server Instance Config Wizard

Configuring MySql Community Server

If you are configuring MySql Community Server to serve data for anything more than testing, we recommend you refer to the MySql 5.1 Reference Manual, otherwise for simple testing purposes, the configuration wizard is fairly straightforward. In general, the defaults for all of the wizard options are acceptable, save a few points which we will outline here. Again, refer to the official documentation for more details on the available configuration options.

If you are running your database server on a networked machine, make sure you enable the TCP/IP networking option, as well as add the firewall exception to windows. We will not cover any details for setting up your MySql server to handle database requests on the network or internet. We recommend that you also install MySql as a Windows service, even if are only using it for testing. There are some options that are set and new configuration options become available when you select this, which just makes everything easier. You can always disable the service after the installation is complete. Also, we also recommend you check the option to include the MySql bin directory in windows PATH, which will allow you to use the mysql utilities in the command prompt window (makes it easier to import the MySql table data later). Lastly, make sure you enter a root password - leaving root passwords blank is bad form, even for testing.

Although it is also bad form to use the root account to access your MySql data, we are going to anyways to simply the configuration process. If you would like to add your own user, please refer to the Adding User Accounts section of the MySql documentation. Make sure you create the user and grant the user rights to access and update the tables specified in the following section. You will also have to change the user name used in the sample IB programs to connect to the MySql database.

Example Database and Tables

All of our sample programs and code are designed to work with our example database named sampledb. In order to set up this database on your MySql instance, you must download the table creation and data file here. Next, open up the mysql command-line client (usually found at Start->Programs->MySQL->MySQL Server X.X->MySQL Command Line Client), and create the sampledb database by typing:

mysql> CREATE DATABASE sampledb;

Next you have to import the tables and data from the DOS command prompt (cmd.exe). To import the file, under the command prompt type:

shell> mysql sampledb < path\to\mysqldumpfile.txt --user=root -p

Or, if you did not include the MySql bin directory in the windows PATH:

shell> path\to\mysql.exe sampledb < path\to\mysqldumpfile.txt --user=root -p

You will be prompted for the root password, which will allow you to create tables and insert the data from the file. Once the database has been imported, under the MySql command-line client, you should be able to access the database (using the "USE sampledb" command), and see that there are multiple tables (using "SHOW TABLES" command) with data in them (using "SELECT * FROM..." command). Once you have verified the tables and data exist, you can start using the example programs.

Example IB Programs

Please feel free to add your own examples to this list if you feel they would be helpful for others.

The following programs should give you a basic understanding of how to use the SQL device to perform MySql operations from inside Internet Basic. In all of our examples, we prompt the user for the root password for the MySql database:

print "Enter MySql password: " & input password$
!password$ = "rootpassword"

To bypass the password prompt, comment the first line and set password$ to your MySql root password. In the following programs, we assume password$ contains an unencrypted string with your root password when we connect to the MySql database.

Sample Program 1: Insert and Read

Source: Sql1.ibs

In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:

close(10) & open(10) "SQL", EXCP=Err

data$ = "connect sampledb localhost root " + strip( password$ )
result$ = Control( 10, data$ )

if( sub(result$,1,1) EQ "-" ) Goto ErrSQL

We use the password provided by the prompt, and print the result of the attempted connect. If the connection attempt was unsuccessful, we drop down to the error handling code, which prints out the last SQL device error and exits:

ErrSQL:
result$ = Control( 10, "GETLASTERROR" )
print "Last SQL Error: "; result$

On the other hand, if the connection is successful, we start utilizing the SQL device. First we insert a new row into the 'simple' table using the SQLEXECUTE control. Here we insert a constant string, a random number between 1 and 100, and the current date and time. Since we are executing a non-query SQL statement, we must use the SQLEXECUTE control, not the SQLQUERY control:

result$ = Control( 10, "SQLEXECUTE INSERT INTO simple SET string='sampleprog1', _
                        number=FLOOR(1 + (RAND() * 99)), timestamp=NOW();" )

After that we query the database and select the last 10 rows that were added to the table (selecting all columns, sorted in descending order of time the row was added, limited to 10 result rows). In this case, since we are querying the database with the SELECT statement and expecting a result set of rows, we need to use the SQLQUERY control, not SQLEXECUTE:

result$ = Control( 10, "SQLQUERY SELECT * FROM simple ORDER BY timestamp DESC LIMIT 10" )

Finally, we use the formatted Read() function on the SQL device lun to read in each row into our list of variables, then print the list. Once there are no more rows to read, we will get an exception, which breaks our loop:

SqlFmt: Format id$; string$; number$; timestamp$

!... code mentioned above ...

do
  Read(10, SqlFmt) EXCP=EOF
  print ">> "; id$; " | "; string$; " | "; number$; " | "; timestamp$; " <<"
Loop

EOF:

And the last thing to do is disconnect from the database and close the device:

result$ = Control( 10, "DISCONNECT" )
close( 10 )

And that's it! This sample program should serve as the prototype for any Internet Basic programs you want to write that interface with a MySql database.

Below is an example of the output you might receive after running the program a few times:

Begin sample program 1

Enter MySql password:  testingpassword
Connect: +OK Successfully connected to server and database.
Insert: +OK Statement successfully executed.
Select: +OK Query successfully executed.
>> 18 | sampleprog1 | 6 | 2009-12-08 13:34:09 <<
>> 17 | sampleprog1 | 41 | 2009-12-08 13:34:05 <<
>> 16 | sampleprog1 | 91 | 2009-12-08 13:34:03 <<
>> 15 | sampleprog1 | 1 | 2009-12-08 13:34:00 <<
>> 14 | sampleprog1 | 80 | 2009-12-08 13:33:29 <<
>> 13 | sampleprog1 | 52 | 2009-12-08 13:26:37 <<
>> 12 | sampleprog1 | 12 | 2009-12-08 13:26:20 <<
>> 11 | sampleprog1 | 41 | 2009-12-08 13:26:14 <<
>> 10 | sampleprog1 | 19 | 2009-12-08 13:26:10 <<
>> 9 | sampleprog1 | 92 | 2009-12-08 13:26:05 <<
Disconnect: +OK Disconnected from database and server.

Sample Program 2: Update and GETNEXTROW

Source: Sql2.ibs

This example program is structured similar to sample program 1, except for the internal SQL interface operations. This time, we will update the table with some new info and select the rows that have changed. All of the connect, disconnect, and error handling code has not changed. After connecting to the database, we start by updating the 'simple' table:

result$ = Control( 10, "SQLEXECUTE UPDATE simple SET string='sampleprog2' _
                        WHERE number > 50" )

Here we alter all rows that have their number column greater than 50 by setting the string column. Again, because this is a non-query SQL statement, we must use the SQLEXECUTE control. In order to find out how many rows were affected by this UPDATE command, we execute the GETROWSAFFECTED control:

result$ = Control( 10, "GETROWSAFFECTED" )
print "Number of rows updated: "; result$

The GETROWSAFFECTED control returns the number of rows that were affected by the last SQLEXECUTE control. Any rows that were altered by the SQL statement are counted. Next, we query the database to retrieve all of the rows that have the new string column:

result$ = Control( 10, "SQLQUERY SELECT * FROM simple WHERE string='sampleprog2' _
                        ORDER BY timestamp DESC LIMIT 10" )

We again sort the rows in descending timestamp order and limit the results to 10 rows. This time, instead of using the formatted Read() function to retrieve the result data, we use the GETNEXTROW control to retrieve each row, where each column is delimited by the character provided:

do
  result$ = Control( 10, "GETNEXTROW ," )
  if sub(result$,1,1) EQ "-" Goto EOF
  print ">> "; result$; " <<"
Loop

EOF:

Instead of reading the row into the variable list like in sample program 1, we read all of the columns into a single result string, delimited by commas (','). If the control results in an error string (which starts with the minus '-' character), then there are no more rows to read and we break out.

Sample Program 3: GETALLROWS

Source: Sql3.ibs

This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:

result$ = Control( 10, "SQLQUERY SELECT * FROM custtable" )

A simple query to get all the data from the customer table. Next we execute the control to get the entire result table back in one string:

result$ = Control( 10, "GETALLROWS @0D@ ," )

This control takes two character delimiters as arguments, one to separate rows and one for columns. For this example we are separating columns with commas like before, but we are putting 0x0D (a carriage return) between the rows. This way we can print the entire string to a file, and each row will be on its own line:

erase "sqldump.txt", dir="tmp" noexcp
create "sqldump.txt", dir="tmp" noexcp
close( 11 ) & open( 11 ) "sqldump.txt", dir="tmp"

printfile( 11 ) result$

And once the file is populated with our result rows, we can read the file back line by line to print them out. We only do this here to demonstrate that the results are there, normally you would want to do some non-trivial processing or report generating here.

file( 11) pos=bof
do
  input( 11 ) data$,excp=EOF
  print '>>>'; data$
loop
EOF:

Interactive SQL Interpreter

Source: Sqli.ibs

This program prompts you for MySql database connection info, then once connected allows you to execute Query and Non-Query SQL statements. Once connected, choose "Q" for query, or "N" for non-query, and enter the SQL statement you would like to execute. If you perform an SQLEXECUTE, the number of rows affected will be printed on the bottom. If you perform an SQLQUERY, the query results will be displayed in a pop-up window - hit any key to close the window and return to the main screen. From the prompt enter "E" to exit from the program.