[原创] Pro*C实战

leon1509

本总结跟网上那些垃圾转贴有本质的区别，乃实际环境摸索出来的。

============ 分割线 =============

数据库版本：ORACLE 10.2.0.1.0
操作系统：RHEL4.x

1. 编辑$ORACLE_HOME/precomp/admin/pcscfg.cfg，在sys_include=处加上stddef.h所在的目录。

附：stddef.h查找方法
    用root执行find / -name stddef.h -print
    关键在于确定查找出来的N个stddef.h的目录。以下为10.14.3.10服务器上的查找结果：
/usr/include/linux/stddef.h
/usr/include/xulrunner-sdk-1.9/system_wrappers/stddef.h
/usr/lib/gcc/x86_64-redhat-linux6E/4.3.2/include/stddef.h
/usr/lib/gcc/x86_64-redhat-linux/3.4.6/include/stddef.h
/usr/lib/gcc/x86_64-redhat-linux/4.1.1/include/stddef.h
/usr/lib/bcc/include/stddef.h
/usr/lib/syslinux/com32/include/stddef.h
/usr/lib/syslinux/com32/include/bitsize/stddef.h
/usr/src/kernels/2.6.18-128.el5-x86_64/include/linux/stddef.h
/home/term/pack/STLport-5.2.1/stlport/stddef.h
/home/mytools/STLport-5.1.5/stlport/stddef.h

根据http://blog.chinaunix.net/space.php?uid=20011314&do=blog&cuid=527167帖子介绍，我选择了
    /usr/lib/gcc/x86_64-redhat-linux/4.1.1/include/stddef.h加入到pcscfg.cfg文件中。（此处为关键步骤）


2. 编译执行：
$ proc dept.pc$ cc -o dept dept.c -I $ORACLE_HOME/precomp/public -L $ORACLE_HOME/lib -l clntsh

3. 例一：dept.pc源代码

#include <stdio.h>#include <string.h>#include <stdlib.h>#include <sqlca.h>#include <locale.h>int main(int argc, char* argv[]){  EXEC SQL BEGIN DECLARE SECTION;  char *uid = "ocs_test/ocs_test";  char name[32]="NULL";  /*int i;*/  EXEC SQL VAR name is STRING(32);  EXEC SQL END DECLARE SECTION;  EXEC SQL CONNECT :uid;  if(sqlca.sqlcode == 0)    printf("connect success!\n");  else    printf("connect failure!\n");  EXEC SQL DECLARE cur_client CURSOR FOR select itemname from ocs_sm_function;  EXEC SQL OPEN cur_client;  setlocale( LC_ALL, "chs" );  while(1)  {    strcpy(name, "");    EXEC SQL FETCH cur_client INTO :name;    if(sqlca.sqlcode == 1403)    {        break;    }    printf("name is %s\n", name);  }  EXEC SQL CLOSE cur_client;  /* 释放数据库资源 */  EXEC SQL ROLLBACK WORK RELEASE;  return 0;}

4.例二：emp.pc源代码
#include <stdio.h>#include <string.h>#include <stdlib.h>#include <sqlda.h>#include <sqlcpr.h>EXEC SQL INCLUDE sqlca;/*RELEASE_CURSOR=YES 使PROC 在执行完后释放与嵌入SQL有关资源*/EXEC ORACLE OPTION (RELEASE_CURSOR = YES);EXEC SQL BEGIN DECLARE SECTION;char *vc_user = "scott/tiger";/*varchar vc_user[20];*/long al_empno=0;char ac_ename[11]="";char ac_hiredate[20]="";double af_sal=0;EXEC SQL VAR ac_ename IS STRING(11);EXEC SQL VAR ac_hiredate IS STRING(20);EXEC SQL END DECLARE SECTION;/*错误处理函数*/void sql_error(char *msg){    printf("\n%s,%ld,%s\n", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc);    EXEC SQL ROLLBACK RELEASE;    exit(-1);}int main(int argc, char *argv[]){    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE ERROR: ");    /*连接数据库*//*    strcpy(vc_user.arr,"scott/tiger");    vc_user.len=16;*/    EXEC SQL CONNECT :vc_user;    EXEC SQL DECLARE cur_emp CURSOR FOR    SELECT EMPNO, ENAME,to_char(HIREDATE,'yyyy/mm/dd hh24:mi:ss'),SAL FROM EMP;    EXEC SQL OPEN cur_emp;    while(1)    {        al_empno=0;        strcpy(ac_ename,"");        strcpy(ac_hiredate,"");        af_sal=0;        EXEC SQL FETCH cur_emp INTO :al_empno, :ac_ename, :ac_hiredate, :af_sal;        if( sqlca.sqlcode == 1403)        {            break;        }        printf("empno=%ld,ename=%s,hiredate=%s,sal=%lf\n",al_empno,ac_ename,ac_hiredate,af_sal);    }    EXEC SQL CLOSE cur_emp;    EXEC SQL ROLLBACK WORK RELEASE;}

5. 例三：test1.pc
#include <stdio.h>#include <string.h>#include <stdlib.h>#include <sqlca.h>int main(int argc,char* argv[]) {        EXEC SQL BEGIN DECLARE SECTION;        char *uid = "scott/tiger";        long userid=0;        char username[51]="";        EXEC SQL VAR username IS STRING(51);        EXEC SQL END DECLARE SECTION;        EXEC SQL CONNECT :uid;        if(sqlca.sqlcode == 0)                printf("connect sucess!\n");        else                printf("connect fail!\n");         EXEC SQL DECLARE cur_user CURSOR FOR SELECT DEPTNO, DNAME FROM DEPT;         EXEC SQL OPEN cur_user;         while(1){          userid=0;          strcpy(username,"");          EXEC SQL FETCH cur_user INTO  :userid, :username;          if( sqlca.sqlcode == 1403)          {              break;          }          printf("userid=%ld,username=%s\n",userid,username);         }         EXEC SQL CLOSE cur_user;         // EXEC SQL COMMIT WORK RELEASE;         EXEC SQL ROLLBACK WORK RELEASE;         return 0;}

6. 例四：test.pc
#include <stdio.h>#include <string.h>#include <stdlib.h>EXEC SQL include sqlca;EXEC ORACLE OPTION (RELEASE_CURSOR = YES);EXEC SQL BEGIN DECLARE SECTION;VARCHAR username[20];VARCHAR password[20];EXEC SQL END DECLARE SECTION;void sql_error(){EXEC SQL WHENEVER SQLERROR CONTINUE;printf("/n Oracle error detected:/n");printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL ROLLBACK RELEASE;exit(1);}main(){strcpy(username.arr,"scott");strcpy(password.arr,"tiger");username.len=strlen(username.arr);password.len=strlen(password.arr);EXEC SQL CONNECT :username IDENTIFIED BY :password;printf("sqlca.sqlcode=%d\n",sqlca.sqlcode);printf("login user=%s\n",username.arr);exit(0);return 0;}
问题：dept.pc输出的中文数据是乱码，尚未解决