PLEASE help...SQL problem URGENT
Graeme Barnett could not use variables for the file or table in LOAD FROM ... INSERT INTO in Informix 4GL V4. Art S. Kagel explained LOAD is not an SQL command, but the filename and the whole INSERT statement can be string variables. Douglas Wilson gave a ksh dbaccess coprocess loop, and Sergey Komisar a function loading into a temp table first. No confirmation from Graeme.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I'm trying to load many files into a database using the "LOAD FROM filea
INSERT INTO tableb" command. However, Informix V4 4GL doesn't allow you touse a variable in place of either filea or tableb. How can I do this?
I thought about having a long string containing this information and somehow
directly sending it to the SQL engine. Please help...
Thank you,
Graeme
Do you use DOS, Windows or UNIX/AIX ...
On Thu, 21 Oct 1999 21:42:24 +0100, "Graeme Barnett"
<gbarnett@37gateshead.freeserve.co.uk> wrote:
>I'm trying to load many files into a database using the "LOAD FROM filea
>INSERT INTO tableb" command. However, Informix V4 4GL doesn't allow you to>use a variable in place of either filea or tableb. How can I do this?
>I thought about having a long string containing this information and somehow
>directly sending it to the SQL engine. Please help...
One solution (posix or ksh):
dbaccess dbname 2>&1 |&
pid=$!
exec 3>&p
exec 4<&p
for file in file1 file2 file3 ...
do
print "load from $file insert into tableb;" >&3
done
exec 3>&-
cat <&4
exec 4<&-
wait $pid || print Got an error
Hope that helps,
Douglas Wilson
The LOAD verb is NOT an SQL command and the engine does NOT understand it
so you cannot build a string containing the LOAD command and prepare it
and execute it. HOWEVER, the filename and the ENTIRE INSERT statement
which is the target of the LOAD command CAN be string variables! So:
:::::::
DEFINE insert_str CHAR(1024), loadfile CHAR(1024), tablename(18)
LET tablename = arg_val(2)
LET loadfile = arg_val(1)
LET insert_str = "INSERT INTO ", tablename
LOAD FROM loadfile insert_str
::::::
Art S. Kagel
Graeme Barnett wrote:
>
> I'm trying to load many files into a database using the "LOAD FROM filea
> INSERT INTO tableb" command. However, Informix V4 4GL doesn't allow you to> use a variable in place of either filea or tableb. How can I do this?
> I thought about having a long string containing this information and somehow
> directly sending it to the SQL engine. Please help...
>
> Thank you,
>
> Graeme
Graeme Barnett wrote:
>
> I'm trying to load many files into a database using the "LOAD FROM filea
> INSERT INTO tableb" command. However, Informix V4 4GL doesn't allow you to> use a variable in place of either filea or tableb. How can I do this?
> I thought about having a long string containing this information and somehow
> directly sending it to the SQL engine. Please help...
>
> Thank you,
>
> Graeme
You may try something like that
[...]
FUNCTION LoadFileToTable(filename, tablename)
DEFINE
filename char(255),
tablename char(18),
QStr char(120)
let Qstr = "select * from ",tablename," where rowid = -1 into temp
tmp_load_table"
prepare stCreateTable from QStr
execute stCreateTable
{4GL V4 allow you to use a variable in place of filename !!!!}
load from filename insert into tmp_load_tablelet QStr = "insert into ",tablename," select * from tmp_load_table"
prepare stInsertInto
execute stInsertInto
drop table tmp_load_table
END FUNCTION