SQL Driver

From CometWiki

(Difference between revisions)

Revision as of 17:49, 1 December 2009

Introduction

The Sql device allows you to connect to a MySQL database (remote or local) from within an IB application. It is designed to provide a simple way to access your mysql data for use within Comet. Once the Sql device is open, you can connect to a database and server, perform any Sql statement (queries or modifications), and read the results into a format or string. Please write any questions or comments on the discussion page - this is a work in progress and we'd really like to hear back from any developers that actively use MySql as to what you would like to see here!

Subscriber Product

The Sql device is available in Comet32 for all Comet Subscribers. It will work with c16 compiled programs as well as c32 programs. It will fail to open for those who are not a curent Comet Subscriber.

Usage

Basic Syntax

The Sql device is opened and accessed using a LUN like most devices. All of the Sql device functions are accessed through the IB Control statement, except for the read function (more on that later). For example, to connect to a database, you would execute the following code:

Open( SqlLun ) "SQL"
result$ = Control( SqlLun, "CONNECT sampledb localhost testuser testpassword sqlport" )
Close( SqlLun )

All of the functions in the interface are accessed in this way, with parameters separated by spaces. The return string from a Control statement like this will start either with a "+OK" or "-ERR" to signify whether the control was issued successfully, followed by a short statement with more details (similar to the TCP device). For controls that return data from the SQL server (e.g. getting the number of rows affected, or a result set from a SELECT), there is no "+OK" or "-ERR". Instead the data returned from the server is the only thing contained in the result string. This will be described in more detail below.

Connecting to a Database/Server

You must connect to an SQL database prior to issuing any SQL queries or statements. In order to connect, we must provide the SQL server address/port and database we want to connect to, as well as the username and password to use. The port for the SQL server is optional, and will default to the standard SQL server port (3306) if none is provided. Code to connect and disconnect from an SQL database would usually look like this:

Open( SqlLun ) "SQL"
result$ = Control( SqlLun, "CONNECT sampledb localhost testuser testpassword serverport" )
result$ = Control( SqlLun, "DISCONNECT" )
Close( SqlLun )

The disconnect control takes no arguments. It disconnects from the current server/database, and readies the SQL device for another connection. You must disconnect a connected SQL device before you try to connect to another database/server.

Issuing Statements

Read Function

In the case of reading result rows from a query, you could access the data via the control methods, or there is a simpler method of using the Read() function. If you have a format statement that corresponds to the fields in the result rows, you can execute a Read() on the Sql LUN with the format statement in order to populate the variables with the next row's field data. For instance:

DataFmt: format field1$;field2$;field3$;field4$

!...open and connect...
result$ = Control( SqlLun, "SQLQUERY SELECT id, name, city, state FROM customertable" )

Do
  Read( SqlLun, DataFmt ) Excp=EOF
  Print "id: "; field1$; ", name: "; field2$; ", city: "; field3$; ", state: "; field4$
loop

EOF:
! No more rows

The above code will iterate through all of your customers and print their id, name, city, and state.

ALLATONCE vs. ROWBYROW Query Result Type

The Sql driver provides two methods of returning result sets:

The ALLATONCE result method is the default and the most useful way to get query results back from the database. When the query is executed, the result table is returned from the server all at once, and the rows are read from a local buffer.

The ROWBYROW result method is specialized to deal with very large result tables. When the query is executed, each row is returned on demand from the database server. This is a much less efficient method, and due to the iterative nature of the results, supports less functionality than the ALLATONCE method. Again, this is really only useful for very large result sets - for instance if you are expecting a million records at 1k bytes per record (this would require almost 100GB of local buffer space using the ALLATONCE method).

Interface

The Sql control functions are broken into three categories: Management controls deal with the database connection and configuration, Non-Query Sql controls deal with non-query Sql statements, and Query Sql controls deal with all query-type Sql statements. The Non-Query Sql controls are designed to deal with executing all Sql statements that don't perform queries or return result sets. Sql commands like INSERT, CREATE, and UPDATE all perform some action in Sql, but return a simple result like the number of rows affected. Conversely, the Query Sql controls are designed to deal with executing all the Sql statements that perform queries and return result sets. Basically SELECTS are issued using this category, and controls are provided to get rows and results out of the returned table.

The following sections show the syntax for all of the Sql Control strings:

Management Controls

These allow the programmer to control the database connection as well as any configuration options available for the Sql device.

Connect <database> <server> <user> <password> <port>
- Connects to the database on the server with the parameters specified. If your database is on the local machine, just use localhost. The final three parameters are optional (port defaults to the normal Sql port - 3306, and the user/password default to blank). Although hopefully you don't have any users with blank passwords!
Disconnect
- Disconnects from the database and server.
SetResultMethod <ALLATONCE/ROWBYROW>
- Specifies whether the queries issued should return results using the ALLATONCE or ROWBYROW methods - more details above.
GetLastError
- Returns a string containing details on the last mysql++ error.

Non-Query Sql Controls

These functions are used to issue and report results for Sql commands that are not queries (i.e. INSERT, CREATE, UPDATE, etc.).

SqlExecute <sql_string>
- Issues a non-query Sql command to the database and server. SELECT should not be used with this control.
GetRowsAffected
- Returns the number of rows that were affected by the last SqlExecute control.

Query Sql Constrols

These functions are used to issue Sql queries and retrieve the resulting tables (i.e. SELECT statements). NOTE: result strings from Control statements are restricted to 8192 bytes! If your row or result table is longer than this, you will get a buffer overflow error.

SqlQuery <sql_string>
- Issues a query Sql command to the database and server. You should only use Sql commands that return result tables with this control.
GetNextRow <delimiter>
- Returns the next row as a string delimited by the parameter (single character only).
GetAllRows <row delimiter> <field delimiter>
- (Can only be used on ALLATONCE queries!) Returns all of the result rows as a string delimited by the row and field delimiters (single characters only).

Future Controls?

Some day I think that it would be nice to abstract out the Sql commands by providing functions like these:

QueryOn( "customer_id", "customer_name" )
LimitOn( "customer_state", "NY" )
LimitOn( "customer_since", "1999-12-31" )
SortBy( "customer_id" )

resultSet = SendStatement()

where we can build the Sql queries based on the tables and constraints provided.

Though once we start moving into JOINs and the like, this kind of interface seems hard to implement. My impression is that anybody doing any non-trivial SQL work inside IB will probably have to have an understanding of SQL syntax and implementation.

Implementation Details

The SQL driver utilizes the MySQL++ library to interface with the MySQL server. The library is compiled and linked into our own library, which provides the interface to the IB applications. Currently the MySQL++ library as well as our library is compiled and linked dynamically (.dll). This means that both dlls are required to run comet with Sql support.

The MySQL++ library and documentation can be found at the MySQL++ Website

@@ Line 18: / Line 18: @@
 </pre>
-All of the functions in the interface are accessed in this way, with parameters separated by spaces.  The return string from the Control statement like this will start either with a "+OK" or "-ERR" to signify whether the control was issued successfully, followed by a short statement with more details (similar to the TCP device).  For controls that return data from the SQL server (e.g. getting the number of rows affected, or a result set from a SELECT), there is no "+OK" or "-ERR".  Instead the data returned from the server is the only thing contained in the result string.  This will be described in more detail below.
+All of the functions in the interface are accessed in this way, with parameters separated by spaces.  The return string from a Control statement like this will start either with a "+OK" or "-ERR" to signify whether the control was issued successfully, followed by a short statement with more details (similar to the TCP device).  For controls that return data from the SQL server (e.g. getting the number of rows affected, or a result set from a SELECT), there is no "+OK" or "-ERR".  Instead the data returned from the server is the only thing contained in the result string.  This will be described in more detail below.
 ===Connecting to a Database/Server===