Re: Problem trying insert data into a temp table via SP
- From: Jonathan Leffler <jonathan.leffler@xxxxxxxxx>
- Date: Fri, 02 Nov 2007 12:37:57 -0000
On Nov 1, 7:44 am, an_dario <andro...@xxxxxxxxx> wrote:
I've got a stored procedure which returns data for a report, but for
generate it, needs to load some data into a temp table. I do this with
this syntax:
--The temporary table is called 'xreg_kraft'
system "echo 'load from /tmp/listkraft_tmp.csv insert into
xreg_kraft;' > /tmp/subir_listkraft.sql";
system "dbaccess maindb /tmp/subir_listkraft.sql";
When the SP tries to run this line, throws an -668 exception (System
command cannot be executed), with -255 ISAM error (Not in
transaction). I tried to fix this error handling the exception like
this:
begin
on exception in (-668)
begin work;
end exception with resume;
system "dbaccess maindb /tmp/subir_listkraft.sql";
end
But throws another exception, -535 (Already in transaction), this
time, with -255 ISAM error (Not in transaction), and makes me confuse,
because I thought opened the transaction, but I can't see where it is.
Can you give me some clues about this? I appreciate your valuable
help!
I think that environment is not the problem, just for once. [Or, it
may currently be the problem but you are going to run into some of the
following problems after you resolve the environment problem.]
This time, the trouble is that DB-Access is a separate process with
its own separate transaction - or lack thereof.
So, you will need to echo a begin and a commit (or begin work and
commit work) into the command file, one either side of the load
statement.
One residual issue is what you mean by 'temporary table'. If that is
a CREATE TEMP TABLE or SELECT ... INTO TEMP type temporary table, the
fact that DB-Access is a separate session will kill you again -
because that DB-Access session cannot access the temp table from your
main session. So, your 'temporary' table must be permanent enough
that separate sessions can use it.
Another residual issue that you'll need to address before you go live
with it is the fact that your current design is inherently single-user
- because you are using a fixed file name in /tmp to store the command
file, and indeed the data file. If separate users run the stored
procedure concurrently, they'll interfere with each other.
There is another issue which can sometimes affect you - namely that if
your DB-Access process exits with a status other than zero, that
status will show up as the ISAM code along with error -668. So, if
your DB-Access was exiting with status -1 (equivalent to 255; the exit
status is an 8-bit value), then you'd see the 'not in transaction'
error. I suspect that this is what misled people into thinking about
environment -- and, indeed, it could still be the real problem, but I
think you may have these other issues to deal with too even if you
deal with environment issues. (Try adding the absolute pathname of
dbaccess to run the program.)
[I don't use DB-Access often enough to remember whether it does
automatic transactions around LOAD statements. I use SQLCMD; it does
not do automatic transactions around LOAD statements, but does if you
use RELOAD instead of LOAD.]
-=JL=-
.
- References:
- Problem trying insert data into a temp table via SP
- From: an_dario
- Problem trying insert data into a temp table via SP
- Prev by Date: Re: Problem with UTF8 and Default Roles
- Next by Date: Silly temp table question
- Previous by thread: Re: Problem trying insert data into a temp table via SP
- Next by thread: Re: Problem trying insert data into a temp table via SP
- Index(es):
Relevant Pages
|
|
