Re: Can I do this in ESQL/C

On Wed, 26 Aug 1998 dirk.hellmann@laufenberg.com wrote: > Inside my file foo.ec i want to do the following > . > . > $int dataID; > $int classTYPE; > $char tableName[32]; > > strcpy(tableName, "foo1"); > > $SELECT tableName.dataid, tableName.classtype INTO $dataID, $classTYPE > FROM tableName WHERE tableName.dataid < 50; . . . I want to use an > esql-variable for the tableName instead of the real table-name. The compiler > say nothing but i got an 206 - specified table not in DB - when running, > because esql didn't recognize, that i want to use a variable. How can i tell > esql to use this variable, or is it impossible. All instances of an ESQL/C host variable in an ESQL/C statement have to be prefixed with either a colon ':' or a dollar '$' symbol (apart from one use in a DESCRIBE statement--RTM). So, to have any chance of working, you'd have to write: EXEC SQL SELECT :tableName.dataid, :tableName.classtype -- Won't work! INTO :dataID, :classTYPE -- Won't work! FROM :tableName -- Won't work! WHERE :tableName.dataid < 50; -- Won't work! However, there's another rule which says that there are only a limited number of places where you can use an ESQL/C host variable, and one of the many places where you cannot do this is in the FROM clause. Hence the "Won't work!" comments above. Of course, you can achieve the required effect by preparing the statement: sprintf(bigbuffer, "SELECT %s.dataid, %s.classtype FROM %s WHERE %s.dataid < 50", tableName, tableName, tableName, tableName); or, somewhat more economically, use a table alias to avoid repeating the full table name more than once: sprintf(bigbuffer, "SELECT T.dataid, T.classtype FROM %s T WHERE T.dataid < 50", tableName); Then (with error checking and loop control structures added): EXEC SQL PREPARE p_select FROM :bigbuffer; EXEC SQL DECLARE c_select CURSOR FOR p_select; EXEC OPEN c_select; EXEC FETCH c_select INTO :dataID, :classTYPE; EXEC CLOSE c_select; EXEC SQL FREE c_select; EXEC SQL FREE p_select; Every time you change the value of tableName, you have to re-prepare the statement and re-declare the cursor and so on. Of course, your fixed column names and fixed WHERE clause criterion are a little suspect for a general solution... Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn