Oracle應用Linux開發C

    隨著Linux操作系統的不斷完善與發展，出現了大量基于 Linux平臺的應用開發，原有的基于UNIX平臺的商業軟件也不斷被移植到Linux上來。最典型的，Oracle公司宣布，他的現有的及未來所有的數據庫產品和商業應用都將支持Linux平臺。本文所述OCI for Linux的C語言庫，正是Linux平臺上Oracle的C語言接口。 
    我們知道，在一個復雜的Oracle數據庫應用中，C程序代碼由于其語言本身的靈活性、高效性，往往被加入到其商務邏輯的核心層模塊中。Oracle數據庫對C語言的接口就是OCI(Oracle Common Interface) C-Library，該庫是一個功能十分強大的數據庫操作模塊。它支持事務處理，單事務中的多連接多數據源操作，支持數據的對象訪問、存儲過程的調用等一系列高級應用，并對Oracle下的多種附加產品提供接口。但是我們發現，為了使OCI庫在多種平臺上保持統一的風格并考慮向下兼容性，Oracle對大量的C語言類型和代碼進行了重新封裝，這使得OCI庫初看上去顯得紛繁復雜，初用者不知從何下手。由Kai Poitschke開發的Libsqlora8庫初步解決了這一問題，它使得在Linux下Oracle的非高端C語言開發變得比較方便易用。
    Libsqlora8 for *nix是GNU/Linux組織開發的針對Oracle8 OCI library的易用性C語言封裝。它將大量的OCI數據類型表現為通用C語言數據類型，將OCI函數按類型重新分類封裝，大大減少了函數的調用步驟和程序代碼量。Libsqlora8還有許多引人注目的特性:
        易于使用的動態SQL特性；
    同一連接中具有不同變量綁定的游標的重復打開；
    相同事務中的多數據庫連接；
    Oracle數據庫應用開發中的Build-in trace功能；
    正確處理數據插入操作中的數組變量問題；
    多平臺支持Oracle 8.0.4(HP-UX 9), Oracle 8.05(GNU/Linux), Oracle 8.1.6(GNU/Linux)等；
    可以作為靜態或動態形式鏈接進入應用程序。
    下面我們分步驟詳細闡述如何在Linux平臺上利用Libsqlora8函數庫開發Oracle數據庫應用。
    1.安裝Linux操作系統，并對新系統進行適當的系統配置。在本例中我們選用RedHat Linux 6.2操作系統。在為系統分區時，我們為Oracle數據庫專門分出兩個分區：/u01,/u02,作為Oracle數據庫的系統軟件和數據庫文件的安裝點。安裝好系統后，我們為系統增添兩個新組：oinstall和dba,并創建一個新用戶Oracle，他擁有整個數據庫系統軟件。這里就不詳細說明了。
    2.下面我們應該安裝Oracle數據庫了，這次我們選用Oracle 8.1.6版本，該版數據庫對國際化有很好的支持。在安裝數據庫之前，我們要先對Oracle用戶進行一些設置。主要是在該用戶的啟動腳本中，加入一些必要的環境變量，在本例中可以如下設置：
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/8.1.6
ORACLE_SID=oratest
PATH=$ORACLE_HOME/bin:/usr/bin:/etc:/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
    3.Oracle 8.1.6的數據庫安裝是比較簡單的，我們選擇缺省安裝，在系統的提示下逐一完成安裝過程。注意，Oracle8i對系統的要求是比較高的,特別是內存，在一些特殊應用中，要修改系統的缺省設置以提高數據庫性能。有關數據庫調優的討論與本文無關，在此就不再詳細介紹了。啟動數據庫，好了，現在我們可以用sqlplus登錄數據庫，可以看到，缺省安裝的Oracle數據庫有一類OCITest數據庫表，就使用這些表作為我們例子中的缺省表。
    4.安裝Libsqlora8庫函數。該庫函數當前版本為Libsqlora8-2.1.5，可從許多Linux網站上得到，也可從上下載libsqlora8-2.1.5.tar.gz源程序包。按以下步驟安裝：
