首页 > 代码库 > C语言连接Oracle

C语言连接Oracle

原文: 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 =http://www.mamicode.com/
      (SERVER = DEDICATED)
      (SERVICE_NAME = umail)
    )
  )


EXTPROC_CONNECTION_DATA =http://www.mamicode.com/
  (DEscrīptION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =http://www.mamicode.com/
      (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 =http://www.mamicode.com/
      (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