| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with cursor in ProC (Oracle 8)
A copy of this was sent to tbarrie_at_ibm.net (Trevor Barrie)
(if that email address didn't require changing)
On 31 Mar 1999 22:17:41 GMT, you wrote:
>Has anybody out there experienced an inexplicable error message 1001
>(invalid cursor) when they attempt to close a cursor, followed by a
>program crash when they attempt to disconnect? My code is as follows:
>
You'll need to post your data structures and create table statement as well. I *believe* it just might be the line:
*Courses = (temporarytimetable_rec *) malloc(n*sizeof(simptemp_rec));
I think it should be
*Courses =
(temporarytimetable_rec *) malloc(n*sizeof(temporarytimetable_rec ));
^^^^^^^^^^^^^^^^^^^^^
that very well could be causing the problem.
Is sizeof(simptemp_rec) < sizeof(temporarytimetable_rec) ??? I thing you might have an malloc error, you overrun your buffer and then strange things happen.....
Also, the logic in your loop for error handling is wrong. You need to check the sqlca.sqlcode after each fetch, else you'll have a big risk of seg faulting if the fetch fails, doesn't fill in the buffer and you attempt to strcpy from it anyway. So you have:
> for(i=0;i<n && !sqlca.sqlcode;i++)
> {
> EXEC SQL FETCH sect_cursor INTO :Course;
>
> printf("Error code after fetch: %d\n",sqlca.sqlcode);
>
> strncpy((*Courses)[i].CourseCode,Course.CourseCode,6);
but it should be:
> for(i=0;i<n && !sqlca.sqlcode;i++)
> {
> EXEC SQL FETCH sect_cursor INTO :Course;'
if (sqlca.sqlcode != 0 )
{
do something;
break; or exit(); or something...
}
>
> printf("Error code after fetch: %d\n",sqlca.sqlcode);
>
> strncpy((*Courses)[i].CourseCode,Course.CourseCode,6);
Note also that what you are doing is 'sort of bad'. If there is any chance that someone might be inserting or deleting whilst you are doing this -- the count(*) you get in the select count(*) *won't be* the number of records you actually fetch. I see it all the time "well, it works most of the time but sometimes the last records in my array are corrupt". Its not that they are corrupt but rather someone deleted some data and committed after you counted, but before you fetched. Excessively difficult to pinpoint since the moon must align with jupiter to reproduce :)
Here is perhaps 'more solid' logic for doing this:
static char * USERID = "tkyte/tkyte";
#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nORACLE error detected:"); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
typedef char char5[5];
EXEC SQL TYPE char5 is STRING(5);
typedef char char8[8];
EXEC SQL TYPE char8 is STRING(8);
typedef char char11[11];
EXEC SQL TYPE char11 is STRING(11);
typedef char char15[15];
EXEC SQL TYPE char15 is STRING(15);
typedef char char50[50];
EXEC SQL TYPE char50 is STRING(50);
typedef struct {
char15 empno; char15 ename; char11 job; char8 mgr; char50 hiredate; char11 sal; char11 comm; char5 deptno;
typedef struct {
short empno; short ename; short job; short mgr; short hiredate; short sal; short comm; short deptno;
static void grow( void * * data, int alloced, int recsToAlloc, int size ) {
if (*data) *data = (void *)realloc( *data, (alloced+recsToAlloc)*size ); else *data = (void *)malloc( (alloced+recsToAlloc)*size );
if ( !*data )
{
printf( "malloc/realloc failed\n" );
exit(0);
static void process( int * records,
emprec * * p_data,
emprec_inds * * p_data_i )
{
emprec * data = NULL;
int currRec = 0; int allocedRec = 0; int recsToAlloc = 2; emprec tmpbuff; emprec_inds tmpbuff_i;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL DECLARE C1 CURSOR FOR
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp;
EXEC SQL OPEN C1;
for( ;; )
{
memset( &tmpbuff, 0, sizeof(emprec) );
EXEC SQL WHENEVER NOTFOUND DO break;
EXEC SQL FETCH C1 into tmpbuff:tmpbuff_i;;
if ( currRec >= allocedRec )
{
grow((void **)&data, allocedRec, recsToAlloc, sizeof(emprec) );
grow((void **)&data_i, allocedRec,
recsToAlloc, sizeof(emprec_inds) );
allocedRec += recsToAlloc;
}
memmove( data+currRec, &tmpbuff, sizeof(emprec) );
memmove( data_i+currRec, &tmpbuff_i, sizeof(emprec_inds) );
currRec++;
EXEC SQL CLOSE C1;
*records = currRec;
*p_data = data;
*p_data_i = data_i;
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
int i; int records; emprec * data; emprec_inds * data_i;
strcpy( oracleid.arr, "tkyte/tkyte" ); oracleid.len = strlen( oracleid.arr );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process( &records, &data, &data_i );
for( i = 0; i < records; i++ )
{
printf( "'%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s'\n",
data_i[i].empno?"(null)":data[i].empno,
data_i[i].ename?"(null)":data[i].ename,
data_i[i].job?"(null)":data[i].job,
data_i[i].mgr?"(null)":data[i].mgr,
data_i[i].hiredate?"(null)":data[i].hiredate,
data_i[i].sal?"(null)":data[i].sal,
data_i[i].comm?"(null)":data[i].comm,
data_i[i].deptno?"(null)":data[i].deptno );
}
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
It dynamically allocates the array as it needs it using malloc and realloc (I used a realloc value of 2 elements -- bump that up in the real world). Also, it considers that there might be NULLs in the table and deals with that as well. It simplifies the logic by using memmove instead of a bunch of strcopies (works for ints and floats and strings). It shows how to use the EXEC SQL TYPE to get Null Terminated C strings (in Oracle8 there is a precomiler option CHAR_MAP to make this easier but I don't know what version you might be using, this was done with 7.3).
I see you have a %c in your format below. I would not suggest fetching into chars -- its going to want a null terminated string (2 bytes). put it in a string.
>---------------------------------------------------------------------------
>#include <stdio.h>
>#include <string.h>
>#include <stdlib.h>
>#include <sqlcpr.h>
>#include <sqlca.h>
>#include "/users/students/tbarrie/cs480/dbmodules/include/bjreg.h"
>/*This just defines the temporarytimetable_rec structure*/
>
>/* Define constants for VARCHAR lengths. */
>#define LOGIN_LEN 40
>
>EXEC SQL INCLUDE "/users/students/tbarrie/cs480/dbmodules/include/connect.c";
>
>int RetSectionsAtTime(int ST, int ET, int D, temporarytimetable_rec **Courses,
> int *NumCourses)
>{ int i;
>
> EXEC SQL Begin Declare Section;
> int n;
> temporarytimetable_rec Course;
> int StartTime;
> int EndTime;
> int Days;
> EXEC SQL End Declare Section;
>
> StartTime=ST;
> EndTime=ET;
> Days=D;
>
> EXEC SQL DECLARE sect_cursor CURSOR FOR
> SELECT *
> FROM gmcinnis.TemporaryTimetable
> WHERE StartTime=:StartTime and EndTime=:EndTime and Days=:Days;
>
> EXEC SQL SELECT count(*) INTO :n FROM gmcinnis.TemporaryTimeTable
> WHERE StartTime=:StartTime and EndTime=:EndTime and Days=:Days;
>
> printf("Error code after count: %d\n",sqlca.sqlcode);
>
> *NumCourses = n;
>
> *Courses = (temporarytimetable_rec *) malloc(n*sizeof(simptemp_rec));
>
> if (!sqlca.sqlcode)
> EXEC SQL OPEN sect_cursor;
>
> printf("Error code after cursor open: %d\n",sqlca.sqlcode);
>
> for(i=0;i<n && !sqlca.sqlcode;i++)
> {
> EXEC SQL FETCH sect_cursor INTO :Course;
>
> printf("Error code after fetch: %d\n",sqlca.sqlcode);
>
> strncpy((*Courses)[i].CourseCode,Course.CourseCode,6);
> (*Courses)[i].CourseNum = Course.CourseNum;
> (*Courses)[i].Section = Course.Section;
> strncpy((*Courses)[i].BuildCode,Course.BuildCode,5);
> (*Courses)[i].RoomNum = Course.RoomNum;
> (*Courses)[i].Days = Course.Days;
> (*Courses)[i].StartTime = Course.StartTime;
> (*Courses)[i].EndTime = Course.EndTime;
> strncpy((*Courses)[i].FacultyCode,Course.FacultyCode,6);
> (*Courses)[i].Requirements = Course.Requirements;
> (*Courses)[i].ExpectedSize = Course.ExpectedSize;
> }
>
> if (!sqlca.sqlcode)
> EXEC SQL CLOSE sect_cursor;
>
> return (sqlca.sqlcode);
>}
>
>/* main function for testing purposes */
>void main(int argc,char **argv)
>{ temporarytimetable_rec *results;
> int num,i;
>
> Connect("gmcinnis/jfish_at_osid");
>
> printf("Error code:%d\n",RetSectionsAtTime(830,945,2,&results,&num));
> printf("%d records found\n",num);
> for (i=0;i<num;i++)
> printf("%s %d %c %s %d %d %d %d %s %d %d\n",results[i].CourseCode,
> results[i].CourseNum, results[i].Section, results[i].BuildCode,
> results[i].RoomNum, results[i].Days, results[i].StartTime,
> results[i].EndTime, results[i].FacultyCode,
> results[i].Requirements, results[i].ExpectedSize);
>
> Disconnect();
>
> free(results);
>}
>---------------------------------------------------------------------------
>
>I've been over it a few times without seeing anything wrong. Am I missing
>something silly here?
>
>(Oh, and apologies if this is the wrong part of the comp.databases.oracle.*
>hierarchy for questions of this nature; I couldn't find a FAQ for these
>groups and had to guess.)
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |