Embedded SQL problems in C Server - help, ProC shrink needed.

Dear Fellow Net Readers and Oracle Pro-C Coders:

I have a perplexing problem, limited experience, and I NEED YOUR HELP.

I have coded a server, which is strait-forward C code with embedded Pro-C. I am using the lastest version of Oracle 6 available for Apollo workstations.

What my server should do:

   My server runs on an Apollo workstation, and connects to an Oracle    server running on a Sequent box. The server wakes up once a minute,    connects to the database, and processes records that it finds - rows    with a containing columns with a certain value. When it is finished,    it disconnects from the database, and sleeps for another minute before    waking up again.

This all works very well, until I edit a flat ascii control file (which is closed while the server is sleeping). I change one character on one line of this flat file and then...

What my server then does:

   It processes the first set of records fine, and then sleeps. The flat    file in question is then edited during the minute that the server is    sleeping. THEN the server wakes up, processes the first record perfectly,    but then will start encountering -1001 (Invalid Cursor) errors when    either UPDATING the record, or (in another section of code) upon SELECTING    a new sequence number from a sequence number generator. The server will    have this error upon encountering records 2 through n.

My frustration is that this only occurs when the flat file is modified. Secondly there is no cursor anywhere near the points of failure. True, there is a single cursor in the program, and it is open at the time that this error is encountered. However, an UPDATE really shouldn't be giving me an Invalid Cursor error. (Yes, I understand that TECHNICALLY it IS a cursor because of the "EXEC SQL" interpretation by the precompiler.)

I have asked the best embedded SQL people on my team, and several DBAs about this issue - they have not been able to help me. I am going to be spending my next debugging effort focusing on global variables, but I will be VERY grateful for ANY response. If your suggestion leads to the solution of my problem, I will send you a smoked salmon. Seriously!

Here is a high level pseudo code description of my server:

           EXEC SQL INCLUDE sqlca.h
           declare all host variables globally.

           for  (;;)                         /* for ever - loop endlessly */
             {
             connect to database
             open, read, close flat file
             describe cursor
             open cursor        /* cursor is open and sqlca.sqlerror != 0 */  
             fetch into cursor until 1403       /* no matching rows found */ 
                   process records
                      get new sequence number
                      UPDATE row that was fetched  /* -1001s occur here */
             close cursor
             sleep 1 minute
             }
               

Please respond (even with humor) to robh_at_bcstec.boeing.com instead of humiliating me on the net.

Thanks For Your Help!
Robert.

-- 


-------------------------------------------------------------------------

EMAIL: robh_at_bcstec.boeing.com or uunet!bcstec!robh  Robert Hindman-BCS
Disclaimer: My opinions and postings are not those of the Boeing Company.


-------------------------------------------------------------------------