oracle proc 编程是在我的概念中是非常陌生的,学习了一天下来发现这东西真的好古老,但是依然健壮、稳定,其中间是编辑一个 .pc 的文件使用 proc 进行预处理,预处理后生成一个真正的.c文件,此时再由 gcc 去编译成一个可执行文件运行。因为 proc 涉及到的知识较多,我无法整理到几篇博文中就概述这些所涉及的知识,所以只能总结代码做好注释防止以后自己需要时忘记。如果有想进一步了解 proc 编程的,可以买名为“Oracle9I ProC C++编程指南”。包括 oracle 的环境搭建也并不是我们讨论的重点,如果有精力的话,我会给大家编写类似的教程。
最小化实现代码
#include <stdio.h> #include "sqlca.h" // 包含 proc 头文件 EXEC SQL BEGIN DECLARE SECTION; char* serversid = "scott/tiger@orcl"; // 定义宿主变量 EXEC SQL END DECLARE SECTION; int main(int argc, char* argv) { int ret = 0; printf("connect server str = %s\n", serversid); EXEC SQL connect :serversid; // 连接服务器 if (sqlca.sqlcode != 0) // 错误处理机制,后文会有详细介绍 { ret = sqlca.sqlcode; printf("connect error...errcode = %d\n", ret); return ret; } printf("connect oracler server success...\n"); EXEC SQL commit release; // 提交并关闭连接 return 0; }
使用 proc 命令进行编译,编译结果如下:
[oracle@localhost 0723]$ proc hello.pc Pro*C/C++: Release 11.2.0.1.0 - Production on Fri Jul 24 13:20:03 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. System default option values taken from: /home/oracle_11/app/oracle/product/11.2.0/db_1/precomp/admin/pcscfg.cfg
此时会在当前目录下生成一个预处理过的 .c 文件,.c文件的内容如下:
/* Result Sets Interface */ #ifndef SQL_CRSR # define SQL_CRSR struct sql_cursor { unsigned int curocn; void *ptr1; void *ptr2; unsigned int magic; }; typedef struct sql_cursor sql_cursor; typedef struct sql_cursor SQL_CURSOR; #endif /* SQL_CRSR */ /* Thread Safety */ typedef void * sql_context; typedef void * SQL_CONTEXT; /* Object support */ struct sqltvn { unsigned char *tvnvsn; unsigned short tvnvsnl; unsigned char *tvnnm; unsigned short tvnnml; unsigned char *tvnsnm; unsigned short tvnsnml; }; typedef struct sqltvn sqltvn; struct sqladts { unsigned int adtvsn; unsigned short adtmode; unsigned short adtnum; sqltvn adttvn[1]; }; typedef struct sqladts sqladts; static struct sqladts sqladt = { 1,1,0, }; /* Binding to PL/SQL Records */ struct sqltdss { unsigned int tdsvsn; unsigned short tdsnum; unsigned char *tdsval[1]; }; typedef struct sqltdss sqltdss; static struct sqltdss sqltds = { 1, 0, }; /* File name & Package Name */ struct sqlcxp { unsigned short fillen; char filnam[9]; }; static struct sqlcxp sqlfpn = { 8, "hello.pc" }; static unsigned int sqlctx = 18323; static struct sqlexd { unsigned long sqlvsn; unsigned int arrsiz; unsigned int iters; unsigned int offset; unsigned short selerr; unsigned short sqlety; unsigned int occurs; short *cud; unsigned char *sqlest; char *stmt; sqladts *sqladtp; sqltdss *sqltdsp; unsigned char **sqphsv; unsigned long *sqphsl; int *sqphss; short **sqpind; int *sqpins; unsigned long *sqparm; unsigned long **sqparc; unsigned short *sqpadto; unsigned short *sqptdso; unsigned int sqlcmax; unsigned int sqlcmin; unsigned int sqlcincr; unsigned int sqlctimeout; unsigned int sqlcnowait; int sqfoff; unsigned int sqcmod; unsigned int sqfmod; unsigned char *sqhstv[4]; unsigned long sqhstl[4]; int sqhsts[4]; short *sqindv[4]; int sqinds[4]; unsigned long sqharm[4]; unsigned long *sqharc[4]; unsigned short sqadto[4]; unsigned short sqtdso[4]; } sqlstm = {12,4}; /* SQLLIB Prototypes */ extern sqlcxt ( void **, unsigned int *, struct sqlexd *, struct sqlcxp * ); extern sqlcx2t( void **, unsigned int *, struct sqlexd *, struct sqlcxp * ); extern sqlbuft( void **, char * ); extern sqlgs2t( void **, char * ); extern sqlorat( void **, unsigned int *, void * ); /* Forms Interface */ static int IAPSUCC = 0; static int IAPFAIL = 1403; static int IAPFTL = 535; extern void sqliem( unsigned char *, signed int * ); typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR; typedef struct { unsigned short len; unsigned char arr[1]; } varchar; /* CUD (Compilation Unit Data) Array */ static short sqlcud0[] = {12,4130,1,0,0, 5,0,0,0,0,0,27,13,0,0,4,4,0,1,0,1,97,0,0,1,10,0,0,1,10,0,0,1,10,0,0, 36,0,0,2,0,0,30,22,0,0,0,0,0,1,0, }; #include <stdio.h> #include "sqlca.h" /* EXEC SQL BEGIN DECLARE SECTION; */ char* serversid = "scott/tiger@orcl"; // 定义宿主变量 /* EXEC SQL END DECLARE SECTION; */ int main(int argc, char* argv[]) { int ret = 0; printf("serversid: %s\n", serversid); // 打印一下服务器的连接地址字符串 /* EXEC SQL CONNECT :serversid; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 12; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )10; sqlstm.offset = (unsigned int )5; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstv[0] = (unsigned char *)serversid; sqlstm.sqhstl[0] = (unsigned long )0; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( short *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned long )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlstm.sqparm = sqlstm.sqharm; sqlstm.sqparc = sqlstm.sqharc; sqlstm.sqpadto = sqlstm.sqadto; sqlstm.sqptdso = sqlstm.sqtdso; sqlstm.sqlcmax = (unsigned int )100; sqlstm.sqlcmin = (unsigned int )2; sqlstm.sqlcincr = (unsigned int )1; sqlstm.sqlctimeout = (unsigned int )0; sqlstm.sqlcnowait = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } // 连接服务器 if (sqlca.sqlcode != 0) // 容错处理,后面会有更详细的文章介绍 { ret = sqlca.sqlcode; printf("connect oracle error... code = %d\n", ret); return ret; } printf("connect oracle success...\n"); /* EXEC SQL COMMIT RELEASE; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 12; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlstm.sqltdsp = &sqltds; sqlstm.iters = (unsigned int )1; sqlstm.offset = (unsigned int )36; sqlstm.cud = sqlcud0; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)4352; sqlstm.occurs = (unsigned int )0; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); } // 提交(commit)并关闭(release)连接 return 0; }
这是由 proc 预处理过后的 .c 文件,只不过是将我们之前编写的 pc 文件中的一些 EXE SQL … 等关键字替换成了真正的 c 语言执行语句。最后我们需要使用 gcc 来编译它。如果你直接编译的话,会收到如下报错:
[oracle@localhost 0723]$ gcc hello.c -o hello /tmp/cchZbCeQ.o: In function `main': hello.c:(.text+0x86): undefined reference to `ECPGget_sqlca' hello.c:(.text+0x1ed): undefined reference to `sqlcxt' hello.c:(.text+0x1f2): undefined reference to `ECPGget_sqlca' hello.c:(.text+0x200): undefined reference to `ECPGget_sqlca' hello.c:(.text+0x27f): undefined reference to `ECPGget_sqlca' hello.c:(.text+0x2b7): undefined reference to `sqlcxt'
这提示的意思是找不到动态库,所以我们需要手动指定动态库和头文件所在路径。正确的编译方法如下:
gcc hello.c -o hello -I $ORACLE_HOME/precomp/public -L $ORACLE_HOME/lib -l clntsh
编译完成后运行程序,如果提示连接成功,那么证明你的程序就可以正常连接 oracle 服务器了:
[oracle@localhost 0723]$ ./hello serversid: scott/tiger@orcl connect oracle success...