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网站上得到，也可从http://www.china-linux.org上下载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) 执行查询语句。“iterations”可设为“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：nick_chen@yeah.net

/*————————————————————————-
* 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);
}