$>tar -xzvf libsqlora8-2.1.5.tar.gz
$>cd libsqlora8-2.1.5
$>LD_LIBRARY_PATH=$ORACLE_HOME/lib
$>export LD_LIBRARY_PATH
$>./configure
$>make
$>make install
    對于要使用Oracle build-in trace功能的開發者，還要將以下環境變量設置好,SQLORA_TRACE_LEVEL，SOLORA_TRACE_FILE，SQLORA_ARRAYSIZE，當然，ORACLE_SID是一定要設好的。
    5.下面，我們介紹一下Libsqlora8的主要函數。
1)int sqlo_init(int threaded_mode) 初始化程序庫接口，讀出環境變量，設置相應的全局變量。當前，threaded_mode設為0。
2)int sqlo_connect(int * dbh, char * connect_str) 連接數據庫，dbh為數據庫連接描述符，connect_str為用戶名/口令字符串。
3)int sqlo_finish(int dbh) 斷開數據庫連接。
4)int sqlo_open(int dbh, char * stmt, int argc, char *argv[]) 打開由stmt確定的查詢語句所返回的游標。Argc,argv為查詢的參數，后面我們將用更清晰的方法傳遞參數。
5)int sqlo_close(int sth) 關閉由上一個函數打開的游標。
6)int sqlo_fetch(int sth) 從打開的游標中獲取一條記錄，并將之存入一個已分配內存空間中。
7)const char **sqlo_values(int sth, int *numbalues, int dostrip) 從內存中返回上一次sqlo_fetch取得的值，是以字符串形式返回的。
8)以下介紹另一種檢索方式，int sqlo_prepare(int dbh, char const *stmt)，返回一個打開的游標sth。
9)int sqlo_bind_by_name(int sth, const char * param_name, int param_type, const void * param_addr, unsigned int param_size, short * ind_arr, int is_array) 將查詢語句的傳入參數，按照名字的形式與函數中的變量綁定。如果你使用數組，那么參數param_addr和ind_arr必須指向該數組。
int sqlo_bind_by_pos(int sth, int param_pos, int param_type, const void * param_addr, unsigned int param_size, short * ind_arr, int is_array) 將查詢語句的傳出值，按照位置順序與函數中的變量綁定。
10)int sqlo_execute(int sth, int iterations) 執行查詢語句?！癐terations”可設為“1”。
11)在執行完數據庫操作后，我們可用int sqlo_commit (int dbh)提交操作，或用int sqlo_rollback(int dbh)回滾操作。
12)Libsqlora8還有其他一些操作函數，這里就不一一列出了。
下面舉幾個例子說明這些函數如何使用。
cstr = "ocitest/ocitest"; //用戶名/口令
status = sqlo_init(0);
if (SQLO_SUCCESS != status)
{ printf ("sql_init failed. Exitingn");
exit(1);
}
status = sqlo_connect(&dbh, cstr); // int dbh
以上源代碼，顯示了如何連接數據庫。
/* Select all and display */
char *select_stmt="SELECT cname, clength, colid FROM ocicolu";
if (0>(sd = sqlo_open(dbh, select_stmt, 0, NULL)))
{ printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}
while (0 == sqlo_fetch(sd,1))
{ v = sqlo_values(sd, NULL, 1);
printf("Result: %sn",v);
}
if (0 > sqlo_close(sd))
{ printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}
以上例子展示了第一種查詢方法，顯然，這種方法較簡單，但不夠靈活。
char *update_stmt =
"UPDATE ocitest.upload_log SET upload_fresh = where log_name = :1";
if (0 <= (sth = sqlo_prepare(dbh, update_stmt)))
{ if (SQLO_SUCCESS !=
(sqlo_bind_by_name(sth, ":1", SQLOT_STR, packet_name, 64, NULL, 0)
))
{ printf("sqlo_bind_param failed failed: %sn", sqlo_geterror(dbh) );
return 0;
}
}
if (SQLO_SUCCESS != sqlo_execute(sth, 1))
{ printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return 0;
}
    上面的代碼顯示了如何通過名字綁定變量，“：1”在Oracle SQL語句中表示為一個變量（名字隨意），在sqlo_bind_by_name函數中與packet_name變量綁定。在變量綁定完畢后，就可以調用sqlo_execute函數來執行這個SQL語句。
