我练习ANSI动态SQL 方法4,运行时总是有错误,请高手帮我看看,谢谢!
代码如下:
#include
#include
#include
#include
#define MAX_VAR_LEN 200
#define MAX_NAME_LEN 31
void connect();
void process_input();
void process_output();
void sql_error();
extern sqlgls(char *,size_t *,size_t *);
//定义全局宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char sql_stat[100];
EXEC SQL END DECLARE SECTION;
void main()
{
EXEC SQL WHENEVER SQLERROR DO sql_error();
//连接数据库
connect();
//分配输入描述区和输出描述区
EXEC SQL ALLOCATE DESCRIPTOR'input_descriptor';
EXEC SQL ALLOCATE DESCRIPTOR'output_descriptor';
//循环处理动态语句
for(;
{
puts("请输入动态SQL语句(EXIT:退出)!"
;
gets(sql_stat);
//EXIT退出循环
if((strncmp(sql_stat,"EXIT",4)==0)||(strncmp(sql_stat,"exit",4)==0))
break;
//准备SQL 语句
EXEC SQL PREPARE S FROM :sql_stat;
//定义游标
EXEC SQL DECLARE C CURSOR FOR S;
process_input();
/*打开游标
select语句:处理查询结果
其他语句:执行
*/
EXEC SQL OPEN C USING DESCRIPTOR 'input_descriptor';
if((strncmp(sql_stat,"SELECT",6)==0)||(strncmp(sql_stat,"select",6)==0))
process_output();
//关闭游标
EXEC SQL CLOSE C;
}
//释放输入描述区和输出描述区
EXEC SQL DEALLOCATE DESCRIPTOR 'input_descriptor';
EXEC SQL DEALLOCATE DESCRIPTOR 'output_descriptor';
//提交事务,断开连接
EXEC SQL COMMIT WORK RELEASE;
puts("欢迎使用动态SQL!"
;
}
void connect()
{
EXEC SQL BEGIN DECLARE SECTION;
char username[10],password[10],server[10];
EXEC SQL END DECLARE SECTION;
strcpy(username,"SCOTT"
;
strcpy(password,"TIGER"
;
strcpy(server,"SJK88"
;
EXEC SQL CONNECT:username IDENTIFIED BY
assword USING :server;
if(sqlca.sqlcode == 0)
printf("Connect to oracle as user :%s \n\n",username);
}
void process_input()
{
EXEC SQL BEGIN DECLARE SECTION;
char name[31];
int input_count,input_len,occurs,ANSI_varchar_type;
char input_buf[200];
EXEC SQL END DECLARE SECTION;
int i;
//绑定变量:输入描述区
EXEC SQL DESCRIBE INPUT S
USING DESCRIPTOR 'input_descriptor';
//取得绑定变量的个数
EXEC SQL GET DESCRIPTOR 'input_descriptor'
:input_count = COUNT;
//数值类型:变长字符串
ANSI_varchar_type=12;
for(i=0;i<input_count;i++)
{
occurs=i+1;
//绑定变量名:宿主变量
EXEC SQL GET DESCRIPTOR 'input_descriptor'
VALUE
ccurs :name =NAME;
// 显示绑定变量名
printf("\n输入%s的值: \n",name);
//为输入宿主变量输入数据
gets(input_buf);
fflush(stdin);
//设置变量:NULL终止
input_len=strlen(input_buf);
input_buf[input_len]='\0';
//设置绑定变量值
EXEC SQL SET DESCRIPTOR 'input_descriptor'
VALUE
ccurs TYPE =:ANSI_varchar_type,
LENGTH =:input_len,DATA=:input_buf;
}
}
void process_output()
{
EXEC SQL BEGIN DECLARE SECTION;
int output_count,occurs,type,len;
short indi;
char data[200],name[31];
EXEC SQL END DECLARE SECTION;
int i;
//选择列表项:输出描述区
EXEC SQL DESCRIBE OUTPUT S
USING DESCRIPTOR 'output_descriptor';
//取得选择列表项个数
EXEC SQL GET DESCRIPTOR 'output_descriptor'
utput_count=COUNT;
printf("\n"
;
//设置类型为变长字符串
type=12;
len=MAX_VAR_LEN;
for(i=0;i<output_count;i++)
{
occurs=i+1;
//设置数据类型和长度
EXEC SQL SET DESCRIPTOR 'output_descriptor'
VALUE
ccurs TYPE=:type,LENGTH=:len;
//取得列表项名称
EXEC SQL GET DESCRIPTOR 'output_descriptor'
VALUE
ccurs :name=NAME;
//显示选择列表项名称
printf("\t%s",name);
}
printf("\n\n"
;
//循环提取完毕:退出循环
EXEC SQL WHENEVER NOT FOUND DO BREAK;
for(;
{
//行数据:输出描述区
EXEC SQL FETCH C INTO DESCRIPTOR 'output_descriptor';
//循环显示每列数据
for(i=0;i<output_count;i++)
{
occurs=i+1;
//取得选择列表项数据
EXEC SQL GET DESCRIPTOR 'output_descriptor'
VALUE
ccurs :data = DATA,:indi = INDICATOR;
if(indi == -1)
//NULL:显示为空格
printf("\t%s"," "
;
else
//NOT NULL:显示数据
printf("\t%s",data);
}
printf("\n");
}
}
void sql_error()
{
char msg[512];
int msg_size =512;
int buf_size ;
sqlgls(msg,&msg_size,&buf_size);
msg[msg_size] = '\0';
printf("出错语句:%s\n",msg);
sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
printf("出错原因:%s\n",sqlca.sqlerrm.sqlerrmc);
}
|