C API

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’s 2 very important things to remember about bound parameters:

• 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.
• 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’s a good practice to have the names of bound parameters correspond to the columns they represent, but it’s not required.

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.) 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:

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:

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.