Cong Li

Posted on Aug 20

GBase 8a MPP Cluster Development Interface: C-API

#database

Today, we'll introduce the GBase 8a C API interface driver used to connect to the GBase database server. This section will demonstrate how to use the GBase 8a C API functions and provide examples of programming with the GBase C API.

1. GBase C API

1.1 Data Types

1.1.1 GBASE

Structure Description:

This structure represents a handle to the database connection. It is not recommended to copy the GBase structure as the result of such a copy may not be reliable.

1.1.2 GBASE_RES

Structure Description:

This structure is used to store the result set returned by SELECT, SHOW, DESCRIBE, EXPLAIN queries.

1.1.3 GBASE_ROW

Structure Description:

This structure is used to store one row of data. It is implemented as an array of counted byte strings. (If a field value may contain binary data, it cannot be treated as a null-terminated string because such values may contain null bytes.) It is retrieved using gbase_fetch_row().

1.1.4 GBASE_FIELD

Structure Description:

This structure is used to store field information (name, type, size). You can obtain a GBASE_FIELD structure for each field by repeatedly calling gbase_fetch_field().

Structure Members:

Name	Type	Description
`name`	`char*`	Field name as a null-terminated string. If an alias was specified for this field using an `AS` clause, the value of `name` is the alias.
`org_name`	`char*`	Original field name as a null-terminated string. Ignores the alias.
`table`	`char*`	Name of the table containing the field, if the field is not a computed field. For computed fields, `table` is an empty string. If an alias is specified for the table using an `AS` clause, the value of `table` is the alias.
`org_table`	`char*`	Original table name as a null-terminated string. Ignores the alias.
`db`	`char*`	Name of the database from which the field originates as a null-terminated string. For computed fields, `db` is an empty string.
`catalog`	`char*`	Catalog name, always "def".
`def`	`char*`	Default value of the field as a null-terminated string. It is set only when using `GBASE_list_fields()`.
`length`	`unsigned long`	Width of the field as specified in the table definition.
`max_length`	`unsigned long`	Maximum width of the field in the result set (length of the longest field value in the result set). It contains the maximum length if `gbase_store_result()` or `gbase_list_fields()` is used. If `gbase_use_result()` is used, this variable is 0.
`name_length`	`unsigned int`	Length of the name.
`org_name_length`	`unsigned int`	Length of `org_name`.
`table_length`	`unsigned int`	Length of the `table`.
`org_table_length`	`unsigned int`	Length of `org_table`.
`db_length`	`unsigned int`	Length of the `db`.
`catalog_length`	`unsigned int`	Length of the `catalog`.
`def_length`	`unsigned int`	Length of the `def`.
`flags`	`unsigned int`	Different "bit flags" used for the field.
`decimals`	`unsigned int`	Number of decimals for numeric fields.
`charsetnr`	`unsigned int`	Character set number used for the field.
`type`	`enum_field_types`	Type of the field. The type value can be one of the `GBASE_TYPE_` symbols listed below.

Flag Field Value Collection:

Flag Value	Flag Description
`NOT_NULL_FLAG`	Field cannot be NULL.
`PRI_KEY_FLAG`	Field is part of a primary key.
`UNIQUE_KEY_FLAG`	Field is part of a unique key.
`MULTIPLE_KEY_FLAG`	Field is part of a non-unique key.
`UNSIGNED_FLAG`	Field has the `UNSIGNED` attribute.
`ZEROFILL_FLAG`	Field has the `ZEROFILL` attribute.
`BINARY_FLAG`	Field has the `BINARY` attribute.
`AUTO_INCREMENT_FLAG`	Field has the `AUTO_INCREMENT` attribute.
`ENUM_FLAG`	Field is an `ENUM` (no longer in use).
`SET_FLAG`	Field is a `SET` (no longer in use).
`BLOB_FLAG`	Field is a `BLOB` or `TEXT` (no longer in use).
`TIMESTAMP_FLAG`	Field is a `TIMESTAMP` (no longer in use).

Typical Usage of Flag Values:

if (field->flags & NOT_NULL_FLAG)
    printf("Field can't be null\n");

Macros to Define the Boolean State of Flag Values:

Flag State	Description
`IS_NOT_NULL(flags)`	True if the field is defined as `NOT NULL`.
`IS_PRI_KEY(flags)`	True if the field is a primary key.
`IS_BLOB(flags)`	True if the field is a `BLOB` or `TEXT`. (No longer in use, replaced by testing `field->type`).

