Comdb2 SQL API

The Comdb2 C API (lovingly nicknamed cdb2api) is part of the Comdb2 main repository. See Client setup for steps needed to set up client machines to talk to databases.
All functions and constants in this document are defined in cdb2api.h.
This API implements the protocol detailed in the section on Writing language bindings.
Unless otherwise noted in a 'Return Values' table, all routines return an integer return code.
Applications can call cdb2_errstr to obtain a human-readable string describing the error.

A word on thread safety

cdb2api calls are threadsafe in the sense that no user-supplied locking is necessary.
However, cdb2_hndl_tp objects are not protected from concurrent changes.
Multiple threads can use multiple cdb2_hndl_tp objects without interference, but two threads should not operate on the same cdb2_hndl_tp concurrently.

Connecting to databases

cdb2_open

int cdb2_open(cdb2_hndl_tp **hndl, const char *dbname, const char *type, int flag)

Description:

This routine allocates a cdb2 handle to be used by subsequent calls to cdb2_run_statement. Note that if this call fails you can use cdb2_errstr on the returned handle to find the reason for the failure before freeing the handle object with cdb2_close. cdb2_close should be called regardless of return code from cdb2_open.

Parameters:

cdb2_close

int cdb2_close(cdb2_hndl_tp *hndl);

Description:

This routine closes the cdb2 handle and releases all associated memory. Depending on your client setup making this call may donate the database connection to a system-wide connection pool. Only connections that don't have pending transactions or query results will be donated to the connection pool.

Parameters:

Running queries

cdb2_run_statement

int cdb2_run_statement(cdb2_hndl_tp *hndl, const char *sql);

Description:

Executes the sql query. The query stops at the first semicolon if present - this call cannot be used to execute multiple statements. Anything past the first semicolon is ignored. To run further statements using the same handle, user code MUST call cdb2_next_record until it returns CDB2_OK_DONE (or an error). Running another statement before all records are retrieved is not supported. If the application doesn't need the entire result set, it's still recommended that it reads all the rows - see notes for cdb2_close for rationale. It is not necessary to call cdb2_next_record for INSERT/UPDATE/DELETE statements.

The type of the resulting columns is determined by the database. For more control over return types, see cdb2_run_statement_typed

Parameters:

cdb2_run_statement_typed

int cdb2_run_statement_typed(cdb2_hndl_tp *hndl, const char *sql,int nparms, int *parms);

Description:

Executes the sql query. This is identical to cdb2_run_statement, and all the same notes apply, except that the database will coerce the types of the resulting columns to the types specified in the call. If the types aren't compatible, the database will return CDB2ERR_CONV_FAIL. The type constants accepted are the same constants that cdb2_column_type() can return.

Parameters:

Reading the result set

cdb2_next_record

int cdb2_next_record(cdb2_hndl_tp *hndl);

Description:

This routine retrieves one record from the set referred to by hndl. Each call to this routine retrieves the next record in the set. The data that is returned from this call must be extracted with the cdb2_column_value call.

Parameters:

Return Values:

cdb2_numcolumns

int cdb2_numcolumns(cdb2_hndl_tp *hndl);

Description:

This routine returns the number of columns referred to by the hndl. This will match the number of columns that were selected by your SELECT statement.

Parameters:

Return Values:

cdb2_column_name

const char * cdb2_column_name(cdb2_hndl_tp *hndl, int col);

Description:

This routine returns the name of a column for a given row referred to by the hndl. The name remains valid while the handle is open until or the next query is run.

Parameters:

Return Values:

cdb2_column_size

int cdb2_column_size(cdb2_hndl_tp *hndl, int col);

Description:

This routine returns the size of the data of a column for a given row referred to by the hndl.

Parameters:

Return Values:

cdb2_errstr

const char * cdb2_errstr(cdb2_hndl_tp *hndl);

Description:

This routine returns a printable string for more information about the last error encountered by hndl. The string is valid until the next API call.

Parameters:

Return Values:

cdb2_column_value

void * cdb2_column_value(cdb2_hndl_tp *hndl, int col);

Description:

This routine returns a pointer to the data of a column for a given row referred to by the hndl. The pointer is valid only until the next call to cdb2_next_record. This will return a NULL pointer if the column contains NULL.

