Connect Oracle with C language

Recently, I am engaged in C-language connection to Oracle and DB2 databases. Now I will summarize the article on C connection to Oracle:

 

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

Idea 1)
Local Environment: Ubuntu 7.04, Oracle 10g
Objective: To connect to the local database Sid: umail, IP: 127.0.0.1, User name: umail, password: umail, table tt1.
Displays all records of table TT1.

Idea 2)
Remote Environment Server 1: Linux ES3
Remote database: oracle9i
Purpose: To remotely connect to the database on a remote server. The user name and table are the same as above.
Displays all records of table TT1.

 

I) solution 1:

1) view the/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
Purpose: Solve the Problem of tnsnames. ora

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/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 is as follows:

# Include <stdio. h>

Exec SQL include sqlca;

Int main ()
{
// Declare SQL Variables
Exec SQL begin declare section;
Varchar user [20], pass [20], tnsname [20];
// After varchar is pre-compiled, it is struct {unsigned short Len; unsigned char arr [20];}
Char ename [20];
Int empno;
Exec SQL end declare section;

// Declare the C variable
Int I = 0;

// Variable assignment
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 );

// Connect to the database
Exec SQL CONNECT: User identified by: pass using: tnsname;

Exec SQL declare emp_cursor cursor
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) change the tnsname file as follows:

Umail =
(Descr resume ption =
(Address = (Protocol = TCP) (host = 127.0.0.1) (Port = 1521 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = umail)
)
)

Extproc_connection_data =
(Descr resume ption =
(Address_list =
(Address = (Protocol = IPC) (Key = extproc0 ))
)
(CONNECT_DATA =
(SID = plsextproc)
(Presentation = Ro)
)
)

 

4) compilation method:
Pre-compile proc test. PC
Note: This step depends on $ ORACLE_HOME/precomp/admin/pcscfg. cfg.

Compile:
Gcc-O test. C $ ORACLE_HOME/lib/libclntsh. So

 

Solution 2:

1) create a new directory named tnsname. ora on the remote server.
Mkdir-P/root/Network/admin

2) modify the tnsname. ora file and add the remote database information as follows:
DB =
(Descr resume ption =
(Address = (Protocol = TCP) (host = 192.168.6.217) (Port = 1521 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = umail)
)
)

3) add tnsname. ora to/root/Network/admin.
SCP tnsname. ora 192.168.6.197:/root/Network/admin

4) on the remote server, modify/etc/profile as follows:
Add the following content:
Export ORACLE_HOME =/root
Use Su-to load Environment Variables

5) change the test. PC file of the previous experiment locally.

Original content:
Strcpy (tnsname. Arr, "umail ");
Tnsname. Len = (unsigned short) strlen (char *) tnsname. Arr );

Modified content:
Strcpy (tnsname. Arr, "DB ");
Tnsname. Len = (unsigned short) strlen (char *) tnsname. Arr );

6) use the local environment to re-compile test. PC

Pre-compile proc test. PC

SCP test. c 192.168.6.197:/tmp

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

 

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

8) Upload libclntsh. so.10.1 and libnnz10.so to the/usr/lib directory of the remote server.
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