SQL Driver

From CometWiki

Revision as of 20:10, 19 November 2009

Introduction

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

Usage

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.

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.

Interface

Management Functions

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 Functions

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 Functions

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