Re: why must I declare the table name?

Thanks Dave. I'm trying to test your theory. Maybe I'm not understanding. If I modify the program like so it still doen't run w/o error: DATABASE jts MAIN database jts call get_adj_rows() END MAIN FUNCTION get_adj_rows() define vmj_sort like month_adj.mj_sort, vmj_amount like month_adj.mj_amount, vmj_comment like month_adj.mj_comment, vmj_end_date like month_adj.mj_end_date, m_string char(100), x char(17) #TEST.DG let x="month_adj.mj_sort" #the value of x is submitted to db? declare mj_curs cursor for select x,month_adj.mj_amount,month_adj.mj_end_date from month_adj foreach mj_curs into vmj_sort,vmj_amount,vmj_end_date display vmj_sort," ",vmj_amount," ",vmj_end_date sleep 5 end foreach END FUNCTION "Dave Griffen" <dgriffen@nospam.finishline.com> wrote in message news:c7u5qm$v57$1@news.onecall.net... > 4gl fills in the value of the program variable instead of the field name > into the declare statement before submitting the query to the database for > execution. > > > "PaulS" <psemenick@computer-systems.com> wrote in message > news:10a54ffrl8eot62@corp.supernews.com... > > Thanks June, > > Yes, it works if i change the variable names. Anyone know why? > > In any case I'll try to always make the variable names a little different. > > Paul > > "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message > > news:2gffthF28og7U1@uni-berlin.de... > > > PaulS wrote: > > > > Sorry, the word document was removed by newsgroup apparently. > > > >here is the program etc: > > > > > > I've trimmed this a bit to save space... > > > > > > > Table: month_adj > > > > Column name Type Nulls > > > > > > > > mj_sort smallint yes > > > > mj_end_date date yes > > > > mj_amount money(16,2) yes > > > > mj_comment char(53) yes > > > > > > > > select * from month_adj> > > > > > > > mj_sort 1 > > > > mj_end_date 04/30/2004 > > > > mj_amount $30.00 > > > > mj_comment COFFEE FUND > > > > > > > > mj_sort 2 > > > > mj_end_date 04/30/2004 > > > > mj_amount $65.00 > > > > mj_comment BIRTHDAY FUND > > > > > > > > > > > > DATABASE jts > > > > > > > > MAIN > > > > database jts > > > > > > > > call get_adj_rows() > > > > > > > > END MAIN > > > > > > > > > > > > FUNCTION get_adj_rows() > > > > > > > > define > > > > mj_sort like month_adj.mj_sort, > > > > mj_amount like month_adj.mj_amount, > > > > mj_comment like month_adj.mj_comment, > > > > mj_end_date like month_adj.mj_end_date, > > > > m_string char(100) > > > > > > > > #TEST1 - FAILS > > > > > > > > declare mj_curs cursor for > > > > select mj_sort,mj_amount,mj_end_date > > > > from month_adj> > > > > > > > [other tests clipped] > > > > > > > > foreach mj_curs into mj_sort,mj_amount,mj_end_date > > > > display mj_sort," ",mj_amount," ",mj_end_date sleep 5 > > > > end foreach > > > > > > > > END FUNCTION > > > > > > > > > > > > #TEST1 ERROR: > > > > > > > > Program stopped at "m_test.4gl", line number 17. > > > #17=declare > > > > mj_curs cursor > > > > > > > > SQL statement error number -201. > > > > > > > > A syntax error has occurred. > > > > > > > > > > I am going from memory here, but I seem to remember occasional problems > > when > > > using a variable that is defined with the same name as a column. Try > > making > > > a minor change to your variable names to see if that doesn't fix the > first > > > test case. > > > > > > > > > > > "PaulS" <psemenick@computer-systems.com> wrote in message > > > > news:10a4v87kn5hmt99@corp.supernews.com... > > > > > (Informix SE 7.23.UC13 and several others) > > > > > I have a cursor which selects fields from a single table. > > > > > Why do I have to specify the table name for the select > > > > > to work? > > > > > TEST1 fails w/o table name > > > > > TEST2-TEST4 work > > > > > > > > > > There are no views or synonyms named month_adj. > > > > > The system tables for this table look ok to me. > > > > > Can anyone explain why the prepare statement works > > > > > with & w/o the table name but the declare statement doesn't. > > > > > Thanks, > > > > > Paul > > > > > > > > > > > -- > > > June Hunt > > > > > > > > > > > >