Type Field Value Collection:

Type Value	Type Description
`GBASE_TYPE_TINY`	`TINYINT` field.
`GBASE_TYPE_SHORT`	`SMALLINT` field.
`GBASE_TYPE_LONG`	`INTEGER` field.
`GBASE_TYPE_INT24`	`MEDIUMINT` field.
`GBASE_TYPE_LONGLONG`	`BIGINT` field.
`GBASE_TYPE_DECIMAL`	`DECIMAL` or `NUMERIC` field.
`GBASE_TYPE_NEWDECIMAL`	Precision `DECIMAL` or `NUMERIC`.
`GBASE_TYPE_FLOAT`	`FLOAT` field.
`GBASE_TYPE_DOUBLE`	`DOUBLE` or `REAL` field.
`GBASE_TYPE_BIT`	`BIT` field.
`GBASE_TYPE_TIMESTAMP`	`TIMESTAMP` field.
`GBASE_TYPE_DATE`	`DATE` field.
`GBASE_TYPE_TIME`	`TIME` field.
`GBASE_TYPE_DATETIME`	`DATETIME` field.
`GBASE_TYPE_YEAR`	`YEAR` field.
`GBASE_TYPE_STRING`	`CHAR` field.
`GBASE_TYPE_VAR_STRING`	`VARCHAR` field.
`GBASE_TYPE_BLOB`	`BLOB` or `TEXT` field (use `max_length` to determine maximum length).
`GBASE_TYPE_SET`	`SET` field.
`GBASE_TYPE_ENUM`	`ENUM` field.
`GBASE_TYPE_GEOMETRY`	Spatial field.
`GBASE_TYPE_NULL`	`NULL`-type field.
`GBASE_TYPE_CHAR`	No longer in use, replaced by `GBASE_TYPE_TINY`.

1.1.5 GBASE_FIELD_OFFSET

Structure Description:

This is a "type-safe" representation of the GBASE field list offset (used by gbase_field_seek()). The offset is the field number within the row, starting at 0.

1.1.6 gs_ulonglong

Structure Description:

This type is used for row counts and the return types of gbase_affected_rows() and gbase_num_rows(). The range provided by this type is 0-1.84e19. On some systems, printing values of type gs_ulonglong may not be possible. To print such values, cast them to an unsigned long integer type and use the %lu format specifier, like so: printf("Number of rows: %lu\n", (unsigned long) gbase_num_rows(result));

2 C API Prepared Statements

2.1 Data Types

2.1.1 `GBASE_STMT`

Description:

This structure represents a prepared statement. The statement is created by calling gbase_stmt_init(), which returns a statement handle, i.e., a pointer to GBASE_STMT. This handle is used in all subsequent functions related to the statement until it is closed using gbase_stmt_close().

The GBASE_STMT structure has no parameters accessible by the application. Additionally, copying the GBASE_STMT structure is not recommended, as such copies are not guaranteed to be useful. Multiple statement handles can be associated with a single connection, with the number of handles limited only by system resources.

2.1.2 `GBASE_BIND`

Description:

This structure is used for both input (data values sent to the server) and output (result values returned from the server) in statements. For input, it is used with gbase_stmt_bind_param() to bind parameter data values to buffers for use by gbase_stmt_execute(). For output, it is used with gbase_stmt_bind_result() to bind result buffers for use by gbase_stmt_fetch() to retrieve rows.

The GBASE_BIND structure contains the following members for use by the application. Each member is used for both input and output, but in some cases, they serve different purposes depending on the direction of data transmission.

Structure Members:

Name	Type	Description
`buffer_type`	`enum_field_types`	The type of the buffer.
`buffer`	`void *`	A pointer to the buffer.
`buffer_length`	`unsigned long`	The actual size of the buffer in bytes.
`length`	`unsigned long *`	A pointer to an `unsigned long` variable that indicates the actual number of bytes stored in `*buffer`.
`is_null`	`gs_bool *`	Points to a `gs_bool` variable. If the value is `NULL`, the variable is `true`; if non-NULL, the variable is `false`. `is_null` is a pointer to a boolean, not a boolean scalar, allowing usage as follows: 1. If the data value is always `NULL`, bind the column using `GBASE_TYPE_NULL`. 2. If the data value is always `NOT NULL`, set `is_null = (gs_bool*) 0`. 3. In all other cases, `is_null` should be set to the address of a `gs_bool` variable, and its value should be appropriately changed between executions to indicate whether the data value is `NULL` or `NOT NULL`.
`is_unsigned`	`gs_bool`	For unsigned types, set `is_unsigned` to `true`; for signed types, set it to `false`.
`error`	`gs_bool`	For output, this member is used to report data truncation errors. To enable truncation reporting, call `gbase_options()` with the `GBASE_REPORT_DATA_TRUNCATION` option. When enabled, `gbase_stmt_fetch()` returns `GBASE_DATA_TRUNCATED`, and for parameters that experience truncation, the error flag in the `GBASE_BIND` structure is set to `true`. Truncation indicates the loss of a sign or significant digits, or that a string is too long to fit within a column.

2.1.3 `GBASE_TIME`

Description:

This structure is used to send DATE, TIME, DATETIME, and TIMESTAMP data directly to the server or receive such data directly from the server.

Structure Members:

Name	Type	Description
`year`	`unsigned int`	Year
`month`	`unsigned int`	Month
`day`	`unsigned int`	Day
`hour`	`unsigned int`	Hour
`minute`	`unsigned int`	Minute
`second`	`unsigned int`	Second
`neg`	`unsigned int`	Boolean flag indicating if the time is negative

3 GBase C API Application Examples

3.1 Creating a Connection Using the GBase C API

GBASE* gbase = NULL;
/* Initialize GBASE structure */
if(!(gbase = gbase_init(0))) {
    fprintf(stderr, "Unable to initialize GBASE structure!\n");
    exit(1);
}
/* Database connection */
if(!gbase_real_connect(gbase, host, user, passwd, db, port, NULL, 0)) {
    fprintf(stderr, "\n%s\n", gbase_error(gbase));
    exit(1);
}
/* Release database connection handle */
gbase_close(gbase);

3.2 Connecting to a Cluster Using the GBase C API

GBASE* gbase = NULL;
CHAR* host = "192.168.5.64;192.168.5.67;"; // IP addresses of cluster nodes
/* Initialize GBASE structure */
if(!(gbase = gbase_init(0))) {
    fprintf(stderr, "Unable to initialize GBASE structure!\n");
    exit(1);
}
/* Database connection */
if(!gbase_real_connect(gbase, host, user, passwd, db, port, NULL, 0)) {
    fprintf(stderr, "\n%s\n", gbase_error(gbase));
    exit(1);
}
/* Release database connection handle */
gbase_close(gbase);

3.3 Executing a Stored Procedure Using the GBase C API

/* Execute stored procedure */
void demo_3(void) {
    GBASE* gbase = NULL;
    char* sql_drop = "DROP TABLE IF EXISTS g_demo3";
    char* sql = "CREATE TABLE g_demo3(a VARCHAR(10), b INT)";
    char* p_drop = "DROP PROCEDURE IF EXISTS demo_p";
    char* p = "CREATE PROCEDURE demo_p(IN a VARCHAR(10), IN b INT) BEGIN INSERT INTO g_demo3(a, b) VALUES (a, b); END;";
    char* call_p = "CALL demo_p('call_p', 1)";

    if(!(gbase = gbase_init(0))) {
        fprintf(stderr, "Unable to initialize GBASE structure!\n");
        exit(1);
    }
    if(!gbase_real_connect(gbase, host, user, passwd, db, port, NULL, 0)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }
    /* Create table in the database */
    if(gbase_query(gbase, sql_drop)) {
        fprintf(stderr, "%s", gbase_error(gbase));
        exit(1);
    }
    if(gbase_query(gbase, sql)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }
    if(gbase_query(gbase, p_drop)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }
    if(gbase_query(gbase, p)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }
    if(gbase_query(gbase, call_p)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }
    printf("PROCEDURE execution result\n");
    print_table(gbase, "g_demo3");
    gbase_close(gbase);
}

3.4 Using the GBase C API in a Multi-threaded Environment

#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include "gbase.h"
#include <pthread.h>

static pthread_mutex_t lock_thread_num;
static pthread_cond_t cond_thread_num;
static int thread_num;

void* th_new(void* arg) {
    GBASE* gbase;
    GBASE_RES* res;
    gbase_thread_init();
    gbase = gbase_init(0);

    if(!gbase_real_connect(gbase, "192.168.111.96", "root", "1111", "test", 5258, NULL, 0)) {
        fprintf(stderr, "%s\n", gbase_error(gbase));
        goto exit;
    }

exit:
    gbase_close(gbase);
    gbase_thread_end();

    pthread_mutex_lock(&lock_thread_num);
    thread_num--;
    pthread_cond_signal(&cond_thread_num);
    pthread_mutex_unlock(&lock_thread_num);
    pthread_exit(0);
    return 0;
}

