Tutorial: Simple api use
Author: Jörg Hundertmarck
Version: 2
Date: 21.12.2005
Unicode support (Coming soon)
CDB is a simple wrapper for different client interfaces of sql databases. It does not extend the sql language. The only difference is the definition syntax of bind variabes (chapter 6). This small tutorial should explain the basic concepts for using the programming interface of CDB.
The declarations of the CDB programming interface is defined in CDB.h. Your application should link to libCDB.so. Simple example for printing CDBs version number:
// test1.cpp
#include <iostream>
#include "CDB.h"
int main() {
// Check for correct library version.
CDB_DEFAULT_RUNTIME_VERSION_CHECK;
int ma, mi, re;
get_cdb_runtime_version(&ma, &mi, &re);
std::cout << "CDB-" << ma << "." << mi << "." << re << std::endl;
return 0;
}
I compile with:
# g++ -g -Wall test1.cpp -o test1 -I/usr/include/CDB -lCDB
My CDB libraray is configured with:
# ./configure -–prefix=/usr
you should modify your -I path to your include destination. The CDB_DEFAULT_RUNTIME_VERSION_CHECK definition compares the version of linked and runtime library version. It is not very important for running the application but it is usefull to be sure you are using the correct versions.
All low level operations to the driver are encapsulted into the CDB class. It loads, closes the driver and is the low level interface for every other CDB* class. The simpliest way to explain the use is a simple example:
// test2.cpp
#include <iostream>
#include "CDB.h"
int main() {
// Check for correct library version.
CDB_DEFAULT_RUNTIME_VERSION_CHECK;
CDB *db = new CDB();
if ( db->load("libdriver_pgsql.so") < 1 ) {
std::cout << "Driver initialization failed: ";
std::cout << db->error_msg() << std::endl;
return 1;
}
std::cout << "Driver initialized" << std::endl;
delete db;
return 0;
}
The connection to your database is handled with the CDBconnection class. It has a similar interface to the CDB class. Here is the next example:
// test3.cpp
#include <iostream>
#include "CDB.h"
int main() {
// Check for correct library version.
CDB_DEFAULT_RUNTIME_VERSION_CHECK;
CDB *db = new CDB();
if ( db->load("libdriver_pgsql.so") < 1 ) {
std::cout << "Driver initialization failed: ";
std::cout << db->error_msg() << std::endl;
return 1;
}
std::cout << "Driver initialized" << std::endl;
CDBconnection *conn = new CDBconnection(db);
if ( conn->Connect("user=user password=pw dbname=db") < 1 ) {
std::cout << "Connection initialization failed: ";
std::cout << conn->Error() << std::endl;
delete db;
return 1;
}
std::cout << "Connection established." << std::endl;
delete conn;
delete db;
return 0;
}
In this chapter we are talking about the class CDBquery. This class implements a basic execution and querying interface with:
Result set evalutation
Bindvariable interface (see chapter 6)
CDB differentiate between two statement types:
exec - The first is a statement without a result set (like insert ...).
open - Tthe second one returns a result set (like select ...).
A CDBquery::open("select ...“) call is used to open a result set. A CDBquery::exec("insert ...“) call is used to execute the query without opening a result set.
CDBquery class throws a CEXquery exception. All operations to the class need to be done within a try-catch-block. The CEXquery class contains a pointer to the CDBquery instance which throws the exception. This pointer can be used to obain
informations from the error origin.
// test4.cpp
#include <iostream>
#include "CDB.h"
int main() {
// Check for correct library version.
CDB_DEFAULT_RUNTIME_VERSION_CHECK;
CDB *db = new CDB();
if ( db->load("libdriver_pgsql.so") < 1 ) {
std::cout << "Driver initialization failed: ";
std::cout << db->error_msg() << std::endl;
return 1;
}
std::cout << "Driver initialized" << std::endl;
CDBconnection *conn = new CDBconnection(db);
if ( conn->Connect("user=user password=pw dbname=db") < 1 ) {
std::cout << "Connection initialization failed: ";
std::cout << conn->Error() << std::endl;
delete db;
return 1;
}
std::cout << "Connection established." << std::endl;
try {
CDBquery *query = new CDBquery(conn);
query->exec("insert into testtab1(field) values(0)");
query->close();
delete query;
}
catch(CEXquery &e) {
std::cerr << "Exception: " << e.caller()->sqlerr() << std::endl;
}
delete conn;
delete db;
return 0;
}
Before you compile and run this program, you need to change the values applied to the Connect() function to your requirements. If the connection is successfull established, the program should do the following output:
Driver initialized
Connection established.
Exception: FEHLER: Relation >>testtab1<< existiert nicht
This programm executes a statement without returning a result set. See next chapter to find out howto execute select-statements and access their result set.
5.4 Select-statements and result sets
A select-statement should return at least an empty result set. The result set may be very large in some cases. We need an effective way to manage and access the stored data. In CDB the contents of a result set is handled by the appropriate database client library. The driver performs calls to the client library which brings the data into a suitable structure for readonly access by the CDB core. The content in the result set is orgranized in rows and columns. The rows can be accessed via the index operator (CDBrow CDBquery::operator[](int)) . This operator returns a temporary instance of class CDBrow. This class handles the access to the contents of a row. Two possibilities exist to acces a column of a row. CDBrow defines two operators:
CDBfield CDBrow::operator[](int)
CDBfield CDBrow::operator[](const CDBstring &)
The CDBfield class is used to access the value in a column. It can convert the value into different datatypes via the following functions:
const char* CDBfield::string()
CDBstring CDBfield::ustr()
Tdate CDBfield::date()
Ttime CDBfield::time()
Tdatetime CDBfield::datetime()
int CDBfield::isnull()
int CDBfield::integer()
int64_t CDBfield::long_integer()
double CDBfield::floating()
The CDBfield::string() operator allocates memory for a copy of the string by it self. A second call to the same field allocates new memory. The memory of the content is deleted when the close() function of CDBquery is called. This function is deprecated and should not be used. It is only implemented for backwards compatibility. The Tdatetime which is originally a class and has the following interesting members:
int year
int month
int day
int hour
int minute
int second
int msecond;
Here is a code chip for querying and evaluating a select-statement:
...
try {
CDBquery *query = new CDBquery(conn);
query->open("select field from testtab1");
for(int i = 0; i < query->rows(); i++) {
std::cout << (*query)[i]["field“].ustr() << std::endl;
}
query->close();
}
...
Bindvariables are a very effective way to pass values to the database. In theory you include a substitute symbol in your sql command. After the execution/preparation a function is called to bind a value to this substitution symbol. CDB defines two kind of functions to bind data to the variables. The syntax of the substitution symbol should look like this:
:NAME<TYPE>
First character is a ':' followed by the name of the bindvariable. A bindvariable is indicated by a datatype. The datatype is set in '<' and '>' characters. The type may be one of the following:
„int“ (Datatype int)
„long“ (Datatype int64_t)
„char“ (With index operator [] for maximum amount of characters and datatype CDBstring)
„float“ (Datatype double)
„double“ (Datatype double)
„date“ (Datatype Tdate)
„time“ (Datatype Ttime)
„datetime (Datatype Tdatetime)
6.1 First way: Operator based binding
The class CDBquery defines some stream input operators (operator<<). This operator fills the bindvariables in a sequence.
We want to process the following sql statement:
insert into testtab(field1, field2, field3) values(:field1<int>, :field2<char[256]>, :field3<double>)
This insert statement includes three variables. The variables are defined as specified in chapter 6. Here comes a code chip for binding calls:
(*query) << 4711 << "Hello world!" << 47.11;
When all bindvariables are filled, the statement is executed. This operator solution is reuseable. If you have n bindvariables and your statement should be executed m times the operator<< can be called n*m times. Here is a code chip which inserts 3 new rows into testtab:
(*query) << 4711 << "Hello world!" << 47.11;
(*query) << 4712 << "Hello world the second!" << 47.12;
(*query) << 4713 << "Hello world the third!" << 47.13;
This is an effective way to insert a large amount of data with the same prepared statement.
6.2 Second way: Position or name based binding
In CDB there there is a second way to bind values to variables. A substitution symbold could be identified by the name or bei the position. The CDBquery functions named bind() can bind by position or by name, depending on the parameters. In this example we are using sql from chapter 6.1:
query->bind(0, 4711);
query->bind(1, "Hello world!");
query->bind(2, 47.11);
query->process();
// or
query->bind("field1", 4712);
query->bind("field2", "Hello world the second!");
query->bind("field3", 47.12);
query->process();
This interface is more clear but you need to write a lot of extra code than in the first way.
// test5.cpp
#include <iostream>
#include "CDB.h"
int main() {
// Check for correct library version.
CDB_DEFAULT_RUNTIME_VERSION_CHECK;
CDB *db = new CDB();
if ( db->load("libdriver_sqlite.so") < 1 ) {
std::cout << "Driver initialization failed: ";
std::cout << db->error_msg() << std::endl;
return 1;
}
std::cout << "Driver initialized" << std::endl;
CDBconnection *conn = new CDBconnection(db);
if ( conn->Connect("test5.db") < 1 ) {
std::cout << "Connection initialization failed: ";
std::cout << conn->Error() << std::endl;
delete db;
return 1;
}
std::cout << "Connection established." << std::endl;
conn->setopt(OPT_SQL_TRACE);
try {
CDBquery *query = new CDBquery(conn);
try {
query->exec("drop table testtab1");
}
catch(...) {}
query->exec("create table testtab1("
"testid int primary key, "
"field1 int, "
"field2 varchar(80) default '' NOT NULL,"
"field3 timestamp NULL)");
query->close();
int max = 25;
Mchar tmp[81];
query->exec("insert into testtab1(field1,field2,field3)"
" values(:field1<int>, "
":field2<char[80]>, "
":field3<datetime>)");
for(int i = 0; i < max; i++) {
query->bind("field1", i);
Msnprintf(tmp, Msizeof(tmp), "Record %i of %i", i + 1, max);
query->bind("field2", tmp);
query->bind("field3", Tdatetime::now());
query->process();
}
query->close();
delete query;
}
catch(CEXquery &e) {
std::cerr << "Exception: " << e.caller()->sqlerr() << std::endl;
}
delete conn;
delete db;
return 0;
}