支持嵌入式SQL预处理器（ECPG）

#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;
}

//基础的增删改

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

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

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

//对于动态 SQL 其中一种方法为使用 SQLDA 储存数据结构，SQLDA 结构定义于 sqlda.h 头文件中。在此将介绍如何使用 SQLDA
#include <sqlda.h>
SQLDA *bind_dp;
SQLDA *select_dp;

//该 SQLDA 结构体初始化可以使用如下方式
bind_dp = SQLSQLDAAlloc(runtime_context,size,name_lenght,ind_name_length);

//在 ORACLE 早期版本中使用 sqlald()函数来分配描述符
EXEC SQL FETCH ... USING DESCRIPTOR ...
EXEC SQL OPEN ... USING DESCRIPTOR ...

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

//定义列和绑定变量的最大个数
#define MAX_ITEMS 40
//定义列名的最大值
#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;

//宿主变量定义：
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>");

    //给sqlda类型 分配数据
    alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN);   

    //建表语句
    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语句
    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);   
}   
//分配描述符空间：
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;   
}   
//绑定变量的设置：
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;   
    }   
}

//选择列处理
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;   
    //对格式作处理
    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);//检查类型是否为空
        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';//加上终止符
        select_p->T[i] = 1;//把所有类型转换为字符型
    }   
} 
//释放内存SQLDA的函数：
void free_da()   
{    
    sqlclu(bind_p);   
    sqlclu(select_p);   
}   

//错误处理
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);   
}