oracle proc 定义宿主,oracle proc 编程基础及最小化案例

oracle proc 编程是在我的概念中是非常陌生的，学习了一天下来发现这东西真的好古老，但是依然健壮、稳定，其中间是编辑一个 .pc 的文件使用 proc 进行预处理，预处理后生成一个真正的.c文件，此时再由 gcc 去编译成一个可执行文件运行。因为 proc 涉及到的知识较多，我无法整理到几篇博文中就概述这些所涉及的知识，所以只能总结代码做好注释防止以后自己需要时忘记。如果有想进一步了解 proc 编程的，可以买名为“Oracle9I ProC C++编程指南”。包括 oracle 的环境搭建也并不是我们讨论的重点，如果有精力的话，我会给大家编写类似的教程。

最小化实现代码

#include

#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

#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...

相关