好了，我們已經向大家介紹了Libsqlora8的基本使用方法，如果希望了解更多內容，Libsqlora8的程序包中帶有詳細的說明和例子，大家不妨自己鉆研一下。有什么心得，歡迎和我聯系。E-mail：

 

/*-------------------------------------------------------------------------
* testlora.c
* Test programm for libsqlora8(Kai Poitschke)
* Assuming you installed the library with prefix=/usr/local, the command
* to compile this is:
* gcc -o sample sample.c -lsqlora8 -L$ORACLE_HOME/lib -lclntsh
*-----------------------------------------------------------------------*/
#include
#include
#include

#include "sqlora.h"

#define MAX_ITERS 10

#define MAX_LOOPS 1 /* how many time we run the tests */

#define CLOSE_CURSOR 1

 

/*-------------------------------------------------------------------------
* create our test table
*-----------------------------------------------------------------------*/
int create_table( int dbh )
{
int nkey;
char ckey[6];
double nval;
char cval[21];
char dval[11];

int sth;

char * create_table =
"CREATE TABLE T_SQLORA_TEST (n"
"NKEY NUMBER(8) NOT NULL,n"
"CKEY VARCHAR2(5) NOT NULL,n"
"NVAL NUMBER(16,4) NULL,n"
"CVAL VARCHAR2(20) NULL,n"
"DVAL DATE)";

/* Check if the table already exists */
if (SQLO_NO_DATA ==
sqlo_exists(dbh, "USER_TABLES", "TABLE_NAME", "T_SQLORA_TEST", NULL))
{
/* No, create it */
if (SQLO_SUCCESS != sqlo_exec(dbh, create_table))
{
printf("create_table failed: %sn%sn", sqlo_geterror(dbh),
create_table);
return 0;
}
printf("Table T_SQLORA_TEST createdn");
}

return 1;
}

