1.表管理
表创建,表删除,表修改,无动态输入条件即无宿主变量参与。
例如创建表ln001
EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE ln001 (name VARCHAR(20), sex char(2),certinum varchar(18))" ;
2.表查询
2.1 有固定有效查询条件,无取值操作。
PREPARE,EXECUTE 用法:
EXEC SQL BEGIN DECLARE SECTION;
char sql[256+1];
EXEC SQL END DECLARE SECTION;
memset(sql,0x00,sizeof(sql));
sprintf(sql," delete *from ln001 where certinum='%s' ",certinum);
EXEC SQL PREPARE sql_pre FROM :sql;
EXEC SQL EXECUTE sql_pre;
if(SQLCODE && SQLCODE!=100)
{
PRINTF(__FILE__,__LINE__,"执行sql出错SQLCODE[%d]",SQLCODE);
return -1;
}
2.2 有不定项可选查询条件取多条数据,取值到宿主变量校验。
EXEC SQL BEGIN DECLARE SECTION;
char sql[256+1];
char name[40+1];
short id=0;
EXEC SQL END DECLARE SECTION;
memset(sql,0x00,sizeof(sql));
memset(name,0x00,sizeof(name));
strcpy(sql,"select name from ln001 where 1=1 ");
if(strlen(certinum)>0)
{
sprintf(sql," %s and certinum='%s' ",sql,argv[1]);
}
if(strlen(sex)>0)
{
sprintf(sql," %s and sex='%s' ",sql,argv[2]);
}
EXEC SQL PREPARE sql_pre FROM :sql;
EXEC SQL DECLARE SQL_CUR CURSOR FOR sql_pre;
if(SQLCODE)
{
}
EXEC SQL OPEN SQL_CUR;
if(SQLCODE)
{
}
WHILE(1)
{
EXEC SQL FETCH SQL_CUR INTO:certinum:id;
if(SQLCODE && SQLCODE!=100)
{
PRINTF(__FILE__,__LINE__,"执行sql出错SQLCODE[%d]",SQLCODE);
EXEC SQL CLOSE SQL_CUR;
return -1;
}
if(SQLCODE=100)
{
break;
}
}