达梦Proc*示例-封装数据到sqlDa结构体中

前言

此Demo是直接将proc查询出来的数据，封装到sqlda中，注意使用dpc_new工具编译时的兼容oracle参数

proc代码

#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 BEGIN DECLARE SECTION;   
    char sql_statement[256]= "select * from test_ora";   
    char type_statement[256]="select f1,f2 from test_ora where f1=1";   
    int f1 = 1;
    int i;   
    VARCHAR username[10];
VARCHAR password[10];
VARCHAR servername[20];
EXEC SQL END DECLARE SECTION;   	
SQLDA *bind_p;   
SQLDA *select_p;   	
int main()    
	{   
	    strcpy(username.arr,"SYSDBA");
	    username.len = strlen(username.arr);
	    username.arr[username.len] = '\0';	
	    strcpy(password.arr,"SYSDBA");
	    password.len = strlen(password.arr);
	    password.arr[password.len] = '\0';

        strcpy(servername.arr,"localhost:5236");
	    servername.len = strlen(servername.arr);
	    servername.arr[servername.len] = '\0';
		
	    EXEC SQL CONNECT :username identified by :password using :servername;
	    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 CREATE TABLE TEST_ORA(f1 INT, f2 INT);
        EXEC SQL INSERT INTO TEST_ORA VALUES(1,2);
        EXEC SQL INSERT INTO TEST_ORA VALUES(1,4);
		EXEC SQL commit;
        //prepare语句
	    EXEC SQL PREPARE S from :type_statement;   
	    EXEC SQL DECLARE C1 CURSOR FOR S;    
	    set_bind_v();   
	    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(); 
	    for(;;)   
	    {    EXEC SQL WHENEVER NOT FOUND DO break;          
	         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)   
	{   
	    int i;
if((bind_p=SQLSQLDAAlloc(0,size,max_vname_len,max_iname_len))==(SQLDA*)0)   
	    {   
	        printf("can't allocate memory for bind_p.");   
	        return -1;   
	    }   
if((select_p=SQLSQLDAAlloc(0,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()   
	{   
	    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()   
	{   
		    int i,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(&(select_p->T[i]), &(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()   
	{    
	    SQLSQLDAFree(0,bind_p);   
	    SQLSQLDAFree(0,select_p);   
  }   
//错误处理
	void sql_error(char *msg)   
	{   
	    printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc);   
	    EXEC SQL ROLLBACK RELEASE;   
	    exit(0);   
	}  

编译参数

export LD_LIBRARY_PATH=/opt/dmdbms/bin
./dpc_new FILE=test_ora_20231024.pc MODE=ORACLE TYPE=C TYPE_MODE=ORACLE UNSAFE_NULL=Y 
gcc  -g -o  test_ora_20231024  test_ora_20231024.c -I"/opt/dmdbms/include"  -L"/opt/dmdbms/bin"   -ldmdpc