c语言连接的作用,C语言连接Oracle （转载）

最近在搞C语言连接Oracle、DB2数据库，现把C连接Oracle的文章总结下：

用C语言连接ORACLE数据库。

有两个思路和目的

思路一)

本地环境：UBUNTU 7.04,ORACLE 10G

目的:用C语连接本地数据库sid:umail,ip:127.0.0.1,用户名:umail，密码:umail,表tt1.

显示表tt1的所有记录.

思路二)

远程环境服务器1：linux es3

远程数据库：oracle9i

目的:在远程一台服务器上，再远程连接数据库，用户名和表同上

显示表tt1的所有记录.

一)思路一解决办法：

1) 查看/etc/profile文件

export ORACLE_BASE=/share/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.1

export ORACLE_SID=umail

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$LD_LIBRARY_PATH

export JAVA_HOME=/share/jdk

export AWT_TOOLKIT=MToolkit

作用：解决tnsnames.ora问题

2)更改$ORACLE_HOME/precomp/admin/pcscfg.cfg

sys_include=(/usr/include,/usr/lib/gcc/i486-linux-gnu/4.1.2/include,/usr/lib/gcc/i486-linux-gnu/3.4.6/include,/share/oracle/product/10.2.1/precomp/public,/usr/include/sys)

include=(/share/oracle/product/10.2.1/precomp/public)

include=/share/oracle/product/10.2.1/precomp/hdrs

include=/share/oracle/product/10.2.1/tpcc2x_2/src

include=/share/oracle/product/10.2.1/precomp/precomp/include

include=/share/oracle/product/10.2.1/precomp/oracore/include

include=/share/oracle/product/10.2.1/precomp/oracore/public

include=/share/oracle/product/10.2.1/precomp/rdbms/include

include=/share/oracle/product/10.2.1/precomp/rdbms/public

include=/share/oracle/product/10.2.1/precomp/rdbms/demo

include=/share/oracle/product/10.2.1/precomp/nlsrtl/include

include=/share/oracle/product/10.2.1/precomp/nlsrtl/public

include=/share/oracle/product/10.2.1/precomp/network_src/include

include=/share/oracle/product/10.2.1/precomp/network_src/public

include=/share/oracle/product/10.2.1/precomp/network/include

include=/share/oracle/product/10.2.1/precomp/network/public

include=/share/oracle/product/10.2.1/precomp/plsql/public

ltype=short

3)新test.pc文件，如下:

#include

EXEC SQL INCLUDE SQLCA;

int main()

{

//声明SQL变量

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR user[20],pass[20],tnsname[20];

//VARCHAR预编译后为struct { unsigned short len; unsigned char arr[20];}

char ename[20];

int empno;

EXEC SQL END DECLARE SECTION;

//声明C变量

int i=0;

//变量赋值

strcpy(user.arr,"umail");

user.len=(unsigned short)strlen((char *)user.arr);

strcpy(pass.arr,"umail");

pass.len=(unsigned short)strlen((char *)pass.arr);

strcpy(tnsname.arr,"umail");

tnsname.len=(unsigned short)strlen((char *)tnsname.arr);

//连接数据库

EXEC SQL CONNECT :user IDENTIFIED BY :pass USING :tnsname;

EXEC SQL declare emp_cursor cursor for

select id,ename from umail.tt1;

EXEC SQL open emp_cursor;

EXEC SQL WHENEVER NOT FOUND DO break;

while(1)

{

EXEC SQL fetch emp_cursor into :empno,:ename;

printf("the empno %d/'s name is %s/n",empno,ename);

i=i+1;

}

printf("Yeah!We get %d records/n",i);

EXEC SQL close emp_cursor;

EXEC SQL commit work release;

}

3)更改tnsname文件如下:

UMAIL =

(DEscrīptION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = umail)

)

)

EXTPROC_CONNECTION_DATA =

(DEscrīptION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

4)编译方法:

预编译 proc test.pc

注：这步是依赖于$ORACLE_HOME/precomp/admin/pcscfg.cfg

编译:

gcc -o test test.c $ORACLE_HOME/lib/libclntsh.so

思路二解决办法：

1)在远程服务器上新建一个放tnsname.ora的目录

mkdir -p /root/network/admin

2)修改tnsname.ora文件，加入远程数据库信息，如下:

db =

(DEscrīptION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.217)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = umail)

)

)

3)将以上的tnsname.ora放入到/root/network/admin下面

scp tnsname.ora 192.168.6.197:/root/network/admin

4)在远程服务器上，将/etc/profile修改如下：

增加以下内容:

export ORACLE_HOME=/root

用su -加载环境变量

5)在本地，更改上一个实验的test.pc文件

原内容：

strcpy(tnsname.arr,"umail");

tnsname.len=(unsigned short)strlen((char *)tnsname.arr);

更改后的内容:

strcpy(tnsname.arr,"db");

tnsname.len=(unsigned short)strlen((char *)tnsname.arr);

6)利用本地环境重新编译test.pc

预编译 proc test.pc

scp test.c 192.168.6.197:/tmp

编译:

gcc -o test test.c /usr/lib/libclntsh.so.10.1

7)将test上传到远程服务器

scp test 192.168.6.197:/tmp

8)将libclntsh.so.10.1,libnnz10.so上传到远程服务器的/usr/lib下面

scp libclntsh.so.10.1 192.168.6.197:/usr/lib

scp libnnz10.so 192.168.6.197:/usr/lib

9)执行测试test

cd /tmp;./test