SQL Driver Sample Programs

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|Example Database and Tables]] section.

If you are setting up a MySql server, we recommend you download the free Community Server version of [http://dev.mysql.com/downloads/mysql/5.1.html#downloads 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 [http://dev.mysql.com/doc/refman/5.1/en/ 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 [http://dev.mysql.com/doc/refman/5.1/en/adding-users.html 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 [[Media:sampledbdump.txt|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:

<pre>
mysql> CREATE DATABASE sampledb;
</pre>

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:

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

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

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

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:

<pre>
print "Enter MySql password: " & input password$
!password$ = "rootpassword"
</pre>

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: Read and Insert===

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:

<pre>
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
</pre>

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:

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

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:

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

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:

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

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:

<pre>
SqlFmt: Format id$; string$; number$; timestamp$

!... code mentioned above ...

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

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

<pre>
result$ = Control( 10, "DISCONNECT" )
close( 10 )
</pre>