PRO*C编程动态SQL中SQLDA小试牛刀

Wenxin

最近准备ORACLE的作业，其中有一题要求模糊，不管怎么，我用了一个看似很麻烦的方法完成了这样一件事：
输入一个表名，输出它的列名类型，然后输出所有的里面的值。
具体做法使用了动态SQL里面的最麻烦的方法，即使用SQLDA，网上的使用例子很少，所以我把自己写的贴上来，希望能给像我一样的PRO*C初学者一点启发，其中肯定有不足之处，还望高手能不吝赐教。
#include "sqlca.h"#include <stdio.h>#include <string.h>#include <stdlib.h>#include <sqlda.h>#include <sqlcpr.h>#include "oci.h"//定义列和绑定变量的最大个数#define MAX_ITEMS 40//定义列名的最大值#define MAX_VNAME_LEN 30#define MAX_INAME_LEN 30extern void sqlnul( unsigned short*, unsigned short*, int*);extern void sqlprc( unsigned int*, int*, int*);extern SQLDA *SQLSQLDAAlloc(dvoid *context, unsigned int max_vars,unsigned int max_name, unsigned int max_ind_name);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 ORACLE OPTION (ORACA = YES); EXEC ORACLE OPTION (RELEASE_CURSOR = YES); EXEC SQL INCLUDE SQLDA;  //变量定义区： EXEC SQL BEGIN DECLARE SECTION;    VARCHAR oraCN[30];    char sql_statement[256]= "select * from ";    char type_statement[256]="select COLUMN_NAME,DATA_TYPE from all_tab_columns where table_name='";    char tablename[20]="";    int i; EXEC SQL END DECLARE SECTION;  SQLDA *bind_p; SQLDA *select_p; int main() {    strcpy(oraCN.arr,"scott/Tiger@orcl");    oraCN.len = strlen(oraCN.arr);    oraCN.arr[oraCN.len]='\0';      EXEC SQL CONNECT :oraCN;       printf("\n [OK Connected!] \n\n");        EXEC SQL WHENEVER SQLERROR DO sql_error("<ERROR>");    printf("请输入表名:");    scanf("%s",tablename);    strupr(tablename);    strcat(sql_statement,tablename);    strcat(type_statement,tablename);    strcat(type_statement,"'");    alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN);    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("COLUMN_NAME\t\tDATA_TYPE\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);} 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); printf("\n");}free_da();EXEC SQL CLOSE C;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;}select_p->N = MAX_ITEMS;for(i=0;i<MAX_ITEMS;i++){bind_p->I = (short*)malloc(sizeof(short));select_p->I = (short*)malloc(sizeof(short));bind_p->V = (char*)malloc(1);select_p->V = (char*)malloc(1);}return 0;}//绑定变量的设置：void set_bind_v(){int i;//char bind_var[64];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 = 1;}}//释放内存SQLDA的函数：void free_da(){int i,j;for (i=0; i < MAX_ITEMS; i++)    {if (select_p->V != (char *)NULL)free(select_p->V);free(select_p->I);    }for (j=0; j < MAX_ITEMS; j++){if (bind_p->V[j] != (char *)NULL)free(bind_p->V[j]);free(bind_p->I[j]);}SQLSQLDAFree(0,bind_p);SQLSQLDAFree(0,select_p);}//选择列处理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), &(select_p->T), &null_ok);//检查类型是否为空switch (select_p->T)        {           case 1://VARCHAR2                break;           case 2://NUMBER                sqlprc(&(select_p->L), &precision, &scale);                if (precision == 0)                  precision = 40;                select_p->L = precision + 2;                break;   case 8://LONG select_p->L = 240; break;   case 11://ROWID select_p->L = 18; break;   case 12://DATE select_p->L = 9; break;   case 23://RAW break;           case 24://LONGRAW select_p->L = 240;     break;}select_p->V = (char *)realloc(select_p->V, select_p->L+1);select_p->V[select_p->L] ='\0';//加上终止符select_p->T = 1;//把所有类型转换为字符型     }}void sql_error(char *msg){    printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc);    EXEC SQL ROLLBACK RELEASE;    exit(0);}
如果有人需要以此完成作业，请做出相应的修改，避免雷同哦