Re: fetch ... into ... (mismatched # of fields)

On Tue, 2 Feb 1999, Colin M McGrath wrote: > Question: When we do a "FETCH ... INTO ...", and the # of fields listed > on the FETCH side is greater the the # of fields listed on the INTO side, > where in memory do the extra fields go? What are the consequences, if any, > of doing that with a 5.x and/or a 7.x engine? In the latest documentation > I've seen (Informix Guide to SQL Syntax, Version 7.2, Volume 1), it is > explicitly stated that "Each value from the select list of the query ... > must be returned into a memory location." (page 1-301) It depends more on the version of ESQL/C than it does on the server. IIRC, in older versions, if the database returned unused values, then the unused values were simply discarded, but if too few values were returned, then an error was generated; I expected to see error -254 (Too many or too few host variable given). However, I wanted to check on this, and I wrote the following simple program: #include <stdio.h> int main(void) { EXEC SQL BEGIN DECLARE SECTION; long i1, j1; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER ERROR STOP; EXEC SQL DATABASE stores; EXEC SQL CREATE TEMP TABLE Wotsit(i INTEGER NOT NULL, j INTEGER NOT NULL); EXEC SQL INSERT INTO Wotsit VALUES(1, 2); EXEC SQL WHENEVER ERROR CONTINUE; EXEC SQL SELECT i, j INTO :i1 FROM Wotsit; printf("too few host variables: SQLCA.SQLCODE = %ld", sqlca.sqlcode); /* Beware - there's a cheat below */ printf(": warnings <<%8.8s>>\\n", &sqlca.sqlwarn.sqlwarn0); EXEC SQL SELECT i, j INTO :i1, :j1 FROM Wotsit; printf("correct host variables: SQLCA.SQLCODE = %ld", sqlca.sqlcode); printf(": warnings <<%8.8s>>\\n", &sqlca.sqlwarn.sqlwarn0); EXEC SQL SELECT i INTO :i1, :j1 FROM Wotsit; printf("too many host variables: SQLCA.SQLCODE = %ld", sqlca.sqlcode); printf(": warnings <<%8.8s>>\\n", &sqlca.sqlwarn.sqlwarn0); return(0); } With ESQL/C versions 5.08.UD1, 7.24.UC1, 9.15.UC2 (ClientSDK 2.01.UC2) and 9.16.UC1 (ClientSDK 2.10.UC1), I got the same results: too few host variables: SQLCA.SQLCODE = 0: warnings <<W W >> correct host variables: SQLCA.SQLCODE = 0: warnings << >> too many host variables: SQLCA.SQLCODE = 0: warnings <<W W >> That is, a warning is given, but not an error. I've not been bothered to test whether declared cursors with FETCH statements behave differently, nor have I run up a 4.x database (I've got 4 different versions of OnLine running on my machine at the moment, and that's enough!). An exercise for the reader. Also, self-evidently, I don't remember correctly. Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn