Confusing 4GL Error

I've run into an error 217 [Column (XXXX) not found in any table in the query] while executing an SQL statement within a 4GL. The confusing part is that the column most definitely does exist in the referenced table. Example 4gl - --------------------------------------------------------------------------- database sysmaster main unload to "test.unl" select syschunks.chknum, (syschunks.chksize - syschunks.nfree) col2 from syschunks end main --------------------------------------------------------------------------- compiling with "c4gl -o test.4ge test.4gl" test.4ge Program stopped at "test.4gl", line number 7. SQL statement error number -217. Column (nfree) not found in any table in the query (or SLV is undefined). Additional notes - If the syschunks qualifier is omitted from nfree, the error does not occur. If the unload is removed and results are instead directed to a temp table, the error does not occur. My question - is there some known and accepted explanation for this behavior? Or should I be submitting this to IBM as a bug? Thanks, Dave Griffen 4GL - 7.50.FC3 IDS - 11.50.FC5 OS - HP-UX B.11.23 U ia64

I've run into an error 217 [Column (XXXX) not found in any table in the query] while executing an SQL statement within a 4GL. The confusing part is that the column most definitely does exist in the referenced table. Example 4gl - --------------------------------------------------------------------------- database sysmaster main unload to "test.unl" select syschunks.chknum, (syschunks.chksize - syschunks.nfree) col2 from syschunks end main It's the "UNLOAD" clause. Don't think you can do that from inside of 4gl in the form or from a PREPARE. Maybe in the form of a REPORT funciton.

Works fine on my system. I think it's a bug. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DAVE GRIFFEN > Sent: Wednesday, April 07, 2010 10:37 AM > To: ids@iiug.org > Subject: Confusing 4GL Error [19536] > > I've run into an error 217 [Column (XXXX) not found in any table in the > query] > while executing an SQL statement within a 4GL. The confusing part is > that the > column most definitely does exist in the referenced table. > > Example 4gl - > ----------------------------------------------------------------------- > ---- > database sysmaster > main > > unload to "test.unl"> > select syschunks.chknum, (syschunks.chksize - syschunks.nfree) col2> > from syschunks > > end main > ----------------------------------------------------------------------- > ---- > > compiling with "c4gl -o test.4ge test.4gl" > > test.4ge > Program stopped at "test.4gl", line number 7. > SQL statement error number -217. > Column (nfree) not found in any table in the query (or SLV is > undefined). > > Additional notes - If the syschunks qualifier is omitted from nfree, > the error > does not occur. If the unload is removed and results are instead > directed to a > temp table, the error does not occur. > > My question - is there some known and accepted explanation for this > behavior? > Or should I be submitting this to IBM as a bug? > > Thanks, > Dave Griffen > > 4GL - 7.50.FC3 > IDS - 11.50.FC5 > OS - HP-UX B.11.23 U ia64 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

UNLOAD is an SQL Statement just like SELECT is also. Why wouldn't it be able to execute from within 4GL or ACE?

FRANK@ FRANKCOMPUTER.COM wrote: > UNLOAD is an SQL Statement just like SELECT is also. Why wouldn't it be able > to execute from within 4GL or ACE? UNLOAD is not an SQL statement, but is instead provided as part of some client applications (like 4gl or dbaccess or isql) -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

When did UNLOAD become a SQL statement ? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK@ FRANKCOMPUTER.COM Sent: Wednesday, April 07, 2010 11:27 AM To: ids@iiug.org Subject: Re: Confusing 4GL Error [19542] UNLOAD is an SQL Statement just like SELECT is also. Why wouldn't it be able to execute from within 4GL or ACE? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 100407-0, 04/07/2010 Tested on: 4/7/2010 11:38:25 AM avast! - copyright (c) 1988-2010 ALWIL Software.

Paul Watson wrote: > When did UNLOAD become a SQL statement ? When HBASE / Hadoop implemented it. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

DAVE GRIFFEN wrote: > I've run into an error 217 [Column (XXXX) not found in any table in the query] > while executing an SQL statement within a 4GL. The confusing part is that the > column most definitely does exist in the referenced table. > > Example 4gl - > --------------------------------------------------------------------------- > database sysmaster > main > > unload to "test.unl"> > select syschunks.chknum, (syschunks.chksize - syschunks.nfree) col2> > from syschunks > > end main > --------------------------------------------------------------------------- > > compiling with "c4gl -o test.4ge test.4gl" > > test.4ge > Program stopped at "test.4gl", line number 7. > SQL statement error number -217. > Column (nfree) not found in any table in the query (or SLV is undefined). > > Additional notes - If the syschunks qualifier is omitted from nfree, the error > does not occur. If the unload is removed and results are instead directed to a > temp table, the error does not occur. > > My question - is there some known and accepted explanation for this behavior? > Or should I be submitting this to IBM as a bug? > > Thanks, > Dave Griffen > > 4GL - 7.50.FC3 > IDS - 11.50.FC5 > OS - HP-UX B.11.23 U ia64 defect, in my book. out of curiosity, what happens if you store the select in a char variable and pass that to UNLOAD? could we also have a quick look at at the relevant c code generated? -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

