SQL Driver

From CometWiki

Contents 1 Introduction 2 Usage 3 Interface 3.1 Management Functions 3.2 Non-Query Sql Functions 3.3 Query Sql Functions 4 Implementation Details

Introduction

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

Usage

All of the Sql device functions (except for reading result table rows) are accessed through the IB Control statement. The interface is divided into three sections:

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