Oracle hangs on Pro*C select (??)
Date: Wed, 29 Jul 1998 21:31:37 GMT
Message-ID: <EwvL4q.GH0_at_world.std.com>
Hi everbuddy!
This may be a variable initialization problem on a Pro*C select.
Can't get the Pro*C code below to work, though it will work with the "/* works if commented out */" line commented out. As is, the code works fine on a development machine, but fails on production (hangs on the fetch). I've recoded it in pl/sql, and everything works fine on both machines.
I'd appreciate any insights. I've been struggling for daze on this one. I included a describe on the oracle table, as well.
doug
#include <stdio.h> #include <stdlib.h> #include <sqlca.h>
void OLogon (char *user);
void OLogoff ();
static int OpenCursor();
static int GetNextRow ();
static int CloseCursor();
void sql_error ();
/* ---------------------------------------------------------------------- */int main(int argc, char **argv)
{
int rc;
OLogon( "faxbill/faxbill" );
OpenCursor();
GetNextRow();
CloseCursor();
OLogoff();
return 0;
}
/* ---------------------------------------------------------------------- */
static int OpenCursor()
{ int ReturnCode = 0;
exec sql
declare
cCursor
cursor for
select
machine_number
, machine_type_code
, machine_status_code
, fax_number /* works if commented out */
from machine where machine_number = 10000;
exec sql open cCursor;
if( sqlca.sqlcode != 0 ) {
sql_error();
}
return;
}
/* ---------------------------------------------------------------------- */static int GetNextRow ()
{
exec sql begin declare section;
long MachNumber;
long *mpn;
char MachTypeCode[7];
char MachStatus[7];
char MachFaxNumber[17];
exec sql end declare section;
mpn = &MachNumber;
MachNumber = 0L;
memset( MachTypeCode, '\0', 7 );
memset( MachStatus, '\0', 7 );
memset( MachFaxNumber, '\0', 17 );
printf( "Before fetch...\n" );
exec sql
fetch
cCursor
into
:MachNumber
, :MachTypeCode
, :MachStatus
, :MachFaxNumber /* works if commented out */
;
printf( "After fetch...\n" );
printf( "%ld:%s:%s:%s\n", MachNumber, MachTypeCode, MachStatus, MachFaxN
umber );
if( sqlca.sqlcode != 0 ) {
sql_error();
}
return;
}
/* ---------------------------------------------------------------------- */void OLogon (char *user)
{
EXEC SQL whenever sqlerror do sql_error ();
EXEC SQL CONNECT :user;
}
/* ---------------------------------------------------------------------- */void OLogoff ()
{
EXEC SQL whenever sqlerror do sql_error (); EXEC SQL commit work release;
}
/* ---------------------------------------------------------------------- */static int CloseCursor()
{
exec sql close cCursor;
}
/* ---------------------------------------------------------------------- */void sql_error ()
{
char buf[512];
char sqlbuf[2048];
long sqlfc;
long stmlen = 2048L;
int bufsiz;
int msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf ("\nOracle error.\n");
bufsiz = sizeof (buf);
sqlglm (buf, &bufsiz, &msglen);
printf ( "%.*s\n", msglen, buf);
sqlgls (sqlbuf, &stmlen, &sqlfc);
printf ( "%.*s\n", stmlen, sqlbuf);
EXEC SQL ROLLBACK WORK RELEASE;
exit (1);
}
/*
desc machine
- -------- ---- MACHINE_NUMBER NOT NULL NUMBER(9) MACHINE_TYPE_CODE NOT NULL VARCHAR2(6) AUTODIALER_INVENTORY_ID NUMBER(6) CUST_NUMBER NOT NULL NUMBER(8) DEPT_ID NUMBER(6) DEPT_EMP_ID NUMBER(6) MACHINE_STATUS_CODE NOT NULL VARCHAR2(6) NOTZ_MACHINE_STATUS_DATE NOT NULL DATE NOTZ_MACH_PREV_BILL DATE INSTALL_TYPE_CODE VARCHAR2(6) APPROACH_TYPE_CODE NOT NULL VARCHAR2(6) ZONE_TYPE_CODE VARCHAR2(6) AR_ROUTING_TYPE_CODE VARCHAR2(6) FAX_NUMBER NOT NULL VARCHAR2(16) STAT_REP_NUMBER VARCHAR2(16) STAT_REP_HOURS NUMBER(2) STANDARD_RETRY_MINS NOT NULL NUMBER(3) PASSWORD NUMBER(4) OPERATOR NOT NULL VARCHAR2(25) PBX_CONNECTED VARCHAR2(1) HUNT_CONNECTED VARCHAR2(1) OUTSIDE_ACCESS_CODE VARCHAR2(4) GMT_CREATE_DATE NOT NULL DATE GMT_DATETIME NOT NULL DATE etc.. .