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 

 

http://blog.csdn.net/lidongtang/article/details/5859578