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. Exiting/n");
    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: %s/n", sqlo_geterror(dbh));
    return 0;
    }
    while (0 == sqlo_fetch(sd,1))
    { v = sqlo_values(sd, NULL, 1);
    printf("Result: %s/n",v);
    }
    if (0 > sqlo_close(sd))
    { printf("sqlo_open failed: %s/n", 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: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    }
    if (SQLO_SUCCESS != sqlo_execute(sth, 1))
    { printf("sqlo_execute failed: %s/n", 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 <stdio.h>
    #include <unistd.h>
    #include <signal.h>

    #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: %s/n%s/n", sqlo_geterror(dbh),
    create_table);
    return 0;
    }
    printf("Table T_SQLORA_TEST created/n");
    }

    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: %s/n", 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%11s/n", v[0], v[1], v[2], v[3], v[4]);
    }

    #ifdef CLOSE_CURSOR
    if (0 > sqlo_close(sd))
    {
    printf("sqlo_open failed: %s/n", 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 methods/n");
    /* Select all and display */
    if (0>(sth = sqlo_prepare(dbh, select_stmt)))
    {
    printf("sqlo_prepare failed: %s/n", 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: %s/n", 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: %s/n", 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: %s/n", 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 rows/n", rows_fetched);

    printf("Fetched all in one go/n");

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

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

    printf("%3d %5s %19f %20s %10s/n",
    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: %s/n", 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 rows/n", rows_fetched);
    }
    else if (status == SQLO_SUCCESS)
    {
    printf("sqlo_fetch fetched %d rows/n", rows_fetched);

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

    for (i = 0; i < rows_fetched; ++i)
    {
    printf("%3d %5s %19f %20s %10s/n",
    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: %s/n", 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: %s/n", sqlo_geterror(dbh));
    return 0;
    }

    while (0 == sqlo_fetch(sth,1))
    {
    v = sqlo_values(sth, NULL, 1);
    printf("%s|%6s%19s%21s%11s/n", 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: %s/n", sqlo_geterror(dbh));
    return 0;
    }

    printf("Fetch again/n");

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

    #ifdef CLOSE_CURSOR
    if (0 > sqlo_close(sth))
    {
    printf("sqlo_open failed: %s/n", 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 IS/n"
    " 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 IS/n"
    " PROCEDURE P1(ip1 IN NUMBER, ip2 IN NUMBER, op1 OUT NUMBER, op2 OUT VARCHAR)/n"
    " IS /n"
    " BEGIN/n"
    " op1 := TO_NUMBER(ip1) + ip2;/n"
    " op2 := TO_CHAR(op1);/n"
    " END;/n"
    "END;/n";

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

    int sth;

    printf("Testing PL/SQL procedure/n");

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

    printf("Package created/n");

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

    printf("Package body created/n");

    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: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    else
    {
    if (SQLO_SUCCESS != sqlo_execute(sth, 1))
    {
    printf("sqlo_execute failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    }
    #ifdef CLOSE_CURSOR
    if (SQLO_SUCCESS != sqlo_close(sth))
    {
    printf("sqlo_close failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    #endif
    printf ("ip1: %.16f, ip2: %d, op1: %d, op2: %s/n", ip1, ip2, op1, op2);
    }
    else
    {
    printf("sqlo_open failed: Status: %d, %s/n", 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: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    else
    {
    if (SQLO_SUCCESS != sqlo_execute(sth, 1))
    {
    printf("sqlo_execute failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    }
    #ifdef CLOSE_CURSOR
    if (SQLO_SUCCESS != sqlo_close(sth))
    {
    printf("sqlo_close failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    #endif
    do_select(dbh);
    }
    else
    {
    printf("sqlo_open failed: Status: %d, %s/n", sth, sqlo_geterror(dbh) );
    return 0;
    }

    printf("finished test_insert/n");
    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: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    else
    {
    if (SQLO_SUCCESS != sqlo_execute(sth, MAX_ITERS))
    {
    printf("sqlo_execute failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    }
    #ifdef CLOSE_CURSOR
    if (SQLO_SUCCESS != sqlo_close(sth))
    {
    printf("sqlo_close failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    #endif
    do_select(dbh);
    }
    else
    {
    printf("sqlo_open failed: Status: %d, %s/n", sth, sqlo_geterror(dbh) );
    return 0;
    }

    if (SQLO_SUCCESS != (status = sqlo_commit(dbh))) {
    printf("commit failed (%d): %s/n", 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: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    else
    {
    if (SQLO_SUCCESS != sqlo_execute(sth, MAX_ITERS))
    {
    printf("sqlo_execute failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    }
    #ifdef CLOSE_CURSOR
    if (SQLO_SUCCESS != sqlo_close(sth))
    {
    printf("sqlo_close failed: %s/n", sqlo_geterror(dbh) );
    return 0;
    }
    #endif
    do_select(dbh);
    }
    else
    {
    printf("sqlo_open failed: Status: %d, %s/n", sth, sqlo_geterror(dbh) );
    return 0;
    }

    if (SQLO_SUCCESS != (status = sqlo_commit(dbh))) {
    printf("commit failed (%d): %s/n", 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) ok/n");
    else
    {
    printf("test_exists(1) failed: %s/n", sqlo_geterror(dbh));
    return 0;
    }

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

    }

    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) ok/n");
    else
    {
    printf("test_count(1) failed: %s/n", 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): %s/n", count, sqlo_geterror(dbh));
    return 0;
    }
    else
    printf("test_count(2) ok/n");
    }

    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("-------------------------------------------------------------/n/n");

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

    status = sqlo_init(0);

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

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

    if (SQLO_SUCCESS == status)
    printf("Connected. dbh[i]=%d/n", dbh[i]);
    else
    {
    printf("connect failed with status: %d, %s/n", 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): %s/n", 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: %d/n%s/n", dbh[i],
    sqlo_geterror(dbh[i]));
    exit(1);
    }
    }
    return (0);
    }
Logo

更多推荐