/*-------------------------------------------------------------------------
* Query the test table
*-----------------------------------------------------------------------*/
int do_select( int dbh )
{
int sd;
const char **v;
int argc;
const char *argv[1];
char * select_stmt =
"SELECT NKEY, CKEY, NVAL, CVAL, DVAL FROM T_SQLORA_TEST WHERE NKEY >= :1";

argc = 0;
argv[argc++] = "0";

/* Select all and display */
if (0>(sd = sqlo_open(dbh, select_stmt, argc, argv)))
{
printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}
/* sqlo_print(sd);*/
while (0 == sqlo_fetch(sd,1))
{
v = sqlo_values(sd, NULL, 1);
printf("%4s%6s%19s%21s%11sn", v[0], v[1], v[2], v[3], v[4]);
}

#ifdef CLOSE_CURSOR
if (0 > sqlo_close(sd))
{
printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}
#endif
return 1;

}
/*-------------------------------------------------------------------------
* Select with prepare/execute/fetch.
*-----------------------------------------------------------------------*/
int test_select2( int dbh )
{
int sth;
int nkey[MAX_ITERS];
char ckey[MAX_ITERS][6];
double nval[MAX_ITERS];
char cval[MAX_ITERS][21];
char dval[MAX_ITERS][11];
int wc = 1;
int status;
int i;

short nkeyl[MAX_ITERS];
short ckeyl[MAX_ITERS];
short nvall[MAX_ITERS];
short cvall[MAX_ITERS];
short dvall[MAX_ITERS];

int rows_fetched = 0;
int rows_fetched_total = 0;
int rows_to_fetch;
int done_fetching = 0;

char * select_stmt =
"SELECT NKEY, CKEY, NVAL, CVAL, DVAL FROM T_SQLORA_TEST WHERE NKEY >= :1";

printf("Test select via classic methodsn");
/* Select all and display */
if (0>(sth = sqlo_prepare(dbh, select_stmt)))
{
printf("sqlo_prepare failed: %sn", sqlo_geterror(dbh));
return 0;
}

/* Bind input */
if (SQLO_SUCCESS !=
(sqlo_bind_by_name(sth, ":1", SQLOT_INT, &wc, sizeof(int), NULL, 0)))
{
printf("sqlo_bind_by_name failed: %sn", sqlo_geterror(dbh));
return 0;
}

/* Define Output */

if (SQLO_SUCCESS !=
(sqlo_define_by_pos(sth, 1, SQLOT_INT, nkey, sizeof(int),0,nkeyl, 1) ||
sqlo_define_by_pos(sth, 2, SQLOT_STR, ckey[0], 6, 0, ckeyl, 1) ||
sqlo_define_by_pos(sth, 3, SQLOT_FLT, nval, sizeof(double),0,nvall,1) ||
sqlo_define_by_pos(sth, 4, SQLOT_STR, cval[0], 21, 0, cvall, 1) ||
sqlo_define_by_pos(sth, 5, SQLOT_STR, dval[0], 11, 0, dvall, 1)))
{
printf("sqlo_define_by_pos failed: %sn", sqlo_geterror(dbh));
return 0;
}

rows_to_fetch = 3;
rows_fetched = rows_to_fetch;

status = sqlo_execute(sth, rows_to_fetch);

if (status < 0)
{
printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return(0);
}
else if (status == SQLO_NO_DATA)
{
/* arrays were filled fully. Get rowcount */
rows_fetched = sqlo_prows(sth);
done_fetching = 1;
printf("Execute fetched all %d rowsn", rows_fetched);

printf("Fetched all in one gon");

for (i = 0; i < rows_fetched; ++i)
{
printf("%3d %5s %19f %20s %10sn",
nkey[i], ckey[i], nval[i], cval[i], dval[i]);
}
}

for (i = 0; i < rows_fetched; ++i)
{
if (!i)
printf("Execute fetched %d rowsn", rows_fetched);

printf("%3d %5s %19f %20s %10sn",
nkey[i], ckey[i], nval[i], cval[i], dval[i]);
}

rows_fetched_total += rows_fetched;
rows_to_fetch = 4;

while(!done_fetching)
{
rows_fetched = rows_to_fetch;
status = sqlo_fetch(sth, rows_to_fetch);
if (status < 0)
{
printf("sqlo_fetch failed: %sn", sqlo_geterror(dbh));
return 0;
}

if (status == SQLO_NO_DATA)
{
rows_fetched = sqlo_prows(sth);

if (rows_fetched_total == rows_fetched)
{
/* no new fetches */
done_fetching = 1;
rows_fetched = 0;
}
else
{
rows_fetched = rows_fetched - rows_fetched_total;
done_fetching = 1;
}
printf("sqlo_fetch fetched last %d rowsn", rows_fetched);
}
else if (status == SQLO_SUCCESS)
{
printf("sqlo_fetch fetched %d rowsn", rows_fetched);

}
else
{
printf("sqlo_fetch failed: %sn", sqlo_geterror(dbh));
return 0;
}

for (i = 0; i < rows_fetched; ++i)
{
printf("%3d %5s %19f %20s %10sn",
nkey[i], ckey[i], nval[i], cval[i], dval[i]);
}
rows_fetched_total += rows_fetched;
}
#ifdef CLOSE_CURSOR
if (0 > sqlo_close(sth))
{
printf("sqlo_close failed: %sn", sqlo_geterror(dbh));
return 0;
}
#endif
return 1;

}

/*-------------------------------------------------------------------------
* test_reopen
*-----------------------------------------------------------------------*/
int test_reopen( int dbh )
{
int sth;
const char **v;
int argc;
const char *argv[1];
char * select_stmt =
"SELECT NKEY, CKEY, NVAL, CVAL, DVAL FROM T_SQLORA_TEST WHERE NKEY >= :1";

argc = 0;
argv[argc++] = "0";

/* Select all and display */
if (0>(sth = sqlo_open(dbh, select_stmt, argc, argv)))
{
printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}

while (0 == sqlo_fetch(sth,1))
{
v = sqlo_values(sth, NULL, 1);
printf("%s|%6s%19s%21s%11sn", v[0], v[1], v[2], v[3], v[4]);
}

argv[0] = "5";

if (SQLO_SUCCESS != sqlo_reopen(sth, argc, argv))
{
printf("sqlo_reopen failed: %sn", sqlo_geterror(dbh));
return 0;
}

printf("Fetch againn");

while (0 == sqlo_fetch(sth,1))
{
v = sqlo_values(sth, NULL, 0);
printf("%s|%6s%19s%21s%11sn", v[0], v[1], v[2], v[3], v[4]);
}

#ifdef CLOSE_CURSOR
if (0 > sqlo_close(sth))
{
printf("sqlo_open failed: %sn", sqlo_geterror(dbh));
return 0;
}
#endif
return 1;

}
/*-------------------------------------------------------------------------
* test_plsql
*-----------------------------------------------------------------------*/
int test_plsql( int dbh )
{
int ip2, op1;
double ip1;
char op2[40];

char * create_pack =
"CREATE OR REPLACE PACKAGE SQLORA_TEST ISn"
" PROCEDURE P1(ip1 IN NUMBER, ip2 IN NUMBER, op1 OUT NUMBER, op2 OUT VARCHAR);n"
"END;n";

char * create_pack_body =
"CREATE OR REPLACE PACKAGE BODY SQLORA_TEST ISn"
" PROCEDURE P1(ip1 IN NUMBER, ip2 IN NUMBER, op1 OUT NUMBER, op2 OUT VARCHAR)n"
" IS n"
" BEGINn"
" op1 := TO_NUMBER(ip1) + ip2;n"
" op2 := TO_CHAR(op1);n"
" END;n"
"END;n";

char * stmt =
"BEGINn"
" SQLORA_TEST.P1(:ip1, :ip2, :op1, :op2);n"
"END;n";

int sth;

printf("Testing PL/SQL proceduren");

if (SQLO_SUCCESS != sqlo_exec(dbh, create_pack))
{
printf("sqlo_exec failed: %sn%sn",sqlo_geterror(dbh), create_pack );
return 0;
}

printf("Package createdn");

if (SQLO_SUCCESS != sqlo_exec(dbh, create_pack_body))
{
printf("sqlo_exec failed: %sn%sn",sqlo_geterror(dbh), create_pack_body );
return 0;
}

printf("Package body createdn");

ip1 = 1.123456789012345;
ip2 = 20;
op1 = 0;
*op2 = 0;

if (0 <= (sth = sqlo_prepare(dbh, stmt)))
{
if (SQLO_SUCCESS !=
(sqlo_bind_by_name(sth, ":ip1", SQLOT_FLT, &ip1, sizeof(ip1),0,0) ||
sqlo_bind_by_name(sth, ":ip2", SQLOT_INT, &ip2, sizeof(ip2),0,0) ||
sqlo_bind_by_name(sth, ":op1", SQLOT_INT, &op1, sizeof(op1),0,0) ||
sqlo_bind_by_name(sth, ":op2", SQLOT_STR, op2, sizeof(op2),0,0)
))
{
printf("sqlo_bind_param failed failed: %sn", sqlo_geterror(dbh) );
return 0;
}
else
{
if (SQLO_SUCCESS != sqlo_execute(sth, 1))
{
printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return 0;
}
}
#ifdef CLOSE_CURSOR
if (SQLO_SUCCESS != sqlo_close(sth))
{
printf("sqlo_close failed: %sn", sqlo_geterror(dbh) );
return 0;
}
#endif
printf ("ip1: %.16f, ip2: %d, op1: %d, op2: %sn", ip1, ip2, op1, op2);
}
else
{
printf("sqlo_open failed: Status: %d, %sn", sth, sqlo_geterror(dbh) );
return 0;
}
return 1;
}