A non-NULL pointer can be dereferenced to fetch the data after being cast to the correct type. The types are:

Parameters:

cdb2_column_type

int cdb2_column_type(cdb2_hndl_tp *hndl, int col);

Description:

This routine returns the datatype of a column for a given row referred to by the hndl.

Parameters:

Return Values:

cdb2_bind_param

int cdb2_bind_param(cdb2_hndl_tp *hndl, const char *name, int type, const void *varaddr, int length);

Description:

This routine is used to bind value pointers with the names in cdb2 handle. The name should be present in the query as replaceable parameters. For example:

char *sql = "INSERT INTO t1(a, b) values(@a, @b)"

char *a = "foo";
int64_t b = "bar";

/* return code checks omitted for brevity */
cdb2_bind_param(db, "a", CDB2_CSTRING, a, strlen(a));
cdb2_bind_param(db, "b", CDB2_INTEGER, &b, sizeof(int64_t));
cdb2_run_statement(db, sql);

There are two very important things to remember about bound parameters:

1. We're passing addresses of variables. The values are copied and sent to the database at cdb2_run_statement time - it's important that these values don't go out of scope between when the binding is established and when the values are fetched.
2. The bindings remain after the call is made. You can populate the addresses with new values and call cdb2_run_statement again without redoing the bindings. This is useful if you're running lots of identical statements with similar values in a loop. Bindings should be cleared with cdb2_clearbindings if you need to run a different statement.

It is good practice to have the names of bound parameters correspond to the columns they represent, but it is not required.

Parameters:

cdb2_bind_index

int cdb2_bind_index(cdb2_hndl_tp *hndl, int index, int type, const void *varaddr, int length);

Description:

This routine is used to bind value pointers to named or unnamed replaceable params in sql statement. The index starts from 1, and increases for every new parameter in the statement. This version of cdb2_bind_* is faster than cdb2_bind_param.

For example:

char *sql = "INSERT INTO t1(a, b) values(?, ?)”

char *a = "foo";
int64_t b = "bar";

