Dynamic sql in esql/c 
Author Message
 Dynamic sql in esql/c

I'm porting my company's C++ c-isam based class library to esql/c.
In the process I need to provide dynamice read, write, update, delete
for all the tables in the databases. Here are some of the problems I ran
into:

1. I can't open a cursor order by for update. I'm trying to replicate the
c-isam functionality where you do a start using an index and the
reads will be done in the index order which works fine when i'm not updating
the rows, but when I try to do prepare /open the cursor I get an error
and the manual confirms that you cannot open an cursor containing order by
for update. Why? Any ideas for work arounds.

2. Locking. I need to read a row, lock it, update it, and release the lock.
All of this has to be done dynamically which meens that I can't use host
variables or static cursor names. The way I have it working right now is
to do
 a. select *, rowid from params for update
 b. update params set * = (?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?
 ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,? ) where rowid = ?
 c. fetch, exectue ...

 The problem is that the row remains locked until I do the next fetch.
 I would like to be able to explicitly unlock it after the update.
 I looked at update where current of, but couldn't think of doing
 that with dynamic cursors. I guess I could try using transactions since
 the manual says that all locks are released at the end of a transaction,
 but I'd rather not introduce this additional complexity unless there
 is no other way of doing this.

3. Dynamic single row selects.
        Why can I do :
    update params set * = (?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?
     ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,?  ,? ) where rowid = ?
        But can't do:
    select *, rowid from params where a = ? and b = ?
        when I do for the above
        $open $cursor_name;
        I get
    -254    Too many or too few host variables given.

    The '?' for the where clause are interpreted at open time rather than
    at fetch time where their definition would be taken from the sqlda
    structure. This is what happens with updates.

    So. Short of declaring, preparing openning fetching and closing
    each time I want to get a single row is there a work around.

    I might just have to hack this and generate code to create an array
    of    struct sqlvar_struct _sqobind[] and imitate the way
    esqlc does the binding, but I hate doing this kind of things mainly
    when they're bound to change with each upgrade.

Any ideas, remarks or work arounds would be apreciated,

Thanks

Dror matalon
Real Time Solutions.



Sun, 16 Oct 1994 02:37:08 GMT
 Dynamic sql in esql/c

Quote:

> 1. I can't open a cursor order by for update....Why?

Because ordering implies a sort and the return of rows from a
temporary table created by the sort.  Potentially a very long
time lag between OPEN and the FETCH of the first row.  Hence
in order to allow update with order-by while maintaining integrity,
the engine would have to (a) retain info on the original location
of each sorted row and (b) lock each original selected row before
sorting it, and keep the locks as long as the cursor was open.

Quote:
> ...Any ideas for work arounds.

The best is to not require updating in some sequence; the serial
dependency implied is counter to the relational model. However,
the usual workaround is to make the ordered cursor not for update,
but to have a second cursor that fetches only a single row, by
rowid, for update.  Open it, fetch it, re-verify the row needs
updating, update where current, and close.

[lots deleted which I didn't understand, sorry]

Quote:
>     I might just have to hack this and generate code to create an array
>     of    struct sqlvar_struct _sqobind[] and imitate the way
>     esqlc does the binding, but I hate doing this kind of things mainly
>     when they're bound to change with each upgrade.

This is not a hack, but rather the standard, supported approach.
Read the manual on DESCRIBE and ALLOCATE DESCRIPTOR.


Mon, 17 Oct 1994 03:42:31 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Need help on dynamic SQL of esql

2. Question about Dynamic SQL in ESQL

3. dynamic SQL in ESQL 7.22

4. dynamic SQL in ESQL/C

5. Help On Dynamic SQL Using ESQL/C

6. Using ESQL/C Dynamic SQL System Descriptors

7. Dynamic SQL SELECT Statements and text blobs using ESQL/C and OnLine 4.10

8. Using dynamic sql parameters in ESQL/C

9. Dynamic SQL vs. ESQL/C

10. Problems with dynamic SQL statements in ESQL/C

11. ESQL/C dynamic SQL and BLOBS

12. ESQL dynamic selects question


 
Powered by phpBB® Forum Software