SQL Driver

From CometWiki

Revision as of 20:46, 19 November 2009

Introduction

The SQL driver allows you to connect to a MySQL database (remote or local) from within an IB application.

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.

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" )

while 1=1
  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.

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>
• Disconnect
• SetResultMethod <ALLATONCE/ROWBYROW>
• GetLastError

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.).

• Execute <sql_string>
• GetRowsAffected

Query Sql Constrols

These functions are used to issue Sql queries and retrieve the resulting tables (i.e. SELECT statements).

• Query <sql_string>
• GetNextRow <delimiter>
• GetAllRows <row delimiter> <field delimiter>

We're not sure how the final interface will be provided, but the following functionality should be addressed at minimum:

• Connect
  • Specify Database (Could be done under Connect()?)
  • Most likely we should be using the connection pool so that we function well under multiple threads... maybe we don't need this, as long as we stick to one connection per thread, and don't share MySQL++ structures between threads.
• Disconnect
• Build Statement
  • There is functionality provided that allows you to build generalized query statements using variables. This is useful if you're going to be sending the same query multiple times but with different specifications.
• Send Statement
• Get Result
  • Returns simple results for SQL statements like CREATE TABLE, ADD INDEX, etc.
• Get Result Set
  • Returns all data in a multi-dim vector. I.e. resultSet[4][5] would give you the 5th field from the 4th row.
• Get Large Result Set
  • Returns one row at a time from the server. This can be useful if you're returning many many records that would otherwise fill up memory and require disk space to cache. I.e. if you were returning a million records at 10k per record using the previous method you would need almost 10gigs of space.

This would provide all of the necessary functionality needed to access and update MySQL data, but requires a working (and probably thorough) knowledge of SQL. We could also provide functions that wrap SQL statements like the following:

• Query
• Update
• Insert

We could even add the major functions, although i think these are probably better performed at the server themselves, rather than through an IB program...

• Create Table
• Drop Table
• Modify Index

Ideally, i think a good example of the type of interface for those that don't know SQL syntax would be something like:

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

resultSet = SendStatement()

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 is compiled and linked dynamically (.dll), and our library is compiled and linked statically (.lib). This means that the MySQL++ .dll file must be included in the Comet release.

There are some legal issues with compiling and including the library statically under the GPL. My understanding is that any source code that links to the MySQL++ static library must be made available (tho not necessarily licensed under the GPL?). Thus if we write a static library that links to (and wraps up) the MySQL++ static library, then we can make the source for our library available, but still link to our library without being required to make the Comet source available. maybe someone else has some insight into this one...

There is a basic explanation of the legalities for using MySQL++ under the FAQ at MySQL++ Website