/*-------------------------------------------------------------------------
* test_insert with bind by pos
*-----------------------------------------------------------------------*/
int test_insert( int dbh )
{
int nkey;
char ckey[6];
double nval;
char cval[21];
char dval[11];

int sth;

char * insert_stmt =
"INSERT INTO T_SQLORA_TEST (NKEY, CKEY, NVAL, CVAL, DVAL) VALUES (:NKEY, :CKEY, :NVAL, :CVAL, :DVAL)";

 

printf("Testing Insert (bind by pos)n");

if (!create_table(dbh))
return 0;

nkey = 100;
strcpy(ckey, "CKEY");
nval = 1234567890.001;

strcpy(cval,"aaaaaaaaaaaaaaaaaaaa");
strcpy(dval,"01-JUL-00");

if (0 <= (sth = sqlo_prepare(dbh, insert_stmt)))
{
if (SQLO_SUCCESS !=
(sqlo_bind_by_pos(sth, 1, SQLOT_INT, &nkey, sizeof(int),0,0) ||
sqlo_bind_by_pos(sth, 2, SQLOT_STR, ckey, 6,0,0) ||
sqlo_bind_by_pos(sth, 3, SQLOT_FLT, &nval, sizeof(double),0,0) ||
sqlo_bind_by_pos(sth, 4, SQLOT_STR, cval, 21,0,0) ||
sqlo_bind_by_pos(sth, 5, SQLOT_STR, dval, 11,0,0)
))
{
printf("sqlo_bind_param failed failed: %sn", sqlo_geterror(dbh) );
return 0;
}
else
{
if (SQLO_SUCCESS != sqlo_execute(sth, 1))
{
printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return 0;
}
}
#ifdef CLOSE_CURSOR
if (SQLO_SUCCESS != sqlo_close(sth))
{
printf("sqlo_close failed: %sn", sqlo_geterror(dbh) );
return 0;
}
#endif
do_select(dbh);
}
else
{
printf("sqlo_open failed: Status: %d, %sn", sth, sqlo_geterror(dbh) );
return 0;
}

printf("finished test_insertn");
return 1;
}

/*-------------------------------------------------------------------------
* test_array_insert
*-----------------------------------------------------------------------*/
int test_array_insert( int dbh )
{
int nkey[MAX_ITERS];
char ckey[MAX_ITERS][6];
double nval[MAX_ITERS];
char cval[MAX_ITERS][21];
char dval[MAX_ITERS][11];

short nind[MAX_ITERS];
short cind[MAX_ITERS];
short dind[MAX_ITERS];

int sth, i, j;
int status;

char * insert_stmt =
"INSERT INTO T_SQLORA_TEST (NKEY, CKEY, NVAL, CVAL, DVAL) VALUES (:NKEY, :CKEY, :NVAL, :CVAL, :DVAL)";

printf("Testing Array Insert (bind by name)n");

if (!create_table(dbh))
return 0;

/* setup bind arrays */
for ( i = 0 ; i < MAX_ITERS; i++)
{
nkey[i] = i+1;
sprintf(ckey[i], "%c", A + i % 26 );
nval[i] = 1234567890.0 + i / 1000.0;
for (j = 0; j < 20; j++)
cval[i][j] = a + i % 26;
cval[i][20] = ;
sprintf(dval[i], "%02d-JUL-00", (i % 30 ) + 1);

nind[i] = 0;
cind[i] = 0;
dind[i] = 0;
}

if (0 <= (sth = sqlo_prepare(dbh, insert_stmt)))
{
if (SQLO_SUCCESS !=
(sqlo_bind_by_name(sth, ":NKEY", SQLOT_INT, &nkey[0], sizeof(int), NULL,1) ||
sqlo_bind_by_name(sth, ":CKEY", SQLOT_STR, &ckey[0], 6, NULL,1) ||
sqlo_bind_by_name(sth, ":NVAL", SQLOT_FLT, &nval[0], sizeof(double), nind,1) ||
sqlo_bind_by_name(sth, ":CVAL", SQLOT_STR, &cval[0], 21, cind,1) ||
sqlo_bind_by_name(sth, ":DVAL", SQLOT_STR, &dval[0], 11, dind,1)
))
{
printf("sqlo_bind_param failed failed: %sn", sqlo_geterror(dbh) );
return 0;
}
else
{
if (SQLO_SUCCESS != sqlo_execute(sth, MAX_ITERS))
{
printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return 0;
}
}
#ifdef CLOSE_CURSOR
if (SQLO_SUCCESS != sqlo_close(sth))
{
printf("sqlo_close failed: %sn", sqlo_geterror(dbh) );
return 0;
}
#endif
do_select(dbh);
}
else
{
printf("sqlo_open failed: Status: %d, %sn", sth, sqlo_geterror(dbh) );
return 0;
}

if (SQLO_SUCCESS != (status = sqlo_commit(dbh))) {
printf("commit failed (%d): %sn", status, sqlo_geterror(dbh));
return 0;
}
return 1;
}

