倾情奉送!-pro*c动态查询结果集写文件
看了傻瓜手册2.0感觉不错希望能得到2.1版本,我添加了一个动态查询处理的函数,希望能收藏到此手册中。
下面得程序在linux7.2+oracle8i中通过。
但是发现凡是varchar的字段长度大于8时候,有时SelectUnit->V[i] 打印信息里面老是多出了一个无关的字符"p"或者是“h”(如:SelectUnit->V[i] is ??1 P
),找了几天还是没有看到错误所在,其它情况正常谢谢,如果那位大侠能找出原因发个mail给我mhjob@sina.com!!!
程序如下:
#define MAX_ITEMS 20 /*定义最大字段数*/
#define MAX_VNAME_LEN 30 /*定义选择表项最大长度*/
#define MAX_INAME_LEN 30 /*定义指示器变量名字的最大长度*/
EXEC SQL INCLUDE sqlca; /*说明SQL通讯区*/
EXEC SQL INCLUDE oraca; /*说明ORACLE通讯区*/
EXEC SQL INCLUDE sqlda; /*说明SQL语句描述结构 SQLDA结构体请查相关资料*/
EXEC ORACLE OPTION (ORACA = YES);
EXEC ORACLE OPTION (RELEASE_CURSOR = YES);
SQLDA *SelectUnit=NULL; /*定义选择项描述*/
SQLDA *BindUnit=NULL; /*定义输入项空间*/
/*-------------------------------------------------------------------
// 函数名: DbsTBL_Get_SmallInfo
// 描述: 在不确定SQL语句的选择项与输入项,
// 且不知个数与数据类型的情况下,要求sql语句的的空列必须把它修改成“~”,不允许有空列出现,
// 同时日前必须格式化为YYYYMMDD 24HH:MM:SS或YYYYMMDD
// (适合少数据量的处理)
// 输入参数: 查询语句,导出文件
// 输出参数:
// 返回值: 0(DB_OK)成功,其他失败
-------------------------------------------------------------------*/
int DbsTBL_Get_SmallInfo(const char *Sql/*SQL选择语句*/, const char *FileName/*导出目标文本文件名*/)
/*返回结果集记录文件 其中列与列直接用“`”分号间隔,一条记录中止直接用\n分号间隔,空格用“~”代替 */
{
int null_ok, precision, scale;
FILE *fp;
int i,j,length;
/*定义变量,以存放SQL语句*/
char sqlstr[256];
fp = fopen(FileName, "w+");
if ( fp == NULL)
{
return -1;
}
/*赋值*/
strcpy(sqlstr, Sql);
/*给描述区分配空间*/
if ((SelectUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL)
{
/*空间分配失败*/
return -3;
}
if ((BindUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL)
{
/*空间分配失败*/
return -3;
}
/*给查询返回值存储区分配空间*/
SelectUnit->N = MAX_ITEMS;
for (i=0; i < MAX_ITEMS; i++)
{
BindUnit->I[i] = NULL;
BindUnit->V[i] = NULL;
BindUnit->I[i] = (short *)malloc(sizeof(short *));
BindUnit->V[i] = (char *)malloc(MAX_VNAME_LEN);
}
for (i=0; i < MAX_ITEMS; i++)
{
SelectUnit->I[i] = NULL;
SelectUnit->V[i] = NULL;
SelectUnit->I[i] = (short *)malloc(sizeof(short *));
SelectUnit->V[i] = (char *)malloc(MAX_VNAME_LEN);
}
EXEC SQL WHENEVER SQLERROR GOTO sqlerr;
EXEC SQL PREPARE SQLSA FROM :sqlstr;
EXEC SQL DECLARE Cursorbase CURSOR FOR SQLSA;
/*输入描述处理*/
BindUnit->N = MAX_ITEMS;
EXEC SQL DESCRIBE BIND VARIABLES for SQLSA INTO BindUnit;
if (BindUnit->F < 0)
{
return -4;
/*输入项过多*/
}
BindUnit->N = BindUnit->F;
/*打开光标*/
EXEC SQL OPEN Cursorbase USING DESCRIPTOR BindUnit;
/*选择项处理*/
EXEC SQL DESCRIBE SELECT LIST for SQLSA INTO SelectUnit;
if (SelectUnit->F < 0)
{
return -4;
/*选择表项过多*/
}
SelectUnit->N = SelectUnit->F;
/*因为所有格式,类型都是不确定的,所以要得到正确的返回值就要处理格式*/
for (i=0; i < SelectUnit->F; i++)
{
sqlnul(&(SelectUnit->T[i]), &(SelectUnit->T[i]), &null_ok);
switch (SelectUnit->T[i])
{
case 1:/*CHAR*/
break;
case 2:/*NUMBER*/
sqlprc(&(SelectUnit->L[i]), &precision, &scale);
if (precision == 0)
precision = 40;
SelectUnit->L[i] = precision + 2;
break;
case 8:/*LONG*/
SelectUnit->L[i] = 240;
break;
case 11:/*ROWID*/
SelectUnit->L[i] = 18;
break;
case 12:/*DATE*/
SelectUnit->L[i] = 9;
break;
case 23:/*RAW*/
break;
case 24:/*LONGRAW*/
SelectUnit->L[i] = 240;
break;
}
SelectUnit->V[i] = NULL;
SelectUnit->V[i] = (char *)realloc(SelectUnit->V[i], SelectUnit->L[i]+1);
SelectUnit->T[i] = 1;/*把所有类型转换为字符型*/
}
EXEC SQL WHENEVER NOT FOUND goto EndFor;
for (;;)
{
EXEC SQL FETCH Cursorbase USING DESCRIPTOR SelectUnit;
/*输出各字段*/
for (i=0; i < SelectUnit->F; i++)
{
char buffer[256];
/*记录链表 其中列与列直接用“`”分号间隔,一条记录中止直接用“^”分号间隔,空格用“~”代替*/
if (i != SelectUnit->F-1)
sprintf(buffer, "%s`", SelectUnit->V[i]);
else
sprintf(buffer, "%s\r\n", SelectUnit->V[i]);
fputs(buffer,fp);
printf("----------------------SelectUnit->V[i] is %s\n",SelectUnit->V[i]);
}
}
close(fp);
EndFor:
for (i=0; i < MAX_ITEMS; i++)
{
if (SelectUnit->V[i] != (char *)NULL)
{
free(SelectUnit->V[i]);
SelectUnit->V[i]=NULL;
}
free(SelectUnit->I[i]);
SelectUnit->I[i]=NULL;
}
for (j=0; j < MAX_ITEMS; j++)
{
if (BindUnit->V[j] != (char *)NULL)
{
free(BindUnit->V[j]);
BindUnit->V[j]=NULL;
}
free(BindUnit->I[j]);
BindUnit->I[j]=NULL;
}
sqlclu(SelectUnit);
sqlclu(BindUnit);
EXEC SQL CLOSE Cursorbase;
return 0;
sqlerr:
return -6;
}