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 >X-Informix-List-Id: <news.6306> >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: > > $PREPARE foo FROM "SELECT * FROM table"; > $ALLOCATE DESCRIPTOR 'bar'; > $DESCRIBE foo USING SQL DESCRIPTOR 'bar'; > $GET DESCRIPTOR 'bar' $count = COUNT; > for (i=1; i<=count; i++) { > $GET DESCRIPTOR 'desc' VALUE $i > $type = TYPE, > $name = NAME; > printf("%d %s\\n", type, name); > } > > Is there another way to this without $PREPARE, $ALLOCATE, etc. > (i.e. less overhead)? Not using standard SQL. If you want to investigate old-style SQLDA structures, you can, but it is not any tidier. >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: > > $PREPARE foo FROM "INSERT INTO table VALUES(?,?,?)"; > > were I need to know the number of rows in order to specify the > correct number of "?" in the VALUE clause. > (I am using "$SET DESCRIPTOR" and "$PUT cursor USING DESCRIPTOR" to > actually do the insert.) Not really. You can do it by column-name/value association if you don't mind leaving some columns at default or null: INSERT INTO SomeTable(Column01, Column02, Column03) VALUES (?, ?, ?) >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? You specify the name of the cursor in the string that contains the UPDATE statement. If you've used: DECLARE c_name CURSOR FOR SELECT ... Then you embed "c_name" in the prepared UPDATE statement. Since you are using 5.00 ESQL/C, you can also use string value cursor names, and variables, so I'd do: EXEC SQL BEGIN DECLARE SECTION; char c_name[19]; /* Cursor name */ char p_name[19]; /* Statement name */ EXEC SQL END DECLARE SECTION; strcpy(p_name, "some_statement"); strcpy(c_name, "some_cursor"); EXEC SQL PREPARE :p_name FROM "SELECT ..." EXEC SQL DECLARE :c_name CURSOR FOR :p_name; sprintf(stmt, "UPDATE %s SET %s WHERE CURRENT OF %s", tablename, set_clause, c_name); >4. Is there a way to determine the number of rows returned by a > $PREPAREd SELECT WHERE... statement? None. Or rather, you can't execute a PREPAREd SELECT statement; you DECLARE a cursor, OPEN it, and FETCH repeatedly. While you're doing the FETCHes, you can count the number of rows easily enough. When you've FETCHed the last row and got SQLNOTFOUND, then the number of rows returned should be available from the SQLCA area. >5. Is there a way to use rowid() from within ESQL/C? If not, is there a > similar function? There's no problem using ROWID without any parentheses. I'm not sure what trying to use it with parentheses means -- but since I'm confused, there's a moderate chance the Engines and/or ESQL/C compiler is confused too. Have you remembered to allow for MODE ANSI databases where the table must be prefixed by the owner name (especially system catalogue tables)? And where there are implicit transactions? And how does your program handle transaction on logged databases? These are rhetorical questions. I don't mind what the answer is as long as you've thought briefly about them. Writing a general purpose program for any mode of database is harder than dealing with non-logged databases. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>