0 Replies - 5208 Views - Last Post: 01 April 2013 - 02:14 PM

#1 edwards12691  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 18
  • Joined: 12-February 10

Cursor fetch out of sequence with SQL-embedded C++

Posted 01 April 2013 - 02:14 PM

Sorry if this is the wrong place, I wasn't sure if I should put it here or in the C++ forum.

I'm having cursor issues. For an assignment, we have to query a database from inside a C++ program. User input is to dictate which of several queries is to be made. The prof gave us some sample code, and told us to modify that program for the homework. I think I have everything almost taken care of, except whenever I make a selection and the program tries the query the database, it returns error ORA-01002: Fetch out of sequence. I've been reading all around the internet, but I have yet to find a fix for my problem.

The sample code works fine, and the prepareAndExecuteIt function is almost completely unchanged, which makes me think that the problem is somewhere in the buildAfixedSQL function, though I could be wrong. Thank you for any help!

#include <iostream.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
//#include <stream.h>

// Parse=partial by default when code=cpp so, preprocessor directives are
// recognized and parsed fully.
#define     UNAME_LEN      20
#define     PWD_LEN        40

// Declare section is required when CODE=CPP and/or PARSE={PARTIAL|NONE}
exec sql begin declare section;
  VARCHAR username[UNAME_LEN];  // VARCHAR is an ORACLE supplied struct
				// Has actual data & length
  varchar password[PWD_LEN];    // varchar can be in lower case also  
  varchar sql_statement[1000];	//holds the sql stat being constructed..
  varchar outputInfo[500];	//retrieved output from database

exec sql end declare section;

// Declare error handling function
void sql_error(char *msg);    
void connectToDatabase();
void commitAndExit() ;
void buildAfixedSQL();
void prepareAndExecuteIt ();
exec sql include sqlca;

main()
{

//  Call sql_error() function on any error in an embedded SQL statement
    exec sql WHENEVER SQLERROR DO sql_error("Oracle error");


    exec sql 
	WHENEVER SQLERROR 
        DO sql_error("ORACLE error:"); 
    connectToDatabase();

    buildAfixedSQL();
    prepareAndExecuteIt ();
    commitAndExit();

}

void connectToDatabase() {

/*  Fixed username and password  */
    strcpy((char *)username.arr,"sp201368");
    strcpy((char *)password.arr,"");
    username.len = strlen((char *) username.arr);
    password.len = strlen((char *) password.arr);

//  CONNECTS TO DATABASE 
    exec sql CONNECT :username IDENTIFIED BY :password;
    cout << "\nConnected to ORACLE as user: "
                << (char *)username.arr << endl << endl; 

}


void commitAndExit() {

    exec sql commit work release;
    exit(0);  
}
void sql_error(char *msg)
{
    exec sql WHENEVER SQLERROR CONTINUE;
    cout << endl << msg << endl;
    cout << sqlca.sqlerrm.sqlerrmc << endl;	
    exec sql rollback release;
    exit(1);
}      

void buildAfixedSQL() {
	int choice;
	cout << "Please enter a number to select a choice:" << endl;
	cout << "1.		Find the names of aircraftsuch that all pilots certified to operate them have salaries over $80,000" << endl;
	cout << "2.		For each pilot who is certified on more than X1 aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified" << endl;
	cout << "3.		For all aircraft with cruisingrange over X2 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft" << endl;
	cin >> choice;
	char num[2];
	switch(choice){
		case 1:	strcpy((char *)sql_statement.arr,"SELECT aname FROM aircraft A, certified C, employees E");
				strcat((char *)sql_statement.arr," WHERE A.aid = C.aid AND C.eid = E.eid AND salary > 80000");
				break;
		case 2: cout << "How many aircraft should each pilot be certified with?" << endl;
				cin >> num;
				strcpy((char *)sql_statement.arr,"SELECT DISTINCT E.eid, MAX(cruisingrange) FROM aircraft A, certified C, employees E");
				strcat((char *)sql_statement.arr," WHERE E.eid = C.eid AND A.aid = C.aid GROUP BY C.eid HAVING COUNT(*) > ");
				strcat((char *)sql_statement.arr, num);
				break;
		case 3: cout << "What is the minimum cruising range to be searched for?" << endl;
				cin >> num;
				strcpy((char *)sql_statement.arr,"SELECT aname, AVG(salary) FROM aircraft A, certified C, employees E");
				strcat((char *)sql_statement.arr," WHERE C.eid = E.eid AND C.aid = A.aid GROUP BY A.aid HAVING cruisingrange > ");
				strcat((char *)sql_statement.arr, num);
				break;
	}
}

void  prepareAndExecuteIt() {

//  Prepare the query; define a cursor, execute it...

    sql_statement.len = strlen((char *) sql_statement.arr);
    exec sql PREPARE S1 FROM :sql_statement;

/* The declare statement associates a cursor with a
 * PREPAREd statement.  The cursor name, like the statement
 * name, does not appear in the Declare Section.
 * A single cursor name can not be declared more than once.

*/

    exec sql declare C1 cursor FOR S1;


    exec sql open C1;

    exec sql FETCH C1 INTO :outputInfo;

     cout << sqlca.sqlerrm.sqlerrmc << endl;
     if ( sqlca.sqlcode !=  0) {
        cout << "past fetch..." <<endl;

	cout << sqlca.sqlerrm.sqlerrmc << endl;
        exec sql rollback release;
        exit(1);
     }
     cout << (char *)outputInfo.arr << endl;
     exec sql close C1;
}


Is This A Good Question/Topic? 0
  • +

Page 1 of 1