ex9.c

Example for calling PL/SQL blocks.

00001 /* $Id: ex9.c 221 2002-08-24 12:54:47Z kpoitschke $ */
00002 #include <stdio.h>
00003 #include <stdlib.h>
00004 #include "examples.h"
00005 
00006 static void create_sp __P((sqlo_db_handle_t dbh));
00007 
00008 int call_plsql(sqlo_db_handle_t dbh)
00009 {
00010   double ip1;
00011   int  ip2;
00012   char op[80];
00013   sqlo_stmt_handle_t sth = SQLO_STH_INIT;
00014 
00015   /* the stored procecdure call */
00016   char * stmt = 
00017     "BEGIN\n"
00018     "    EX9(:ip1, :ip2, :op);\n"
00019     "END;\n";
00020 
00021 
00022   create_sp(dbh);               /* create the test stored procedure */
00023 
00024   ip1 = 1.5;
00025   ip2 = 20;
00026 
00027 
00028   /* parse the statement */
00029   if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) {
00030     /* bind all variables */
00031     if (SQLO_SUCCESS != 
00032         (sqlo_bind_by_name(sth, ":ip1", SQLOT_FLT, &ip1, sizeof(ip1), 0, 0) ||
00033          sqlo_bind_by_name(sth, ":ip2", SQLOT_INT, &ip2, sizeof(ip2), 0, 0) ||
00034          sqlo_bind_by_name(sth, ":op", SQLOT_STR, &op, sizeof(op), 0, 0)
00035          )) {
00036 
00037       error_exit(dbh, "sqlo_bind_by_name");
00038     } else {
00039       /* execute the call */
00040       if (SQLO_SUCCESS != sqlo_execute(sth, 1))
00041         error_exit(dbh, "sqlo_execute");
00042     }
00043 
00044     /* print the result */
00045     if (atof(op) != (ip1 + ip2))
00046       printf("Stored procudure returned wrong result %s, expected %6.2f\n",
00047              op, (ip1 + ip2));
00048 
00049     if (SQLO_SUCCESS != sqlo_close(sth))
00050       error_exit(dbh, "sqlo_execute");
00051 
00052   } else {
00053     error_exit(dbh, "sqlo_prepare");
00054   }
00055 
00056   if ( 0 > sqlo_exec(dbh, "DROP PROCEDURE EX9"))
00057     error_exit(dbh, "sqlo_exec");
00058 
00059   return 1;
00060 }
00061 
00062 /* creates the stored procedure used above */
00063 static void
00064 create_sp(sqlo_db_handle_t dbh)
00065 {
00066   char * stmt =
00067     "CREATE OR REPLACE PROCEDURE EX9(ipNum1 IN NUMBER, ipNum2 IN NUMBER, opStr OUT VARCHAR)\n"
00068     "IS\n"
00069     "BEGIN\n"
00070     "  opStr := TO_CHAR(ipNum1 + ipNum2);\n"
00071     "END;\n";
00072 
00073   if (SQLO_SUCCESS != sqlo_exec(dbh, stmt))
00074     error_exit(dbh, stmt);
00075 
00076 }
00077 
00078 /* $Id: ex9.c 221 2002-08-24 12:54:47Z kpoitschke $ */

Generated on Mon May 21 13:43:28 2007 for libsqlora8 by  doxygen 1.4.7