Libzdb

Connection URL:

The URL given to a Connection Pool at creation time specify a database connection on the standard URL format. The format of the connection URL is defined as:

database://[user:password@][host][:port]/database[?name=value][&name=value]...

The property names user and password are always recognized and specify how to login to the database. Other properties depends on the database server in question. User name and password can alternatively be specified in the auth-part of the URL. If port number is omitted, the default port number for the database server is used. Reserved characters used in the Connection URL must be URL encoded.

MySQL:

Here is an example on how to connect to a MySQL database server:

mysql://localhost:3306/test?user=root&password=swordfish

In this case the username, root and password, swordfish are specified as properties to the URL. An alternative is to use the auth-part of the URL to specify authentication information:

mysql://root:swordfish@localhost:3306/test

See mysql options for all properties that can be set for a mysql connection URL.

SQLite:

For a SQLite database the connection URL should simply specify a database file, since a SQLite database is just a file in the filesystem. SQLite uses pragma commands for performance tuning and other special purpose database commands. Pragma syntax on the form, name=value can be added as properties to the URL and will be set when the Connection is created. In addition to pragmas, the following properties are supported:

• heap_limit=value [KB] - Make SQLite auto-release unused memory if memory usage goes above the specified value.

An URL for connecting to a SQLite database might look like:

sqlite:///var/sqlite/test.db?synchronous=normal&heap_limit=8000&foreign_keys=on

PostgreSQL:

The URL for connecting to a PostgreSQL database server might look like:

postgresql://localhost:5432/test?user=root&password=swordfish

As with the MySQL URL, the username and password are specified as properties to the URL. Likewise, the auth-part of the URL can be used instead to specify the username and the password:

postgresql://root:swordfish@localhost/test?use-ssl=true

In this example we have also omitted the port number to the server, in which case the default port number, 5432, for PostgreSQL is used. In addition we have added an extra parameter to the URL, so connection to the server is done over a secure SSL connection.

See postgresql options for all properties that can be set for a postgresql connection URL.

Oracle:

The URL for connecting to an Oracle database server might look like:

oracle://localhost:1521/servicename?user=scott&password=tiger

Instead of a database name, Oracle uses a service name. The information in the url above is typically specified in a tnsnames.ora configuration file, pointed to by the environment variable TNS_ADMIN. In the example below, instead of host, port and service name, we use a tnsname as defined in tnsnames.ora. We also use the auth-part of the URL to specify the username and the password. Finally, we specify that we want to connect to Oracle with the SYSDBA role.

oracle://sys:secret@/tnsname?sysdba=true

See oracle options for all properties that can be set for an oracle connection URL.

URL_T url = URL_new("mysql://localhost/test?user=root&password=swordfish");
ConnectionPool_T pool = ConnectionPool_new(url);
ConnectionPool_start(pool);

Connection_T con = ConnectionPool_getConnection(pool);
ResultSet_T result = Connection_executeQuery(con, 
                     "select id, name, image from employee where salary > %d", aNumber);
while (ResultSet_next(result)) 
{
     int id = ResultSet_getInt(result, 1);
     const char *name = ResultSet_getString(result, 2);
     int blobSize;
     const void *image = ResultSet_getBlob(result, 3, &blobSize);
     [..]
}
                

ResultSet_T r = Connection_executeQuery(con, "SELECT count(*) FROM users");
printf("Number of users: %s\n", ResultSet_next(r) ? ResultSet_getString(r, 1) : "no users");
                

Connection_execute(con, "INSERT INTO employee (name, hired) VALUES ('%s', %ld);", 
data.name, data.hired);
                

Connection_execute(con, "UPDATE employee SET name = '%s' WHERE id = %d", 
data.name, data.id);
                

Connection_execute(con, "DELETE FROM employee WHERE id = %d", data.id);
                

PreparedStatement_T p = Connection_prepareStatement(con, 
                        "INSERT INTO employee(name, picture) VALUES(?, ?)");
PreparedStatement_setString(p, 1, "Kamiya Kaoru");
PreparedStatement_setBlob(p, 2, jpeg, jpeg_size);
PreparedStatement_execute(p);
               

PreparedStatement_T p = Connection_prepareStatement(con, 
                        "SELECT id FROM employee WHERE name LIKE ?"); 
PreparedStatement_setString(p, 1, "%Kaoru%");
ResultSet_T r = PreparedStatement_executeQuery(p);
while (ResultSet_next(r))
       printf("employee.id = %d\n", ResultSet_getInt(r, 1));
               

#include <zdbpp.h>
using namespace zdb;
               

ConnectionPool pool("mysql://192.168.11.100:3306/test?user=root&password=dba");
pool.start();
Connection con = pool.getConnection();
// Use C++ variadic template feature to bind parameter 
ResultSet result = con.executeQuery(
     "select id, name, hired, image from employee where id < ? order by id", 100
);
// Optionally set row prefetch, default is 100
result.setFetchSize(10);
while (result.next()) {
     int id = result.getInt("id");
     const char *name = result.getString("name");
     time_t hired = result.getTimestamp("hired");
     auto [image, size] = result.getBlob("image");
     ...
}
               

Connection con = pool.getConnection();
// Any execute or executeQuery statement which takes parameters are 
// automatically translated into a prepared statement. Here we also 
// demonstrate how to set a SQL null value by using nullptr
con.execute("update employee set image = ? where id = ?", nullptr, 11);
                

ResultSet result = con.executeQuery("select name, image from employee");
while (result.next()) {
    if (result.isnull("image")) {
        ...
    }
}
               

Connection con = pool.getConnection();
PreparedStatement prep = con.prepareStatement(
     "insert into employee (name, hired, image) values(?, ?, ?)"
);
con.beginTransaction();
for (const auto &employee : employees) {
        // Polymorphic bind
        prep.bind(1, employee.name);
        prep.bind(2, employee.hired);
        prep.bind(3, employee.image);
        prep.execute();
}
con.commit();
               

try {
    con = pool.getConnection();
    con.executeQuery("invalid query");
} catch (sql_exception& e) {
    std::cout <<  e.what();
}
               

Libzdb is the perfect glue between your databases

You can use libzdb to instantiate as many ConnectionPool objects as needed and against as many different database systems as needed. This means libzdb is perfect for querying, copying and moving data between different database systems and databases.

Below is a somewhat contrived example where we copy data from a MySQL database to a PostgreSQL database. The main point, however, is to demonstrate how libzdb can serve as an efficient glue layer between your various databases.

// Start a ConnectionPool to the Apples Warehouse Database 
ConnectionPool apple_store("mysql://root:fruit@192.168.1.101:3306/apples");
apple_store.start();

// Start a ConnectionPool to the Oranges Warehouse Database                     
ConnectionPool orange_store("postgresql://root:ninja@192.168.1.210/oranges");
orange_store.start();

// Get a Connection to the Orange store
Connection orange_connection = orange_store.getConnection();

// Select Apples we want to copy to Oranges
ResultSet apples = apple_store.getConnection().executeQuery(
     "select name, color, weight from apples"
);

// Create a Prepared statements for storing Oranges
PreparedStatement orange = orange_connection.prepareStatement(
    "insert into oranges (name, color, weight) values(?, ?, ?)"
);

// Copy all Apples to Oranges under a transaction
orange_connection.beginTransaction();
while (apples.next()) {
        orange.bind(1, apples.getString("name"));
        orange.bind(2, apples.getString("color"));
        orange.bind(3, apples.getDouble("weight"));
        orange.execute();
}
orange_connection.commit();