Solaris 10下 Oracle 11G proc 的C程序示例

要做一个Solaris下的C程序，要求访问Oracle数据库。试着写了个Sample。这个Sample均是Select语句，第一个是查询单条记录，单个字段；第二个是查询单条记录，多个字段，不同数据类型；第三个是查询多条记录，多个字段，不同数据类型；参考：Oracle9i Database List of Bookshttp://download.oracle.com/docs/cd/B10501_01/nav/docindex.htm -> >嵌入式SQL（E-SQL）简介3http://bbs.chinaunix.net/thread-164775-1-1.html环境Solaris 10 (SunOS JAL001 5.10 Generic_141445-09 i86pc i386 i86pc)SunStudio12u1-SunOS-x86-tar-ML.tar.bz2Oracle 11gbasic-11.2.0.1.0-solaris-x86.zip (Oracle 11g 32bit 客户端类库，解压至 ${ORACLE_HOME}/lib32 下，并 ln -s libclntsh.so.11.1 libclntsh.so)环境变量/etc/profileexport PS1="\u@\h \W\$ "export JAVA_HOME=/export/home/data/jdk1.6.0_21export SUNSTUDIO_HOME=/export/home/data/sunstudio12.1export TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/export/home/oracle/oracle11gexport ORACLE_HOME=${ORACLE_BASE}/db_1export ORACLE_SID=JALexport CPATH=${ORACLE_HOME}/precomp/publicexport LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:/lib/32:/usr/lib:${ORACLE_HOME}/odg/libexport SHLIB_PATH=${LD_LIBRARY_PATH}export PATH=${JAVA_HOME}/bin:${SUNSTUDIO_HOME}/bin:${ORACLE_HOME}/bin:/usr/ucb:/usr/local/bin:/usr/openwin/bin:/usr/ccs/bin:/usr/sfw/bin:/usr/X11/bin:${PATH}export DISPLAY=172.16.200.11:0.0xhost 172.16.200.11输出结果proc.out.txtps:这个是NetBeans整个的Make和运行的记录，全部给出是为了方便以后查找各种命令及参数。/export/home/zhangll/NetBeansProjects/CDS-JAL で "/export/home/data/sunstudio12.1/bin/dmake -f Makefile CONF=Debug" を実行中dmake: 並列モードをデフォルトとして使用します。.dmakerc ファイルの設定については、dmake(1) のマニュアルページを参照してください。gmake -C ./sample .build-pregmake[1]: Entering directory `/export/home/zhangll/NetBeansProjects/CDS-JAL/sample'#proc INCLUDE=./inc LTYPE=NONE HEADER=hdr INAME=inc/jal_types.hproc INCLUDE=./inc LTYPE=NONE CHAR_MAP=STRING INAME=src/DBSelect1.scPro*C/C++: Release 11.2.0.1.0 - Production on Wed Jul 14 17:06:09 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.System default option values taken from: /export/home/oracle/oracle11g/db_1/precomp/admin/pcscfg.cfgproc INCLUDE=./inc LTYPE=NONE CHAR_MAP=STRING INAME=src/DBSelect2.scPro*C/C++: Release 11.2.0.1.0 - Production on Wed Jul 14 17:06:09 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.System default option values taken from: /export/home/oracle/oracle11g/db_1/precomp/admin/pcscfg.cfgproc INCLUDE=./inc LTYPE=NONE CHAR_MAP=STRING INAME=src/DBSelect3.scPro*C/C++: Release 11.2.0.1.0 - Production on Wed Jul 14 17:06:09 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.System default option values taken from: /export/home/oracle/oracle11g/db_1/precomp/admin/pcscfg.cfggmake[1]: Leaving directory `/export/home/zhangll/NetBeansProjects/CDS-JAL/sample'JAL001 --> 1 個のジョブJAL001 --> 2 個のジョブgmake -f nbproject/Makefile-Debug.mk SUBPROJECTS= .build-confgmake[1]: Entering directory `/export/home/zhangll/NetBeansProjects/CDS-JAL'gmake -f nbproject/Makefile-Debug.mk dist/Debug/GNU-Solaris-x86/cds-jalgmake[2]: Entering directory `/export/home/zhangll/NetBeansProjects/CDS-JAL'mkdir -p build/Debug/GNU-Solaris-x86/sample/srcrm -f build/Debug/GNU-Solaris-x86/sample/src/DBSelect3.o.dgcc -m32 -I./sample/inc -L/export/home/oracle/oracle11g/db_1/lib32 -lclntsh -c -g -MMD -MP -MF build/Debug/GNU-Solaris-x86/sample/src/DBSelect3.o.d -o build/Debug/GNU-Solaris-x86/sample/src/DBSelect3.o sample/src/DBSelect3.cgcc: -lclntsh: リンクが完了しなかったのでリンカの入力ファイルは使われませんでしたmkdir -p build/Debug/GNU-Solaris-x86/sample/srcrm -f build/Debug/GNU-Solaris-x86/sample/src/DBSelect2.o.dgcc -m32 -I./sample/inc -L/export/home/oracle/oracle11g/db_1/lib32 -lclntsh -c -g -MMD -MP -MF build/Debug/GNU-Solaris-x86/sample/src/DBSelect2.o.d -o build/Debug/GNU-Solaris-x86/sample/src/DBSelect2.o sample/src/DBSelect2.cgcc: -lclntsh: リンクが完了しなかったのでリンカの入力ファイルは使われませんでしたmkdir -p build/Debug/GNU-Solaris-x86/sample/srcrm -f build/Debug/GNU-Solaris-x86/sample/src/DBSelect1.o.dgcc -m32 -I./sample/inc -L/export/home/oracle/oracle11g/db_1/lib32 -lclntsh -c -g -MMD -MP -MF build/Debug/GNU-Solaris-x86/sample/src/DBSelect1.o.d -o build/Debug/GNU-Solaris-x86/sample/src/DBSelect1.o sample/src/DBSelect1.cgcc: -lclntsh: リンクが完了しなかったのでリンカの入力ファイルは使われませんでしたmkdir -p dist/Debug/GNU-Solaris-x86gcc -m32 -I./sample/inc -L/export/home/oracle/oracle11g/db_1/lib32 -lclntsh -o dist/Debug/GNU-Solaris-x86/cds-jal build/Debug/GNU-Solaris-x86/sample/src/main.o build/Debug/GNU-Solaris-x86/sample/src/DBSelect3.o build/Debug/GNU-Solaris-x86/sample/src/DBSelect2.o build/Debug/GNU-Solaris-x86/sample/src/DBSelect1.o build/Debug/GNU-Solaris-x86/sample/src/FileIO.ogmake[2]: Leaving directory `/export/home/zhangll/NetBeansProjects/CDS-JAL'gmake[1]: Leaving directory `/export/home/zhangll/NetBeansProjects/CDS-JAL'構築成功。終了値 0。/export/home/zhangll/NetBeansProjects/CDS-JAL で "/export/home/zhangll/NetBeansProjects/CDS-JAL/dist/Debug/GNU-Solaris-x86/cds-jal" を実行中----------------------------select01()ename = [FORD]----------------------------select02()empno ename job mgr hirdate sal comm deptno7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000.00 -99999.99 20----------------------------select03()empno ename job mgr hirdate sal comm deptno7369 SMITH CLERK 7902 1980/12/17 00:00:00 800.00 -99999.99 207499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600.00 300.00 307521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250.00 500.00 307566 JONES MANAGER 7839 1981/04/02 00:00:00 2975.00 -99999.99 207654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250.00 1400.00 307698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850.00 -99999.99 307782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450.00 -99999.99 107788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000.00 -99999.99 207839 KING PRESIDENT -9999 1981/11/17 00:00:00 5000.00 -99999.99 107844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500.00 0.00 307876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100.00 -99999.99 207900 JAMES CLERK 7698 1981/12/03 00:00:00 950.00 -99999.99 307902 FORD ANALYST 7566 1981/12/03 00:00:00 3000.00 -99999.99 207934 MILLER CLERK 7782 1982/01/23 00:00:00 1300.00 -99999.99 10実行成功。終了値 0。源文件DBSelect1.c文件:proc.zip大小:65KB下载:下载#include sqlca.h>#include stdio.h>#include string.h>/* 1. get an instance of struct SQLCA */EXEC SQL INCLUDE SQLCA;int perr01(const char * msg){ printf("Error occured when %s\n", msg); printf("{\n"); printf(" sqlcaid = %s\n",sqlca.sqlcaid); printf(" sqlabc = %d\n",sqlca.sqlabc); printf(" sqlcode = %d\n",sqlca.sqlcode); printf(" sqlerrm.sqlerrml = %d\n",sqlca.sqlerrm.sqlerrml); printf(" sqlerrm.sqlerrmc = %s\n",sqlca.sqlerrm.sqlerrmc); printf(" sqlerrp = %s\n",sqlca.sqlerrp); printf(" sqlerrd = %d\n",sqlca.sqlerrd); printf(" sqlwarn = %s\n",sqlca.sqlwarn); printf(" sqlext = %s\n",sqlca.sqlext); printf("}\n"); return 1;}int notfound01(){ return 2;}void select01() { printf("----------------------------select01()\n"); char buf[1024]; int errorFlag = 0; int notFoundFlag =0; /* 2. declare host variables */ EXEC SQL BEGIN DECLARE SECTION; char user[20] = "scott"; char passwd[20] = "123456"; char dbStr[20]="JAL"; int empno=7902; VARCHAR ename[11]; EXEC SQL END DECLARE SECTION; /* 3. connect DB server */ char * msg = "connect db"; EXEC SQL WHENEVER SQLERROR DO errorFlag=perr01(msg); /* connection error*/ EXEC SQL CONNECT :user IDENTIFIED BY :passwd USING :dbStr ; if(errorFlag){ exit(errorFlag); } /* 4. query DB */ EXEC SQL WHENEVER SQLERROR DO errorFlag = perr01("query DB"); /* query error*/ EXEC SQL WHENEVER NOT FOUND DO notFoundFlag = notfound01(); memset(ename.arr, NULL, 11); EXEC SQL SELECT ENAME INTO :ename FROM EMP WHERE EMPNO = :empno; if(errorFlag){ exit(errorFlag); } /* 5. handle data */ if (notFoundFlag) { printf("Not found employee[EMPNO=%d]\n", empno); } else { memset(buf, NULL, 11); memcpy(buf, ename.arr, ename.len); printf("ename = [%s]\n", buf); } /* 6. disconnect DB */ EXEC SQL ROLLBACK WORK RELEASE ;}DBSelect2.sc#include sqlca.h>#include stdio.h>#include string.h>/* 1. get an instance of struct SQLCA */EXEC SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION; typedef struct { int empno; /* NUMBER(4) */ char ename[10 + 1]; /* VARCHAR2(10) */ char job[9 + 1]; /* VARCHAR2(9) */ int mgr; /* NUMBER(4) */ char hiredate[30]; /* DATE */ double sal; /* NUMBER(7,2) */ double comm; /* NUMBER(7,2) */ int deptno; /* NUMBER(4) */ } Emp02; typedef struct { short empno; short ename; short job; short mgr; short hiredate; short sal; short comm; short deptno; } IdcEmp02;EXEC SQL END DECLARE SECTION;int perr02(const char * msg){ printf("Error occured when %s\n", msg); printf("{\n"); printf(" sqlcaid = %s\n",sqlca.sqlcaid); printf(" sqlabc = %d\n",sqlca.sqlabc); printf(" sqlcode = %d\n",sqlca.sqlcode); printf(" sqlerrm.sqlerrml = %d\n",sqlca.sqlerrm.sqlerrml); printf(" sqlerrm.sqlerrmc = %s\n",sqlca.sqlerrm.sqlerrmc); printf(" sqlerrp = %s\n",sqlca.sqlerrp); printf(" sqlerrd = %d\n",sqlca.sqlerrd); printf(" sqlwarn = %s\n",sqlca.sqlwarn); printf(" sqlext = %s\n",sqlca.sqlext); printf("}\n"); return 1;}int notfound02(){ return 2;}void select02() { printf("----------------------------select02()\n"); char buf[1024]; int errorFlag = 0; int notFoundFlag =0; /* 2. declare host variables */ EXEC SQL BEGIN DECLARE SECTION; /* using for connect DB*/ char user[20] = "scott"; char passwd[20] = "123456"; char dbStr[20]="JAL"; int empno=7902; Emp02 emp; IdcEmp02 idcEmp; EXEC SQL END DECLARE SECTION; /* 3. connect DB server */ char * msg = "connect db"; EXEC SQL WHENEVER SQLERROR DO errorFlag=perr02(msg); EXEC SQL CONNECT :user IDENTIFIED BY :passwd USING :dbStr ; if(errorFlag){ exit(errorFlag); } /* 4. query DB */ EXEC SQL WHENEVER SQLERROR DO errorFlag = perr02("query DB"); EXEC SQL WHENEVER NOT FOUND DO notFoundFlag = notfound02(); /*EXEC SQL SELECT EMPNO, ENAME, JOB, MGR, TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO INTO :emp.empno:idcEmp.empno, :emp.ename:idcEmp.ename, :emp.job:idcEmp.job, :emp.mgr:idcEmp.mgr, :emp.hiredate:idcEmp.hiredate, :emp.sal:idcEmp.sal, :emp.comm:idcEmp.comm, :emp.deptno:idcEmp.deptno FROM EMP WHERE EMPNO = :empno;*/ EXEC SQL SELECT EMPNO, ENAME, JOB, MGR, TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO INTO :emp INDICATOR :idcEmp FROM EMP WHERE EMPNO = :empno; if(errorFlag){ exit(errorFlag); } /* 5. handle data */ if (notFoundFlag) { printf("Not found employee[EMPNO=%d]\n", empno); } else { printf("%-5s %-10s %-10s %-5s %-20s %-10s %-10s %-5s \n", "empno", "ename", "job", "mgr", "hirdate","sal", "comm", "deptno"); emp.hiredate[19]=NULL; printf("%-5d %-10s %-10s %-5d %-20s %-10.2f %-10.2f %-5d \n", emp.empno, emp.ename, emp.job, -1 == idcEmp.mgr ? -9999 : emp.mgr, -1 == idcEmp.hiredate ? "" : emp.hiredate, -1 == idcEmp.sal ? -99999.99 : emp.sal, -1 == idcEmp.comm ? -99999.99 : emp.comm, -1 == idcEmp.deptno ? -99 : emp.deptno); } /* 6. disconnect DB */ EXEC SQL ROLLBACK WORK RELEASE ;}DBSelect3.sc#include sqlca.h>#include stdio.h>#include string.h>/* 1. get an instance of struct SQLCA */EXEC SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION; typedef struct { int empno; /* NUMBER(4) */ char ename[10 + 1]; /* VARCHAR2(10) */ char job[9 + 1]; /* VARCHAR2(9) */ int mgr; /* NUMBER(4) */ char hiredate[30]; /* DATE */ double sal; /* NUMBER(7,2) */ double comm; /* NUMBER(7,2) */ int deptno; /* NUMBER(4) */ } Emp03; typedef struct { short empno; short ename; short job; short mgr; short hiredate; short sal; short comm; short deptno; } IdcEmp03;EXEC SQL END DECLARE SECTION;int perr03(const char * msg){ printf("Error occured when %s\n", msg); printf("{\n"); printf(" sqlcaid = %s\n",sqlca.sqlcaid); printf(" sqlabc = %d\n",sqlca.sqlabc); printf(" sqlcode = %d\n",sqlca.sqlcode); printf(" sqlerrm.sqlerrml = %d\n",sqlca.sqlerrm.sqlerrml); printf(" sqlerrm.sqlerrmc = %s\n",sqlca.sqlerrm.sqlerrmc); printf(" sqlerrp = %s\n",sqlca.sqlerrp); printf(" sqlerrd = %d\n",sqlca.sqlerrd); printf(" sqlwarn = %s\n",sqlca.sqlwarn); printf(" sqlext = %s\n",sqlca.sqlext); printf("}\n"); return 1;}int notfound03(){ return 2;}void select03() { printf("----------------------------select03()\n"); char buf[1024]; int errorFlag = 0; int notFoundFlag =0; /* 2. declare host variables */ EXEC SQL BEGIN DECLARE SECTION; /* using for connect DB*/ char user[20] = "scott"; char passwd[20] = "123456"; char dbStr[20]="JAL"; int empno=7902; Emp03 emp; IdcEmp03 idcEmp; EXEC SQL END DECLARE SECTION; /* 3. connect DB server */ char * msg = "connect db"; EXEC SQL WHENEVER SQLERROR DO errorFlag=perr03(msg); EXEC SQL CONNECT :user IDENTIFIED BY :passwd USING :dbStr ; if(errorFlag){ exit(errorFlag); } /* 4. query DB */ EXEC SQL SELECT EMPNO, ENAME, JOB, MGR, TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO INTO :emp INDICATOR :idcEmp FROM EMP WHERE EMPNO = :empno; EXEC SQL DECLARE C1 CURSOR FOR SELECT EMPNO, ENAME, JOB, MGR, TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO FROM SCOTT.EMP; EXEC SQL OPEN C1; EXEC SQL WHENEVER SQLERROR DO errorFlag = perr03("query DB"); EXEC SQL WHENEVER NOT FOUND DO notFoundFlag = notfound03(); printf("%-5s %-10s %-10s %-5s %-20s %-10s %-10s %-5s \n", "empno", "ename", "job", "mgr", "hirdate","sal", "comm", "deptno"); do{ EXEC SQL FETCH C1 INTO :emp INDICATOR :idcEmp; if(!errorFlag && !notFoundFlag){ /* 5. handle data */ printf("%-5d %-10s %-10s %-5d %-20s %-10.2f %-10.2f %-5d \n", emp.empno, emp.ename, emp.job, -1 == idcEmp.mgr ? -9999 : emp.mgr, -1 == idcEmp.hiredate ? "" : emp.hiredate, -1 == idcEmp.sal ? -99999.99 : emp.sal, -1 == idcEmp.comm ? -99999.99 : emp.comm, -1 == idcEmp.deptno ? -99 : emp.deptno); } }while(!errorFlag && !notFoundFlag); EXEC SQL CLOSE C1; if(errorFlag){ exit(errorFlag); } /* 6. disconnect DB */ EXEC SQL ROLLBACK WORK RELEASE ;}

btpka3

Solaris 10下 Oracle 11G proc 的C程序示例