WinOCI & Cursor Variables...
Date: 1996/09/06
Message-ID: <32307CA7.3A14_at_comdisco.com>
After a bit of a reprieve I am back to trying to make cursor variables work under windows.
Can someone please look at the following and tell me what I am missing. Code executes through the parse but the cursor variable does not appear to be set up properly by the stored procedure resulting in a GPF on the odescr call.
Here is the stored package...
CREATE OR REPLACE PACKAGE EmployeePkg AS
TYPE EmpRec IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
job emp.job%TYPE,
mgr emp.mgr%TYPE,
hiredate emp.hiredate%TYPE,
sal emp.sal%TYPE,
deptno emp.deptno%TYPE);
TYPE EmpCursor IS REF CURSOR RETURN EmpRec; PROCEDURE GetEmployees(p_Cursor IN OUT EmpCursor, p_jobtitle VARCHAR2); END EmployeePkg;
/
CREATE OR REPLACE PACKAGE BODY EmployeePkg AS PROCEDURE GetEmployees (p_Cursor IN OUT EmpCursor, p_jobtitle VARCHAR2) IS BEGIN
OPEN p_Cursor FOR
SELECT empno, ename, job, mgr, hiredate, sal, deptno FROM EMP
WHERE Job = p_JobTitle;
END GetEmployees;
END EmployeePkg;
And now for the source code...
/*
- winoci.c
*
- Example OCI Application for Windows V3.0.
*
- by D. Colello -- Desktop Products Division.
*
- Copyright 1991, Oracle Corporation.
*
- Modified -
- cchui 10/25/94 - replace calls to osql3 with oparse - bug 229431
- cchui 10/25/94 - replace call to olon with orlon - bug 229431
- dcriswel 12/03/93 - Port to v7
- Colello 07/09/91 - Created from winsam.pc
*/
#include <windows.h> #include "winoci.h" #include <string.h> #include "ociapr.h" #include "ocidem.h" #include "ocidfn.h"
/* oparse flags */
#define DEFER_PARSE 1
#define VERSION_7 2
#define NPOS 16
#define DSCLEN 240
text sJobKind[50] = "ANALYST";
static sword retval;
static ub4 hstb[HDA_SIZE/sizeof(ub4)]; /*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];
/********************************************************************/
/* Oracle Declaration Section */
/********************************************************************/
/* Cursor and LDA for communicating with ORACLE */
struct cda_def lda; /* lda area */
#define LDA (struct cda_def far *)&lda
static struct cda_def sel_curs; /* cursor for SELECT */
#define SEL_CURS (struct cda_def far *)&sel_curs
struct cda_def dml_curs; /* cursor for DML...insert,update,delete */
#define DML_CURS (struct cda_def far *)&dml_curs
struct cda_def sel_curs_var; /* cursor for SELECT */
#define SEL_CURS_VAR (struct cda_def far *)&sel_curs_var
/* Input host variables for logging into ORACLE. */
char userid[ MAX_USERID + 1 ]; /*used at CONNECT time*/
char password[ MAX_PASSWORD + 1 ]; /*used at CONNECT time*/
char connect[ MAX_CONNECT + 1 ]; /*used at CONNECT time*/
/* Input/Output host variables for EMP table. */
char emp_empno[ 5 ]; /*used for EMP table*/
char emp_ename[ 11 ]; /*used for EMP table*/
char emp_job[ 10 ]; /*used for EMP table*/
char emp_mgr[ 5 ]; /*used for EMP table*/
char emp_hiredate[ 10 ]; /*used for EMP table*/
char emp_sal[ 9 ]; /*used for EMP table*/
char emp_comm[ 9 ]; /*used for EMP table*/
char emp_deptno[ 3 ]; /*used for EMP table*/
/* Null field indicators for table EMP. */
short ind_emp_empno; /*used for EMP table*/
short ind_emp_ename; /*used for EMP table*/
short ind_emp_job; /*used for EMP table*/
short ind_emp_mgr; /*used for EMP table*/
short ind_emp_hiredate; /*used for EMP table*/
short ind_emp_sal; /*used for EMP table*/
short ind_emp_comm; /*used for EMP table*/
short ind_emp_deptno; /*used for EMP table*/
/* Structure to keep info on current session */ struct oracle_session session;
char hda[256];
/********************************************************************/ /* Windows Declaration Section */ /********************************************************************/
static HANDLE hInst;
FARPROC lpprocAbout;
/********************************************************************/ /* OCI and Windows Code. */ /********************************************************************/ /*
*EmployeesDlg
*/
BOOL FAR PASCAL EmployeesDlg(hDlg, message, wParam, lParam) HWND hDlg;
unsigned message;
WORD wParam;
LONG lParam;
{
/* SQL statement used to get info from database */ /* char far *empsel="SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, \
DEPTNO FROM EMP FOR UPDATE OF EMPNO, ENAME, JOB, MGR, \
HIREDATE, SAL, COMM, DEPTNO";
*/
char far *empsel=
"begin \
EmployeePkg.GetEmployees(:cCursor, :sJob);\
end;";
/*****************************************************************/
/* You need the "FOR UPDATE OF" clause so that you can use */
/* the "rowid" field with UPDATE and DELETE statements. This also*/
/* applies to a PRO*C 'WHERE CURRENT OF' clause. This field will */
/* be valid for other statements to use if and only if you */
/* use the "FOR UPDATE OF" clause with the SELECT since this */
/* locks the ROWID until the next COMMIT [see SQL manuals]. */
/*****************************************************************/
/* SQL statement to delete currently fetched emp record */ char far *delete="DELETE FROM EMP WHERE ROWID = :row_id";
/* SQL statement to update currently fetched emp record */ char far *update="UPDATE EMP SET EMPNO=:empno, ENAME=:ename, JOB=:job, \
MGR=:mgr, HIREDATE=:hiredate, SAL=:sal, COMM=:comm, \
DEPTNO=:deptno WHERE ROWID = :row_id";
/* SQL statement to insert an emp record */ char far *insert="INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM, \
DEPTNO) VALUES (:empno,:ename,:job,:mgr,:hiredate,:sal, \
:comm,:deptno)";
ub4 pos;
switch (message) {
case WM_COMMAND :
switch (wParam) {
case ID_EMPLOYEES_EXIT :
EndDialog(hDlg, 1);
break;
case ID_EMPLOYEES_SELECT :
/* check to see if we have an open, parsed, defined cursor */
/* if so, all we need to do below is re-execute the query
in order to get a new active set. Otherwise, open,
parse, and define output buffers for the query */
if (session.opened == FALSE) {
/* open a cursor for the select from emp */
if (oopen(SEL_CURS_VAR, LDA, (char far *)0,
-1, -1, (char far *)0, -1)) {
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
/* parse cursor */
// if (osql3(SEL_CURS_VAR, (char far *)empsel, -1)) {
if (oparse(SEL_CURS_VAR, (char far *)empsel, -1, (sword) TRUE, (ub4)2)) {
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
if (obndra(SEL_CURS_VAR, (text *)":cCursor", -1,
(ub1 *)&sel_curs,
-1, SQLT_CUR, -1, (sb2 *)0,(ub2 *)0, (ub2
*)0,
(ub4) 0, (ub4 *)0, (text *)0,0,0)){
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
if (obndra(SEL_CURS_VAR, (text *)":sJob", -1, (ub1
*)sJobKind,
-1, SQLT_STR, -1, (sb2 *)0,(ub2 *)0, (ub2
*)0,
(ub4) 0, (ub4 *)0, (text *)0,0,0)){
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
/* execute the query to get a new active set */
if (oexec(SEL_CURS_VAR)) {
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
if(oclose(SEL_CURS_VAR)){
ProcessOracleErrorCode(hDlg, SEL_CURS_VAR);
break;
} /*if*/
for (pos=0;pos<NPOS;pos++){
cbufl[pos]=DSCLEN;
if(odescr(SEL_CURS, (sword)(pos+1), &dbsize[pos],&dbtype[pos],
(sb1 *)cbufl[pos], &cbufl[pos], &dsize[pos],
&prec[pos], &scale[pos], &nullok[pos])){
if (sel_curs.rc == 1007)
break;
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
}
}
/* define output buffers for the all fields
in the select list of our query */
if (odefin(SEL_CURS, 1, (char far *)emp_empno,
sizeof(emp_empno),
NULLTERM, -1, (short far *)&ind_emp_empno,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 2, (char far *)emp_ename,
sizeof(emp_ename),
NULLTERM, -1, (short far *)&ind_emp_ename,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 3, (char far *)emp_job,
sizeof(emp_job),
NULLTERM, -1, (short far *)&ind_emp_job,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 4, (char far *)emp_mgr,
sizeof(emp_mgr),
NULLTERM, -1, (short far *)&ind_emp_mgr,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 5,
(char far *)emp_hiredate,
sizeof(emp_hiredate),
NULLTERM, -1, (short far *)&ind_emp_hiredate,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 6, (char far *)emp_sal,
sizeof(emp_sal),
NULLTERM, -1, (short far *)&ind_emp_sal,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 7, (char far *)emp_comm,
sizeof(emp_comm),
NULLTERM, -1, (short far *)&ind_emp_comm,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
if (odefin(SEL_CURS, 8, (char far *)emp_deptno,
sizeof(emp_deptno),
NULLTERM, -1, (short far *)&ind_emp_deptno,
(char far *)0, -1, -1, (short far *)0,
(short far *)0)) {
ProcessOracleErrorCode(hDlg, SEL_CURS);
break;
} /*if*/
} /*if*/
session.opened = TRUE;
session.nomore = FALSE;
case ID_EMPLOYEES_FETCH :
// More code but it is the same as in the WinOCI sample... Received on Fri Sep 06 1996 - 00:00:00 CEST