[boost] Interest in an ODBC library?

From: Hajo Kirchhoff (mailinglists_at_[hidden])
Date: 2005-07-27 04:31:49

Hi all,

I'd like to gauge interest in an odbc wrapper library. I am currently
writing one and the most important difference to other libraries I've 
seen is the ease with which my library allows binding variables to odbc 
columns and parameters.

Given a table in the database named 'my_table' and a struct my_table,
this would be the shortest code to retrieve all rows of that table and
do something with them:

odbc::table t;
my_table data;
t.bind(data);	//!!! This does all the binding!
t.open();
while (t.fetch().ok()) {
}

Note that this is the full, actual working code, not pseudo code. I have
*not* omitted any function arguments. The only thing thats missing is 
setting the DSN and credentials for the default connection (see below). 
'bind' makes use of another library 'data adapters', which allow writing 
runtime generic methods. I hope to make a separate announcement shortly, 
once I get my sourceforge CVS connection going agan. In the meantime, 
have a look at http://tinyurl.com/8ctju to get an idea of it.

Other features:
+ ANSI SQL-92 "schema" support
+ uses DBMS strategy objects that handle DBMS specific functions such as
creating a database, creating users and groups etc...
+ unit tests currently run with MS-Jet, MS-SQL Server 2000, postgres
+ has a macro 'preprocessor' for SQL statements built in that hides some
of the differences between DBMS system. Example:
CREATE TABLE abc(id $$SERIAL primary key not null) will expand to INT
AUTOINCREMENT with MS-JET but to SERIAL on postgres. That way you can
have one script that works with different databases.
+ always tries to fail gracefully if a feature is not supported by a
specific DBMS

Any interest?

Best regards

Hajo

Two examples:

Example ONE:

Given this struct definition, which mirrors the table definition

struct my_table
{
   int m_id;
   char m_name[51];
   string m_long_textfield;
};
LWL_BEGIN_ADAPTER(my_table)
   PROP(m_id)
   PROP(m_name)
   PROP(m_long_textfield)
LWL_END_ADAPTER()

This is how you'd use the library

In your main(), set the login data

        // set the credentials for the default 'unnamed' connection
        // all table/statement etc... will use this connection unless
        // specified otherwise
odbc::connection::pool().set("MyDSN", "MyUserName", "MyPassword");

Anywhere use

        // instantiate my_table struct
my_table data;
        // table uses the unnamed default connection
odbc::table t;
        // bind does the magic. it automatically binds
        // all my_table member variables to the columns
t.bind(data);
        // open the table
t.open();
        // fetch the rows
while (t.fetch().ok()) {
   // do something
   cout << data.m_long_textfield;
}
        // check for errors
if (t.last_error().no_error()==false) {
   cerr << "ODBC error: " << t.last_error().as_string() << endl;
}

Example TWO:

        // create a statement with parameter marker
statement s("SELECT * FROM my_table WHERE id=?([in]p_id)");
        // bind the data to the columns
my_table data;
s.bind(data);
        // bind the WHERE parameter
long parameter;
s.bind(parameter, "p_id");

parameter=5;
if (s.execute().ok() && s.fetch().ok()) {
        // data contains the row WHERE id = 5
   cout << data.m_long_textfield;
} else
   cerr << s.last_error().as_string() << endl;
-- 
--------------------------------------------
Lit Window Library - Speed up GUI coding 10x
   http://www.litwindow.com/library?src=ml
wxVisualSetup - integrate wxWidgets into Visual Studio .NET
   http://www.litwindow.com/Products/products.html?src=ml
BugLister - Defect Tracker
   http://www.litwindow.com/buglister?src=ml
Tips & Tricks for wxWidgets & MS Visual Studio
   http://www.litwindow.com/Knowhow/knowhow.html?src=ml