PaulS wrote: > Thanks Dave. > I'm trying to test your theory. > Maybe I'm not understanding. There have been a couple of slightly unclear attempts to explain what's going on, and they're hitting around the edge of the problem, but not hitting the problem exactly. My turn to muddy the waters :-) <explanation> Roughly speaking, when the I4GL compiler looks at an SQL statement, it tokenizes it to produce either simple names (eg 'x' or 'month_adj') or dot-separated chains (eg month_adj.mj_amount). It then tries to identify whether this is an I4GL variable - and if not, assumes the database will understand it. If there is a variable that matches the name, I4GL works with the program variable. You can suppress this behaviour by prefixing a name with '@' to indicate that this is a database variable -- it is the inverse of the dollar '$' or colon ':' used to indicate a host variable in ESQL/C. </explanation> So, now I need to analyze the various bits of I4GL below and show why each one fails or works - this is going to be interesting. Let's see how long it takes before I have to edit what I've said above... > If I modify the program like so it still doen't run w/o error: > DATABASE jts > MAIN > database jts This database statement is counter-productive; it slows down the process because it connects to the database implicitly because of the non-procedural database statement preceding MAIN, then disconnects and reconnects because of the procedural database statement in the body of the program. When you use a non-procedural database statement in the module that contains MAIN, you automatically get a connection to that database without any further ado. Occasionally, you want to select the database at run time instead of using the name of the database against which you compiled the program. Then you create a simple module with no non-procedural database statement, which suppresses the default connection. You can then connect to the database of choice at run time, and invoke code in other functions in other source files which were compiled against a different database. The main caveat here is that the run time database must have a schema that is sufficiently similar to the compile time - for a moderately complex definition of 'sufficiently similar'. The simple version is 'the tables all have the same names and columns and column types'. More complex versions are possible. > call get_adj_rows() > END MAIN > > FUNCTION get_adj_rows() > define > vmj_sort like month_adj.mj_sort, > vmj_amount like month_adj.mj_amount, > vmj_comment like month_adj.mj_comment, > vmj_end_date like month_adj.mj_end_date, > m_string char(100), > x char(17) > > #TEST.DG > let x="month_adj.mj_sort" #the value of x is submitted to db? > declare mj_curs cursor for > select x,month_adj.mj_amount,month_adj.mj_end_date > from month_adj This one is probably the trickiest. The I4GL compiler generates ESQL/C like this: EXEC SQL DECLARE mj_curs CURSOR FOR SELECT :x, month_adj.mj_amount, month_adj.mj_end_date FROM month_adj; (The code will be in all-lower case with scratty spacing, but functionally equivalent. It might use $ instead of EXEC SQL, too.) And, at run-time, the IDS (or SE) server says "you can't do that", referring to the use of :x in the select-list. The generated C code contains the string "select ?, month_adj.mj_amount, month_adj.mj_end_data FROM month_adj". It is the ? in the select-list that causes the trouble. You can verify this by running the c4gl compiler with the -keep option and look at the xyz.ec and xyz.c files. If you have the p-code compiler, you'd have to use 'strings' or something similar on the .4go or .4gi file to see the SQL statements. [ Note that question marks are permitted in the select-list if they appear as an argument to a function: SELECT NVL(column, ?) FROM ... ] > [...] > "Dave Griffen" <dgriffen@nospam.finishline.com> wrote: >>4gl fills in the value of the program variable instead of the field name >>into the declare statement before submitting the query to the database for >>execution. >> >> >>"PaulS" <psemenick@computer-systems.com> wrote: >>>Thanks June, >>>Yes, it works if i change the variable names. Anyone know why? >>>In any case I'll try to always make the variable names a little >>>different. >>>"June C. Hunt" <june_c_hunt@hotmail.com> wrote: >>>>PaulS wrote: >>>> >>>>>Sorry, the word document was removed by newsgroup apparently. >>>>>here is the program etc: >>>> >>>>I've trimmed this a bit to save space... >>>> >>>>>Table: month_adj >>>>>Column name Type Nulls >>>>> >>>>>mj_sort smallint yes >>>>>mj_end_date date yes >>>>>mj_amount money(16,2) yes >>>>>mj_comment char(53) yes >>>>> >>>>>select * from month_adj>>>>> >>>>>mj_sort 1 >>>>>mj_end_date 04/30/2004 >>>>>mj_amount $30.00 >>>>>mj_comment COFFEE FUND >>>>> >>>>>mj_sort 2 >>>>>mj_end_date 04/30/2004 >>>>>mj_amount $65.00 >>>>>mj_comment BIRTHDAY FUND >>>>> >>>>> >>>>>DATABASE jts >>>>> >>>>>MAIN >>>>> database jts >>>>> >>>>> call get_adj_rows() >>>>> >>>>>END MAIN >>>>> >>>>> >>>>>FUNCTION get_adj_rows() >>>>> >>>>> define >>>>> mj_sort like month_adj.mj_sort, >>>>> mj_amount like month_adj.mj_amount, >>>>> mj_comment like month_adj.mj_comment, >>>>> mj_end_date like month_adj.mj_end_date, >>>>> m_string char(100) >>>>> >>>>> #TEST1 - FAILS >>>>> >>>>> declare mj_curs cursor for >>>>> select mj_sort,mj_amount,mj_end_date >>>>> from month_adj This fails because mj_sort is first and foremost a local variable (and so are both mj_amount and mj_end_date), so that ends up as: select ?,?,? from month_adj; This is not legitimate SQL syntax. >>>>>[other tests clipped - by June] >>>> I am going from memory here, but I seem to remember >>>> occasional problems when using a variable that is defined >>>> with the same name as a column. Try making a minor change to >>>> your variable names to see if that doesn't fix the first test >>>> case. >>>> >>>> >>>>>"PaulS" <psemenick@computer-systems.com> wrote: >>>>>>(Informix SE 7.23.UC13 and several others) >>>>>>I have a cursor which selects fields from a single table. >>>>>>Why do I have to specify the table name for the select >>>>>>to work? >>>>>>TEST1 fails w/o table name >>>>>>TEST2-TEST4 work >>>>>> >>>>>>There are no views or synonyms named month_adj. >>>>>>The system tables for this table look ok to me. >>>>>>Can anyone explain why the prepare statement works >>>>>>with & w/o the table name but th

