by Harald Fernengel

In the first part of this article we look at Qt 3.1's new value binding syntax to simplify passing Unicode and binary data. In the second part we see how to ease database application deployment. The third part describes an alternative to ODBC DSNs.

• Value Binding
• Deploying Database Applications
• Using ODBC without a DSN

Standard SQL statements like DELETE, INSERT, UPDATE, SELECT, etc., work well for short pieces of Latin-1 text, but they have two major limitations:

• Most database servers require the use of database-specific macros to insert Unicode or binary data, because they normally only allow Latin-1 characters.
• Many servers impose a maximum length on queries which limits the amount of data that can be passed.

There is no standard approach to binding. Qt's solution is to support the two most commonly used methods, "positional binding" and "named binding", for all supported platforms and databases. Qt emulates the functionality where the underlying database does not provide it, to ensure that Qt database code is platform-independent.

Positional binding uses single question marks, "?", as placeholders, and values are assigned using their positional index. This method is easy to use, but if you insert a new placeholder, the indexes of any following placeholders are changed. Named binding gives each placeholder a unique name. This is often more convenient than positional binding, since adding new placeholders does not affect existing placeholders, and using names makes the code clearer. Here is an example that demonstrates positional binding:

query.prepare( "INSERT INTO atable (id, name) VALUES (?, ?)" ); 
query.bindValue( 0, 1001 );
query.bindValue( 1, "Harry" );
query.exec(); 

query.prepare( "INSERT INTO atable (id, name) VALUES (:id, :name)" ); 
query.bindValue( ":id", 1001 ); 
query.bindValue( ":name", "Harry" );
query.exec();

Each database management system uses its own proprietary protocol for the low-level communication between client and server. Qt database plugins depend on the databases client libraries to establish connections. Usually these libraries must be installed on each machine that runs the Qt application. This can cause problems if the client machines have incompatible database client libraries or do not have them at all. These problems can be avoided by statically linking the database client libraries with the Qt SQL plugins. This has the disadvantage of making the plugins larger, but the advantage that only the plugins need to be deployed.

Instead of passing the shared object via the -l (hyphen ell) parameter, it is possible to include the static library into the SQL plugin by adding the full path and the name of the .a file. For building PostgreSQL with GCC, you must replace "-L/path/to/psql" -lpq with "/path/to/psql/libpq.a" -lcrypt -lresolv -lnsl. For MySQL replace "-L/path/to/mysql" -lmysqlclient with "/path/to/mysql/libmysqlclient.a" -lz -lcrypt -lnsl -lm. Since the .a files are just a collection of object files, it is necessary to satisfy their external dependencies by hand. This is achieved by adding any additional system-wide libraries to the linker line yourself. To find out what the dependencies are, run the ldd command on the client library's shared object, for example ldd libpq.so.

Statically linking on Windows is easier. For MySQL, instead of linking to libmysql.lib, link to mysqlclient.lib; they are normally in the same directory. Use the MSVC dependency walker to make sure that there is no dependency on mysql.dll. A similar approach works for other client libraries.

In Qt 3.0, an ODBC connection required a DSN (Data Source Name) which had to have been set up by the ODBC Connection Manager. In Qt 3.1 this limitation is gone. Instead of passing a DSN name, you can pass the driver name and server name as the database name using the following syntax: DRIVER={driver};SERVER=server. The driver can be any valid ODBC driver, and the server can be any host. Note that some ODBC Connection Managers do not support this syntax.