cdb2_bind_index(db, 1, CDB2_CSTRING, a, strlen(a));
cdb2_bind_index((db, 2, CDB2_INTEGER, &b, sizeof(int64_t));
cdb2_run_statement(db, sql);

Parameters:

cdb2_bind_array

int cdb2_bind_array(cdb2_hndl_tp *hndl, const char *name, int type, const void *varaddr, unsigned int count, int typelen)

Description:

This routine is used to bind arrays in an sql statement, for use with sql statements with IN clause with many parameters example: SELECT * FROM t1 WHERE a IN CARRAY(@myarray) as a replacement for 'select * from t1 where a IN (1,2,3,4,...)`.

Usage example for an array with 10 elements:

char *sql = "SELECT * FROM t1 WHERE a IN CARRAY(@myarray)”
int arr[10] = {1,2,3,4...};

cdb2_bind_array(hndl, "myarray", CDB2_INTEGER, arr, 10, sizeof(int));
cdb2_run_statement(db, sql);

Notice that you can name parameter myarray any valid varable name you want.

Other usage of such binding can be an insert statement such as this: INSERT INTO t2 SELECT * FROM CARRAY(@myintarr).

Parameters:

cdb2_get_effects

int cdb2_get_effects(cdb2_hndl_tp *hndl, cdb2_effects_tp *effects);

Description:

This routine allows the caller to to get the number rows affected, selected, updated, deleted and inserted in the last query, (or until last query from the start of the transaction.). This routine initialize the data members of the cdb2_effects_tp structure (see below), the address of which is supplied as an argument. Number of rows affected is a sum of number of rows updated, deleted and inserted. These values only make sense after COMMIT, since the transaction may get replayed and the values for each individual statements may change. The replay can be turned off by running "SET VERIFYRETRY OFF", after which you can call cdb2_get_effects in the middle of a transaction. For rationale, see the Comdb2 transaction model section.

Parameters:

typedef struct cdb2_effects_type {
    int num_affected;
    int num_selected;
    int num_updated;
    int num_deleted;
    int num_inserted;
} cdb2_effects_tp;

cdb2_clearbindings

int cdb2_clearbindings(cdb2_hndl_tp *hndl);

Description:

This routine is to clear the bindings done to the handle. See cdb2_bind_param.

Parameters:

cdb2_use_hint

int cdb2_use_hint(cdb2_hndl_tp *hndl);

Description:

This routine enables a per-handle setting that makes the database cache statements, and allows the client code to only send a short unique id instead of the entire SQL string. This may give you a small performance boost for very long SQL strings.

Parameters:

cdb2_set_comdb2db_config

int cdb2_set_comdb2db_config(char *cfg_file);

Description:

This function sets location of a config file the API uses to discover databases. See the Client setup section for more details.

Parameters:

cdb2_set_comdb2db_info

int cdb2_set_comdb2db_info(char *cfg_info);

Description:

This functions is like cdb2_set_comdb2db_config, but passes the contents of the configuration instead of its location. This may be useful for programs that fetch database location information from other systems start databases dynamically.

Parameters:

cdb2_init_ssl

int cdb2_init_ssl(int init_libssl, int init_libcrypto)

Description:

The function initializes libssl and libcrypto.
Generally you don't need to call the function by yourself. However if libssl and libcrypto are initialized by your application, you need to call the function with (0, 0) to prevent libcdb2api from initializing these libraries again.

Parameters:

cdb2_is_ssl_encrypted

int cdb2_is_ssl_encrypted(cdb2_hndl_tp *hndl)

Description:

The function returns 1 if the handle is protected by SSL, 0 otherwise. This is useful only if the SSL mode is ALLOW.

Parameters:

cdb2_register_event

cdb2_event *cdb2_register_event(cdb2_hndl_tp *hndl, cdb2_event_type types,
                                cdb2_event_ctrl ctrls, cdb2_event_callback cb,
                                void *user_arg, int argc, ...);

Description:

The function registers an event with a callback cb. The function returns an opaque structure which can be used to unregister the event using cdb2_unregister_event().

If hndl is NULL, the event will be registered globally and thus will be inherited by all handles created afterwards. Otherwise, the event will be registered locally to the handle, thus will be visible to the handle only.

types specifies when the callback should be invoked. It can be set to a bitwise OR'd combination of the events in the table below.

ctrls specifies how a callback should be handled by the API. It can be set to 0, or a bitwise OR'd combination of the following values.

cb is the event callback. If user_arg is not NULL, the API will pass it to cb; Otherwise, the API will pass the handle-specific user argument, if any, to cb.

The prototype of the event callback is as below.

typedef void *(*cdb2_event_callback)(cdb2_hndl_tp *cb_hndl, void *user_arg, int argc, void **argv);

where cb_hndl is the handle upon which the event is fired.

Besides the user argument, one can request additional arguments by setting argc to the number of the arguments, followed by the argument types. The arguments will be passed to cb in argv. The table below lists the argument types.

Return Value:

The function returns an opaque structure which can be used to unregister the event using cdb2_unregister_event().

cdb2_unregister_event

int cdb2_unregister_event(cdb2_hndl_tp *hndl, cdb2_event *e);

Description:

The function unregisters and destroys an event. If hndl is NULL, the function unregisters the event from all handles. Any handle created afterwards will not inherit the event either. Otherwise, the function unregisters the event from the handle.

Return Value:

The function returns 0 on success, and returns EINVAL if the event could not be found.

Errors

Comdb2 Return Codes

These return codes can be found in cdb2api.h

cdb2api errors

Error handling.

An application that gets an error while in a transaction (after running "BEGIN", before any call to "COMMIT" or "ROLLBACK") should roll back the transaction (by calling "ROLLBACK"). Any error returned by "COMMIT" will roll back the transaction automatically. A handle that has finished reading a result set, and has no active transaction can be used to run another query/statement. It is an error to begin a statement before the entire result from the previous statement has been read.

"Logical" errors including CDB2ERR_DUPLICATE / CDB2ERR_FKEY_VIOLATION / CDB2ERR_NULL_CONSTRAINT / CDB2ERR_ACCESS / CDB2ERR_CONV_FAIL / CDB2ERR_TZNAME_FAIL / CDB2ERR_READONLY / CDB2ERR_PREPARE_ERROR / CDB2ERR_MALLOC are application errors (or expected by the application).
Any other error is a (usually transient) condition, and the database should log the error. See the section on examining logs for more information.

Debugging

An application can set the $CDB2_LOG_CALLS environment variable to have cdb2api log all calls to stderr.