Re: Questions about ESQL/C 5.0

->From: wd@infodn.rmi.de (Walter Doerr) ->Subject: Questions about ESQL/C 5.0 ->Date: 13 Apr 94 16:44:32 GMT ->Reply-To: wd@infodn.rmi.de (Walter Doerr) ->Organization: infodn, Dueren, Germany -> ->Hello! -> ->I am using ESQL/C 5.0 and I am looking for information on how ->to write certain functions. -> ->As an exercise, I am writing a program that is given a database and table ->name. The program should be able (without modifications!) to display the ->structure of the table (number of columns, name and type of columns, number ->of rows, etc.), display the contents of the table and allow inserts, updates ->and deletes. ->The basic things such as SELECT, INSERT and UPDATE are working, ->but I think that some things can be done in a more elegant and/or simpler way. -> -> ->1. I would like to obtain information on a table (such as column name, -> type, etc.). Currently I am using a program like this: -> ... details omitted ... -> -> Is there another way to this without $PREPARE, $ALLOCATE, etc. -> (i.e. less overhead)? -> ->2. Is there a way to specify an INSERT statement (via $PREPARE or some -> other way) without knowing the number of columns? -> Currently I am using something like: -> ... more stuff omitted ... -> ->3. Using a cursor, I would like to UPDATE a row that I have just FETCHed. -> How can I write an "UPDATE WHERE CURRENT OF cursor" statement that uses -> the cursor from a $PREPAREd SELECT statement? -> ->4. Is there a way to determine the number of rows returned by a -> $PREPAREd SELECT WHERE... statement? -> ->5. Is there a way to use rowid() from within ESQL/C? If not, is there a -> similar function? -> ->Thanks for any help you may provide! -> ->-Walter First a disclaimer, then on to the answers! Disclaimer: I don't use ESQL/C; I do all my work in 4GL with occasional C subroutines. The DESCRIBE ... USING DESCRIPTOR and related features available in ESQL/C may actually be better than what I am about to suggest. Answers, by number: 1. Check out the `systables' and `syscolumns' tables in the system catalog that is part of every Informix database. Documentation on these moves around a little by version, but is usually in an appendix of the SQL reference or user guide. Table and column names are EASY to get from these tables. Datatypes are encoded, so they are a little harder to translate, but not too bad. Former threads in this forum produced functions for decoding them, etc., etc. I probably have them in my data attic somewhere. 2. systables.ncols gives the number of columns in a table. Similarly, systables.nrows gives the number of rows, assuming that UPDATE STATISTICS has been done recently. 3. & 5. I can't help you with these. 4. If you can afford to do a double select, then execute a prepared SELECT COUNT(*) WHERE ... using the same where conditions before you do your actual SELECT. I am not aware of another way to predict exactly how many rows a selection set will contain. Obviously the optimizer uses some estimates of the selection set size, but I don't know how to return the values that it uses. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\