int main() {
    pthread_t t;
    unsigned int i = 10;
    int error;
    pthread_attr_t thr_attr;

    if(gbase_library_init(0, NULL, NULL)) {
        exit(1);
    }

    thread_num = i;

    if ((error = pthread_cond_init(&cond_thread_num, NULL))) {
        exit(1);
    }

    pthread_mutex_init(&lock_thread_num, NULL);

    if ((error = pthread_attr_init(&thr_attr))) {
        exit(1);
    }

    if ((error = pthread_attr_setdetachstate(&thr_attr, PTHREAD_CREATE_DETACHED))) {
        exit(1);
    }

    while(i--) {
        pthread_create(&t, &thr_attr, th_new, NULL);
    }

    pthread_mutex_lock(&lock_thread_num);
    while(thread_num > 0) {
        if ((error = pthread_cond_wait(&cond_thread_num, &lock_thread_num))) {
            fprintf(stderr, "\nGot error: %d from pthread_cond_wait\n", error);
        }
    }

    pthread_mutex_unlock(&lock_thread_num);
    pthread_mutex_destroy(&lock_thread_num);

    gbase_library_end();
    return 0;
}

3.5 Fast Data Insertion Using Prepared Statements

#include <windows.h>
#include <time.h>
#include <stdio.h>
#include "gbase.h"

#define NUM_INSERT 1000

void main(void) {
    GBASE* gbase = NULL;
    GBASE_STMT* stmt;
    GBASE_BIND bind[2];
    const char* sql_drop_table = "DROP TABLE IF EXISTS g_demo2";
    const char* sql_create_table = "CREATE TABLE g_demo2(id INT, name VARCHAR(20))";
    const char* insert_mode = "INSERT INTO g_demo2(id, name) VALUES(?, ?)";

    int in_id[NUM_INSERT];
    char in_name[NUM_INSERT][20];
    unsigned int i = 0;

    char* host = "192.168.111.96";
    char* user = "gbase";
    char* passwd = "gbase20110531";
    char* db = "test";
    int port = 5258;

    /* Initialize GBASE structure */
    if(!(gbase = gbase_init(0))) {
        fprintf(stderr, "Unable to initialize GBASE structure!\n");
        exit(1);
    }

    /* Database connection */
    if(!gbase_real_connect(gbase, host, user, passwd, db, port, NULL, 0)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }

    gbase->reconnect = 1;

    /* Create table in the database */
    if(gbase_query(gbase, sql_drop_table)) {
        fprintf(stderr, "%s", gbase_error(gbase));
        exit(1);
    }

    if(gbase_query(gbase, sql_create_table)) {
        fprintf(stderr, "\n%s\n", gbase_error(gbase));
        exit(1);
    }

    gbase_autocommit(gbase, 0);

    /* Insert data into the table */
    for(i = 0; i < NUM_INSERT; i++) {
        in_id[i] = i;
        sprintf(in_name[i], "name_%d", i);
    }

    if(!(stmt = gbase_stmt_init(gbase))) {
        fprintf(stderr, "\n%s\n", gbase_stmt_error(stmt));
        exit(1);
    }

    if(gbase_stmt_prepare(stmt, insert_mode, strlen(insert_mode))) {
        fprintf(stderr, "\n%s\n", gbase_stmt_error(stmt));
        exit(1);
    }

    memset(bind, 0, sizeof(bind));
    bind[0].buffer_type = GBASE_TYPE_LONG;
    bind[1].buffer_type = GBASE_TYPE_STRING;

    printf("Starting to insert %d records\n", NUM_INSERT);
    for(i = 0; i < NUM_INSERT; i++) {
        bind[0].buffer = (void*)&in_id[i];
        bind[1].buffer = (void*)in_name[i];
        bind[1].buffer_length = strlen(in_name[i]);

        if(gbase_stmt_bind_param(stmt, bind)) {
            fprintf(stderr, "\n%s\n", gbase_stmt_error(stmt));
            exit(1);
        }

        if(gbase_stmt_execute(stmt)) {
            fprintf(stderr, "\n%s\n", gbase_stmt_error(stmt));
            exit(1);
        }
    }

    gbase_commit(gbase);

    /* Close the statement handle */
    if(gbase_stmt_close(stmt)) {
        fprintf(stderr, "%s\n", gbase_stmt_error(stmt));
        exit(1);
    }

    /* Release database connection handle */
    gbase_close(gbase);
}

