#ifdef rcsid
static char *rcsid =
"$header: cdemodr1.c 14-jul-99.13:07:43 mjaeger exp $ ";
#endif /* rcsid */
/* 凯发app官方网站 copyright (c) 1997, 1999, oracle corporation. all rights reserved.
*/
/*
name
cdemord1.c - c demo program for dml with returning clause - #1.
description
this demo program demonstrates the use of insert/update/delete
statements with a returning clause in it.
public function(s)
private function(s)
returns
notes
adopted from tkp8rv1.c; need to run cdemodr1.sql before running
this demo program.
modified (mm/dd/yy)
svedala 10/18/99 -
mjaeger 07/14/99 - bug 808870: occs: convert tabs, no long lines
svedala 09/09/98 - lines longer than 79 chars reformatted - bug 722491
azhao 08/19/97 - remove explicit bindhp allocation
echen 07/30/97 - fix bug 516406
azhao 05/30/97 - creation
*/
#include <cdemodr1.h>
/*------------------------ global variables -------------------------------*/
static boolean logged_on = false;
/* tab1 columns */
static int in1[maxiter]; /* for integer */
static text in2[maxiter][40]; /* for char(40) */
static text in3[maxiter][40]; /* for varchar2(40) */
static float in4[maxiter]; /* for float */
static int in5[maxiter]; /* for decimal */
static float in6[maxiter]; /* for decimal(8,3) */
static int in7[maxiter]; /* for numeric */
static float in8[maxiter]; /* for numeric(7,2) */
static ub1 in9[maxiter][7]; /* for date */
static ub1 in10[maxiter][40]; /* for raw(40) */
/* output buffers */
static int *p1[maxiter]; /* for integer */
static text *p2[maxiter]; /* for char(40) */
static text *p3[maxiter]; /* for varchar2(40) */
static float *p4[maxiter]; /* for float */
static int *p5[maxiter]; /* for decimal */
static float *p6[maxiter]; /* for decimal(8,3) */
static int *p7[maxiter]; /* for numeric */
static float *p8[maxiter]; /* for numeric(7,2) */
static ub1 *p9[maxiter]; /* for date */
static ub1 *p10[maxiter]; /* for raw(40) */
static short *ind[maxcols][maxiter]; /* indicators */
static ub2 *rc[maxcols][maxiter]; /* return codes */
static ub4 *rl[maxcols][maxiter]; /* return lengths */
/* skip values for binding tab1 */
static ub4 s1 = (ub4) sizeof(in1[0]);
static ub4 s2 = (ub4) sizeof(in2[0]);
static ub4 s3 = (ub4) sizeof(in3[0]);
static ub4 s4 = (ub4) sizeof(in4[0]);
static ub4 s5 = (ub4) sizeof(in5[0]);
static ub4 s6 = (ub4) sizeof(in6[0]);
static ub4 s7 = (ub4) sizeof(in7[0]);
static ub4 s8 = (ub4) sizeof(in8[0]);
static ub4 s9 = (ub4) sizeof(in9[0]);
static ub4 s10= (ub4) sizeof(in10[0]);
/* rows returned in each iteration */
static ub2 rowsret[maxiter];
/* indicator skips */
static ub4 indsk[maxcols] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
/* return length skips */
static ub4 rlsk[maxcols] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
/* return code skips */
static ub4 rcsk[maxcols] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
static int lowc1[maxiter], highc1[maxiter];
static ub4 pos[maxcols];
static ocierror *errhp;
/*------------------------end of global variables--------------------*/
/*========================== utility functions ======================*/
/*
* these functions are generic functions that can be used in any
* oci program.
*/
/* ----------------------------------------------------------------- */
/* initialize environment, allocate handles */
/* ----------------------------------------------------------------- */
sword init_handles(envhp, svchp, errhp, srvhp, authp, init_mode)
ocienv **envhp;
ocisvcctx **svchp;
ocierror **errhp;
ociserver **srvhp;
ocisession **authp;
ub4 init_mode;
{
(void) printf("environment setup ....\n");
/* initialize the oci process */
if (ociinitialize(init_mode, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 ))
{
(void) printf("failed: ociinitialize()\n");
return oci_error;
}
/* inititialize the oci environment */
if (ocienvinit((ocienv **) envhp, (ub4) oci_default,
(size_t) 0, (dvoid **) 0 ))
{
(void) printf("failed: ocienvinit()\n");
return oci_error;
}
/* allocate a service handle */
if (ocihandlealloc((dvoid *) *envhp, (dvoid **) svchp,
(ub4) oci_htype_svcctx, (size_t) 0, (dvoid **) 0))
{
(void) printf("failed: ocihandlealloc() on svchp\n");
return oci_error;
}
/* allocate an error handle */
if (ocihandlealloc((dvoid *) *envhp, (dvoid **) errhp,
(ub4) oci_htype_error, (size_t) 0, (dvoid **) 0))
{
(void) printf("failed: ocihandlealloc() on errhp\n");
return oci_error;
}
/* allocate a server handle */
if (ocihandlealloc((dvoid *) *envhp, (dvoid **) srvhp,
(ub4) oci_htype_server, (size_t) 0, (dvoid **) 0))
{
(void) printf("failed: ocihandlealloc() on srvhp\n");
return oci_error;
}
/* allocate a authentication handle */
if (ocihandlealloc((dvoid *) *envhp, (dvoid **) authp,
(ub4) oci_htype_session, (size_t) 0, (dvoid **) 0))
{
(void) printf("failed: ocihandlealloc() on authp\n");
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* attach to server with a given mode. */
/* ----------------------------------------------------------------- */
sword attach_server(mode, srvhp, errhp, svchp)
ub4 mode;
ociserver *srvhp;
ocierror *errhp;
ocisvcctx *svchp;
{
text *cstring = (text *)"";
if (ociserverattach(srvhp, errhp, (text *) cstring,
(sb4) strlen((char *)cstring), (ub4) oci_default))
{
(void) printf("failed: ociserverattach()\n");
return oci_error;
}
/* set the server handle in the service handle */
if (ociattrset((dvoid *) svchp, (ub4) oci_htype_svcctx,
(dvoid *) srvhp, (ub4) 0, (ub4) oci_attr_server, errhp))
{
(void) printf("failed: ociattrset() server attribute\n");
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* logon to the database using given username, password & credentials*/
/* ----------------------------------------------------------------- */
sword log_on(authp, errhp, svchp, uid, pwd, credt, mode)
ocisession *authp;
ocierror *errhp;
ocisvcctx *svchp;
text *uid;
text *pwd;
ub4 credt;
ub4 mode;
{
/* set attributes in the authentication handle */
if (ociattrset((dvoid *) authp, (ub4) oci_htype_session,
(dvoid *) uid, (ub4) strlen((char *) uid),
(ub4) oci_attr_username, errhp))
{
(void) printf("failed: ociattrset() userid\n");
return oci_error;
}
if (ociattrset((dvoid *) authp, (ub4) oci_htype_session,
(dvoid *) pwd, (ub4) strlen((char *) pwd),
(ub4) oci_attr_password, errhp))
{
(void) printf("failed: ociattrset() passwd\n");
return oci_error;
}
(void) printf("logging on as %s ....\n", uid);
if (ocisessionbegin(svchp, errhp, authp, credt, mode))
{
(void) printf("failed: ociattrset() passwd\n");
return oci_error;
}
(void) printf("%s logged on.\n", uid);
/* set the authentication handle in the service handle */
if (ociattrset((dvoid *) svchp, (ub4) oci_htype_svcctx,
(dvoid *) authp, (ub4) 0, (ub4) oci_attr_session, errhp))
{
(void) printf("failed: ociattrset() session\n");
return oci_error;
}
return oci_success;
}
/*---------------------------------------------------------------------*/
/* allocate all required bind handles */
/*---------------------------------------------------------------------*/
sword init_bind_handle(stmthp, bndhp, nbinds)
ocistmt *stmthp;
ocibind *bndhp[];
int nbinds;
{
int i;
/*
* this function init the specified number of bind handles
* from the given statement handle.
*/
for (i = 0; i < nbinds; i)
bndhp[i] = (ocibind *) 0;
return oci_success;
}
/* ----------------------------------------------------------------- */
/* print the returned raw data. */
/* ----------------------------------------------------------------- */
void print_raw(raw, rawlen)
ub1 *raw;
ub4 rawlen;
{
ub4 i;
ub4 lim;
ub4 clen = 0;
if (rawlen > 120)
{
ub4 llen = rawlen;
while (llen > 120)
{
lim = clen 120;
for(i = clen; i < lim; i)
(void) printf(".2x", (ub4) raw[i] & 0xff);
(void) printf("\n");
llen -= 120;
clen = 120;
}
lim = clen llen;
}
else
lim = rawlen;
for(i = clen; i < lim; i)
(void) printf(".2x", (ub4) raw[i] & 0xff);
(void) printf("\n");
return;
}
/* ----------------------------------------------------------------- */
/* free the specified handles */
/* ----------------------------------------------------------------- */
void free_handles(envhp, svchp, srvhp, errhp, authp, stmthp)
ocienv *envhp;
ocisvcctx *svchp;
ociserver *srvhp;
ocierror *errhp;
ocisession *authp;
ocistmt *stmthp;
{
(void) printf("freeing handles ...\n");
if (srvhp)
(void) ocihandlefree((dvoid *) srvhp, (ub4) oci_htype_server);
if (svchp)
(void) ocihandlefree((dvoid *) svchp, (ub4) oci_htype_svcctx);
if (errhp)
(void) ocihandlefree((dvoid *) errhp, (ub4) oci_htype_error);
if (authp)
(void) ocihandlefree((dvoid *) authp, (ub4) oci_htype_session);
if (stmthp)
(void) ocihandlefree((dvoid *) stmthp, (ub4) oci_htype_stmt);
if (envhp)
(void) ocihandlefree((dvoid *) envhp, (ub4) oci_htype_env);
return;
}
/* ----------------------------------------------------------------- */
/* print the error message */
/* ----------------------------------------------------------------- */
void report_error(errhp)
ocierror *errhp;
{
text msgbuf[512];
sb4 errcode = 0;
(void) ocierrorget((dvoid *) errhp, (ub4) 1, (text *) null, &errcode,
msgbuf, (ub4) sizeof(msgbuf), (ub4) oci_htype_error);
(void) printf("error code = %d\n", errcode);
(void) printf("%.*s\n", 512, msgbuf);
return;
}
/*-------------------------------------------------------------------*/
/* logout and detach from the server */
/*-------------------------------------------------------------------*/
void logout_detach_server(svchp, srvhp, errhp, authp, userid)
ocisvcctx *svchp;
ociserver *srvhp;
ocierror *errhp;
ocisession *authp;
text *userid;
{
if (ocisessionend(svchp, errhp, authp, (ub4) 0))
{
(void) printf("failed: ocisessionend()\n");
report_error(errhp);
}
(void) printf("%s logged off.\n", userid);
if (ociserverdetach(srvhp, errhp, (ub4) oci_default))
{
(void) printf("failed: ocisessionend()\n");
report_error(errhp);
}
(void) printf("detached from server.\n");
return;
}
/*---------------------------------------------------------------------*/
/* finish demo and clean up */
/*---------------------------------------------------------------------*/
sword finish_demo(loggedon, envhp, svchp, srvhp, errhp, authp, stmthp, userid)
boolean loggedon;
ocienv *envhp;
ocisvcctx *svchp;
ociserver *srvhp;
ocierror *errhp;
ocisession *authp;
ocistmt *stmthp;
text *userid;
{
if (loggedon)
logout_detach_server(svchp, srvhp, errhp, authp, userid);
free_handles(envhp, svchp, srvhp, errhp, authp, stmthp);
return oci_success;
}
/*===================== end of utility functions ======================*/
/*========================= main ======================================*/
int main(argc, argv)
int argc;
char *argv[];
{
text *username = (text *)"scott";
text *password = (text *)"tiger";
ocienv *envhp;
ociserver *srvhp;
ocisvcctx *svchp;
ocisession *authp;
ocistmt *stmthp = (ocistmt *) null;
ocibind *bndhp[maxbinds];
int i;
/* initialize the environment and allocate handles */
if (init_handles(&envhp, &svchp, &errhp, &srvhp, &authp, (ub4)oci_default))
{
(void) printf("failed: init_handles()\n");
return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
stmthp, username);
}
/* attach to the database server */
if (attach_server((ub4) oci_default, srvhp, errhp, svchp))
{
(void) printf("failed: attach_server()\n");
return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
stmthp, username);
}
/* logon to the server and begin a session */
if (log_on(authp, errhp, svchp, username, password,
(ub4) oci_cred_rdbms, (ub4) oci_default))
{
(void) printf("failed: log_on()\n");
return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
stmthp, username);
}
logged_on = true;
/* allocate a statement handle */
if (ocihandlealloc((dvoid *)envhp, (dvoid **) &stmthp,
(ub4)oci_htype_stmt, (const size_t) 0, (dvoid **) 0))
{
(void) printf("failed: alloc statement handle\n");
return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
stmthp, username);
}
/* bind handles will be implicitly allocated in the bind calls */
/* need to initialize them to null prior to first usage in bind calls */
for (i = 0; i < maxbinds; i)
bndhp[i] = (ocibind *) 0;
/* demonstrate insert with returning clause */
if (demo_insert(svchp, stmthp, bndhp, errhp))
(void) printf("failed: demo_insert()\n");
else
(void) printf("success: demo_insert()\n");
/* demonstrate update with returning clause */
if (demo_update(svchp, stmthp, bndhp, errhp))
(void) printf("failed: demo_update()\n");
else
(void) printf("success: demo_update()\n");
/* demonstrate delete with returning clause */
if (demo_delete(svchp, stmthp, bndhp, errhp))
(void) printf("failed: demo_delete()\n");
else
(void) printf("success: demo_delete()\n");
/* clean up */
return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
stmthp, username);
}
/* =================== end main =====================================*/
/* ===================== local functions ============================*/
/* ----------------------------------------------------------------- */
/* bind all the columns of tab1 by positions. */
/* ----------------------------------------------------------------- */
static sword bind_pos(ocistmt *stmthp, ocibind *bndhp[], ocierror *errhp)
{
if (ocibindbypos(stmthp, &bndhp[0], errhp, (ub4) 1,
(dvoid *) &in1[0], (sb4) sizeof(in1[0]), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[1], errhp, (ub4) 2,
(dvoid *) in2[0], (sb4) sizeof(in2[0]), sqlt_afc,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[2], errhp, (ub4) 3,
(dvoid *) in3[0], (sb4) sizeof(in3[0]), sqlt_chr,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[3], errhp, (ub4) 4,
(dvoid *) &in4[0], (sb4) sizeof(in4[0]), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[4], errhp, (ub4) 5,
(dvoid *) &in5[0], (sb4) sizeof(in5[0]), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[5], errhp, (ub4) 6,
(dvoid *) &in6[0], (sb4) sizeof(in6[0]), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[6], errhp, (ub4) 7,
(dvoid *) &in7[0], (sb4) sizeof(in7[0]), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[7], errhp, (ub4) 8,
(dvoid *) &in8[0], (sb4) sizeof(in8[0]), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[8], errhp, (ub4) 9,
(dvoid *) in9[0], (sb4) sizeof(in9[0]), sqlt_dat,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbypos(stmthp, &bndhp[9], errhp, (ub4) 10,
(dvoid *) in10[0], (sb4) sizeof(in10[0]), sqlt_bin,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default))
{
(void) printf("failed: ocibindbypos()\n");
report_error(errhp);
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* bind all the columns of tab1 by name. */
/* ----------------------------------------------------------------- */
static sword bind_name(ocistmt *stmthp, ocibind *bndhp[], ocierror *errhp)
{
if (ocibindbyname(stmthp, &bndhp[10], errhp,
(text *) ":out1", (sb4) strlen((char *) ":out1"),
(dvoid *) 0, (sb4) sizeof(int), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[11], errhp,
(text *) ":out2", (sb4) strlen((char *) ":out2"),
(dvoid *) 0, (sb4) maxcollen, sqlt_afc,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[12], errhp,
(text *) ":out3", (sb4) strlen((char *) ":out3"),
(dvoid *) 0, (sb4) maxcollen, sqlt_chr,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[13], errhp,
(text *) ":out4", (sb4) strlen((char *) ":out4"),
(dvoid *) 0, (sb4) sizeof(float), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[14], errhp,
(text *) ":out5", (sb4) strlen((char *) ":out5"),
(dvoid *) 0, (sb4) sizeof(int), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[15], errhp,
(text *) ":out6", (sb4) strlen((char *) ":out6"),
(dvoid *) 0, (sb4) sizeof(float), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[16], errhp,
(text *) ":out7", (sb4) strlen((char *) ":out7"),
(dvoid *) 0, (sb4) sizeof(int), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[17], errhp,
(text *) ":out8", (sb4) strlen((char *) ":out8"),
(dvoid *) 0, (sb4) sizeof(float), sqlt_flt,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[18], errhp,
(text *) ":out9", (sb4) strlen((char *) ":out9"),
(dvoid *) 0, (sb4) datbuflen, sqlt_dat,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec)
|| ocibindbyname(stmthp, &bndhp[19], errhp,
(text *) ":out10", (sb4) strlen((char *) ":out10"),
(dvoid *) 0, (sb4) maxcollen, sqlt_bin,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_data_at_exec))
{
(void) printf("failed: ocibindbyname()\n");
report_error(errhp);
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* bind array structs for tab1 columns. */
/* ----------------------------------------------------------------- */
static sword bind_array(ocibind *bndhp[], ocierror *errhp)
{
if (ocibindarrayofstruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0])
|| ocibindarrayofstruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1])
|| ocibindarrayofstruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2])
|| ocibindarrayofstruct(bndhp[3], errhp, s4, indsk[3], rlsk[3], rcsk[3])
|| ocibindarrayofstruct(bndhp[4], errhp, s5, indsk[4], rlsk[4], rcsk[4])
|| ocibindarrayofstruct(bndhp[5], errhp, s6, indsk[5], rlsk[5], rcsk[5])
|| ocibindarrayofstruct(bndhp[6], errhp, s7, indsk[6], rlsk[6], rcsk[6])
|| ocibindarrayofstruct(bndhp[7], errhp, s8, indsk[7], rlsk[7], rcsk[7])
|| ocibindarrayofstruct(bndhp[8], errhp, s9, indsk[8], rlsk[8], rcsk[8])
|| ocibindarrayofstruct(bndhp[9], errhp, s10, indsk[9], rlsk[9], rcsk[9]))
{
(void) printf("failed: ocibindarrayofstruct()\n");
report_error(errhp);
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* bind dynamic for returning tab1 columns. */
/* ----------------------------------------------------------------- */
static sword bind_dynamic(ocibind *bndhp[], ocierror *errhp)
{
/*
* note here that both in & out bind callback functions have to be
* provided. however, since the bind variables in the returning
* clause are pure out binds the in callback fuctions (cbf_no_data)
* is essentially a "do-nothing" function.
*
* also note here that although in this demonstration the in and out
* callback functions are same, in practice you can have a different
* callback function for each bind handle.
*/
ub4 i;
for (i = 0; i < maxcols; i)
pos[i] = i;
if (ocibinddynamic(bndhp[10], errhp, (dvoid *) &pos[0], cbf_no_data,
(dvoid *) &pos[0], cbf_get_data)
|| ocibinddynamic(bndhp[11], errhp, (dvoid *) &pos[1], cbf_no_data,
(dvoid *) &pos[1], cbf_get_data)
|| ocibinddynamic(bndhp[12], errhp, (dvoid *) &pos[2], cbf_no_data,
(dvoid *) &pos[2], cbf_get_data)
|| ocibinddynamic(bndhp[13], errhp, (dvoid *) &pos[3], cbf_no_data,
(dvoid *) &pos[3], cbf_get_data)
|| ocibinddynamic(bndhp[14], errhp, (dvoid *) &pos[4], cbf_no_data,
(dvoid *) &pos[4], cbf_get_data)
|| ocibinddynamic(bndhp[15], errhp, (dvoid *) &pos[5], cbf_no_data,
(dvoid *) &pos[5], cbf_get_data)
|| ocibinddynamic(bndhp[16], errhp, (dvoid *) &pos[6], cbf_no_data,
(dvoid *) &pos[6], cbf_get_data)
|| ocibinddynamic(bndhp[17], errhp, (dvoid *) &pos[7], cbf_no_data,
(dvoid *) &pos[7], cbf_get_data)
|| ocibinddynamic(bndhp[18], errhp, (dvoid *) &pos[8], cbf_no_data,
(dvoid *) &pos[8], cbf_get_data)
|| ocibinddynamic(bndhp[19], errhp, (dvoid *) &pos[9], cbf_no_data,
(dvoid *) &pos[9], cbf_get_data))
{
(void) printf("failed: ocibinddynamic()\n");
report_error(errhp);
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* bind input variables. */
/* ----------------------------------------------------------------- */
static sword bind_input(ocistmt *stmthp, ocibind *bndhp[], ocierror *errhp)
{
/* bind the input data by positions */
if (bind_pos(stmthp, bndhp, errhp))
return oci_error;
/* bind input array attributes*/
return (bind_array(bndhp, errhp));
}
/* ----------------------------------------------------------------- */
/* bind output variables. */
/* ----------------------------------------------------------------- */
static sword bind_output(ocistmt *stmthp, ocibind *bndhp[], ocierror *errhp)
{
/* bind the returning bind buffers by names */
if (bind_name(stmthp, bndhp, errhp))
return oci_error;
/* bind the returning bind buffers dynamically */
return (bind_dynamic(bndhp, errhp));
}
/* ----------------------------------------------------------------- */
/* bind row indicator variables. */
/* ----------------------------------------------------------------- */
static sword bind_low_high(ocistmt *stmthp, ocibind *bndhp[], ocierror *errhp)
{
if (ocibindbyname(stmthp, &bndhp[23], errhp,
(text *) ":low", (sb4) strlen((char *) ":low"),
(dvoid *) &lowc1[0], (sb4) sizeof(lowc1[0]), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default)
|| ocibindbyname(stmthp, &bndhp[24], errhp,
(text *) ":high", (sb4) strlen((char *) ":high"),
(dvoid *) &highc1[0], (sb4) sizeof(highc1[0]), sqlt_int,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) oci_default))
{
(void) printf("failed: ocibindbyname()\n");
report_error(errhp);
return oci_error;
}
if (ocibindarrayofstruct(bndhp[23], errhp, s1, indsk[0], rlsk[0], rcsk[0])
|| ocibindarrayofstruct(bndhp[24], errhp, s1, indsk[0], rlsk[0], rcsk[0]))
{
(void) printf("failed: ocibindarrayofstruct()\n");
report_error(errhp);
return oci_error;
}
return oci_success;
}
/* ----------------------------------------------------------------- */
/* demontrate insert with returning clause. */
/* ----------------------------------------------------------------- */
static sword demo_insert(ocisvcctx *svchp, ocistmt *stmthp,
ocibind *bndhp[], ocierror *errhp)
{
int i, j;
/*
* this function inserts values for 10 columns in table tab1 and
* uses the return clause to get back the inserted column values.
* it inserts maxiter (10) such rows. thus it expects maxiter values
* for each column to be returned.
*/
/* the insert statement with returning clause */
text *sqlstmt = (text *)
"insert into tab1 values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) \
returning c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 \
into :out1, :out2, :out3, :out4, :out5, :out6, \
:out7, :out8, :out9, :out10";
/* prepare the statement */
if (ocistmtprepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) oci_ntv_syntax, (ub4) oci_default))
{
(void) printf("failed: ocistmtprepare() insert\n");
report_error(errhp);
return oci_error;
}
/* initialise the buffers for update */
for (i = 0; i < maxiter; i)
{
in1[i] = i 1;
memset((void *)in2[i], (int) 'a'i%26, (size_t) 40);
memset((void *)in3[i], (int) 'a'i%26, (size_t) 40);
in4[i] = 400.555 (float) i;
in5[i] = 500 i;
in6[i] = 600.250 (float) i;
in7[i] = 700 i;
in8[i] = 800.350 (float) i;
in9[i][0] = 119;
in9[i][1] = 185 (ub1)i%10;
in9[i][2] = (ub1)i%12 1;
in9[i][3] = (ub1)i%25 1;
in9[i][4] = 0;
in9[i][5] = 0;
in9[i][6] = 0;
for (j = 0; j < 40; j)
in10[i][j] = (ub1) (i%0x10);
rowsret[i] = 0;
}
/* bind all the input buffers to place holders (:1, :2. :3, etc ) */
if (bind_input(stmthp, bndhp, errhp))
return oci_error;
/* bind all the output buffers to place holders (:out1, :out2 etc */
if (bind_output(stmthp, bndhp, errhp))
return oci_error;
/* execute the insert statement */
if (ocistmtexecute(svchp, stmthp, errhp, (ub4) maxiter, (ub4) 0,
(const ocisnapshot*) 0, (ocisnapshot*) 0,
(ub4) oci_default))
{
(void) printf("failed: ocistmtexecute() insert\n");
report_error(errhp);
return oci_error;
}
/* commit the changes */
(void) ocitranscommit(svchp, errhp, (ub4) 0);
/* print out the values in the return rows */
(void) printf("\n\n demonstrating insert....returning \n");
(void) print_return_data((int)maxiter);
return oci_success;
}
/* ----------------------------------------------------------------- */
/* demonstrate update with returning clause. */
/* ----------------------------------------------------------------- */
static sword demo_update(ocisvcctx *svchp, ocistmt *stmthp,
ocibind *bndhp[], ocierror *errhp)
{
int i, j;
int range_size = 3; /* iterations */
/*
* this function updates columns in table tab1, for certain rows
* depending on the values of the :low and :high values in
* in the where clause. it executes this statement 3 times, (3 iterations)
* each time with a different set of values for :low and :high
* thus for each iteration, multiple rows are returned depending
* on the number of rows that matched the where clause.
*
* the rows it updates here are the rows that were inserted by the
* cdemodr1.sql script.
*/
/* the update statement with returning clause */
text *sqlstmt = (text *)
"update tab1 set c1 = c1 :1, c2 = :2, c3 = :3, \
c4 = c4 :4, c5 = c5 :5, c6 = c6 :6, \
c7 = c7 :7, c8 = c8 :8, c9 = :9, c10 = :10 \
where c1 >= :low and c1 <= :high \
returning c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 \
into :out1, :out2, :out3, :out4, :out5, :out6, \
:out7, :out8, :out9, :out10";
/* prepare the statement */
if (ocistmtprepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) oci_ntv_syntax, (ub4) oci_default))
{
(void) printf("failed: ocistmtprepare() update\n");
report_error(errhp);
return oci_error;
}
/* initialise the buffers for insertion */
for (i = 0; i < maxiter; i)
{
in1[i] = 300 i;
memset((void *)in2[i], (int) 'a'i%26, (size_t) 40);
memset((void *)in3[i], (int) 'a'i%26, (size_t) 40);
in4[i] = 400.555 (float)i;
in5[i] = 500 i;
in6[i] = 600.280 (float)i;
in7[i] = 700 i;
in8[i] = 800.620 (float)i;
in9[i][0] = 119;
in9[i][1] = 185 - (ub1)i%10;
in9[i][2] = (ub1)i%12 1;
in9[i][3] = (ub1)i%25 1;
in9[i][4] = 0;
in9[i][5] = 0;
in9[i][6] = 0;
for (j = 0; j < 40; j)
in10[i][j] = (ub1) (i%0x08);
rowsret[i] =0;
}
/* bind all the input buffers to place holders (:1, :2. :3, etc ) */
if (bind_input(stmthp, bndhp, errhp))
return oci_error;
/* bind all the output buffers to place holders (:out1, :out2 etc */
if (bind_output(stmthp, bndhp, errhp))
return oci_error;
/* bind row indicator low, high */
if (bind_low_high(stmthp, bndhp, errhp))
return oci_error;
/* update rows
between 101 and 103; -- expecting 3 rows returned (update 3 rows)
between 105 and 106; -- expecting 2 rows returned (update 2 rows)
between 109 and 113; -- expecting 5 rows returned (update 5 rows)
*/
lowc1[0] = 101;
highc1[0] = 103;
lowc1[1] = 105;
highc1[1] = 106;
lowc1[2] = 109;
highc1[2] = 113;
(void) printf("\n\n demonstrating update....returning \n");
if (ocistmtexecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
(const ocisnapshot*) 0, (ocisnapshot*) 0,
(ub4) oci_default))
{
(void) printf("failed: ocistmtexecute() update\n");
report_error(errhp);
return oci_error;
}
/* commit the changes */
(void) ocitranscommit(svchp, errhp, (ub4) 0);
/* print out the values in the return rows */
(void) print_return_data(range_size);
return oci_success;
}
/* ----------------------------------------------------------------- */
/* demonstrate delete with returning clause. */
/* ----------------------------------------------------------------- */
static sword demo_delete(ocisvcctx *svchp, ocistmt *stmthp,
ocibind *bndhp[], ocierror *errhp)
{
int i, range_size = 3; /* iterations */
sword retval;
/*
* this function deletes certain rows from table tab1
* depending on the values of the :low and :high values in
* the where clause. it executes this statement 3 times, (3 iterations)
* each time with a different set of values for :low and :high
* thus for each iteration, multiples rows are returned depending
* on the number of rows that matched the where clause.
*
* the rows it deletes here are the rows that were inserted by the
* cdemodr1.sql script.
*/
/* the delete statement with returning clause */
text *sqlstmt = (text *)
"delete from tab1 where c1 >= :low and c1 <= :high \
returning c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 \
into :out1, :out2, :out3, :out4, :out5, :out6, \
:out7, :out8, :out9, :out10";
/* prepare the statement */
if (ocistmtprepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) oci_ntv_syntax, (ub4) oci_default))
{
(void) printf("failed: ocistmtprepare() delete\n");
report_error(errhp);
return oci_error;
}
/* bind all the output buffers to place holders (:out1, :out2 etc */
if (bind_output(stmthp, bndhp, errhp))
return oci_error;
/* bind row indicator low, high */
if (bind_low_high(stmthp, bndhp, errhp))
return oci_error;
/* delete rows
between 201 and 203; -- expecting 3 rows returned (3 rows deleted)
between 205 and 209; -- expecting 5 rows returned (2 rows deleted)
between 211 and 213; -- expecting 3 rows returned (5 rows deleted)
*/
lowc1[0] = 201;
highc1[0] = 203;
lowc1[1] = 205;
highc1[1] = 209;
lowc1[2] = 211;
highc1[2] = 213;
for (i=0; i<maxiter; i)
rowsret[i] = 0;
(void) printf("\n\n demonstrating detele....returning \n");
if ((retval = ocistmtexecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
(const ocisnapshot*) 0, (ocisnapshot*) 0,
(ub4) oci_default)) != oci_success &&
retval != oci_success_with_info)
{
(void) printf("failed: ocistmtexecute() delete, retval = %d\n", retval);
report_error(errhp);
}
/* commit the changes */
(void) ocitranscommit(svchp, errhp, (ub4) 0);
/* print out the values in the return rows */
(void) print_return_data(range_size);
return oci_success;
}
/* ----------------------------------------------------------------- */
/* in bind callback that does not do any data input. */
/* ----------------------------------------------------------------- */
static sb4 cbf_no_data(dvoid *ctxp, ocibind *bindp, ub4 iter, ub4 index,
dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp)
{
/*
* this is a dummy input callback function that provides input data
* for the bind variables in the returning clause.
*/
*bufpp = (dvoid *) 0;
*alenpp = 0;
*indpp = (dvoid *) 0;
*piecep = oci_one_piece;
return oci_continue;
}
/* ----------------------------------------------------------------- */
/* outbind callback for returning data. */
/* ----------------------------------------------------------------- */
static sb4 cbf_get_data(dvoid *ctxp, ocibind *bindp, ub4 iter, ub4 index,
dvoid **bufpp, ub4 **alenp, ub1 *piecep,
dvoid **indpp, ub2 **rcodepp)
{
/*
* this is the callback function that is called to receive the out
* bind values for the bind variables in the returning clause
*/
static ub4 rows = 0;
ub4 pos = *((ub4 *)ctxp);
/* for each iteration the oci_attr_rows_returned tells us the number
* of rows returned in that iteration. so we can use this information
* to dynamically allocate storage for all the returned rows for that
* bind.
*/
if (index == 0)
{
(void) ociattrget((const dvoid *)bindp, oci_htype_bind, (dvoid *)&rows,
(ub4 *) sizeof(ub4), oci_attr_rows_returned, errhp);
rowsret[iter] = (ub2)rows;
/* dynamically allocate storage */
if (alloc_buffer(pos, iter, rows))
return oci_error;
}
/* provide the address of the storage where the data is to be returned */
switch(pos)
{
case 0:
rl[pos][iter][index] = sizeof(int);
*bufpp = (dvoid *) (p1[iter] index);
break;
case 1:
rl[pos][iter][index] = (ub4) maxcollen;
*bufpp = (dvoid *) (p2[iter](index * maxcollen));
break;
case 2:
rl[pos][iter][index] = (ub4) maxcollen;
*bufpp = (dvoid *) (p3[iter](index * maxcollen));
break;
case 3:
rl[pos][iter][index] = sizeof(float);
*bufpp = (dvoid *) (p4[iter] index);
break;
case 4:
rl[pos][iter][index] = sizeof(int);
*bufpp = (dvoid *) (p5[iter]index);
break;
case 5:
rl[pos][iter][index] = sizeof(float);
*bufpp = (dvoid *) (p6[iter]index );
break;
case 6:
rl[pos][iter][index] = sizeof(int);
*bufpp = (dvoid *) (p7[iter] index);
break;
case 7:
rl[pos][iter][index] = sizeof(float);
*bufpp = (dvoid *) (p8[iter]index);
break;
case 8:
rl[pos][iter][index] = datbuflen;
*bufpp = (dvoid *) (p9[iter](index * datbuflen));
break;
case 9:
rl[pos][iter][index] = (ub4) maxcollen;
*bufpp = (dvoid *) (p10[iter](index * maxcollen));
break;
default:
*bufpp = (dvoid *) 0;
*alenp = (ub4 *) 0;
(void) printf("error: invalid position number: %d\n", *((ub2 *)ctxp));
}
*piecep = oci_one_piece;
/* provide address of the storage where the indicator will be returned */
ind[pos][iter][index] = 0;
*indpp = (dvoid *) &ind[pos][iter][index];
/* provide address of the storage where the return code will be returned */
rc[pos][iter][index] = 0;
*rcodepp = &rc[pos][iter][index];
/*
* provide address of the storage where the actual length will be
* returned
*/
*alenp = &rl[pos][iter][index];
return oci_continue;
}
/* ----------------------------------------------------------------- */
/* allocate buffers for callback. */
/* ----------------------------------------------------------------- */
static sword alloc_buffer(ub4 pos, ub4 iter, ub4 rows)
{
switch(pos)
{
case 0:
p1[iter] = (int *) malloc(sizeof(int) * rows);
break;
case 1:
p2[iter] = (text *) malloc(rows * maxcollen);
break;
case 2:
p3[iter] = (text *) malloc(rows * maxcollen);
break;
case 3:
p4[iter] = (float *) malloc(sizeof(float) * rows);
break;
case 4:
p5[iter] = (int *) malloc(sizeof(int) * rows);
break;
case 5:
p6[iter] = (float *) malloc(sizeof(float) * rows);
break;
case 6:
p7[iter] = (int *) malloc(sizeof(int) * rows);
break;
case 7:
p8[iter] = (float *) malloc(sizeof(float) * rows);
break;
case 8:
p9[iter] = (ub1 *) malloc(rows * datbuflen);
break;
case 9:
p10[iter] = (ub1 *) malloc(rows * maxcollen);
break;
default:
(void) printf("error: invalid position number: %d\n", pos);
return oci_error;
}
ind[pos][iter] = (short *) malloc(rows * sizeof(short));
rc[pos][iter] = (ub2 *) malloc(rows * sizeof(ub2));
rl[pos][iter] = (ub4 *) malloc(rows * sizeof(ub4));
return oci_success;
}
/* ----------------------------------------------------------------- */
/* print the returned data. */
/* ----------------------------------------------------------------- */
static sword print_return_data(iters)
int iters;
{
int i, j;
for (i = 0; i < iters; i)
{
(void) printf("\n*** iteration *** : %d\n", i);
(void) printf("(...returning %d rows)\n", rowsret[i]);
for (j = 0; j < rowsret[i] ; j)
{
/* column 1 */
(void) printf("col1 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[0][i][j], rc[0][i][j], rl[0][i][j]);
if (ind[0][i][j] == -1)
(void) printf("col1 [%d]: null\n", j);
else
(void) printf("col1 [%d]: %d\n", j, *(p1[i]j) );
/* column 2 */
(void) printf("col2 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[1][i][j], rc[1][i][j], rl[1][i][j]);
if (ind[1][i][j] == -1)
(void) printf("col2 [%d]: null\n", j);
else
(void) printf("col2 [%d]: %.*s\n",j, rl[1][i][j], p2[i](j*maxcollen));
/* column 3 */
(void) printf("col3 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[2][i][j], rc[2][i][j], rl[2][i][j]);
if (ind[2][i][j] == -1)
(void) printf("col3 [%d]: null\n", j);
else
(void) printf("col3 [%d]: %.*s\n",j, rl[2][i][j], p3[i](j*maxcollen));
/* column 4 */
(void) printf("col4 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[3][i][j], rc[3][i][j], rl[3][i][j]);
if (ind[3][i][j] == -1)
(void) printf("col4 [%d]: null\n", j);
else
(void) printf("col4 [%d]: %8.3f\n", j, *(p4[i]j) );
/* column 5 */
(void) printf("col5 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[4][i][j], rc[4][i][j], rl[4][i][j]);
if (ind[4][i][j] == -1)
(void) printf("col5 [%d]: null\n", j);
else
(void) printf("col5 [%d]: %d\n", j, *(p5[i]j) );
/* column 6 */
(void) printf("col6 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[5][i][j], rc[5][i][j], rl[5][i][j]);
if (ind[5][i][j] == -1)
(void) printf("col6 [%d]: null\n", j);
else
(void) printf("col6 [%d]: %8.3f\n", j, *(p6[i]j) );
/* column 7 */
(void) printf("col7 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[6][i][j], rc[6][i][j], rl[6][i][j]);
if (ind[6][i][j] == -1)
(void) printf("col7 [%d]: null\n", j);
else
(void) printf("col7 [%d]: %d\n", j, *(p7[i]j) );
/* column 8 */
(void) printf("col8 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[7][i][j], rc[7][i][j], rl[7][i][j]);
if (ind[7][i][j] == -1)
(void) printf("col8 [%d]: null\n", j);
else
(void) printf("col8 [%d]: %8.3f\n", j, *(p8[i]j) );
/* column 9 */
(void) printf("col9 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[8][i][j], rc[8][i][j], rl[8][i][j]);
if (ind[8][i][j] == -1)
(void) printf("col9 [%d]: null\n", j);
else
(void) printf("col9 [%d]: %u-%u-%u%u\n", j,
*(p9[i](j*datbuflen3)),
*(p9[i](j*datbuflen2)),
*(p9[i](j*datbuflen0)) - 100,
*(p9[i](j*datbuflen1)) - 100 );
/* column 10 */
(void) printf("col10 [%d]: ind = %d, rc = %d, retl = %d\n",
j, ind[9][i][j], rc[9][i][j], rl[9][i][j]);
if (ind[9][i][j] == -1)
(void) printf("col10 [%d]: null\n", j);
else
{
(void) printf("col10 [%d]: ", j);
print_raw(p10[i](j*maxcollen), rl[9][i][j]);
}
(void) printf("\n");
}
}
return oci_success;
}
/* end of file cdemodr1.c */
|
