v5.0

Embedded SQL Preprocessor ECPG

Availability

This feature is available since MogDB 5.0.0.

Introduction

Embedded SQL programs in C language are supported.

Benefits

Embedded SQL programs compiled for other SQL databases can be easily migrated to MogDB, ensuring quick service migration.

Description

An embedded SQL program consists of code written in an ordinary programming language, in this case C, mixed with SQL commands in specially marked sections. To build the program, the source code (.pgc) is first passed through the embedded SQL preprocessor, which converts it to an ordinary C program (.c), and afterwards it can be processed by a C compiler. Converted ECPG applications call functions in the libpq library through the embedded SQL library (ecpglib), and communicate with the MogDB server using the normal frontend-backend protocol. The embedded SQL program has an advantage over other methods in processing SQL commands for C code.

It handles the lengthy information transfer between variables in C programs.
The SQL code in the program is checked during compilation to ensure syntax correctness.
SQL embedded in C is specified in SQL standards and is supported by many other SQL database systems.

Enhancements

For compatibility with Oracle Pro*C, to smoothly use ECPG to replace Pro*C for implementing business logic, MogDB 5.0.8 has implemented the following features:

Support for EXEC SQL FOR FETCH to retrieve multiple rows of results into the SQLDA structure.
Support for EXEC SQL EXECUTE IMMEDIATE {:host_string}.
Support for dynamic SQL PREPARE host variables.
Declaration and sharing of host variables.
Array-style indicator variables.
Compatibility with SQLDA's DESCRIPTOR.
Compatibility with DESCRIBE SELECT LIST FOR and DESCRIBE BIND VARIABLES FOR.
Compatibility with Pro*C-style connection establishment methods.
Data type conversion and compatibility.
Handling of multi-row data with structure arrays.
Limiting the number of rows with the FOR clause.
Transaction processing syntax: commit, release, rollback, and release.
Support for anonymous block syntax.
Compatibility with EXECUTE IMMEDIATE string_literal.
Compatibility with PREPARE FROM [SelectStmt|UpdateStmt|InsertStmt|DeleteStmt|MergeStmt].

Constraints

Some OCI types have been implemented, but the MogOCI is immature and prohibited from use.
When binding these data types in sqlda, their type codes are consistent with ORACLE.
Consistent with PRO*C, users need to implement binding variables and handle output column types when querying in the pre-compiled code.
For EXECUTE IMMEDIATE, only hoststring is supported, not string_literal.
PREPARE FROM only supports host variables and does not support SELECT syntax.
When using SQLDA to receive data, variable-length strings can only be correctly obtained when a length limit is specified for the column, such as: char[10].

Dependencies

None.

Usage

Using the following commands to compile ECPG:

ecpg testecpg.ecpg -o testecpg.c
gcc -l$GAUSSHOME/include/postgresql/server/ -l$GAUSSHOME/include -L$GAUSSHOME/lib -lpq -lecpg -o testecpg testecpg.c

Example

#include <stdlib.h>
#include <stdio.h>

/* error handlers for the whole program */
EXEC SQL WHENEVER NOT FOUND DO BREAK;


int main(int argc, char **argv)
{
    EXEC SQL BEGIN DECLARE SECTION;
    int v_id, v_name_ind;
    char v_name[32];
    char *url="tcp:postgresql://127.0.0.1:5432/postgres";
    char *username="ecpg";
    char *password="Test@123";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL DECLARE c CURSOR FOR
        SELECT id, name
        FROM test_ecpg_tab
        ORDER BY 1;

    /* connect to the database */
    EXEC SQL CONNECT TO :url USER :username USING :password;

    /* open a cursor */
    EXEC SQL OPEN c;

    /* loop will be left if the cursor is done */
    for(;;)
    {
        /* get the next result row */
        EXEC SQL FETCH NEXT FROM c INTO :v_id, :v_name :v_name_ind;

        printf(
            "id = %d, name = %s\n",
            v_id,
            v_name_ind ? "(null)" : v_name
        );
    }

    EXEC SQL CLOSE c;
    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT;

    return 0;
}

// Basic operations

// Query
EXEC SQL SELECT ename,job,sal +2000 into :emp_name , :job_title,:salary from emp where empno = :emp_number;

// Insert
EXEC SQL INSERT INTO emp (empno,ename,sal,deptno) VALUES (:emp_number,:emp_name,:salary,:dept_number);
 
// Update
EXEC SQL UPDATE emp SET sal = :salary , comm = :commission WHERE empno =:emp_number;

// Delete
EXEC SQL DELETE FROM emo WHERE deptno = :dept_number;

//For dynamic SQL, one method is to use the SQLDA data structure to store data. The SQLDA structure is defined in the sqlda.h header file. Here we will introduce how to use SQLDA.
#include <sqlda.h>
SQLDA *bind_dp;
SQLDA *select_dp;

