Cursor select and update

I have a situation where I'm using Dynamic SQL at the ESQL/C level to
process "generic" SQL statements from another application and apply
them to our Ingres database (which is version 6.4 or so, various
platforms).

One of the possible scenarios is that the user controlling this
application can generate a query of the form

	    SELECT * FROM SALES

which my converter program assumes is for read only.  Accordingly I do
the PREPARE, DESCRIBE, then the OPEN CURSOR (which is implicitly read
only) and finally enough FETCHes to get the data.

Unfortunately the user might _occasionally_ want to update one of the
rows in this selection, so the generated statement is then something
like this
	UPDATE SALES ... WHERE CURRENT OF CURSOR

The problem is that the cursor is read-only so I can't process this!
There is no way I can tell in advance that the user might want to
update a table.  Concurrency is important, so I don't think I can lock
the table/s on the offchance that this is what they want to do.

What are the implications of using different locking strategies for
this?  Has anyone else been here before me and can point me in the
right direction?  Can I do this at all?

I'd appreciate email and I'll summarize, but I _do read this group
regularly!

Chris
--
            VISIONWARE LTD, 57 Cardigan Lane, LEEDS LS4 2LE, England
  Tel +44 532 788858 x238.  Fax +44 532 304676.  Email chris@visionware.co.uk
---------- "VisionWare:   The home of DOS/SQL/UNIX/X/VMS integration" ---------
> 
> 
> I have a situation where I'm using Dynamic SQL at the ESQL/C level to
> process "generic" SQL statements from another application and apply
> them to our Ingres database (which is version 6.4 or so, various
> platforms).
> [Rest deleted..]
 
There is a solution to your problem that uses the multiple connection approach.
When you first perform your database connection using the SQL CONNECT statement,use the syntax that provides a session id:

   EXEC SQL BEGIN DECLARE SECTION;
   int session1_id;
   int session2_id;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL CONNECT dbname SESSION session1_id;
   EXEC SQL CONNECT dbname SESSION session2_id;

This will get you two connected sessions in your single program execution.  Next
use the SET_SQL command to tell your program to communicate with the first
session:

   EXEC SQL SET_SQL(session=:session1_id);

Next, perform your cursor select, and if the user wants to update, switch
sessions and perform a normal update with keyed access:

   EXEC SQL OPEN etc.
   while(forever)
   {
      EXEC SQL FETCH etc.

      if user wants to update
      {
         EXEC SQL SET_SQL(session=:session2_id);
         EXEC SQL UPDATE table SET column = :new_value
         WHERE table.key_columns = :key_values
      }
   }

You should be aware that there may be locking implications with this scenario,
since INGRES sees your two sessions for what they are: two independent sessions
that must be protected from each other regarding data integrity.  You may need
to set readlock to nolock before beginning the cursor.


Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
Ingres Q & A
To William's Home Page
Email William