Error when executing PREPARE statement

This is a multi-part message in MIME format. ------=_NextPart_000_0154_01BEEB28.CA55C370 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, Consider the following SQL (I already have a temp table created) INSERT INTO t_temp_table (col1,col2, col3,col4, col5) SELECT =table1.col1,'', table2.col3, table1.col4, table1.col5 FROM table1, = table2 WHERE table1.col1 =3D table2.col1 Notice that I want to insert a NULL in col2 of the temp table. I'm able to run this statement in SQL editor, and even through DBACCESS. But when I try to PREPARE it in ESQLC I get the error=20 =20 -410:Prepare statement failed or was not executed. Why am I getting this? Please help. Thanks George georgem@its.soft.net ------=_NextPart_000_0154_01BEEB28.CA55C370 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-1" = http-equiv=3DContent-Type> <META content=3D"MSHTML 5.00.2014.210" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Hi All,</FONT></DIV> <DIV>&nbsp;</DIV> <DIV><FONT face=3DArial size=3D2>Consider the following SQL (I already = have a temp=20 table created)</FONT></DIV> <DIV>&nbsp;</DIV> <DIV><FONT face=3DArial size=3D2>INSERT INTO t_temp_table (col1,col2, = col3,col4,=20 col5) SELECT table1.col1,'', table2.col3, table1.col4, table1.col5 FROM = table1,=20 table2 WHERE table1.col1 =3D table2.col1</FONT></DIV> <DIV>&nbsp;</DIV> <DIV><FONT face=3DArial size=3D2>Notice that I want to insert a NULL in = col2 of the=20 temp table.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>I'm able to run this statement in SQL = editor, and=20 even through DBACCESS.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>But when I try to PREPARE it in ESQLC I = get the=20 error </FONT></DIV> <DIV><FONT face=3DArial=20 size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;=20 </FONT></DIV> <DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20 &nbsp;&nbsp;&nbsp; -410:<FONT face=3D"Times New Roman" size=3D2>Prepare = statement=20 failed or was not executed.</FONT></FONT></DIV> <DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman"=20 size=3D2></FONT></FONT>&nbsp;</DIV> <DIV><FONT size=3D2><FONT size=3D2><FONT face=3DArial>Why am I getting = this? Please=20 help.</FONT></FONT></FONT></DIV> <DIV><FONT size=3D2><FONT size=3D2></FONT></FONT>&nbsp;</DIV> <DIV><FONT size=3D2><FONT size=3D2><FONT=20 face=3DArial>Thanks</FONT></FONT></FONT></DIV> <DIV><FONT size=3D2><FONT size=3D2><FONT face=3DArial>George&nbsp; <A=20 href=3D"mailto:georgem@its.soft.net">georgem@its.soft.net</A></FONT></DIV= ></FONT></FONT></BODY></HTML> ------=_NextPart_000_0154_01BEEB28.CA55C370--

george wrote: > Consider the following SQL (I already have a temp table created) > > INSERT INTO t_temp_table (col1,col2, col3,col4, col5) SELECT > table1.col1,'', table2.col3, table1.col4, table1.col5 FROM table1,> table2 WHERE table1.col1 = table2.col1 > > Notice that I want to insert a NULL in col2 of the temp table. > I'm able to run this statement in SQL editor, and even through > DBACCESS. But when I try to PREPARE it in ESQLC I get the error > > -410:Prepare statement failed or was not executed. > > Why am I getting this? Please help. I'm not sure what the trouble is. Is there any information in the ISAM error (sqlca.sqlerrd[1] unless my memory is failing again).The error message is unusual; it doesn't indicate that there's anything wrong with the statement per se (syntax error or the like). It does suggest that there is some problem with the ESQL/C environment. What version of ESQL/C are you using. What platform? Which database? What is the smallest code you can create which reproduces the problem? It would need a DATABASE, CREATE TEMP TABLE and a PREPARE statement -- shall we say 30 lines of ESQL/C? If you can reproduce the problem with 30 lines of code, then post them. If you can't reproduce the problem with 30 lines of code, then you may have a stray pointer problem, and you're accidentally corrupting something, somewhere. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

The quotes are probably screwing up your prepared string. Try the following instead: INSERT INTO t_temp_table (col1,col2, col3,col4, col5) SELECT table1.col1,NULL, table2.col3, table1.col4, table1.col5 FROM table1,table2 WHERE table1.col1 =3D table2.col1 Hal Maner M Systems george wrote in message <7pjdce$qdj$1@news.xmission.com>... > >This is a multi-part message in MIME format. > >------=_NextPart_000_0154_01BEEB28.CA55C370 >Content-Type: text/plain; > charset="iso-8859-1" >Content-Transfer-Encoding: quoted-printable > >Hi All, > >Consider the following SQL (I already have a temp table created) > >INSERT INTO t_temp_table (col1,col2, col3,col4, col5) SELECT =>table1.col1,'', table2.col3, table1.col4, table1.col5 FROM table1, = >table2 WHERE table1.col1 =3D table2.col1 > >Notice that I want to insert a NULL in col2 of the temp table. >I'm able to run this statement in SQL editor, and even through DBACCESS. >But when I try to PREPARE it in ESQLC I get the error=20 > =20 > -410:Prepare statement failed or was not executed. > >Why am I getting this? Please help. > >Thanks >George georgem@its.soft.net >

Since you do not want col2 populated, just leave it out of both the insert and select statements. Unless you have a default value for col2, it will have a null value. You are trying to select your null value from a table(select NULL from table1/select '' from table1), which I don't think is valid in a select statement. george <georgem@its.soft.net> wrote in article <7pjdce$qdj$1@news.xmission.com>... > > Hi All, > > Consider the following SQL (I already have a temp table created) > > INSERT INTO t_temp_table (col1,col2, col3,col4, col5) SELECT => table1.col1,'', table2.col3, table1.col4, table1.col5 FROM table1, = > table2 WHERE table1.col1 =3D table2.col1 > > Notice that I want to insert a NULL in col2 of the temp table. > I'm able to run this statement in SQL editor, and even through DBACCESS. > But when I try to PREPARE it in ESQLC I get the error=20 > =20 > -410:Prepare statement failed or was not executed. > > Why am I getting this? Please help. > > Thanks > George georgem@its.soft.net