Thanks Jonathan et al, I took a look at the 4go using strings. Unfortunately it doesn't show all that I would like but when I have some time I'll check the archive. I think I saw something from you about this topic but didn't investigate further. Paul "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:CXCoc.18338$Hs1.7718@newsread2.news.pas.earthlink.net... > PaulS wrote: > > > Thanks Dave. > > I'm trying to test your theory. > > Maybe I'm not understanding. > > There have been a couple of slightly unclear attempts to explain > what's going on, and they're hitting around the edge of the problem, > but not hitting the problem exactly. > > My turn to muddy the waters :-) > > <explanation> > Roughly speaking, when the I4GL compiler looks at an SQL statement, it > tokenizes it to produce either simple names (eg 'x' or 'month_adj') or > dot-separated chains (eg month_adj.mj_amount). It then tries to > identify whether this is an I4GL variable - and if not, assumes the > database will understand it. If there is a variable that matches the > name, I4GL works with the program variable. > > You can suppress this behaviour by prefixing a name with '@' to > indicate that this is a database variable -- it is the inverse of the > dollar '$' or colon ':' used to indicate a host variable in ESQL/C. > </explanation> > > So, now I need to analyze the various bits of I4GL below and show why > each one fails or works - this is going to be interesting. Let's see > how long it takes before I have to edit what I've said above... > > > If I modify the program like so it still doen't run w/o error: > > DATABASE jts > > MAIN > > database jts > > This database statement is counter-productive; it slows down the > process because it connects to the database implicitly because of the > non-procedural database statement preceding MAIN, then disconnects and > reconnects because of the procedural database statement in the body of > the program. When you use a non-procedural database statement in the > module that contains MAIN, you automatically get a connection to that > database without any further ado. > > Occasionally, you want to select the database at run time instead of > using the name of the database against which you compiled the program. > Then you create a simple module with no non-procedural database > statement, which suppresses the default connection. You can then > connect to the database of choice at run time, and invoke code in > other functions in other source files which were compiled against a > different database. The main caveat here is that the run time > database must have a schema that is sufficiently similar to the > compile time - for a moderately complex definition of 'sufficiently > similar'. The simple version is 'the tables all have the same names > and columns and column types'. More complex versions are possible. > > > call get_adj_rows() > > END MAIN > > > > FUNCTION get_adj_rows() > > define > > vmj_sort like month_adj.mj_sort, > > vmj_amount like month_adj.mj_amount, > > vmj_comment like month_adj.mj_comment, > > vmj_end_date like month_adj.mj_end_date, > > m_string char(100), > > x char(17) > > > > #TEST.DG > > let x="month_adj.mj_sort" #the value of x is submitted to db? > > declare mj_curs cursor for > > select x,month_adj.mj_amount,month_adj.mj_end_date > > from month_adj> > This one is probably the trickiest. The I4GL compiler generates > ESQL/C like this: > > EXEC SQL DECLARE mj_curs CURSOR FOR SELECT :x, month_adj.mj_amount, > month_adj.mj_end_date FROM month_adj; > > (The code will be in all-lower case with scratty spacing, but > functionally equivalent. It might use $ instead of EXEC SQL, too.) > > And, at run-time, the IDS (or SE) server says "you can't do that", > referring to the use of :x in the select-list. The generated C code > contains the string "select ?, month_adj.mj_amount, > month_adj.mj_end_data FROM month_adj". It is the ? in the select-list > that causes the trouble. > > You can verify this by running the c4gl compiler with the -keep option > and look at the xyz.ec and xyz.c files. If you have the p-code > compiler, you'd have to use 'strings' or something similar on the .4go > or .4gi file to see the SQL statements. > > [ > Note that question marks are permitted in the select-list if they > appear as an argument to a function: SELECT NVL(column, ?) FROM ... > ] > > > [...] > > > "Dave Griffen" <dgriffen@nospam.finishline.com> wrote: > >>4gl fills in the value of the program variable instead of the field name > >>into the declare statement before submitting the query to the database for > >>execution. > >> > >> > >>"PaulS" <psemenick@computer-systems.com> wrote: > >>>Thanks June, > >>>Yes, it works if i change the variable names. Anyone know why? > >>>In any case I'll try to always make the variable names a little > >>>different. > > >>>"June C. Hunt" <june_c_hunt@hotmail.com> wrote: > >>>>PaulS wrote: > >>>> > >>>>>Sorry, the word document was removed by newsgroup apparently. > >>>>>here is the program etc: > >>>> > >>>>I've trimmed this a bit to save space... > >>>> > >>>>>Table: month_adj > >>>>>Column name Type Nulls > >>>>> > >>>>>mj_sort smallint yes > >>>>>mj_end_date date yes > >>>>>mj_amount money(16,2) yes > >>>>>mj_comment char(53) yes > >>>>> > >>>>>select * from month_adj> >>>>> > >>>>>mj_sort 1 > >>>>>mj_end_date 04/30/2004 > >>>>>mj_amount $30.00 > >>>>>mj_comment COFFEE FUND > >>>>> > >>>>>mj_sort 2 > >>>>>mj_end_date 04/30/2004 > >>>>>mj_amount $65.00 > >>>>>mj_comment BIRTHDAY FUND > >>>>> > >>>>> > >>>>>DATABASE jts > >>>>> > >>>>>MAIN > >>>>> database jts > >>>>> > >>>>> call get_adj_rows() > >>>>> > >>>>>END MAIN > >>>>> > >>>>> > >>>>>FUNCTION get_adj_rows() > >>>>> > >>>>> define > >>>>> mj_sort like month_adj.mj_sort, > >>>>> mj_amount like month_adj.mj_amount, > >>>>> mj_comment like month_adj.mj_comment, > >>>>> mj_end_date like month_adj.mj_end_date, > >>>>> m_string char(100) > >>>>> > >>>>> #TEST1 - FAILS > >>>>> > >>>>> declare mj_curs cursor for > >>>>> select mj_sort,mj_amount,mj_end_date > >>>>> from month_adj> > This fails because mj_sort is first and foremost a local variable (and > so are both mj_amount and mj_end_date), so that ends up as: > > select ?,?,? from month_adj; > > This is not legitimate SQL syntax. > > >>>>>[other tests clipped - by June] > > > >>>> I am going from memory here, but I seem to remember > >>>> occasional problems when using a variable that