//The SQLDA structure can be initialized using the following method
bind_dp = SQLSQLDAAlloc(runtime_context,size,name_lenght,ind_name_length);

//In early versions of ORACLE, the sqlald() function was used to allocate a descriptor
EXEC SQL FETCH ... USING DESCRIPTOR ...
EXEC SQL OPEN ... USING DESCRIPTOR ...

The constructed SQLDA structure contains several members, and users need to understand the semantics of each member and construct and populate the SQLDA descriptor on their own. The details are as follows:

The N variable: The maximum number of select-list items or placeholders that can be Described.
The V variable: A pointer to the array of addresses of data buffers for storing the values of the select-list or bound variables.

Before using the select-list or bound variables, the corresponding space for V must be allocated, and declared.
The L variable: A pointer to the array of lengths of the data buffers for the select-list or bound variable values.
The T variable: A pointer to the array of data type codes for the data buffers of the select-list or bound variables.
The I variable: A pointer to the data buffer for the indicator variables.
The F variable: The number of select-list items or placeholders actually found by DESCRIBE.
The S variable: A pointer to the array of names of the data buffers for the select-list or placeholders.
The M variable: The length of the names of the select-list items or placeholders.
The C variable: An array of the current lengths of the names of the select-list items or placeholders.
The X variable: An array for storing the names of the indicator variables.
The Y variable: An array of the maximum lengths of the names of the indicator variables.
The Z variable: An array of the current lengths of the names of the indicator variables.

Users need to understand the implementation details of SQLDA mentioned above because they need to handle how to use SQLDA in their pre-compiled C code. The specific process is as follows:

Declare a host string in the Declare Section to save the query text.
Declare the SQLDA for select and bind.
Allocate storage space for the select and bind descriptors.
Set the maximum number of select lists and placeholders that can be Described.
Place the query text in the host string.
Prepare the query from the host string.
Declare a cursor for the query.
DESCRIBE the variables to be bound into the bind descriptor.
Reset the number of placeholders to the number actually found by DESCRIBE.
Get the values and allocate space for the bound variables found by DESCRIBE.
Use the bind descriptor to open the cursor.
DESCRIBE the select-list into the descriptor.
Reset the number of select-list items to the number actually found by DESCRIBE.
Reset the length and data type of each select-list column for display.
FETCH a row from the database into the allocated data buffer pointed to by the select descriptor.
Process the select-list values returned by FETCH.
Free the storage space used for select-list items, placeholders, indicator variables, and descriptors.
Close the cursor.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

// Define the maximum number of columns and bound variables
#define MAX_ITEMS 40
// Define the maximum length of column names
#define MAX_VNAME_LEN 30  
#define MAX_INAME_LEN 30

int alloc_descriptor(int size,int max_vname_len,int max_iname_len);   
void set_bind_v();    
void set_select_v();
void free_da();   
void sql_error(char *msg);  

EXEC SQL INCLUDE sqlca;   
EXEC SQL INCLUDE sqlda; 
EXEC SQL INCLUDE sqlcpr;

// Host variable definitions:
EXEC SQL BEGIN DECLARE SECTION;
float f1 = 12.34;  
VARCHAR f2[64];
char sql_statement[256]= "select * from test_ora";   
char type_statement[256]="select f1,f2 from test_ora where f1<? and f2=:f2";   
int i;   
EXEC SQL END DECLARE SECTION;  

SQLDA *bind_p;   
SQLDA *select_p;  

int main()    
{   
    //ECPGdebug(1, stderr);

    exec sql connect to REGRESSDB1;
    printf("\n [OK Connected!] \n\n");
    EXEC SQL WHENEVER SQLERROR DO sql_error("<ERROR>");

    // Allocate data for the sqlda type
    alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN);   

    // Table creation statement
    EXEC SQL DROP TABLE IF EXISTS TEST_ORA;
    EXEC SQL CREATE TABLE TEST_ORA(f1 float, f2 text);
    EXEC SQL INSERT INTO TEST_ORA VALUES(12.34,'abcd123');
    EXEC SQL INSERT INTO TEST_ORA VALUES(12,'e234d');
    EXEC SQL INSERT INTO TEST_ORA VALUES(12.34,'abcd123');
    EXEC SQL INSERT INTO TEST_ORA VALUES(333.33,'abcd');
    EXEC SQL commit;
    // Prepare statement
    EXEC SQL PREPARE S from :type_statement;   
    EXEC SQL DECLARE C1 CURSOR FOR S;    
    set_bind_v();  

    strcpy(f2.arr,"abcd123");
    f2.len = strlen(f2.arr);
    f2.arr[f2.len] = '\0';

    bind_p->L[0] = sizeof(float);
    bind_p->V[0] = (char*)malloc(bind_p->L[0]);
    memcpy(bind_p->V[0], &f1, sizeof(float));
    bind_p->T[0] = 4;   /* EXTERNAL_PROC_FLOAT */
    bind_p->L[1] = sizeof(char) * 64;
    bind_p->V[1] = (char*)malloc(bind_p->L[1] + 1);
    memcpy(bind_p->V[1], &f2, sizeof(char) * 64);
    bind_p->T[1] = 1;   /* EXTERNAL_PROC_VARCHAR2 */

    EXEC SQL OPEN C1 USING DESCRIPTOR bind_p;   
    EXEC SQL DESCRIBE SELECT LIST for S INTO select_p;

    set_select_v();
    printf("f1\t\tf2\n");   
    printf("----------------------------------------------------------\n");   
    for(;;)   
    {    
        EXEC SQL WHENEVER NOT FOUND DO break;   
        EXEC SQL FETCH C1 USING DESCRIPTOR select_p;  

        for(i = 0;i<select_p->F;i++){   
            printf("%s ",select_p->V[i]);   
        }   
        printf("\n");  
    }   
    free_da();   
    EXEC SQL CLOSE C1;   
    printf("\n-----------------------------------------------------\n");   
    alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN);   
    EXEC SQL PREPARE S from :sql_statement;   
    EXEC SQL DECLARE C CURSOR FOR S;    
    set_bind_v();  
    EXEC SQL OPEN C USING DESCRIPTOR bind_p;   
    EXEC SQL DESCRIBE SELECT LIST for S INTO select_p;  
    set_select_v();   
    EXEC SQL WHENEVER NOT FOUND DO break; 
    for (;;) {        
        EXEC SQL FETCH C USING DESCRIPTOR select_p; 
        for(i = 0;i<select_p->F;i++){
            printf("%s ",select_p->V[i]);   
        }
        printf("\n");
    }
    free_da();   
    EXEC SQL CLOSE C;          
    EXEC SQL DROP TABLE TEST_ORA;
    EXEC SQL COMMIT WORK RELEASE;   
    exit(0);   
}   
// Allocate descriptor space:
int alloc_descriptor(int size,int max_vname_len,int max_iname_len)   
{   
    if((bind_p=sqlald(size,max_vname_len,max_iname_len))==(SQLDA*)0)   
    {   
        printf("can't allocate memory for bind_p.");   
        return -1;   
    }   

    if((select_p=sqlald(size,max_vname_len,max_iname_len))==(SQLDA*)0)   
    {   
        printf("can't allocate memory for select_p.");   
        return -1;   
    }   

    return 0;   
}   
// Setting of binding variables:
void set_bind_v()   
{   
    unsigned int i;   
    EXEC SQL WHENEVER SQLERROR DO sql_error("<ERROR>");   
    bind_p ->N = MAX_ITEMS;   
    EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_p; 
 
    if(bind_p->F<0)   
    {   
        printf("Too Many bind varibles");   
        return;   
    }   
    bind_p->N = bind_p->F;   
    for(i=0;i<bind_p->N;i++)   
    {
        bind_p->T[i] = 1;   
    }   
}

// Processing of select columns
void set_select_v()   
{   
    unsigned int i;
    int null_ok,precision,scale;   
    EXEC SQL DESCRIBE SELECT LIST for S INTO select_p;  

    if(select_p->F<0)   
    {   
        printf("Too Many column varibles");   
        return;   
    }   
    select_p->N = select_p->F;   
    // Process the format
    for(i = 0;i<select_p->N;i++)   
    {   
        sqlnul((short unsigned int*)&(select_p->T[i]), (short unsigned int*)&(select_p->T[i]), &null_ok);// Check if the type is null
        switch (select_p->T[i])
        {   
            case 1://VARCHAR2   
                break;   
            case 2://NUMBER   
                sqlprc(&(select_p->L[i]), &precision, &scale);   
                if (precision == 0)   
                    precision = 40;   
                select_p->L[i] = precision + 2;  
                break;   
            case 8://LONG   
                    select_p->L[i] = 240;   
                    break;   
            case 11://ROWID   
                    select_p->L[i] = 18;   
                    break;   
            case 12://DATE   
                    select_p->L[i] = 9;   
                    break;   
            case 23://RAW   
                    break;   
            case 24://LONGRAW   
                    select_p->L[i] = 240;   
                break;   
        }   
        select_p->V[i] = (char *)realloc(select_p->V[i], select_p->L[i]+1);   
        select_p->V[i][select_p->L[i]] ='\0';// Add terminator
        select_p->T[i] = 1;// Convert all types to character type
    }   
} 
// Function to free SQLDA memory:
void free_da()   
{    
    sqlclu(bind_p);   
    sqlclu(select_p);   
}   

// Error handling
void sql_error(char *msg)   
{   
    printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc);   
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK RELEASE;   
    exit(0);   
}

Create a database user

create user ecpg identified by 'Test@123';

Create a test table

drop table if exists ecpg.test_ecpg_tab;
create table ecpg.test_ecpg_tab as select id , ' name '||id name from generate_series(1,20) id;

Issue