GFS (BigTable) also implemented UNLOAD, however its not of course, ANSI.

UNLOAD is NOT an SQL statement in Informix, it is a command verb implemented in dbaccess and ISQL. Now, it happens that 4GL also has an UNLOAD command which works almost identically, but the performance is usually not quite as good. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 7, 2010 at 12:27 PM, FRANK@ FRANKCOMPUTER.COM < frank@frankcomputer.com> wrote: > UNLOAD is an SQL Statement just like SELECT is also. Why wouldn't it be > able > to execute from within 4GL or ACE? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c92edeaacb660483a91d64

>DAVE GRIFFEN wrote: >> I've run into an error 217 [Column (XXXX) not found in any table in the query] >> while executing an SQL statement within a 4GL. The confusing part is that the >> column most definitely does exist in the referenced table. >> >> Example 4gl - >> --------------------------------------------------------------------------- >> database sysmaster >> main >> >> unload to "test.unl">> >> select syschunks.chknum, (syschunks.chksize - syschunks.nfree) col2>> >> from syschunks >> >> end main >> --------------------------------------------------------------------------- >> >> compiling with "c4gl -o test.4ge test.4gl" >> >> test.4ge >> Program stopped at "test.4gl", line number 7. >> SQL statement error number -217. >> Column (nfree) not found in any table in the query (or SLV is undefined). >> >> Additional notes - If the syschunks qualifier is omitted from nfree, the error >> does not occur. If the unload is removed and results are instead directed to a >> temp table, the error does not occur. >> >> My question - is there some known and accepted explanation for this behavior? >> Or should I be submitting this to IBM as a bug? >> >> Thanks, >> Dave Griffen >> >> 4GL - 7.50.FC3 >> IDS - 11.50.FC5 >> OS - HP-UX B.11.23 U ia64 > Marco Greco wrote: >defect, in my book. >out of curiosity, what happens if you store the select in a char variable and >pass that to UNLOAD? >could we also have a quick look at at the relevant c code generated? >-- >Ciao, >Marco >______________________________________________________________________________ >Marco Greco /UK /IBM Standard disclaimers apply! The following edit unloaded results without error... ------------------------------------------------------------------------- database sysmaster main define cvar char(1000) let cvar = 'select syschunks.chknum, ', '(syschunks.chksize - syschunks.nfree) col2 from syschunks' unload to "test.unl" cvar end main ------------------------------------------------------------------------- Hopefully these will answer your c code question... ------------------------------------------------------------------------- test.4ec ------------------------------------------------------------------------- #include <decimal.h> #include <locator.h> #include <datetime.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <unistd.h> #include <fglrep.h> #define I4GL_SQLCA /* I4GLC1 */ #ifdef AIX_53 typedef ifx_loc_t loc_t; #endif /* AIX_53 */ $extern struct { int4 sqlcode; int1 sqlerrm[72]; int1 sqlerrp[8]; int4 sqlerrd[6]; int1 sqlawarn[8]; } sqlca; $extern int4 status; extern mint ibm_efm_statusCode; extern mint ibm_lib4gl_expressionCode; extern int2 ibm_lib4gl_anyError; $extern int4 int_flag; $extern int4 quit_flag; static char fgl_modname[] = "test.4gl"; int main(int fgl_argc, char **fgl_argv) { ibm_lib4gl_fglInitialize(fgl_argc, fgl_argv); ibm_lib4gl_initSignalHandeler(); ibm_lib4gl_recordTssAndHwmState((char *)0); ibm_lib4gl_recordDynArrStack(); ibm_lib4gl_anyError = 0; $ database "sysmaster"; status = sqlca.sqlcode; if (status < 0) { ibm_lib4gl_fglFatalError(fgl_modname, 3, status); } $ unload to "test.unl" select syschunks.chknum , ( syschunks.chksize -syschunks . chksize . nfree ) col2 from syschunks; status = sqlca.sqlcode; if (status < 0) { ibm_lib4gl_fglFatalError(fgl_modname, 7, status); } $ exit form mode; ibm_lib4gl_autoFreeBlobLocators(0); ibm_lib4gl_releaseDynArrStack(); ibm_lib4gl_releaseTssAndHwmStack(); return(0); } ------------------------------------------------------------------------- ------------------------------------------------------------------------- test.ec ------------------------------------------------------------------------- #include <sqlfm.h> #include <decimal.h> #include <locator.h> #include <datetime.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <unistd.h> #include <fglrep.h> #define I4GL_SQLCA /* I4GLC1 */ #ifdef AIX_53 typedef ifx_loc_t loc_t; #endif /* AIX_53 */ $extern struct { int4 sqlcode; char sqlerrm[72]; char sqlerrp[8]; int4 sqlerrd[6]; char sqlawarn[8]; } sqlca; $extern int4 status; extern mint ibm_efm_statusCode; extern mint ibm_lib4gl_expressionCode; extern int2 ibm_lib4gl_anyError; $extern int4 int_flag; $extern int4 quit_flag; static char fgl_modname[] = "test.4gl"; int main(int fgl_argc, char **fgl_argv) { ibm_lib4gl_fglInitialize(fgl_argc, fgl_argv); ibm_lib4gl_initSignalHandeler(); ibm_lib4gl_recordTssAndHwmState((char *)0); ibm_lib4gl_recordDynArrStack(); ibm_lib4gl_anyError = 0; $ database "sysmaster"; status = sqlca.sqlcode; if (status < 0) { ibm_lib4gl_fglFatalError(fgl_modname, 3, status); } /* * $ unload to "test.unl" select syschunks.chknum , ( syschunks.chksize - */ { static int1 *sqlcmdtxt[] = { " select syschunks . chknum , ( syschunks . chksize - syschunks .", " chksize . nfree ) col2 from syschunks", (int1 *) 0 }; fgl_unload("test.unl", (int1 *)0, sqlcmdtxt, 0, (struct sqlvar_struct *)0); } status = sqlca.sqlcode; if (status < 0) { ibm_lib4gl_fglFatalError(fgl_modname, 7, status); } /* * $ exit form mode; */ { ibm_efm_exitScreenMode(); } ibm_lib4gl_autoFreeBlobLocators(0); ibm_lib4gl_releaseDynArrStack(); ibm_lib4gl_releaseTssAndHwmStack(); return(0); } ------------------------------------------------------------------------- ------------------------------------------------------------------------- test.c ------------------------------------------------------------------------- #include <sqlhdr.h> #include <sqliapi.h> #line 1 "test.ec" #include <sqlfm.h> #include <decimal.h> #include <locator.h> #include <datetime.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <unistd.h> #include <fglrep.h> #define I4GL_SQLCA /* I4GLC1 */ #ifdef AIX_53 typedef ifx_loc_t loc_t; #endif /* AIX_53 */ /* * $extern struct * { * int4 sqlcode; * char sqlerrm[72]; * char sqlerrp[8]; * int4 sqlerrd[6]; * char sqlawarn[8]; * } sqlca; */ #line 15 "test.ec" extern struct { int4 sqlcode; char sqlerrm[72]; char sqlerrp[8]; int4 sqlerrd[6]; char sqlawarn[8]; } sqlca; /* * $extern int4 status; */ #line 23 "test.ec" extern int4 status; extern mint ibm_efm_statusCode; extern mint ibm_lib4gl_expressionCode; extern int2 ibm_lib4gl_anyError; /* * $extern int4 int_flag; */ #line 27 "test.ec" extern int4 int_flag; /* * $extern int4 quit_fl

