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.

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

Enhancements

None.

Constraints

ECPG supports most of the MogDB SQL syntax. However, the current syntax and lexical of the ECPG do not support the processing of anonymous block statements and package statements. Therefore, anonymous block statements and package creation statements cannot be used as embedded SQL statements.

Dependencies

None.

Usage

Using the following commands to compile ECPG:

1. ecpg testecpg.ecpg -o testecpg.c

2. 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;
}

1. Create a database user

   create user ecpg identified by 'Test@123';

2. 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;