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 $ */