guess we're playing with semantics then, because it is usually referenced as an SQL statement and a KEYWORD in many examples like the following found in: www.barrodale.com "The Impact of DBXten on IBM Informix Performance" (IDS 11.50FC6) Page 11. "Native Informix Queries For the native Informix cases the following SQL was run: UNLOAD TO file SELECT latitude,longitude,timeval,depth,temperature,salinity FROM occam_conventional_hpl_ind_comp_${SIZE} WHERE latitude BETWEEN $MINLAT AND $MAXLAT AND longitude BETWEEN $MINLONG AND $MAXLONG AND timeval BETWEEN $MINDATE AND $MAXDATE AND depth BETWEEN $MINDEPTH AND $MAXDEPTH AND temperature > $MINTEMP AND salinity < $MAXSAL ;

FRANK@ FRANKCOMPUTER.COM wrote: > guess we're playing with semantics then, because it is usually referenced as > an SQL statement and a KEYWORD in many examples like the following found in: Are you a professional dickhead or is the talent natural? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Maybe you are one!.. Why waste time telling me UNLOAD is not an SQL statement and fuzzing over the topic when the original question was why the user was getting a -217 error.

DAVE GRIFFEN wrote: > [snip] > > ------------------------------------------------------------------------- > test.c > ------------------------------------------------------------------------- > #include <sqlhdr.h> > ... > > /* > * $ unload to "test.unl" select syschunks.chknum , ( syschunks.chksize - > */ > { > static int1 *sqlcmdtxt[] = > { > " select syschunks . chknum , ( syschunks . chksize - syschunks .", > > " chksize . nfree ) col2 from syschunks", > (int1 *) 0 > }; > fgl_unload("test.unl", (int1 *)0, sqlcmdtxt, 0, (struct sqlvar_struct *)0); > } > status = sqlca.sqlcode; > ... I reckon fgl_unload() messes up concatenating the two command text lines, possibly eating up the final dot in the first. Try leaving a space between "syschnks." and "nfree", or better still put "(syschunks.chsize - syschunks.nfree) into its own line? Anyway, you have a nice little testcase - open a pmr and have it escalated to the CCT team -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm