Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro C likes DECLARE, OPEN cursor, but not FETCH?
Pro C likes DECLARE, OPEN cursor, but not FETCH? [message #94052] Mon, 24 November 2003 09:05 Go to next message
Chris Cleveland
Messages: 3
Registered: November 2003
Junior Member
Hi All, I was wondering if anyone has insight on this one... I'm using Oracle 9i Personal database and the corresponding version of Pro*C/C++ for a school project. After a lengthy configuration debaucle, I finally got the whole thing installed properly.

I won't go into the gory details of the project itself, but it will involve numerous queries (selects, updates, inserts) and I'm basically stuck at the SELECT portion. I can get a single row returned fine, but am unable to successfully use cursors for some reason. I have the syntax correct, but Pro*C/C++ returns the error:

"Semantic error at line 52, column 35, file C:Documents and SettingsChrisMy DocumentsCOSC Lab 6proctestCpp2.pc:
EXEC SQL FETCH flightinfo INTO :flight_ptr;
..................................1
PCC-S-02322, found undefined identifier"

...each time I try to precompile. Here's the code (note that I removed my username, password and db name for obvious reasons):

===============================

#include <stdio.h>
#include <sqlca.h>

EXEC SQL BEGIN DECLARE SECTION;
char username[[200]] = <my username>; /* Give your username*/
char password[[100]] = <my password>; /* Give your password */
char str[[100]] = <my database>;
char strSelect[[200]]= "SELECT flightno,maxseats FROM flight ORDER BY flightno ASC";
EXEC SQL END DECLARE SECTION;

struct flights
{
int flightno;
int maxseats;
};

/* Declare function to handle unrecoverable errors. */
void sql_error();

void main()
{
struct flights *flight_ptr;
/* Allocate memory for flights struct */
if ((flight_ptr =
(struct flights *) malloc(sizeof(struct flights))) == 0)
{
fprintf(stderr, "Memory Allocation Error.n");
exit(1);
}

/* Connect to ORACLE */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :str;
printf("nConnected to ORACLE as user: %sn", username);

/* Declare the cursor */
EXEC SQL PREPARE sql_stmt FROM :strSelect;
EXEC SQL DECLARE flightinfo CURSOR FOR sql_stmt;

/* Open the cursor */
EXEC SQL OPEN flightinfo;

/* Get ready to print results */
printf("Flight # Max Seatsn");
printf("-------- ---------n");

/* Loop, fetching all of the flights */
EXEC SQL WHENEVER NOT FOUND DO break;

for(;;)
{
EXEC SQL FETCH flightinfo INTO :flight_ptr;
printf("%-11d%4dn",flight_ptr->flightno,flight_ptr->maxseats);
}

/* Close the cursor */
EXEC SQL CLOSE flightinfo;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

void
sql_error(msg)
char *msg;
{
char err_msg[[512]];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("n%sn", msg);

/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*sn", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(1);
}

======== END OF CODE ==========

I've been racking my brain on this one. Spent about 6 hours yesterday trying to figure out what I'm doing wrong. I'm using all of the default options in the precompile phase, and if I comment out the lines inside the for(;;) loop:

//EXEC SQL FETCH flightinfo INTO :flight_ptr;

And the line right below this one, then the code precompiles just fine.

Any ideas? I know that other parts of my code probably aren't very refined... forgive me please, I'm a n00b! :)

Thanks in advance for any assistance you can offer...
Re: Pro C likes DECLARE, OPEN cursor, but not FETCH? [message #94076 is a reply to message #94052] Mon, 05 January 2004 02:24 Go to previous messageGo to next message
Lakshmi Narayana P
Messages: 2
Registered: January 2004
Junior Member
Please check the error code at each level
Example
-- After EXEC SQL PREPARE
-- After EXEC SQL DECLARE
-- After EXEC SQL OPEN Cursor

I think either of the one statement is failing to execute. So it is not able to proceed next, that is for fetching.

Checking the error code
if (sqlca.sqlcode)
{
printf ("Failed to ….. error %d opening ", sqlca.sqlcode);
}

If opening cursor is failed , may be this is due ORACLE is not having sufficient space to open cursors.

Please declare indicator variable and check the content of the indicator variable before
printing the actual variable.

If you need any help Please feel free to mail me @ lakshminarayanap@ctd.hcltech.com
Re: Pro C likes DECLARE, OPEN cursor, but not FETCH? [message #94079 is a reply to message #94052] Wed, 07 January 2004 21:50 Go to previous messageGo to next message
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
Member
Hi chris,
I included SQLCA in your program and tested. It worked fine.
Just sending that code to you for your reference.
It should work for you.

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
char user[[100]]="id/password@mydev";
char strSelect[[200]]= "SELECT flightno,maxseats FROM flight ORDER BY flightno ASC";
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
struct flights
{
int flightno;
int maxseats;
};

/* Declare function to handle unrecoverable errors. */
void sql_error();

void main()
{
struct flights *flight_ptr;
/* Allocate memory for flights struct */
if ((flight_ptr =
(struct flights *) malloc(sizeof(struct flights))) == 0)
{
fprintf(stderr, "Memory Allocation Error.n");
exit(1);
}

/* Connect to ORACLE */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :user ;
printf("nConnected to ORACLE as user: %sn", user);

/* Declare the cursor */
EXEC SQL PREPARE sql_stmt FROM :strSelect;
EXEC SQL DECLARE flightinfo CURSOR FOR sql_stmt;

/* Open the cursor */
EXEC SQL OPEN flightinfo;

/* Get ready to print results */
printf("Flight # Max Seatsn");
printf("-------- ---------n");

/* Loop, fetching all of the flights */
EXEC SQL WHENEVER NOT FOUND DO break;

for(;;)
{
EXEC SQL FETCH flightinfo INTO :flight_ptr;
printf("%-11d%4dn",flight_ptr->flightno,flight_ptr->maxseats);
}

/* Close the cursor */
EXEC SQL CLOSE flightinfo;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

void
sql_error(msg)
char *msg;
{
char err_msg[[512]];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("n%sn", msg);

/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*sn", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(1);
}

Let me know if it is not working still.

Giridhar Kodakalla
Re: Pro C likes DECLARE, OPEN cursor, but not FETCH? [message #94080 is a reply to message #94052] Thu, 08 January 2004 03:22 Go to previous message
Chris Cleveland
Messages: 3
Registered: November 2003
Junior Member
Thank you Giridhar and Lakshmi for your assistance! After much effort with the code samples you provided and still no success, I began to try different combinations in the Options of Pro*C/C++ and determined that it was a problem with the Parse level that I was using. After changing the parse level, I was able to successfully pre-compile and Visual C++ was then able to compile the code.

Thanks again for your help!!
Previous Topic: fetching into a double from pro*c program
Next Topic: Delete inside a cursor loop
Goto Forum:
  


Current Time: Thu Mar 28 09:50:09 CDT 2024