"G" Moment: Efficient Connection to GBase 8s Database via ESQL/C on Linux

In Linux environments, connecting to GBase 8s databases using the C language via ESQL/C is a common development requirement. This article provides a detailed guide on how to connect to a GBase 8s database using ESQL/C on Linux, covering environment setup, code writing, compilation, and execution steps.

ESQL/C is a technology that allows embedding SQL statements into C programs. With ESQL/C, developers can directly manipulate databases within C programs, enabling data operations such as insert, delete, update, and query. This article demonstrates how to connect to a GBase 8s database using ESQL/C on Linux through a complete example.

Environment Preparation

Database Information

The following is the database server information used in the example:

Introduction to GBase 8s ESQL/C

GBase 8s ESQL/C is an SQL application programming interface (API) that enables embedding SQL statements in C programs. The ESQL/C preprocessor, esql, converts SQL statements into C source code and invokes the C compiler for compilation.

ESQL/C consists of the following components:

• GBase 8s ESQL/C library: Provides C functions for accessing the database server.

• ESQL/C header files: Provide data structures, constants, and macro definitions.

• esql command: Processes ESQL/C source code to create C source files and passes them to the C compiler.

• finderr utility: Used on UNIX systems to retrieve GBase 8s error messages.

• GLS locale and code-set conversion files: Provide information for specific language environments.

Installing and Configuring CSDK on Linux

(I) Installing CSDK

1. Create user group and user

• Create gbasedbt user group and user:

2. Extract the CSDK package

• Create a directory and extract the package:

3. Perform silent installation

• Use the following command for silent installation:

(II) Configuring CSDK

1. Set environment variables

• Log in as the gbasedbt user and set the following environment variables:

2. Modify the GBASEDBTSQLHOSTS configuration file

• Add the following content to the configuration file:

ESQL/C Code Example

The following is a complete ESQL/C sample code demonstrating how to connect to a GBase 8s database and perform basic operations.

Sample Code

#include
EXEC SQL define NAME_LEN 20;
EXEC SQL define ADDRESS_LEN 200;
main()
{
EXEC SQL BEGIN DECLARE SECTION;
int rnum;
int snum;
char sname[NAME_LEN + 1];
char saddress[ADDRESS_LEN + 1];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER ERROR STOP;
EXEC SQL connect to 'testzh';
// Drop table
EXEC SQL drop table if exists tab2;
printf("Drop table successfully.\n\n");
// Create table
EXEC SQL create table tab2 (num int, name char(20), address varchar2(200));
printf("Create table successfully.\n\n");
// Insert without using host variables
EXEC SQL insert into tab2 values (1, "Jack", "Beijing");
printf("Insert (without using host variables) successfully.\n\n");
// Insert using host variables
snum = 2;
sprintf(sname, "Mary");
sprintf(saddress, "Shanghai");
EXEC SQL insert into tab2 values (:snum, :sname, :saddress);
printf("Insert (with using host variables) successfully.\n\n");
// Use a static SQL statement to select data
EXEC SQL declare mycursor1 cursor for
select num, name, address from tab2;
EXEC SQL open mycursor1;
printf("The output of the first select statement:\n");
for (;;)
{
EXEC SQL fetch mycursor1 into :snum, :sname, :saddress;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
printf("%d %s %s\n", snum, sname, saddress);
}
if (strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\n", SQLSTATE);
EXEC SQL close mycursor1;
EXEC SQL free mycursor1;
// Use a dynamic SQL statement to select data
EXEC SQL prepare mystmt from "select num, name, address from tab2 where num = ?";
EXEC SQL declare mycursor2 cursor for mystmt;
rnum = 2;
EXEC SQL open mycursor2 using :rnum;
printf("\nThe output of the second select statement:\n");
for (;;)
{
EXEC SQL fetch mycursor2 into :snum, :sname, :saddress;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
printf("%d %s %s\n", snum, sname, saddress);
}
if (strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\n", SQLSTATE);
EXEC SQL close mycursor2;
EXEC SQL free mystmt;
EXEC SQL free mycursor2;
EXEC SQL disconnect current;
exit(0);
}

Compilation and Execution

1. Save the code as Demo.ec file.

2. Compile using the esql command:

esql Demo.ec

3. Compile the generated C file:

gcc -o Demo Demo.c -I${GBASEDBTDIR}/include -L${GBASEDBTDIR}/lib -lcli

4. Run the program:

./Demo

Program Output

After running the above program, you will see the following output:

Through this article, we believe you now understand how to connect to a GBase 8s database using ESQL/C on Linux.