Re: Syntax error on Informix server 10.00FC1
Hi,
Here's the script :
CREATE PROCEDURE XXXXX ()
DEFINE f_table_name VARCHAR(30);
DEFINE f_column_name VARCHAR(60);
DEFINE sql_stmt CHAR(1000); --utilise pour construire la requete
dynamique
LET f_table_name = "";
LET f_column_name = "";
LET sql_stmt = "";
insert into debug_cobac values ('>>>>>ETAPE BACKUP DEBUT<<<<<','***************',today);
foreach select table_name, column_name into f_table_name, f_column_name
from I2015_list_tab
insert into debug_cobac values ('>>>>>Table traitee<<<<<',trim(f_table_name),today);
LET sql_stmt = 'TRUNCATE TABLE
'||trim(f_table_name||'_BACKUP_I2015')||' ;' ;
execute immediate sql_stmt;
insert into debug_cobac values ('Table BACKUPvider',sql_stmt,today);
LET sql_stmt = 'insert into
'||trim(f_table_name||'_BACKUP_I2015')||' select * from '||
trim(f_table_name) || ' ;' ;
execute immediate sql_stmt;
insert into debug_cobac values ('Insertion dans la tablebackup_I2015 depuis >'||trim(f_table_name),sql_stmt,today);
END FOREACH;
insert into debug_cobac values ('>>>>>ETAPE BACKUP FIN<<<<<','***************',today);
INSERT INTO EVHISTSCRIPT (SCRIPT, DMAJ) VALUES ('XXXXX', today);
end PROCEDURE
On 28/12/2017 19:09, Everett Mills wrote:
> Attachments are stripped out by the system, you will have to include your SQL
> in the body of your email.
>
> --EEM
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Henri
> Moukouri
> Sent: Thursday, December 28, 2017 10:33 AM
> To: ids@iiug.org
> Subject: Syntax error on Informix server 10.00FC1 [40422]
>
> Hi,
> I have attached a script which runs without any problem on Informix server
> version 11.
> On Informix V10.00FC1, running the same script through dbaccess, I have the
> followingerror message "201 : A syntax error has occured" and the cursor is
> pointing on immediate keyword in execute immediate statement.
> I couldn't find the reason of this problem on V10, where I must run that
> script.
> What's wrong with the syntax?Can anyone help me solve this issue ?
>
> Best Regards,Henri MOUKOURI
>
> Sent from Samsung tablet
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
You canât do dynamic SQL in 10.00 SPL. You need 11.70; maybe 11.50.
You will have to think again, maybe using DB-Access to generate the SQL and
then run the generated SQL.
On Thu, Dec 28, 2017 at 10:22 Henri Moukouri <h.moukouri@moukouri.net>
wrote:
> Hi,
>
> Here's the script :
>
> CREATE PROCEDURE XXXXX ()>
> DEFINE f_table_name VARCHAR(30);
>
> DEFINE f_column_name VARCHAR(60);
>
> DEFINE sql_stmt CHAR(1000); --utilise pour construire la requete
> dynamique
>
> LET f_table_name = "";
>
> LET f_column_name = "";
>
> LET sql_stmt = "";
>
> insert into debug_cobac values ('>>>>>ETAPE BACKUP DEBUT<<<<<> ','***************',today);
>
> foreach select table_name, column_name into f_table_name, f_column_name
> from I2015_list_tab
>
> insert into debug_cobac values ('>>>>>Table traitee<<<<<> ',trim(f_table_name),today);
>
> LET sql_stmt = 'TRUNCATE TABLE
> '||trim(f_table_name||'_BACKUP_I2015')||' ;' ;
>
> execute immediate sql_stmt;
>
> insert into debug_cobac values ('Table BACKUP> vider',sql_stmt,today);
>
> LET sql_stmt = 'insert into
> '||trim(f_table_name||'_BACKUP_I2015')||' select * from '||
> trim(f_table_name) || ' ;' ;
>
> execute immediate sql_stmt;
>
> insert into debug_cobac values ('Insertion dans la table> backup_I2015 depuis >'||trim(f_table_name),sql_stmt,today);
> END FOREACH;
>
> insert into debug_cobac values ('>>>>>ETAPE BACKUP FIN<<<<<> ','***************',today);
>
> INSERT INTO EVHISTSCRIPT (SCRIPT, DMAJ) VALUES ('XXXXX', today);>
> end PROCEDURE
>
> On 28/12/2017 19:09, Everett Mills wrote:
> > Attachments are stripped out by the system, you will have to include your
> SQL
> > in the body of your email.
> >
> > --EEM
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Henri
> > Moukouri
> > Sent: Thursday, December 28, 2017 10:33 AM
> > To: ids@iiug.org
> > Subject: Syntax error on Informix server 10.00FC1 [40422]
> >
> > Hi,
> > I have attached a script which runs without any problem on Informix
> server
> > version 11.
> > On Informix V10.00FC1, running the same script through dbaccess, I have
> the
> > followingerror message "201 : A syntax error has occured" and the cursor
> is
> > pointing on immediate keyword in execute immediate statement.
> > I couldn't find the reason of this problem on V10, where I must run that
> > script.
> > What's wrong with the syntax?Can anyone help me solve this issue ?
> >
> > Best Regards,Henri MOUKOURI
> >
> > Sent from Samsung tablet
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> --
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
Is this not what exec_sql_udr on iiug provides for 10?
> On 28 Dec 2017, at 18:46, Jonathan Leffler =
<jonathan.leffler@gmail.com> wrote:
>=20
> You can=E2=80=99t do dynamic SQL in 10.00 SPL. You need 11.70; maybe =
11.50.=20
>=20
> You will have to think again, maybe using DB-Access to generate the =
SQL and=20
> then run the generated SQL.=20
>=20
> On Thu, Dec 28, 2017 at 10:22 Henri Moukouri <h.moukouri@moukouri.net>=20=
> wrote:=20
>=20
>> Hi,=20
>>=20
>> Here's the script :=20
>>=20
>> CREATE PROCEDURE XXXXX ()=20>>=20
>> DEFINE f_table_name VARCHAR(30);=20
>>=20
>> DEFINE f_column_name VARCHAR(60);=20
>>=20
>> DEFINE sql_stmt CHAR(1000); --utilise pour construire la requete=20
>> dynamique=20
>>=20
>> LET f_table_name =3D "";=20
>>=20
>> LET f_column_name =3D "";=20
>>=20
>> LET sql_stmt =3D "";=20
>>=20
>> insert into debug_cobac values ('>>>>>ETAPE BACKUP DEBUT<<<<<=20>> ','***************',today);=20
>>=20
>> foreach select table_name, column_name into f_table_name, =
f_column_name=20
>> from I2015_list_tab=20
>>=20
>> insert into debug_cobac values ('>>>>>Table traitee<<<<<=20>> ',trim(f_table_name),today);=20
>>=20
>> LET sql_stmt =3D 'TRUNCATE TABLE=20
>> '||trim(f_table_name||'_BACKUP_I2015')||' ;' ;=20
>>=20
>> execute immediate sql_stmt;=20
>>=20
>> insert into debug_cobac values ('Table BACKUP=20>> vider',sql_stmt,today);=20
>>=20
>> LET sql_stmt =3D 'insert into=20
>> '||trim(f_table_name||'_BACKUP_I2015')||' select * from '||=20
>> trim(f_table_name) || ' ;' ;=20
>>=20
>> execute immediate sql_stmt;=20
>>=20
>> insert into debug_cobac values ('Insertion dans la table=20>> backup_I2015 depuis >'||trim(f_table_name),sql_stmt,today);=20
>> END FOREACH;=20
>>=20
>> insert into debug_cobac values ('>>>>>ETAPE BACKUP FIN<<<<<=20>> ','***************',today);=20
>>=20
>> INSERT INTO EVHISTSCRIPT (SCRIPT, DMAJ) VALUES ('XXXXX', today);=20>>=20
>> end PROCEDURE=20
>>=20
>> On 28/12/2017 19:09, Everett Mills wrote:=20
>>> Attachments are stripped out by the system, you will have to include =
your=20
>> SQL=20
>>> in the body of your email.=20
>>>=20
>>> --EEM=20
>>>=20
>>> -----Original Message-----=20
>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of=20
>> Henri=20
>>> Moukouri=20
>>> Sent: Thursday, December 28, 2017 10:33 AM=20
>>> To: ids@iiug.org=20
>>> Subject: Syntax error on Informix server 10.00FC1 [40422]=20
>>>=20
>>> Hi,=20
>>> I have attached a script which runs without any problem on Informix=20=
>> server=20
>>> version 11.=20
>>> On Informix V10.00FC1, running the same script through dbaccess, I =
have=20
>> the=20
>>> followingerror message "201 : A syntax error has occured" and the =
cursor=20
>> is=20
>>> pointing on immediate keyword in execute immediate statement.=20
>>> I couldn't find the reason of this problem on V10, where I must run =
that=20
>>> script.=20
>>> What's wrong with the syntax?Can anyone help me solve this issue ?=20=
>>>=20
>>> Best Regards,Henri MOUKOURI=20
>>>=20
>>> Sent from Samsung tablet=20
>>>=20
>>>=20
>>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>>=20
>>>=20
>>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>>=20
>>=20
>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>=20
>> --=20
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>=20=
> Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org=20
> "Blessed are we who can laugh at ourselves, for we shall never cease =
to be=20
> amused."=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20