GBase 8a MPP Cluster Development Interface: C-API

Cong Li - Aug 20 - - Dev Community

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");
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode
Image
Optimizing Web Performance: Best Practices and Techniques

webdev, javascript, programming, learning
Image
What Are the Key Differences Between SFDC Salesforce Certification and Other CRM Certifications?

salesforce, certification, project, management
Image
Mastering Dynamic Defense: My Experience with SafeLine WAF

cybersecurity, docker, opensource, devops
Image
Web Development Company

web, development
Image
Python Security Best Practices: Keep Your Code Safe
Image
Why custom software solutions are crucial for the healthcare industry?

healthcare, softwaresolution
Image
IT Consulting Services

itconsulting
Image
Laravel Redis Throttle Fine Tuning: Tutorial

webdev, laravel, redis, devops
Image
Placing EC2 Webserver Instances in a Private Subnet with Internet Access via NAT Gateway using Terraform

aws, terraform, awscommunitybuilder, upskilling
Image
Magento Development Company

magneto, development
Image
How to deploy layer python for aws lambda
Image
Understanding and Optimizing Graph Data Models in Neo4j
Image
Benefits of custom insurance automation software

insurance, automation, softwaredevelopment
Image
Master Monero #1 : Fundamentals

beginners, cryptocurrency, opensource, learning
Image
Uratech USA Inc.'s Heavy Duty CNC Tool Holder Cabinets:

productivity, tooling
Image
Best C Language Online Courses for beginners - NareshIT

programming, c, coding
Image
hindi Sex story
Image
Why is Generative AI necessary for improving ERP's efficiency?

gpt3, erp, softwaredevelopment, webdev
Image
How Much Is an E-ZPass Toll Violation?
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player