/*-------------------------------------------------------------------------
* test_array_insert2 (by pos)
*-----------------------------------------------------------------------*/
int test_array_insert2( int dbh )
{
int nkey[MAX_ITERS];
char ckey[MAX_ITERS][6];
double nval[MAX_ITERS];
char cval[MAX_ITERS][21];
char dval[MAX_ITERS][11];

short nind[MAX_ITERS];
short cind[MAX_ITERS];
short dind[MAX_ITERS];

int i, j;
int status;
int sth;

char * insert_stmt =
"INSERT INTO T_SQLORA_TEST (NKEY, CKEY, NVAL, CVAL, DVAL) VALUES (:NKEY, :CKEY, :NVAL, :CVAL, :DVAL)";

printf("Testing Array Insert ( bind by pos)n");

if (!create_table(sth))
return (0);

/* setup bind arrays */
for ( i = 0 ; i < MAX_ITERS; i++)
{
nkey[i] = i+1;
sprintf(ckey[i], "%c", A + i % 26 );
nval[i] = 1234567890.0 + i / 1000.0;
for (j = 0; j < 20; j++)
cval[i][j] = a + i % 26;
cval[i][20] = ;
sprintf(dval[i], "%02d-JUL-00", (i % 30) + 1);

nind[i] = 0;
cind[i] = 0;
dind[i] = 0;
}

if (0 <= (sth = sqlo_prepare(dbh, insert_stmt)))
{
if (SQLO_SUCCESS !=
(sqlo_bind_by_pos(sth, 1, SQLOT_INT, &nkey[0], sizeof(int), NULL,1) ||
sqlo_bind_by_pos(sth, 2, SQLOT_STR, &ckey[0], 6, NULL,1) ||
sqlo_bind_by_pos(sth, 3, SQLOT_FLT, &nval[0], sizeof(double), nind,1) ||
sqlo_bind_by_pos(sth, 4, SQLOT_STR, &cval[0], 21, cind,1) ||
sqlo_bind_by_pos(sth, 5, SQLOT_STR, &dval[0], 11, dind,1)
))
{
printf("sqlo_bind_param failed failed: %sn", sqlo_geterror(dbh) );
return 0;
}
else
{
if (SQLO_SUCCESS != sqlo_execute(sth, MAX_ITERS))
{
printf("sqlo_execute failed: %sn", sqlo_geterror(dbh) );
return 0;
}
}
#ifdef CLOSE_CURSOR
if (SQLO_SUCCESS != sqlo_close(sth))
{
printf("sqlo_close failed: %sn", sqlo_geterror(dbh) );
return 0;
}
#endif
do_select(dbh);
}
else
{
printf("sqlo_open failed: Status: %d, %sn", sth, sqlo_geterror(dbh) );
return 0;
}

if (SQLO_SUCCESS != (status = sqlo_commit(dbh))) {
printf("commit failed (%d): %sn", status, sqlo_geterror(dbh));
return 0;
}
return 1;
}

