QDB Examples

Your QDB client application should perform these general steps:

1. Connect to a database by calling qdb_connect()
2. Query the database:
   1. Execute a statement on the database by calling qdb_statement().
   2. Get the results of the statement (if any) by calling qdb_getresult().
   3. Use the results by calling qdb_cell().
   4. Free the result by calling qdb_freeresult().
   5. Repeat the preceding steps to execute statements and use the results, as required.
3. Close the database connection with qdb_disconnect()

Connecting to the database

Connecting to the database requires that you know the name of the database you want to connect to, and you need a database handle that the qdb client library links against.

 qdb_hdl_t *dbhandle;  // The QDB database handle
 dbhandle = qdb_connect("/dev/qdb/customerdb", 0)
 if (dbhandle == NULL) {
    fprintf(stderr, "Connect failed: %d\n", errno);
 }

---

Two threads can share the same database connection, provided they coordinate between themselves. Alternatively, each thread can call qdb_connect() and have its own connection.

---

Executing a Statement

Executing statements against a QDB database requires that you know and follow the QDB-supported SQL syntax, as described in the QDB SQL reference chapter. You must, of course, connect to the database before attempting to execute statements against it. See “Connecting to the database” above.

One example is to run the following query:

 int rc;
 qdb_hdl_t    *dbhandle;
 rc = qdb_statement(dbhandle, "SELECT * FROM customers;");
 if (rc == -1) {
    char *errmsg;
    errmsg = qdb_geterrmsg(dbhandle);
    fprintf(stderr, "QDB Error: %s\n", errmsg);
 }

It is important to escape any strings that you pass in to qdb_statement(). For example, if you pass in the string:

 SELECT lastname FROM customerdb WHERE lastname='O'Neil';

you would get an error, because the string in the WHERE clause would be interpreted as just 'O', because the second single quotation mark signals the end of the string, and the remaining characters produce an error. To correctly run the query, escape the single quotation mark in the middle of the string, as follows:

 SELECT lastname FROM customerdb WHERE lastname='O''Neil';

The second single quotation mark (') is escaped by the first single quotation mark.

Getting the result of a query

Some queries give results, and others don't. For example, the data results for UPDATE, INSERT, or DELETE statements always contain 0 rows. When running a SELECT statement, there may or may not be rows that matched your query, so it is always a good idea to make sure that you have data by checking the return value of qdb_statement().

---

This does not mean that you can't call qdb_getresult() for statements with 0 rows in the data result. In fact, it may be the only way to retrieve the result. If the connection was opened with the QDB_CONN_STMT_ASYNC flag bit set, then qdb_statement() will return before the statement has been completed. With complex statements this may mean a delayed error.

---

To help you debug your application, you can use qdb_printmsg(stdout, result, QDB_FORMAT_SIMPLE) to print the fetched result to stdout() so that you can visualize your data.

Here's an example of getting the results of an operation:

 qdb_result_t *result;
 // requires a statement previously run
 result = qdb_getresult(dbhandle);

Memory for the results is allocated when the statement is run on the database, so you must free the result structure or you will have memory leaks. Do this by calling qdb_freeresult(), as shown in the example later in this chapter. Never call free() yourself.

Using a result

A result is a block of memory containing a description of each cell and the cell's data. There are functions that give you easy access to this data:

Disconnecting from the Server

To disconnect from the server when you no longer need to use it:

 qdb_disconnect(dbhandle);

Example

#include <unistd.h>
#include <stdlib.h>
#include <errno.h>
#include <stdio.h>
#include <string.h>

#include <qdb/qdb.h>

/**
 * This sample program connects to the database and does one INSERT and one
 * SELECT.
 *
 * The database name is assumed to be /dev/qdb/customerdb
 * with schema:
 *    CREATE TABLE customers(
 *       customerid INTEGER PRIMARY KEY AUTOINCREMENT,
 *       firstname  TEXT,
 *       lastname   TEXT
 *     );
 */
int main(int argc, char **argv) {
   int rc;
   qdb_hdl_t *hdl;
   qdb_result_t *res;
   char *errmsg;

   // Connect to the database
   hdl = qdb_connect("/dev/qdb/customerdb", 0);
   if (hdl == NULL){
      fprintf(stderr, "Error connecting to database: %s\n", strerror(errno));
      return EXIT_FAILURE;
   }

   // INSERT a row into the database.
   rc = qdb_statement(hdl,
      "INSERT INTO customers(firstname, lastname) VALUES('Dan', 'Cardamore');");
   if (rc == -1) {
      errmsg = qdb_geterrmsg(hdl);
      fprintf(stderr, "Error executing INSERT statement: %s\n", errmsg);
      return EXIT_FAILURE;
   }

   // SELECT one row from the database
   // This statement combines the first and last names together into their
   // full name.
   rc = qdb_statement(hdl,
         "SELECT firstname || ' ' || lastname AS fullname FROM customers
           LIMIT 1;");
   if (rc == -1) {
      errmsg = qdb_geterrmsg(hdl);
      fprintf(stderr, "Error executing SELECT statement: %s\n", errmsg);
      return EXIT_FAILURE;
   }
   res = qdb_getresult(hdl); // Get the result
   if (res == NULL) {
      fprintf(stderr, "Error getting result: %s\n", strerror(errno));
      return EXIT_FAILURE;
   }
   if (qdb_rows(res) == 1) {
      printf("Got a customer's full name: %s\n", (char *)qdb_cell(res, 0, 0));
   }
   else {
      printf("No customers in the database!\n");
   }
   // Free the result
   qdb_freeresult(res);

   // Disconnect from the sever
   qdb_disconnect(hdl);

   return EXIT_SUCCESS;
}