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

最近准备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 30

extern 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[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 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[i] = (short*)malloc(sizeof(short));
		select_p->I[i] = (short*)malloc(sizeof(short));
		bind_p->V[i] = (char*)malloc(1);
		select_p->V[i] = (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[i] = 1;
	}
}
//释放内存SQLDA的函数：
void free_da()
{
	int i,j;
	for (i=0; i < MAX_ITEMS; i++)
    {
		if (select_p->V[i] != (char *)NULL)
			free(select_p->V[i]);
		free(select_p->I[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[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;//把所有类型转换为字符型
     }
}
void sql_error(char *msg)
{
    printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK RELEASE;
    exit(0);
}

如果有人需要以此完成作业，请做出相应的修改，避免雷同哦 :) :) :)