| Programmer's Guide to the Oracle7 Server Call Interface |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle7 Server Call Interface | Library |
Product |
Contents |
Index |
gets((char *) sql_statement);
if (*sql_statement != 'y' && *sql_statement != 'Y')
do_exit(EXIT_SUCCESS);
/* Parse, set defflg parameter for non-deferred parse
(to execute the DDL statement immediately). */
if (oparse(&cda, (text *) "DROP TABLE voice_mail", -1,
FALSE, 2))
{
if (cda.rc == 942)
fputs("Table did not exist.\n", stdout);
else
oci_error(&cda);
}
else
fputs("Dropped table \"voice_mail\".\n", stdout);
strcpy((char *) sql_statement, "CREATE TABLE voice_mail\
(msg_id NUMBER(6), msg_len NUMBER(12), msg LONG RAW)");
if (oparse(&cda, sql_statement, -1, FALSE, 2))
oci_error(&cda);
fputs("Created table \"voice_mail\".\n", stdout);
/* Create a dummy message. */
strcpy((char *) sql_statement,
"INSERT INTO voice_mail (msg_id, msg_len, msg) \
VALUES (:1, :2, :3)");
if (oparse(&cda, sql_statement, -1, FALSE, 2))
oci_error(&cda);
if (obndrn(&cda, 1, (ub1 *) &msg_id, 4, 3, -1,
(sb2 *) 0, (text *) 0, 0, -1))
oci_error(&cda);
/* Set buffer address before binding. */
ucp = (ub1 *) malloc(MSG_SIZE);
if (ucp == 0)
{
fputs("malloc error\n", stderr);
do_exit(EXIT_FAILURE);
}
if (obndrn(&cda, 2, (ub1 *) &msg_len, 4, 3, -1,
(sb2 *) 0, (text *) 0, 0, -1))
oci_error(&cda);
if (obndrn(&cda, 3, ucp, MSG_SIZE, 24, -1,
(sb2 *) 0, (text *) 0, 0, -1))
oci_error(&cda);
/* Set bind vars before oexn. */
msg_id = 100;
msg_len = MSG_SIZE;
for (i = 0, ucp1 = ucp; i < MSG_SIZE; i++)
*ucp1++ = (ub1) i % 128;
if (oexn(&cda, 1, 0))
oci_error(&cda);
fputs("Data inserted in table \"voice_mail\".\n", stdout);
/*
* After setting up the test data, do the
* select and fetch the message chunks */
strcpy((char *) sql_statement, "select msg_id, msg_len, msg\
from voice_mail where msg_id = 100");
if (oparse(&cda, sql_statement, -1, 0, 2))
oci_error(&cda);
if (odefin(&cda,
1, /* index */
(ub1 *) &msg_id, /* output variable */
4, /* length */
3, /* datatype */
-1, /* scale */
(sb2 *) 0, /* indp */
(text *) 0, /* fmt */
0, /* fmtl */
-1, /* fmtt */
(ub2 *) 0, /* retl */
(ub2 *) 0)) /* rcode */
oci_error(&cda);
if (odefin(&cda,
2, /* index */
(ub1 *) &msg_len,/* output variable */
4, /* length */
3, /* datatype */
-1, /* scale */
(sb2 *) 0, /* indp */
(text *) 0, /* fmt */
0, /* fmtl */
-1, /* fmtt */
(ub2 *) 0, /* retl */
(ub2 *) 0)) /* rcode */
oci_error(&cda);
if (odefin(&cda,
3, /* index */
ucp, /* output variable */
100, /* length */
24, /* LONG RAW datatype code */
-1, /* scale */
&indp, /* indp */
(text *) 0, /* fmt */
0, /* fmtl */
-1, /* fmtt */
&retl, /* retl */
&rcode)) /* rcode */
oci_error(&cda);
/* Do the query, getting the msg_id and the first
100 bytes of the message. */
if (oexfet(&cda,
(ub4) 1, /* nrows */
0, /* cancel (FALSE) */
0)) /* exact (FALSE) */
{
oci_error(&cda);
}
fprintf(stdout,
"Message %d is available, length is %d.\n", msg_id,
msg_len);
fprintf(stdout,
"indp = %d, rcode = %d, retl = %d\n", indp, rcode, retl);
/* Play the message, looping until there are
no more data points to output. */
for (offset = (ub4) 0; ; offset += (ub4) 0x10000)
{
len = msg_len < 0x10000 ? msg_len : 0x10000;
if (oflng(&cda,
3, /* position */
ucp, /* buf */
len, /* bufl */
24, /* datatype */
&ret_len, /* retl */
offset)) /* offset */
oci_error(&cda);
/* Output the message chunk. */
play_msg(ucp, len);
msg_len -= len;
if (msg_len <= 0)
break;
}
do_exit(EXIT_SUCCESS);
}
dvoid
play_msg(buf, len)
ub1 *buf;
sword len;
{
fprintf(stdout, "\"playing\" %d bytes.\n", len);
}
dvoid
oci_error(cda)
Cda_Def *cda;
{
text msg[200];
sword n;
fputs("\n-- ORACLE ERROR --\n", stderr);
n = oerhms(&lda, (sb2) cda->rc, msg, 200);
fprintf(stderr, "%.*s", n, msg);
fprintf(stderr, "Processing OCI function %s\n",
oci_func_tab[(int) cda->fc]);
do_exit(EXIT_FAILURE);
}
dvoid
do_exit(rv)
sword rv;
{
fputs("Exiting...\n", stdout);
if (oclose(&cda))
{
fputs("Error closing cursor.\n", stderr);
rv = EXIT_FAILURE;
}
if (ologof(&lda))
{
fputs("Error logging off.\n", stderr);
rv = EXIT_FAILURE;
}
exit(rv);
}
/* cdemo4.c * * Demonstrates doing a FETCH from a cursor * into PL/SQL tables. The tables are bound to C * arrays using the obndra routine. * The fully-commented script to create the stored procedure * is in the demo program file calldemo.sql. * * Execute this script using SQL*DBA or SQL*Plus * to store the package before executing this program. * * The script is: * create or replace package calldemo as * * type char_array is table of varchar2(20) index by * binary_integer; * type num_array is table of float index by binary_integer; * * procedure get_employees( * dept_number in integer, -- which department to query * batch_size in integer, -- how many rows at a time * found in out integer, -- n of rows actually * returned * done_fetch out integer, -- all done flag * emp_name out char_array,-- arrays of employee names, * job out char_array,-- jobs, * sal out num_array);-- salaries * * end; * / * * create or replace package body calldemo as * * cursor get_emp( * dept_number in integer) is * select ename, job, sal from emp * where deptno = dept_number; * * -- Procedure get_employees fetches a batch of employee * -- rows (batch size is determined by the client/caller * -- of this procedure). Procedure may be called from * -- other stored procedures or client application * -- programs. The procedure opens the cursor if it is * -- not already open, fetches a batch of rows, and * -- returns the number of rows actually retrieved. At * -- end of fetch, the procedure closes the cursor. * * procedure get_employees( * dept_number in integer, * batch_size in integer, * found in out integer, * done_fetch out integer, * emp_name out char_array, * job out char_array, * sal out num_array) is * * begin * if NOT get_emp%ISOPEN then -- open the cursor if it is * open get_emp(dept_number); -- not already open * end if; * * -- Fetch up to "batch_size" rows into PL/SQL table, * -- tallying rows found as they are retrieved. When end * -- of fetch is encountered, close the cursor and exit * -- the loop, returning only the last set of rows found. * * done_fetch := FALSE; * found := 0; * for i in 1..batch_size loop * fetch get_emp -- get one emp table row * into emp_name(i), job(i), sal(i); * * if get_emp%notfound then -- if no row was found, then * close get_emp; -- close the cursor * done_fetch := TRUE; -- indicate all done * exit; -- exit the loop * else * found := found + 1; -- else count the row and * continue * end if; * end loop; * end; * end; * / */ #include <stdio.h> #include <string.h> #include <oratypes.h> #include <ocidfn.h> #ifdef __STDC__ #include <ociapr.h> #else #include <ocikpr.h> #endif #include <ocidem.h> #define MAX_ARRAY_SIZE 5 #define NO_PARSE_DEFER 0 #define V7_LNGFLG 2 #define VC_LENGTH 20 /* Declare the data areas. */ Cda_Def cda; Lda_Def lda; ub1 hda[512];
/* Declare routines in this program */
dvoid do_fetch(/*_ void _*/);
dvoid oci_error(/*_ void _*/);
main(argc, argv)
sword argc;
text **argv;
{
text username[128];
if (argc > 1)
strncpy((char *) username, (char *) argv[1],
sizeof (username) - 1);
else
strcpy((char *) username, "SCOTT/TIGER");
if (olog(&lda, hda, username, -1, (text *) 0, -1,
(text *) 0, -1, OCI_LM_DEF))
{
printf("Cannot connect as %s. Exiting...\n", username);
exit(-1);
}
else
printf("Connected.\n");
/* Open the OCI cursor. */
if (oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1))
{
printf("Cannot open cursor data area, exiting...\n");
exit(-1);
}
/* Fetch and print the data. */
do_fetch();
/* Close the OCI cursor. */
if (oclose(&cda))
{
printf("Error closing cursor!\n");
exit(-1);
}
/* Disconnect from ORACLE. */
if (ologof(&lda))
{
printf("Error logging off!\n");
exit(-1);
}
exit(0);
}
/* Set up an anonymous PL/SQL call to the stored
procedure that fetches the data. */
dvoid
do_fetch(/*_ void _*/)
{
text *call_fetch = (text *) "\
begin\
calldemo.get_employees(:deptno, :t_size, :num_ret,\
:all_done, :e_name, :job, :sal);\
end;";
sword table_size = MAX_ARRAY_SIZE;
sword i, n_ret, done_flag;
sword dept_num;
sb2 n_ret_indp;
ub2 n_ret_len, n_ret_rcode;
ub4 n_ret_cursiz = 0;
text emp_name[MAX_ARRAY_SIZE][VC_LENGTH];
sb2 emp_name_indp[MAX_ARRAY_SIZE];
ub2 emp_name_len[MAX_ARRAY_SIZE];
ub2 emp_name_rcode[MAX_ARRAY_SIZE];
ub4 emp_name_cursiz = (ub4) MAX_ARRAY_SIZE;
text job[MAX_ARRAY_SIZE][VC_LENGTH];
sb2 job_indp[MAX_ARRAY_SIZE];
ub2 job_len[MAX_ARRAY_SIZE];
ub2 job_rcode[MAX_ARRAY_SIZE];
ub4 job_cursiz = (ub4) MAX_ARRAY_SIZE;
float salary[MAX_ARRAY_SIZE];
sb2 salary_indp[MAX_ARRAY_SIZE];
ub2 salary_len[MAX_ARRAY_SIZE];
ub2 salary_rcode[MAX_ARRAY_SIZE];
ub4 salary_cursiz = (ub4) MAX_ARRAY_SIZE;
/* parse the anonymous SQL block */
if (oparse(&cda, call_fetch, -1,
NO_PARSE_DEFER, V7_LNGFLG))
{
oci_error();
return;
}
/* initialize the bind arrays */
for (i = 0; i < MAX_ARRAY_SIZE; i++)
{
emp_name_len[i] = VC_LENGTH;
job_len[i] = VC_LENGTH;
salary_len[i] = sizeof (float);
}
n_ret_len = sizeof (sword);
/* bind the department number IN parameter */
if (obndrv(&cda, (text *) ":deptno", -1, (ub1 *) &dept_num,
(sword) sizeof (sword), INT_TYPE, -1,
(sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
/* bind the table size IN parameter */
if (obndrv(&cda, (text *) ":t_size", -1, (ub1 *) &table_size,
(sword) sizeof (sword),
INT_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
/* bind the fetch done OUT parameter */
if (obndrv(&cda, (text *) ":all_done", -1, (ub1 *) &done_flag,
(sword) sizeof (sword),
INT_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
/* Bind the OUT n_ret using obndra. obndrv could
have been used just as well, since no arrays
are involved, but it is possible to use obndra
for scalars as well. */
if (obndra(&cda,
(text *) ":num_ret",
-1,
(ub1 *) &n_ret,
(sword) sizeof (sword),
INT_TYPE,
-1,
&n_ret_indp,
&n_ret_len,
&n_ret_rcode,
(ub4) 0, /* pass as 0, not 1, when binding a scalar */
(ub4 *) 0, /* pass as the null pointer when scalar */
(text *) 0,
-1,
-1))
{
oci_error();
return;
}
/* bind the employee name array */
if (obndra(&cda,
(text *) ":e_name",
-1,
(ub1 *) emp_name,
VC_LENGTH,
VARCHAR2_TYPE,
-1,
emp_name_indp,
emp_name_len,
emp_name_rcode,
(ub4) MAX_ARRAY_SIZE,
&emp_name_cursiz,
(text *) 0,
-1,
-1))
{
oci_error();
return;
}
/* bind the job array */
if (obndra(&cda,
(text *) ":job",
-1,
(ub1 *) job,
VC_LENGTH,
VARCHAR2_TYPE,
-1,
job_indp,
job_len,
job_rcode,
(ub4) MAX_ARRAY_SIZE,
&job_cursiz,
(text *) 0,
-1,
-1))
{
oci_error();
return;
}
/* bind the salary array */
if (obndra(&cda,
(text *) ":sal",
-1,
(ub1 *) salary,
(sword) sizeof (float),
FLOAT_TYPE,
-1,
salary_indp,
salary_len,
salary_rcode,
(ub4) MAX_ARRAY_SIZE,
&salary_cursiz,
(text *) 0,
-1,
-1))
{
oci_error();
return;
}
printf("\nenter deptno: ");
scanf("%d", &dept_num);
for (;;)
{
/* execute the fetch */
if (oexec(&cda))
{
oci_error();
return;
}
printf("\n%d row%c returned\n",
n_ret, n_ret == 1 ? '\0' : 's');
if (n_ret > 0)
{
printf("\n%-*.*s%-*.*s%s\n",
VC_LENGTH, VC_LENGTH, "Employee Name",
VC_LENGTH, VC_LENGTH, "Job", " Salary");
for (i = 0; i < n_ret; i++)
{
printf("%.*s", emp_name_len[i], emp_name[i]);
printf("%*c", VC_LENGTH - emp_name_len[i], ' ');
printf("%.*s", job_len[i], job[i]);
printf("%*c", VC_LENGTH - job_len[i], ' ');
printf("%8.2f\n", salary[i]);
}
}
if (done_flag != 0)
{
printf("\n");
break;
}
}
return;
}
dvoid
oci_error(/*_ void _*/)
{
text msg[900];
sword rv;
rv = oerhms(&lda, cda.rc, msg, (sword) sizeof (msg));
printf("\n\n%.*s", rv, msg);
printf("Processing OCI function %s\n",
oci_func_tab[(int) cda.fc]);
return;
}
/*
* -- cdemo5.c --
*
* An example program which demonstrates the use of
* Variable Cursors in an OCI program.
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ocikpr.h>
/* oparse flags */
#define DEFER_PARSE 1
#define VERSION_7 2
#define NPOS 16
#define DSCLEN 240
text *username = (text *) "SCOTT";
text *password = (text *) "TIGER";
static sword retval;
static ub1 hstb[512];
static text errorb[4095];
static text cbuf[NPOS][DSCLEN];
static sb4 cbufl[NPOS];
static sb4 dbsize[NPOS];
static sb4 dsize[NPOS];
static sb2 dbtype[NPOS];
static sb2 prec[NPOS];
static sb2 scale[NPOS];
static sb2 nullok[NPOS];
static Lda_Def lda1;
static text plsql_block[] =
"begin \
OPEN :cursor1 FOR select empno, ename, job, mgr,
hiredate,sal,deptno\
from emp where job=:job order by empno;\
end;";
/* CLIENT CURSORS */
static Cda_Def cursor, cursor_emp;
/* Prototype */
void oracle_error();
int main ()
{
Lda_Def *ldap = &lda1;
ub4 empno;
text ename[11];
text job[10];
ub4 mgr;
text hidate[10];
ub4 sal;
ub4 deptno;
int i;
text job_kind[50];
ub4 pos;
strcpy((char *) job_kind, "ANALYST");
fprintf(stdout,"\n\nFETCHING for job=%s\n\n",job_kind);
/* Connect to Oracle as SCOTT/TIGER. *
* Exit on any error. */
if (olog(ldap, hstb, username, -1, password, -1,
(text *) 0, -1, OCI_LM_DEF))
{
printf("Unable to connect as %s\n", username);
exit(EXIT_FAILURE);
}
printf("Connected to Oracle as %s\n\n", username);
/*
* Open a cursor for executing the PL/SQL block.
*/
if (oopen(&cursor, ldap, (text *) 0, -1, 0, (text *) 0, -1))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/* Parse the PL/SQL block. */
if (oparse(&cursor, plsql_block, (sb4) -1, (sword) TRUE,
(ub4) 2))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/*
* Bind a variable of cursor datatype, the cursor will be opened
* inside the PL/SQL block.
*/
if (obndra(&cursor,(text *) ":cursor1", -1, (ub1 *) &cursor_emp, -1, SQLT_CUR, -1, (sb2 *) 0, (ub2 *) 0, (ub2 *) 0,
(ub4) 0, (ub4 *) 0, (text *) 0, 0, 0))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/*
* Bind a variable of string datatype.
*/
if (obndra(&cursor, (text *) ":job", -1, (ub1 *) job_kind,
-1, SQLT_STR, -1, (sb2 *) 0, (ub2 *) 0, (ub2 *) 0,
(ub4) 0, (ub4 *) 0, (text *) 0, 0, 0))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/*
* Execute the PL/SQL block.
*/
if (oexec(&cursor))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/*
* Close the cursor on which the PL/SQL block executed.
*/
if (oclose(&cursor))
{
oracle_error(&cursor);
exit(EXIT_FAILURE);
}
/*
* Do describe on cursor initialized and returned from the
PL/SQL block.
*/
for (pos = 0; pos < NPOS; pos++)
{
cbufl[pos] = DSCLEN;
if (odescr(&cursor_emp, (sword) (pos+1), &dbsize[pos],
&dbtype[pos],(sb1 *) cbuf[pos], &cbufl[pos],
&dsize[pos], &prec[pos], &scale[pos], &nullok[pos]))
{
if (cursor_emp.rc == 1007)
break;
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
}
printf("Describe select-list returns:\n\n");
printf("----------------------------------------\n");
printf("Item\t\tMaxSize\t\tType\n");
printf("----------------------------------------\n");
for (i = 0; i < pos; i++)
{
cbuf[i][cbufl[i]] = '\0';
printf("%s\t\t%d\t\t%d\n", cbuf[i], dbsize[i], dbtype[i]);
}
/*
* Do client defines.
*/
if (odefin(&cursor_emp, 1, (ub1 *) &empno, (sword) sizeof(ub4),
SQLT_INT, -1, (sb2 *) -1, (text *) 0, (sword) 0,
(sword) 0, (ub2 *) 0,(ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 2, (ub1 *) ename, (sword)
sizeof(ename), SQLT_STR, -1, (sb2 *) -1, (text *)0,
(sword) 0, (sword) 0,(ub2 *) 0, (ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 3, (ub1 *) job, (sword) sizeof(job),
SQLT_STR, -1, (sb2 *) -1, (text *) 0, (sword) 0,
(sword) 0, (ub2 *) 0, (ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 4, (ub1 *)&mgr, (sword) sizeof(ub4),
SQLT_INT, -1, (sb2 *) -1, (text *)0, (sword) 0,
(sword) 0, (ub2 *) 0, (ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 5, (ub1 *)hidate, (sword)sizeof(hidate), SQLT_STR, -1, (sb2 *) -1, (text *) 0, (sword) 0,
(sword) 0, (ub2 *) 0, (ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 6, (ub1 *) &sal, (sword) sizeof(ub4),
SQLT_INT, -1, (sb2 *) -1, (text *) 0, (sword) 0,
(sword) 0, (ub2 *) 0, (ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
if (odefin(&cursor_emp, 7, (ub1 *) &deptno,
(sword)sizeof(deptno), SQLT_INT, -1, (sb2 *) -1,
(text *) 0, (sword) 0, (sword) 0, (ub2 *) 0,
(ub2 *) 0))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
printf("\nFETCH from variable cursor:\n\n");
printf("------------------------------------------------------------\n");
printf("empno\tename\tjob\tmgr\thiredate\tsalary\tdept\n");
printf("------------------------------------------------------------\n");
/*
* Now fetch the result set and display.
*/
while (1)
{
sb4 err = 0;
if (err = ofetch(&cursor_emp))
{
if (cursor_emp.rc == 1403)
break;
else
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
}
else
{
/* A row was returned; have to do the fetch. */
fprintf(stdout, "%d\t%s\t%s\t%d\t%s\t%d\t%d\n\n",
empno, ename, job, mgr, hidate, sal, deptno);
}
}
/*
* Log off.
*/
if (ologof(ldap))
{
oracle_error(&cursor_emp);
exit(EXIT_FAILURE);
}
} /* end of main */
void oracle_error(lda)
Lda_Def * lda;
{
char msgbuf[512];
int n=oerhms(lda, lda->rc, msgbuf, (int) sizeof(msgbuf) );
printf("\n\n%.*s\n",n,msgbuf);
}
/* -- cdemo6.cc -- * An example program which illustrates how a C++ program * can use the OCI interface to access ORACLE database. * * This program retrieves department name, given the * department number. * * The program queries the user for data as follows: * * Enter department number: * * The program terminates if -1 is entered * when the department number is requested. */
extern "C"
{
#include <stdio.h>
#include <oratypes.h>
#include <ociapr.h>
/* demo constants and structs */
#include <ocidem.h>
}
extern "C"
{
#include <oratypes.h>
#include <ocidfn.h>
#include <ocidem.h>
}
/* oparse flags */ #define DEFER_PARSE 1 #define NATIVE 1 #define VERSION_7 2
/* Class forward declarations */ class connection; class cursor;
/*
* This class represents a connection to ORACLE database.
*
* NOTE: This connection class is just given as an example and
* all possible operations on a connection have not been defined.
*/
class connection
{
friend class cursor;
public:
connection()
{ state = not_connected; }
~connection();
sword connect(const text *username, const text *password);
sword disconnect();
void display_error(FILE* file) const;
private:
Lda_Def lda;
ub1 hda[HDA_SIZE];
enum conn_state
{
not_connected,
connected
};
conn_state state;
};
/*
* This class represents an ORACLE cursor.
*
* NOTE: This cursor class is just given as an example and all
* possible operations on a cursor have not been defined.
*/
class cursor
{
public:
cursor()
{state = not_opened; conn = (connection *)0; }
~cursor();
sword open(connection *conn_param);
sword close();
sword parse(const text *stmt)
{ return (oparse(&cda, (text *)stmt, (sb4)-1,
DEFER_PARSE, (ub4) VERSION_7)); }
/* bind an input variable */
sword bind_by_position(sword sqlvnum, ub1 *progvar,
sword progvarlen, sword datatype,
sword scale, sb2 *indicator)
{ return (obndrn(&cda, sqlvnum, progvar, progvarlen,
datatype, scale, indicator, (text *)0, -1, -1)); }
/* define an output variable */
sword define_by_position(sword position, ub1 *buf,
sword bufl, sword datatype, sword scale,
sb2 *indicator, ub2 *rlen, ub2 *rcode)
{ return (odefin(&cda, position, buf, bufl, datatype,
scale, indicator,
(text *)0, -1, -1, rlen, rcode)); }
sword describe(sword position, sb4 *dbsize, sb2 *dbtype,
sb1 *cbuf, sb4 *cbufl, sb4 *dsize, sb2 *prec,
sb2 *scale, sb2 *nullok)
{ return (odescr(&cda, position, dbsize, dbtype,
cbuf, cbufl, dsize, prec, scale, nullok));
}
sword execute()
{ return (oexec(&cda)); }
sword fetch()
{ return (ofetch(&cda)); }
sword get_error_code() const
{ return (cda.rc); }
void display_error( FILE* file) const;
private:
Cda_Def cda;
connection *conn;
enum cursor_state
{
not_opened,
opened
};
cursor_state state;
};
/* Error number macros */ #define CONERR_ALRCON -1 /* already connected */ #define CONERR_NOTCON -2 /* not connected */ #define CURERR_ALROPN -3 /* cursor is already open */ #define CURERR_NOTOPN -4 /* cursor is not opened */
/* exit status upon failure */ #define EXIT_FAILURE 1
const text *username = (text *) "SCOTT"; const text *password = (text *) "TIGER";
/* define SQL statements to be used in the program */ const text *seldept = (text *) "SELECT dname FROM dept WHERE deptno = :1";
void err_report(FILE *file, text *errmsg, sword func_code); void myfflush();
/* connection destructor */
connection::~connection()
{
// disconnect if connection exists
if (state == connected)
{
if (disconnect())
{
display_error(stderr);
}
}
}
/* connect to ORACLE */
sword connection::connect(const text *username, const text *password)
{
sword status;
if (state == connected)
{
// this object is already connected
return (CONERR_ALRCON);
}
if ((status = olog(&lda, hda, (text *)username, -1
(text *)password, -1, (text *) 0, -1,
OCI_LM_DEF)) == 0)
{
// successful login
state = connected;
printf("Connected to ORACLE as %s\n", username);
}
return (status); }
/* disconnect from ORACLE */
sword connection::disconnect()
{
sword status;
if (state == not_connected)
{
// this object has not been connected
return (CONERR_NOTCON);
}
if ((status = ologof(&lda)) == 0)
{
// successful logout
state = not_connected;
}
return (status); }
/* write error message to the given file */
void connection::display_error(FILE *file) const
{
if (lda.rc != 0)
{
sword n;
text msg[512];
n = oerhms((cda_def *)&lda, lda.rc, msg, (sword) sizeof(msg));
err_report(file, msg, lda.fc);
}
}
/* cursor destructor */
cursor::~cursor()
{
if (state == opened)
{
if (close())
display_error(stderr);
}
}
/* open the cursor */
sword cursor::open(connection *conn_param)
{
sword status;
if (state == opened)
{
// this cursor has already been opened
return (CURERR_ALROPN);
}
if ((status = oopen(&cda, &conn_param->lda, (text *)0, -1, -1,
(text *)0, -1)) == 0)
{
// successfull open
state = opened;
conn = conn_param;
}
return (status); }
/* close the cursor */
sword cursor::close()
{
sword status;
if (state == not_opened)
{
// this cursor has not been opened
return (CURERR_NOTOPN);
}
if ((status = oclose(&cda)) == 0)
{
// successful cursor close
state = not_opened;
conn = (connection *)0;
}
return (status); }
/* write error message to the given file */
void cursor::display_error(FILE *file) const
{
if (cda.rc != 0)
{
sword n;
text msg[512];
n = oerhms(&conn->lda, cda.rc, msg, (sword) sizeof(msg));
err_report(file, msg, cda.fc);
}
}
int main()
{
sword deptno;
sword len, dsize;
sb4 deptlen;
sb2 db_type;
sb1 name_buf[20];
text *dept;
/*
* Connect to ORACLE and open a cursor.
* Exit on any error.
*/
connection conn;
if (conn.connect(username, password))
{
conn.display_error(stderr);
return(EXIT_FAILURE);
}
cursor crsr;
if (crsr.open(&conn))
{
crsr.display_error(stderr);
return(EXIT_FAILURE);
}
/* parse the SELDEPT statement */
if (crsr.parse(seldept))
{
crsr.display_error(stderr);
return(EXIT_FAILURE);
}
/* bind the placeholder in the SELDEPT statement */
if (crsr.bind_by_position(1, (ub1 *) &deptno,
(sword) sizeof(deptno),
INT_TYPE, -1, (sb2 *) 0))
{
crsr.display_error(stderr);
return(EXIT_FAILURE);
}
/* describe the select-list field "dname" */
len = sizeof (name_buf);
if (crsr.describe(1, (sb4 *) &deptlen, &db_type,
name_buf, (sb4 *) &len, (sb4 *) &dsize,
(sb2 *) 0, (sb2 *) 0, (sb2 *) 0))
{
crsr.display_error(stderr);
return(EXIT_FAILURE);
}
/* allocate space for dept name now that you have length */
dept = new text((int) deptlen + 1);
/* define the output variable for the select-list */
if (crsr.define_by_position(1, (ub1 *) dept, (sword)deptlen+1,
STRING_TYPE, -1, (sb2 *) 0,
(ub2 *) 0, (ub2 *) 0))
{
crsr.display_error(stderr);
delete dept;
return(EXIT_FAILURE);
}
for (;;)
{
/* prompt for department number, */
/* break if given number == -1 */
printf("\nEnter department number (or -1 to EXIT): ");
while (scanf("%d", &deptno) != 1)
{
myfflush();
printf("Invalid input, please enter a number \
(-1 to EXIT): ");
}
if (deptno == -1)
{
printf("Exiting... ");
break;
}
/* display the name of the corresponding department */
if (crsr.execute() || crsr.fetch())
{
if (crsr.get_error_code() != NO_DATA_FOUND)
{
crsr.display_error(stderr);
delete dept;
return(EXIT_FAILURE);
}
else
printf("\n The department number that you entered \
doesn't exist.\n");
}
else
{
printf("\n Department name = %s \
Department number = %d\n",
dept, deptno);
}
}
delete dept;
printf ("\nG'day\n");
return 0; }
void err_report(FILE *file, text *errmsg, sword func_code)
{
fprintf(file, "\n-- ORACLE error--\n\n%s\n", errmsg);
if (func_code > 0)
fprintf(file, "Processing OCI function %s\n",
oci_func_tab[func_code]);
}
void myfflush()
{
eb1 buf[50];
fgets((char *) buf, 50, stdin);
}
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |