Database API 1 of 11
Connect to a CubeSQL server
int cubesql_connect (csqldb **db, const char *host, int port, const char *username, const char *password, int timeout, int encryption);
Parameters
db: opaque datatype to the connection (on output)
host: hostname c-string
port: connection port
username: username used in this connection
password: password used in this connection
timeout: timeout value (in seconds) for this connection
encryption: encryption used in this connection
Note:
port can be kDEFAULT_PORT
timeout can be kDEFAULT_TIMEOUT
encryption can be kAESNONE, kAES128, kAES192, kAES256
Example
csqldb *db = NULL;
int ret = 0;
ret = cubesql_connect(db, "localhost", kDEFAULT_PORT, "admin", "admin",
kDEFAULT_TIMEOUT, kAESNONE);
Return values:
Upon successful connection kNOERR is returned otherwise returned values are kERR, kPARAMETER_ERROR, kMEMORY_ERROR.
db is NOT NULL in case of kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 2 of 11
Connect to a CubeSQL server with SSL encryption
int cubesql_connect_ssl (csqldb **db, const char *host, int port, const char *username, const char *password, int timeout, char *ssl_certificate_path);
Parameters
db: opaque datatype to the connection (on output)
host: hostname c-string
port: connection port
username: username used in this connection
password: password used in this connection
timeout: timeout value (in seconds) for this connection
ssl_certificate_path: full path to certificate file
Note:
port can be kDEFAULT_PORT
timeout can be kDEFAULT_TIMEOUT
Example
csqldb *db = NULL;
int ret = 0;
ret = cubesql_connect_ssl(db, "localhost", kDEFAULT_PORT, "admin", "admin",
kDEFAULT_TIMEOUT, "/Users/marco/Desktop/SSL/localhost.pem");
Return values:
Upon successful connection kNOERR is returned otherwise returned values are kERR, kPARAMETER_ERROR, kMEMORY_ERROR.
db is NOT NULL in case of kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 3 of 11
Disconnect from a CubeSQL Server
void cubesql_disconnect (csqldb *db, int gracefully);
Parameters
db: a valid connection reference obtained by
cubesql_connect
gracefully: if kTRUE than a proper Close message is sent to the server,
otherwise (kFALSE) socket is closed without any interaction with the server
top
Database API 4 of 11
Execute a SQL statement on the server
int cubesql_execute (csqldb *db, const char *sql);
Parameters
db: a valid connection reference obtained by
cubesql_connect
sql: c-string with a valid sql statement (INSERT, UPDATE, REPLACE, DELETE,
NO SELECT statement must be used with this function)
Example
csqldb *db = NULL;
int ret = 0;
ret = cubesql_connect(db, "INSERT INTO foo (col1) VALUES ('bar');");
Return values:
Upon successful connection kNOERR is returned otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 5 of 11
Perform a SQL query on the server
csqlc * cubesql_select (csqldb *db, const char *sql, int server_side);
Parameters
db: a valid connection reference obtained by
cubesql_connect
sql: c-string with a valid SELECT sql statement
server_side: if kTRUE than a server side cursor is created,
otherwise (kFALSE) the entire cursor is transferred in client's private cache
Example
csqldb *db = NULL;
csqlc c = NULL;
c = cubesql_select(db, "SELECT * FROM foo;", kFALSE);
Return values:
Upon successful execution the opaque cursor pointer csqlc, otherwise NULL and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 6 of 11
Execute an INSERT or UPDATE operation using bindings
int cubesql_bind (csqldb *db, const char *sql, char **colvalue, int *colsize, int *coltype, int ncols);
Parameters
db: a valid connection reference obtained by
cubesql_connect
sql: c-string with a valid sql INSERT/UPDATE statement
colvalue: array of char* to raw data to insert/update
colsize: array of int that contains data size of each entry
coltype: array of int that contains data type of each entry
ncols: number of entries in the arrays
Note:
sql must have proper escaped sql parameters
coltype can be kBIND_INTEGER, kBIND_DOUBLE, kBIND_TEXT, kBIND_BLOB, kBIND_NULL
Example
int ret, len, ncols = 5
int coltype[5];
int colsize[5]:
char *colvalue[5];
char *sql, *p;
// set up column data for a simple string
colvalue[0] = "This is a simple TEXT message";
colsize[0] = strlen(colvalue[0]);
coltype[0] = kBIND_TEXT;
// set up column data for BLOB image
p = my_load_image("/user/marco/mypicture.jpg", &len);
colvalue[1] = p;
colsize[1] = len;
coltype[1] = kBIND_BLOB;
// set up column data for a double number
colvalue[2] = "3.1415";
colsize[2] = strlen(colvalue[2]);
coltype[2] = kBIND_DOUBLE;
// set up column data for an integer number
colvalue[3] = "534765";
colsize[3] = strlen(colvalue[3]);
coltype[3] = kBIND_INTEGER;
// set up column data for NULL
colvalue[4] = "";
colsize[4] = strlen(colvalue[4]);
coltype[4] = kBIND_NULL;
// build proper SQL statement with parameters
sql = "INSERT INTO myTable (comment, image, doublevalue, intvalue, nullvalue)
VALUES (?1, ?2, ?3, ?4, ?5);"
ret = cubesql_bind(db, sql, colvalue, colsize, coltype, ncols);
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 7 of 11
Commit current transaction
int cubesql_commit (csqldb *db);
Parameters
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 8 of 11
Rollback current transaction
int cubesql_rollback (csqldb *db);
Parameters
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
API 9 of 11
Send a PING command to the server (just to keep current connection alive, otherwise clients are disconnected after a certainly amount of inactivity)
int cubesql_ping (csqldb *db);
Parameters
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Database API 10 of 11
Retrieve latest error code from current db connection
int cubesql_errcode (csqldb *db);
Parameters
Return values:
Current error code (or kNOERR).
top
Database API 11 of 11
Retrieve latest error message from current db connection
char * cubesql_errmsg (csqldb *db);
Parameters
Return values:
Current error message (or empty string).
top
Cursor API 1 of 13
Return the number of rows in the result set
int cubesql_cursor_numrows (csqlc *c);
Parameters
Return values:
Number of rows in the result set or -1 if the cursor is server side
top
Cursor API 2 of 13
Return the number of columns in the result set
int cubesql_cursor_numcolumns (csqlc *c);
Parameters
Return values:
Number of columns in the result set
top
Cursor API 3 of 13
Return current row index inside the result set
int cubesql_cursor_currentrow (csqlc *c);
Parameters
Return values:
Row index inside the result set or -1 if the cursor is server side
top
Cursor API 4 of 13
Seek current row inside the result set
int cubesql_cursor_seek (csqlc *c, int index);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
index: index where to set current row
Note:
index can also be kCUBESQL_SEEKNEXT, kCUBESQL_SEEKFIRST,
kCUBESQL_SEEKPREV, kCUBESQL_SEEKLAST
in case of server side cursor only kCUBESQL_SEEKNEXT is allowed
Return values:
Upon successful execution kTRUE is returned, otherwise kFALSE
top
Cursor API 5 of 13
Check if EOF is reached inside the result set
int cubesql_cursor_iseof (csqlc *c);
Parameters
Return values:
kTRUE if EOF is reached otherwise kFALSE
top
Cursor API 6 of 13
Retrieve current column type
int cubesql_cursor_columntype (csqlc *c, int colindex);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
colindex: a valid column index (1 based)
Return values:
TYPE_None, TYPE_Integer, TYPE_Float, TYPE_Text, TYPE_Blob, TYPE_Boolean, TYPE_Date, TYPE_Time, TYPE_Timestamp, TYPE_Currency
top
Cursor API 7 of 13
Retrieve field value
char * cubesql_cursor_field (csqlc *c, int row, int column, int * len);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
row: a valid row index inside the result set (1 based)
column: a valid column index inside the result set (1 based)
len: on output size of the returned buffer
Note:
row can be:
kCUBESQL_CURROW to get values from the current row
kCUBESQL_ROWID to get current rowid (or 0 if it cannot be retrieved)
kCUBESQL_COLNAME to get current column name
kCUBESQL_COLTABLE to get column's table
Return values:
a pointer to the current field
top
Cursor API 8 of 13
Retrieve rowid for specified rowindex
int64 cubesql_cursor_rowid (csqlc *c, int rowindex);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
rowindex: a valid row index inside the result set (or kCUBESQL_CURROW)
Return values:
rowid for specified rowindex or 0 if rowid cannot be computer
top
Cursor API 9 of 13
Retrieve column int value
int cubesql_cursor_int (csqlc *c, int column, int default_value);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
column: a valid column index inside the result set (1 based)
default_value: value to return in case specified column cannot be retrieved
Return values:
column int value or default_value
top
Cursor API 10 of 13
Retrieve column double value
double cubesql_cursor_int (csqlc *c, int column, double default_value);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
column: a valid column index inside the result set (1 based)
default_value: value to return in case specified column cannot be retrieved
Return values:
column double value or default_value
top
Cursor API 11 of 13
Retrieve field pointer
char * cubesql_cursor_cstring (csqlc *c, int row, int column);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
row: a valid row index inside the result set (1 based)
column: a valid column index inside the result set (1 based)
Return values:
cstring for specified row and column or NULL if field cannot be retried. Please note that is your responsability to free the pointer returned by this function.
top
Cursor API 12 of 13
Retrieve field pointer storing its value inside a preallocated buffer
char * cubesql_cursor_cstring_static (csqlc *c, int row, int column, char *static_buffer, int bufferlen);
Parameters
c: a valid cursor opaque datatype obtained from
cubesql_select
row: a valid row index inside the result set (1 based)
column: a valid column index inside the result set (1 based)
static_buffer: static_buffer to use for storing value to return
bufferlen: length of the static_buffer
Return values:
a pointer to static_buffer with the stored value
top
Cursor API 13 of 13
Free memory allocated for the result set
void cubesql_cursor_free (csqlc *c);
Parameters
top
Prepared Statement API 1 of 11
Create a prepared sql statement
csqlvm * cubesql_vmprepare (csqldb *db, const char *sql);
Parameters
db: a valid connection reference obtained by
cubesql_connect
sql: c-string with a valid sql statement
Example
csqlvm *vm1 = NULL;
csqlvm *vm2 = NULL;
csqlc *c = NULL;
int ret;
// bind to an insert statement
vm1 = cubesql_vmprepare(db, "INSERT INTO myTable (col1, col2, col3, col4)
VALUES (?1, ?2, ?3, ?4);");
ret = cubesql_vmbind_int(vm1, 1, 33);
ret = cubesql_vmbind_double(vm1, 2, 3.1415);
ret = cubesql_vmbind_text(vm1, 3, "This is a test", -1);
ret = cubesql_vmbind_null(vm1, 4);
ret = cubesql_vmexecute(vm1);
ret = cubesql_vmclose(vm1);
// bind to a select query
vm2 = cubesql_vmprepare(db, "SELECT * FROM customers WHERE email=?1");
ret = cubesql_vmbind_text(vm2, 1, "marco@sqlabs.com", -1);
c = cubesql_vmselect(vm2);
ret = cubesql_vmclose(vm2);
Return values:
Upon successful compilation a valid opaque csqlvm datatype is returned otherwise NULL and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 2 of 11
Bind an Integer value to a prepared sql statement
int cubesql_vmbind_int (csqlvm *vm, int index, int value);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
value: value to be set
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 3 of 11
Bind a Double value to a prepared sql statement
int cubesql_vmbind_double (csqlvm *vm, int index, double value);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
value: value to be set
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 4 of 11
Bind an Text value to a prepared sql statement
int cubesql_vmbind_text (csqlvm *vm, int index, char * value, int len);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
value: value to be set
len: just pass -1 for c-strings
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 5 of 11
Bind a Blob value to a prepared sql statement
int cubesql_vmbind_blob (csqlvm *vm, int index, void * value, int len);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
value: value to be set
len: length of the blob value
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 6 of 11
Bind a NULL value to a prepared sql statement
int cubesql_vmbind_null (csqlvm *vm, int index);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 7 of 11
Bind an Int64 value to a prepared sql statement
int cubesql_vmbind_int64 (csqlvm *vm, int index, int64 value);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
value: value to be set
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 8 of 11
Bind a Zero Blob value to a prepared sql statement
int cubesql_vmbind_zeroblob (csqlvm *vm, int index, int len);
Parameters
vm: a valid prepared statement reference obtained by
cubesql_vmprepare
index: index of the SQL parameter to be set, the leftmost SQL parameter has
an index of 1.
len: length of the zero blob value to set
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 9 of 11
Execute a prepared SQL statement on the server
int cubesql_vmexecute (csqlvm *vm);
Parameters
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 10 of 11
Perform a prepared SQL query on the server
csqlc * cubesql_vmselect (csqlvm *vm);
Parameters
Return values:
Upon successful execution the opaque cursor pointer csqlc, otherwise NULL and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top
Prepared Statement API 11 of 11
Free memory allocated by a prepared sql statement
int cubesql_vmclose (csqlvm *vm);
Parameters
Return values:
Upon successful execution kNOERR is returned, otherwise kERR and the proper error can be retrieved with cubesql_errcode and cubesql_errmsg.
top