Database access in ClanLib is basically handled through 4 classes:
To use ClanLib Database API with the ClanSqlite provider, you need to include these in your application:
Please note this overview will not learn you how SQL databases work in general, or how to write SQL statements. The purpose of this document is to describe how the ClanLib database API works. We assume you have general database knowledge before reading on.
To connect to a database, you use the clan::DBConnection class. You need to use a provider specific implementation of it, since it depends on the target database system how to connect to it. ClanLib provides one provider currently, the Sqlite provider. Others might be added later, for instance MySQL or MS SQL Server.
Since Sqlite is a file based database system, the connection only takes one parameter for connecting; a filename pointing to the database file.
If something went wrong during this connection, it will throw an exception explaining why.
To issue a SQL query to a connection, you use the clan::DBCommand class. It is created using create_command on the connection object, and it takes a query string and a type. Type can be a sql statement (default) or a stored procedure. Sqlite only supports sql statements, but future providers might support both.
A command can also set input parameters. If the command query string contains a ?x, it requires a corresponding parameter to be set.
A templated function is also provided to reduce verbosity:
Alternatively you may set the input parameters like so:
The clan::DBCommand class support strings, bools, int, doubles, DateTime and DataBuffer (binary) as parameters:
To execute a command, you call one of 4 execute methods on the connection object.
execute_non_query is used if you don't care about any returning values from the command.
execute_scalar_string is used if you have only one returning value. It returns the first column of the first row as a string.
execute_scalar_int is used if you have only one returning value. It returns the first column of the first row as a integer.
execute_reader is used if you have more than one returning value.
Preferred syntax:
You can retrieve values as strings, bools, integers, doubles, datetimes or binaries:
You can retrieve values as strings, bools, integers, doubles, datetimes or binaries:
You can retrieve values as strings, bools, integers, doubles, datetimes or binaries using a column name:
You can also query a reader about column count, column names, etc. Remember to close the reader when you are done with it.
A transaction is a "sequence of operations performed as a single logical unit of work". It is a tool to make sure the entire operation you want to perform on the database is either completely executed, or not executed at all.
You can either commit or rollback a transaction, depending on how the query statements were executed.
If the transaction object gets destroyed without committing, the default behavior is to rollback the transaction.
A database require a database schema which describes tables, relationships, keys etc. This can be created using a set of clan::DBCommand functions, usage of a gui toolkit that supports Sqlite, or use the sqlite command line tool sqlite3.exe.
An example for a schema that sqlite3.exe can execute:
See the SQL example in the ClanLib distribution for a complete example showing Create, Retrieve, Update and Delete operations.
The ClanLib database API is modelled after a provider pattern, making it is possible to add support for new database systems. If you want to look into this, you basically have to make provider-specific implementations of clan::DBConnection, clan::DBCommandProvider, clan::DBConnectionProvider, clan::DBReaderProvider and clan::DBTransactionProvider.
See the ClanSqlite implementation for an example of this.