From: robh@bcstec.ca.boeing.com (Rob Hindman)
Newsgroups: comp.databases.oracle
Subject: Embedded SQL problems in C Server - help, ProC shrink needed.
Keywords: SQL Embedded Oracle Sadness Frustration Desperation
Date: 16 Oct 92 08:14:48 GMT
Organization: Boeing Computer Services, Seattle


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@bcstec.boeing.com instead of
humiliating me on the net.

Thanks For Your Help!
Robert.
-- 
-------------------------------------------------------------------------
EMAIL: robh@bcstec.boeing.com or uunet!bcstec!robh  Robert Hindman-BCS
Disclaimer: My opinions and postings are not those of the Boeing Company.
-------------------------------------------------------------------------
Newsgroups: comp.databases.oracle