原文: 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 <stdio.h>

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

C語言連接Oracle