Re: scroll cursors ? esql-c prototypes ?

In article <BwLFGA.M66@ibg1.ibg.sub.org> ado@ibg1.ibg.sub.org (Christoph Adomeit) writes: >hi esql-c freaks, > >I've got 2 problems with informix : >a) >I wrote a nice tool in esql-c which allows the user to enter a select- >statement and then generates a screen for it and allows the user to >update all the columns he mentioned in the select-statement. This has >nothing to do with perform, i use my own screen handling. >This mainly works by using a scroll-cursor which fetches the data, and >a second "cursor for update" which handles updating. >When the user chooses "Previous", to reupdate the previous updated >statement, I've got a problem: Informix fetches the data from a temporary >table which holds the original data, it does not fetch the data, the >user already updated. So the user gets confused. >How can I force the database then to fetch the newer data. I can close >the cursor and reopen it. This will do it, but then I don't know how to >fetch the same row the user was working at because all this updating >works with a completely dynamical SQL-statement. Is there a way to >determine the number of the row in the active set (not the rowid) ?. >Then I could close the scroll cursor, redeclare and open it and do a >"fetch absolute". Then I would have the right row again. A way to do this is to just select the rowid of each row rather than scrolling cursors. Use a structure similar to this: struct c_row { long rowid; /* rowid of the row */ int delflg; /* deleted flag 1=deleted 0=not deleted */ } Use a regular, non scrolling cursor to select just the rowid from the user's select statement (just replace the select list that the user provided with "select rowid "). Fetch each row, fill a c_row struct defined above with the rowid, and "0" for delflg, write the c_row struct to a temp. file, and increment a "number of records" counter. When all rows have been selected, close the temp file, and re-open for read/write. You know how many rows were found (the "# of records" counter) and the rowid of each. To perform the "Next/Previous" operations, just keep a "current record" counter (set initially to -1). For a "next" operation, increment the current counter -- if it equals "# of records" counter, you are at the end of the list and subtract 1 from it, otherwise, read a c_row struct from the temp. file. For a "previous" operation, decrement the current counter -- if it equals -1 you are at the begnning of the list and reset to 0, otherwise, lseek to the position of the "current row" in the temp. file, and read a c_row struct from the temp. file. For both "next" and "previous", continue adjusting the counter and reading a c_row struct as long as the "delflg" == 1, which indicates that this record has been deleted. To actually select the data for the record to be drawn on your screen, use the user's select list, but replace the where clause with an sprintf that ends like "...where rowid=%ld", c.rowid);". Then, $prepare the select, $describe the select and set up your SQLDA struct, $declare a cursor for the select, $open the cursor, $fetch the cursor using the SQLDA struct, convert the data to ASCII, and display on your screen. To perform the "Update" operation, just build the update statement using the user's select item list, and replacing the where clause with: sprintf(update, "update table blah set ... where rowid=%ld", c.rowid); $prepare the statement, and $execute it. To perform the "Delete" operation, build a delete statement like so: sprintf(delete, "delete from blah where rowid=%ld", c.rowid); $preare the statement, and $execute it. In addition, set "delflg" to 1 in the "current row" c_row struct and write it back out to the temp. file. This is more or less the way that PERFORM keeps track of records. I od'ed a temp. file that perform created, and noticed the rowid of each record in perform's active list in the temp file (among other things). Also, I noticed that when I deleted a row from the active list, that a flag was being set to 1 for that row. I haven't figured out what else their temp keeps track of, but but it definitley has the rowid and a "deleted" flag. -- Ray Tripamer ray@unlv.edu