/*-------------------------------------------------------------------------
* test_exists
*-----------------------------------------------------------------------*/
int test_exists(int dbh)
{
int status;

if (SQLO_SUCCESS ==
(status = sqlo_exists(dbh, "T_SQLORA_TEST", "CKEY", "B", NULL)))
printf("test_exists(1) okn");
else
{
printf("test_exists(1) failed: %sn", sqlo_geterror(dbh));
return 0;
}

if (SQLO_SUCCESS ==
(status = sqlo_exists(dbh, "T_SQLORA_TEST", "CKEY", "xxx", NULL)))
printf("test_exists(2) failedn");
else
{
if (status != SQLO_NO_DATA)
{
printf("test_exists(2) failed: %sn", sqlo_geterror(dbh));
return 0;
}
else
printf("test_exists(2) okn");

}

return 1;
}

/*-------------------------------------------------------------------------
* test_count
*-----------------------------------------------------------------------*/
int test_count(int dbh)
{
int count;

if ((count = sqlo_count(dbh, "T_SQLORA_TEST", NULL, NULL, NULL)))
printf("test_count(1) okn");
else
{
printf("test_count(1) failed: %sn", sqlo_geterror(dbh));
return 0;
}

if ((count = sqlo_count(dbh, "T_SQLORA_TEST", "CKEY", "xxx", NULL)))
{
printf("test_count(2) failed (count=%d)n", count);
}
else
{
if (count < 0)
{
printf("test_count(2) failed (count=%d): %sn", count, sqlo_geterror(dbh));
return 0;
}
else
printf("test_count(2) okn");
}

return 1;
}

/*-------------------------------------------------------------------------
* int cleanup
*-----------------------------------------------------------------------*/
int cleanup(int dbh)
{
/* ignore all errors maybe they weren created */
sqlo_exec(dbh, "DROP TABLE T_SQLORA_TEST");
sqlo_exec(dbh, "DROP PACKAGE BODY SQLORA_TEST");
sqlo_exec(dbh, "DROP PACKAGE SQLORA_TEST");
return 1;
}
/*=========================================================================
* main
*=======================================================================*/
int main (int argc, char * argv[])
{
int status;
int dbh[MAX_LOOPS];
char * cstr;
int i;

printf("-------------------------------------------------------------nn");

if (argc > 1)
cstr = argv[1];
else
cstr = "scott/tiger";

status = sqlo_init(0);

if (SQLO_SUCCESS != status)
{
printf ("sql_init failed. Exitingn");
exit(1);
}

for (i = 0; i < MAX_LOOPS; i++)
{
status = sqlo_connect(&dbh[i], cstr);

if (SQLO_SUCCESS == status)
printf("Connected. dbh[i]=%dn", dbh[i]);
else
{
printf("connect failed with status: %d, %sn", status
, sqlo_geterror(dbh[i]));
exit(1);
}

if (!test_plsql(dbh[i]))
exit(1);

if (!test_insert(dbh[i]))
exit(1);

if (!test_array_insert(dbh[i])) /* bind by name */
exit(1);

if (!test_array_insert2(dbh[i])) /* bind by pos */
exit(1);

if (!test_exists(dbh[i]))
exit(1);

if (!test_count(dbh[i]))
exit(1);

if (!test_reopen(dbh[i]))
exit(1);

if (!test_select2(dbh[i]))
exit(1);

cleanup(dbh[i]);

if (SQLO_SUCCESS != (status = sqlo_rollback(dbh[i])))
printf("rollback failed (%d): %sn", status, sqlo_geterror(dbh[i]));

}

for (i = 0; i < MAX_LOOPS; i++)
{
if (SQLO_SUCCESS != sqlo_finish(dbh[i]))
{
printf("sql_finish failed for dbh: %dn%sn", dbh[i],
sqlo_geterror(dbh[i]));
exit(1);
}
}
return (0);
}

原文轉自：http://www.kjueaiud.com