SQL Driver Sample Programs

From CometWiki

Revision as of 20:14, 7 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 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:

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

The following programs should give you a basic understanding of how to use the SQL device to perform MySql operations from inside Internet Basic. Please feel free to add your own examples to this list if you feel they would be helpful for others.

Simple Read and Insert