服务器的连接:
//connect to local database 连接服务器 szUsrId为用户名 szUsrPwd为密码 con_local 连接代名词 szSrv为数据源 结构为如 192.168.1.1;9000 ip加端口
exec sql CONNECT :szUsrId IDENTIFIED BY :szUsrPwd at :con_local using :szSrv;
exec sql CONNECT :szUsrId2 IDENTIFIED BY :szUsrPwd2 at :con_remote using :szSrv2;
exec sql set close on endtran off;
exec sql disconnect all;
SQL操作(sql循环游标提取数据事例)
// 说明SQL变量
exec sql begin declare section;
CS_CHAR task_id[2048];
CS_INT task_ref_id;
CS_INT task_status;
exec sql end declare section;
memset(szSQLString,0,sizeof(szSQLString));
sprintf(szSQLString,"select task_id,task_ref_id,task_status from aex_int_task where task_status = 3",szModule);
exec sql at :con_local prepare mod_pre from :szSQLString;/*准备SQL语句*/// 用PREPARE语句分析当前的动态sql语句,语句名是mod_pre
exec sql at :con_local declare mod_cursor cursor for mod_pre;//申明游标mod_pre的游标名为mod_cursor
if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
{
exec sql rollback;
return -1;
}
exec sql at :con_local open mod_cursor; //打开游标
if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
{
exec sql rollback;
return -1;
}
exec sql at :con_local fetch mod_cursor into :task_id,:task_ref_id, :task_status;//从游标中提取SQL所执行的数据
while(sqlca.sqlcode == 0)//循环执行游标 循环提取数据
{
//数组处理 存放在内存数据空间
strcpy(aex_int_task_List[aex_int_task_ListCount].task_id,task_id);
aex_int_task_List[aex_int_task_ListCount].task_ref_id=task_ref_id;
aex_int_task_List[aex_int_task_ListCount].task_status=task_status;
WriteLog(__LINE__,"task_ref_id=%d,task_status=%d\n",task_ref_id,task_status);
aex_int_task_ListCount++;
exec sql at :con_local fetch mod_cursor into :task_id,:task_ref_id, :task_status;
}
exec sql at :con_local close mod_cursor;/*关闭游标*/
exec sql at :con_local deallocate cursor mod_cursor;/*销毁游标*/
SQL操作(sql提取数据事例)
memset(szSQLString,0,sizeof(szSQLString));
sprintf(szSQLString,"select isnull(sum(case result_flag when 1 then 1 else 0 end),0) as sccuess, isnull(sum(case result_flag when 2 then 1 else 0 end),0) as fail from pub_cmd_result where log_code='%s' and result_module='aex'",str_task_id);
exec sql at :con_remote prepare count_flag_pre from :szSQLString;
if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
{
exec sql at :con_remote rollback;
return -1;
}
exec sql at :con_remote execute count_flag_pre into :successSQL,:failSQL;//获取成功数和失败数
exec sql at :con_remote commit;//提交事务
SQL操作(sql获取数据参数事例)
// 说明SQL变量
EXEC SQL BEGIN DECLARE SECTION;
char* username=USERNAME;
char* password=PASSWORD;
VARCHAR sqlstmt[80];
int emp_number;
VARCHAR emp_name[15];
VARCHAR job[50],job1[50],job2[50];
float salary;
EXEC SQL END DECLARE SECTION;
//EXEC SQL CONNECT :username IDENTIFIED BY :password;
// 构造动态SQL语句
sqlstmt.len=sprintf(sqlstmt.arr,"INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)VALUES(:V1,:V2,:V3,:V4)");
// 用PREPARE语句分析当前的动态INSERT语句,语句名是S
EXEC SQL PREPARE S FROM :sqlstmt;
// 循环插表
for(;;)
{
printf("/nEnter employee number:"); scanf("%d",&emp_number);
printf("/nEnter employee name:"); scanf("%s",&emp_name.arr);
emp_name.len=strlen(emp_name.arr);
printf("/nEnter employee job:"); scanf("%s",&job.arr); job.len=strlen(job.arr);
salary = 0; // With VC6, Missing this line will cause C Run-Time Error R6002.
printf("/nEnter salary:"); scanf("%f",&salary);
EXEC SQL EXECUTE S USING :emp_number,:emp_name,:job,:salary; //获取参数
}
EXEC SQL COMMIT RELEASE; // 提交事务
// 回滚事务,退出ORACLE
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;