C Language Connection Oracle

Original: C Language connection Oracle

Recently in the C language connection Oracle, DB2 database, now the C connection to Oracle's article summary:

Connect to the Oracle database in C language.
There are two ideas and purposes

Idea one)
Local environment: UBUNTU 7.04,oracle 10G
Objective: To connect the local database with C language sid:umail,ip:127.0.0.1, user name: Umail, Password: umail, table TT1.
Displays all records for the table TT1.

Idea II)
Remote Environment Server 1:linux ES3
Remote database: oracle9i
Purpose: On a remote server, and then remotely connect to the database, user name and table ibid.
Displays all records for the table TT1.

A) idea of a solution:

1) View/etc/profile file
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
Role: Solve Tnsnames.ora problem

2) Change $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) The new test.pc file, as follows:

#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

int main ()
{
Declaring SQL variables
EXEC SQL BEGIN DECLARE section;
VARCHAR USER[20],PASS[20],TNSNAME[20];
VARCHAR pre-compiled for struct {unsigned short len; unsigned char arr[20];}
Char ename[20];
int empno;
EXEC SQL END DECLARE section;

Declaring C variables
int i=0;

Assigning values to variables
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);

Connecting to a database
EXEC SQL Connect:user identified by:p the 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 does 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) Change the Tnsname file as follows:

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) Compile Method:
Pre-compiled proc TEST.PC
Note: This step is dependent on the $oracle_home/precomp/admin/pcscfg.cfg

Compile:
Gcc-o Test test.c $ORACLE _home/lib/libclntsh.so

Two ways to solve the problem:

1) Create a new directory on the remote server to put Tnsname.ora
Mkdir-p/root/network/admin

2) Modify the Tnsname.ora file and add the remote database information as follows:
db =
(Descrīption =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.6.217) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = umail)
)
)

3) put the above Tnsname.ora into the/root/network/admin below
SCP Tnsname.ora 192.168.6.197:/root/network/admin

4) on the remote server, modify the/etc/profile as follows:
Add the following content:
Export Oracle_home=/root
Load environment variables with Su-

5) on the local, change the test.pc file of the previous experiment

Original content:
strcpy (Tnsname.arr, "Umail");
tnsname.len= (unsigned short) strlen ((char *) tnsname.arr);

What's changed:
strcpy (Tnsname.arr, "db");
tnsname.len= (unsigned short) strlen ((char *) tnsname.arr);

6) Recompile test.pc with local environment

Pre-compiled proc TEST.PC

SCP test.c 192.168.6.197:/tmp

Compile:
Gcc-o Test test.c/usr/lib/libclntsh.so.10.1

7) Upload test to the remote server
SCP Test 192.168.6.197:/tmp

8) Upload the libclntsh.so.10.1,libnnz10.so to the remote server under/usr/lib
SCP libclntsh.so.10.1 192.168.6.197:/usr/lib
SCP libnnz10.so 192.168.6.197:/usr/lib

9) Execute test
cd/tmp;. /test

C Language Connection Oracle