Pro*C/Oracle8.0.5/DECOSF- problem with Dynamic SQL/CURSOR

From: CoRey <cjc9024_at_omega.uta.edu>
Date: 29 Apr 1999 07:43:42 GMT
Message-ID: <7g92je$etj$1@news.uta.edu>

Greetings. I'm having a problem with Oracle that's driving me nuts. You'll have to forgive me, as this is the first semester I've worked with Oracle. I'm using Oracle8.0.5 on DEC OSF college account.

I'm a CSE student at UTA (Arlington, TX). We're implementing a simple database for a hypothetical airline that serves special meals. I have gotten a lot of the project to work -I can insert,remove,update data through my C++/embedded SQL program. I've written a Makefile to take care of handling proc invocation for me. I'm storing my files under SCCS. I'm adept at typing in SQL commands into 'sqlplus' interactively. I've figured out the SELECT statements I need to generate my reports, but I am having trouble getting them embedded in the C++ program.

The problem has to do with selection of more than one row from the RESERVATION table. I'm trying to select entries like the following interactive command:

SELECT * FROM RESERVATION WHERE MEALQUALITY<5

   AND FLIGHTDATE BETWEEN '15-APR-1999' AND '17-APR-1999'; The idea is for the user to enter a startdate and an enddate, and have the program list out all reservations where the customer thought their meal quality was < 5 (5 being the best), between the start and end date. Here's the relevant code snippet:

void low_quality() {

    EXEC SQL BEGIN DECLARE SECTION;
    char startDate[FLIGHTDATE_LEN + 1];//FLIGHTDATE_LEN==12     char endDate[FLIGHTDATE_LEN + 1];
    VARCHAR sqlstmt[1024];
    EXEC SQL END DECLARE SECTION;     bzero(startDate, FLIGHTDATE_LEN + 1);     bzero(endDate, FLIGHTDATE_LEN + 1);

    cout << "Low Quality meals report\n";     cout << "Enter start Date: ";
    cin >> startDate;
    cout << "Enter end Date: ";
    cin >> endDate;

  // Dynamic SQL method - build up string first in sqlstmt     strcpy((char *)sqlstmt.arr, "SELECT * FROM RESERVATION WHERE \ MEALQUALITY<5 AND FLIGHTDATE BETWEEN '");

    strcat((char *)sqlstmt.arr, startDate);
    strcat((char *)sqlstmt.arr, "' AND '");
    strcat((char *)sqlstmt.arr, endDate);
    strcat((char *)sqlstmt.arr, "';");

    sqlstmt.len = (unsigned short) strlen((char *) sqlstmt.arr);

    EXEC SQL PREPARE S FROM :sqlstmt;
    EXEC SQL DECLARE C CURSOR FOR S;
    EXEC SQL OPEN C;
    EXEC SQL WHENEVER NOT FOUND DO break;

    while (1) {

        EXEC SQL FETCH C INTO :rrec:rind;

	Reservation R(rrec, rind);
	R.print();

    }
    EXEC SQL CLOSE C;
}

I got this idea from reading cppdemo2.cc, which I happend to find with find(1). The line Reservation R(rrec, rind) just calls a special constructor for the class Reservation (which just mirrors the entries in the RESERVATION table), which fills in the data members in that object with the fields in the rrec structure. This is just so that I can conveniently print it with R.print();

This compiles just fine with proc; however, when I run the code, I get thrown into an infinite loop, with each rrec being filled with nothing but NULL characters.

I'm afraid that there's a memory leak or something in my program, far away, which is causing this to happen. I must admit, this is the first time I've ever worked with SQL. I read a Linux database book on SQL - but it's not Linux specific; it covers relational database theory, then introduces SQL. Unfortunately, it doesn't cover Oracle, I can't find any Oracle documentation in our library, and my GTA doesn't seem to know s**t.

Previous to this, I had tried another method, which did the exact same (wrong) thing as the above code: EXEC SQL DECLARE lowqualty CURSOR FOR
  SELECT * FROM RESERVATION WHERE MEALQUALITY<5 AND   FLIGHTDATE BETWEEN :startDate AND :endDate;

Since I thought that maybe Oracle wasn't recognizing my dates, I threw a TO_DATE(:startDate, 'DD-MON-YYYY') in there; but the same bug was there.

BUT, if I replace that line with something fixed, like BETWEEN '15-APR-1999' AND '17-APR-1999', the code WORKS! This has me TOTALLY boggled.

One other (minor)problem I had is initially, I wanted to use C++ string datatype - but various problems led me back to trusty old char arrays.

Please direct replies to : I hope someone has an easy answer!

cjc9024_at_omega.uta.edu Received on Thu Apr 29 1999 - 02:43:42 CDT