@Sergio PeresI've run a routine based on your code.
I hope the results below will help.
/work2/INFORMIX/1210FC13/skjeong]cat myproc1.sql
DROP PROCEDURE MYPROC1();
CREATE PROCEDURE MYPROC1()
DEFINE tabela VARCHAR(20);
DEFINE comando VARCHAR(100);
DEFINE string VARCHAR(100);
DEFINE airc_dir VARCHAR(100);
LET string="string_test : ";
LET airc_dir="/tmp/";
SET DEBUG FILE TO airc_dir||"errlogspl";
TRACE ON;
FOREACH SELECT TRIM(tabname)
INTO tabela
FROM systables
WHERE tabid > 99 AND tabtype = 'T'
LET comando = "echo "||string||"load from "||tabela||" insert into "||tabela|| "\; >> "||airc_dir||"load.sql";
SYSTEM comando;
END FOREACH
END PROCEDURE;
/work2/INFORMIX/1210FC13/skjeong]dbaccess stores_demo myproc1.sql
Database selected.
Routine dropped.
Routine created.
Database closed.
/work2/INFORMIX/1210FC13/skjeong]echo "execute procedure myproc1()" | dbaccess stores_demo
Database selected.
Routine executed.
Database closed.
/work2/INFORMIX/1210FC13/skjeong]head -20 /tmp/load.sql
string_test : load from customer insert into customer;
string_test : load from orders insert into orders;
string_test : load from manufact insert into manufact;
string_test : load from stock insert into stock;
string_test : load from items insert into items;
string_test : load from state insert into state;
string_test : load from call_type insert into call_type;
string_test : load from cust_calls insert into cust_calls;
string_test : load from catalog insert into catalog;
string_test : load from sysblderrorlog insert into sysblderrorlog;
string_test : load from sysbldobjects insert into sysbldobjects;
string_test : load from sysbldobjdepends insert into sysbldobjdepends;
string_test : load from sysbldobjkinds insert into sysbldobjkinds;
string_test : load from sysbldregistered insert into sysbldregistered;
string_test : load from sysbldirequired insert into sysbldirequired;
string_test : load from sysbldiprovided insert into sysbldiprovided;
string_test : load from calendarpatterns insert into calendarpatterns;
string_test : load from calendartable insert into calendartable;
string_test : load from tsinstancetable insert into tsinstancetable;
string_test : load from tscontainertable insert into tscontainertable;
/work2/INFORMIX/1210FC13/skjeong]head -20 /tmp/errlogspl
trace on
start select cursor.
select TRIM ( BOTH ' ' FROM tabname)
from systables
where (and (> tabid, 99), (= tabtype, "T"))
select cursor iteration.
select cursor returns customer
expression:(|| (|| (|| (|| (|| (|| (|| (|| "echo ", string), "load from "), tabela), " insert into "), tabela), "\; >> "), airc_dir), "load.sql")
evaluates to echo string_test : load from customer insert into customer\; >> /tmp/load.sql
let comando = echo string_test : load from customer insert into customer\; >> /tmp/load.sql
expression:comando
evaluates to echo string_test : load from customer insert into customer\; >> /tmp/load.sql
system : echo string_test : load from customer insert into customer\; >> /tmp/load.sql
select cursor iteration.
select cursor returns orders
expression:(|| (|| (|| (|| (|| (|| (|| (|| "echo ", string), "load from "), tabela), " insert into "), tabela), "\; >> "), airc_dir), "load.sql")
evaluates to echo string_test : load from orders insert into orders\; >> /tmp/load.sql
let comando = echo string_test : load from orders insert into orders\; >> /tmp/load.sql
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Original Message:
Sent: Thu April 09, 2020 06:24 PM
From: Sergio Peres
Subject: Generate SQL from SPL
Thanks for reply,
Yes I have already seen that, my problem is that I can't find some examples and documentation isn't very enlightening.
Also I have tryed to use debug option but I am getting errors and could't find explanations...
Now my code is this and the result file is always empty:
FOREACH SELECT TRIM(tabname)
INTO tabela
FROM systables
WHERE tabid > 99 AND tabtype = 'T'
LET comando = "echo "||TRIM(string)||" "||tabela||" load from "||
tabela||"; >> "||airc_dir||"/load.sql";
SYSTEM comando;
END FOREACH
and if I add this optin before, I get 1213 error A character to numeric conversion process failed:
SET DEBUG FILE TO "||TRIM(airc_dir)||"/"errlogspl";
TRACE ON;
SP
------------------------------
Sergio Peres
AIRC
Coimbra
Original Message:
Sent: Thu April 09, 2020 06:01 AM
From: Andreas Legner
Subject: Generate SQL from SPL
Just for clarification: apparent SQL syntax elements LOAD, UNLOAD, OUTPUT TO are understood by dbaccess only and aren't really sent to the server or part of the SQL the server implements. That's why you cannot use them within SPL either.
------------------------------
Andreas Legner
Original Message:
Sent: Wed April 08, 2020 09:42 PM
From: SangGyu Jeong
Subject: Generate SQL from SPL
Hi Sergio,
It seems that using UNLOAD or OUTPUT in the routine causes an error. So I searched on Google and used the external table as below.
https://stackoverflow.com/questions/31992388/using-an-unload-statement-in-an-informix-stored-procedure
Perhaps soon someone else will come up with another great solution. 😆
/work2/INFORMIX/1210FC13/skjeong]cat myproc.sqlDROP PROCEDURE MYPROC();CREATE PROCEDURE MYPROC()CREATE EXTERNAL TABLE load_tmp( load_stmt char(1024))USING (DATAFILES ("DISK:/tmp/load.sql"),DELIMITER ";");INSERT INTO load_tmp SELECT 'load from ' || trim(tabname) || ' insert into ' || trim(tabname) FROM systables WHERE tabid > 99 AND tabtype = 'T';DROP TABLE load_tmp;END PROCEDURE;/work2/INFORMIX/1210FC13/skjeong]dbaccess stores_demo myproc.sqlDatabase selected.Routine dropped.Routine created.Database closed./work2/INFORMIX/1210FC13/skjeong]cat /tmp/load.sqlload from customer insert into customer;load from orders insert into orders;load from manufact insert into manufact;load from stock insert into stock;load from items insert into items;load from state insert into state;load from call_type insert into call_type;load from cust_calls insert into cust_calls;load from catalog insert into catalog;load from sysblderrorlog insert into sysblderrorlog;load from sysbldobjects insert into sysbldobjects;load from sysbldobjdepends insert into sysbldobjdepends;load from sysbldobjkinds insert into sysbldobjkinds;load from sysbldregistered insert into sysbldregistered;load from sysbldirequired insert into sysbldirequired;load from sysbldiprovided insert into sysbldiprovided;load from calendarpatterns insert into calendarpatterns;load from calendartable insert into calendartable;load from tsinstancetable insert into tsinstancetable;load from tscontainertable insert into tscontainertable;load from tscontainerusageactivewindowvti insert into tscontainerusageactivewindowvti;load from tscontainerusagedormantwindowvti insert into tscontainerusagedormantwindowvti;load from tscontainerwindowtable insert into tscontainerwindowtable;load from ts_data insert into ts_data;load from customer_ts_data insert into customer_ts_data;load from ts_data_v insert into ts_data_v;load from ts_data_multiplier_v insert into ts_data_multiplier_v;load from spatial_references insert into spatial_references;load from geometry_columns insert into geometry_columns;load from st_units_of_measure insert into st_units_of_measure;load from se_metadatatable insert into se_metadatatable;load from se_views insert into se_views;load from ts_data_location insert into ts_data_location;load from tab insert into tab;load from warehouses insert into warehouses;load from classes insert into classes;load from dbms_alert_events insert into dbms_alert_events;load from dbms_alert_registered insert into dbms_alert_registered;load from dbms_alert_signaled insert into dbms_alert_signaled;load from employee insert into employee;load from employee3 insert into employee3;load from tab0 insert into tab0;load from l_nextval insert into l_nextval;load from stock3 insert into stock3;load from test_bk insert into test_bk;load from test4 insert into test4;load from test1 insert into test1;load from stock2_trans insert into stock2_trans;load from catcopy insert into catcopy;load from test insert into test;load from test2 insert into test2;load from test3 insert into test3;load from stock2 insert into stock2;
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
Original Message:
Sent: Wed April 08, 2020 06:25 PM
From: Sergio Peres
Subject: Generate SQL from SPL
Hi,
I am trying to develop one function to generate one script from database contents, to be more specific I am trying to generate one load to my tables from the following SQL:
output to "load.sql" without headings
select "load from " || tabname || " insert into " || tabname || ";"
from systables
where tabid > 99 and tabtype = "T"
As I don't know too much about this can someone give me some help please? And also tell where I can found some practical documentation about this?
Thanks,
SP
------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------
#Informix