简述: 用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
--------------------------------------------------------------------------------------------------------------------
附加:这是本人在网上找到的另外一个测试也通过的连接到ORACLE的程序(注意非win系统里)
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char oc_passwd[101]; /*数据库密码*/
char oc_userid[101]; /*数据库用户名*/
char oc_dbname[101]; /*数据库名*/
char oc_coad[101];
EXEC SQL END DECLARE SECTION;
memset(oc_passwd, 0x00, sizeof(oc_passwd));
memset(oc_userid, 0x00, sizeof(oc_userid));
memset(oc_dbname, 0x00, sizeof(oc_dbname));
/*取数据库用户名*/
strcpy(oc_userid, "userid");
/*取数据库用户密码*/
strcpy(oc_passwd, "passwd") ;
/*取数据库名*/
strcpy(oc_dbname, "dbname");
EXEC SQL CONNECT :oc_userid
IDENTIFIED BY :oc_passwd
USING :oc_dbname;
if (sqlca.sqlcode != 0)
{
printf("用户名[%s]密码[%s]数据库[%s]\n", oc_userid, oc_passwd, oc_dbname);
printf("连接数据库失败,sqlcode=%d\n", sqlca.sqlcode);
return -1;
}
/*读table取coad字段*/
memset(oc_coad, 0x00, sizeof(oc_coad));
EXEC SQL SELECT coad
INTO :oc_coad
FROM table
WHERE 1=1;
if (sqlca.sqlcode == NORECORD)
{
printf("查询无记录\n");
return -1;
}
else if (sqlca.sqlcode != 0)
{
printf("查询失败,sqlcode=%d\n", sqlca.sqlcode);
return -1;
}
return 0;
}