2

I've been trying to create a function to load some files insert them into a temp table based on an existing table, then verifying that there are not duplicated rows on the files loaded and then inserting them into proper tables in the DB, tried using something like this:

let statement = " select * from ", vtable clipped, " where 1=0 into temp t_",vtable clipped
prepare pstatement from  statement 
execute pstatement

to no avail because the temp table seems to be created on a different session than the one im working in.

Any suggestions?

Thank you all beforehand

Arturo Enrique Vittorino Fiocc
  • 21
  • 2

1 Answers1

1

If you prepare and execute a statement as shown, it is created on the connection you're using at the time. If you don't mess with the connections (CONNECT, DISCONNECT, SET CONNECTION), then it should all be clean — if the statement worked at all. Are you checking errors (WHENEVER ERROR STOP, perhaps)? Or have you displayed statement to ensure the SQL is as expected (no untoward chopping of the string, for example — that could account for why the table appears to be missing).

Remember that a temporary table is private to the session. If you run a LOAD statement in the I4GL program, there should be no problem, but you can't use a separate loader program with a temporary table. In terms of the database, it would have to be a 'permanent' or 'regular' table, even if you remove it soon after creating it.

You could also prepare an explicit CREATE TEMP TABLE statement to create a table.

Also consider whether using an external table would help you with the loading. There are also violations tables that could be used to trap problematic rows while loading directly into the main table.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278