3.6 Load Balancing with the GBase C API

When creating a connection to an 8a cluster using the GBase C API with load balancing, the client should at least pass the IP addresses of the 8a cluster gcluster nodes to the GBase C API. For example, host="192.168.1.1;192.168.1.2". If there is only one node, the client should pass it to the GBase C API like this: host="192.168.1.1;". The semicolon after the IP address in the string is mandatory.

When creating a connection to an 8a cluster using the GBase C API with load balancing, if the "GBASE_OPT_USE_SERVER_BALANCE" option is not set (the default value is 0), the GBase C API will perform load balancing between the IP addresses provided by the client. If the "GBASE_OPT_USE_SERVER_BALANCE" option is set to 1, the GBase C API will perform load balancing across all nodes in the 8a cluster. Below is a code example for using load balancing with the GBase C API.

#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include "gbase.h"
int test_banalce()
{
GBASE* gbase = NULL;
char* host = "192.168.1.1;";
char* user = "gbase";
char* pwd = "gbase20110531";
char* db = "test";
int  port = 5258;
int rc = 0;
int use_server_balance = 1;
gbase = gbase_init(NULL);
gbase_options(gbase,  GBASE_OPT_USE_SERVER_BALANCE, (void*)&use_server_balance);
if(!gbase_real_connect(gbase, host, user, pwd, db, port, NULL, 0))
{
fprintf(stderr, "%d\n%s\n",gbase_errno(gbase), gbase_error(gbase));
rc = 1;
}
else
{
printf("%s\n", gbase_get_host_info(gbase));
}    
gbase_close(gbase);
return rc;
}
int main()
{
int i = 0;
for(;i < 20; i++)
{
test_banalce();
usleep(2*1000);
}
return 0;
}

DEV Community

GBase 8a MPP Cluster Development Interface: C-API

1. GBase C API

1.1 Data Types

1.1.1 GBASE

1.1.2 GBASE_RES

1.1.3 GBASE_ROW

1.1.4 GBASE_FIELD

1.1.5 GBASE_FIELD_OFFSET

1.1.6 gs_ulonglong

2 C API Prepared Statements

2.1 Data Types

2.1.1 `GBASE_STMT`

2.1.2 `GBASE_BIND`

2.1.3 `GBASE_TIME`

3 GBase C API Application Examples

3.1 Creating a Connection Using the GBase C API

3.2 Connecting to a Cluster Using the GBase C API

3.3 Executing a Stored Procedure Using the GBase C API

3.4 Using the GBase C API in a Multi-threaded Environment

3.5 Fast Data Insertion Using Prepared Statements

3.6 Load Balancing with the GBase C API

Top comments (0)

Read next

How to connect and query multiple databases with a single REST API

The Medallion Architecture: Refining Data from Bronze to Gold 🏅

Designing robust and scalable relational databases: A series of best practices.

Which AWS Database Service is Best and Cheapest : A comparison guide

1. GBase C API

1.1 Data Types

1.1.1 GBASE

1.1.2 GBASE_RES

1.1.3 GBASE_ROW

1.1.4 GBASE_FIELD

1.1.5 GBASE_FIELD_OFFSET

1.1.6 gs_ulonglong

2 C API Prepared Statements

2.1 Data Types

2.1.1 GBASE_STMT

2.1.2 GBASE_BIND

2.1.3 GBASE_TIME

3 GBase C API Application Examples

3.1 Creating a Connection Using the GBase C API

3.2 Connecting to a Cluster Using the GBase C API

3.3 Executing a Stored Procedure Using the GBase C API

3.4 Using the GBase C API in a Multi-threaded Environment

3.5 Fast Data Insertion Using Prepared Statements

3.6 Load Balancing with the GBase C API

Read next

How to connect and query multiple databases with a single REST API

The Medallion Architecture: Refining Data from Bronze to Gold 🏅

Designing robust and scalable relational databases: A series of best practices.

Which AWS Database Service is Best and Cheapest : A comparison guide

2.1.1 `GBASE_STMT`

2.1.2 `GBASE_BIND`

2.1.3 `GBASE_TIME`