Problem trying insert data into a temp table via SP
Poster's stored procedure used SYSTEM to shell out to dbaccess to LOAD a CSV into a table, getting error -668 (system command cannot be executed) with ISAM -255. Replies suggested the SYSTEM call lacks the proper environment (a wrapper script setting the env vars, e.g. DB_LOCALE, and a full path to dbaccess were recommended), and that ISAM -255 may just be dbaccess's non-zero exit status. Jonathan Leffler added that dbaccess runs as a separate session, so BEGIN/COMMIT must be echoed into the SQL file, a true TEMP table isn't visible to it, and fixed /tmp filenames break concurrency; Art Kagel suggested using an external table instead. No confirmation from the poster that it was fixed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello!
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!
Andres.
this is most likely because of
the env vars missing in
> system "dbaccess maindb /tmp/subir_listkraft.sql";
i would create a script with the env vars set and call that.
debug:
> system " ( dbaccess maindb /tmp/subir_listkraft.sql ) >/tmp/tessie 2>&1 ";
where /tmp/tessie does not exist.
check what is in tessie; it'll probably say that the env is not
there.....
Superboer
way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
_oerend_hard.html
On 1 nov, 15:44, an_dario <andro...@gmail.com> wrote:
> Hello!
> 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!
>
> Andres.
On Nov 1, 2:44 pm, an_dario <andro...@gmail.com> wrote:
> Hello!
> 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!
>
> Andres.
dbaccess will have the wrong enviroment when it is run from the stored
procedure
you should write a little script that sets the environment and runs
the dbaccess command
your SYSTEM command should call the script
On 1 nov, 09:55, Superboer <superbo...@t-online.de> wrote:
> this is most likely because of
> the env vars missing in
>
> > system "dbaccess maindb /tmp/subir_listkraft.sql";
>
> i would create a script with the env vars set and call that.
>
> debug:
>
> > system " ( dbaccess maindb /tmp/subir_listkraft.sql ) >/tmp/tessie 2>&1 ";
>
> where /tmp/tessie does not exist.
> check what is in tessie; it'll probably say that the env is not
> there.....
>
> Superboer
>
> way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
> _oerend_hard.html
>
> On 1 nov, 15:44, an_dario <andro...@gmail.com> wrote:
>
> > Hello!
> > 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!
>
> > Andres.
Hello. Thanks for your help. I've realized DB_LOCALE environment is
not set.
Warning: DB_LOCALE environment variable does not match locale of the
database.
But, now I don't know how to fix this inside the SP? Can you help me
with this?
Thanks again!
On Nov 1, 10:44 am, an_dario <andro...@gmail.com> wrote:
> Hello!
> 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!
>
> Andres.
What version of IDS are you using? If you are using 9.xx or later you
can use an external table to access the data in the disk file and
don't have to load it into a temp table at all. Also, any temp table
created by the dbaccess session loading the file will not be
accessible from the session that launched it - unless the table is
actually a permanent table, then you may have concurrency and
interference issues between sessions to deal with.
Art S. Kagel
On Nov 1, 7:44 am, an_dario <andro...@gmail.com> 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=-
On Nov 1, 8:44 am, an_dario <andro...@gmail.com> wrote:
> Hello!
> 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";
>
Regardless of the explanations/solutions you'll get, I have the
impression that you're forcing an SP to do something it shouldn't. I
have recently read about people trying to load files and run programs
from within an SP... ???
I may be wrong (please someone tell me if I am, thanks) or biased*,
but if I have to load files or invoke the operating system to run
programs for me, I should better write a real program (there are
already enough languages to choose from) instead of an SP.
Just an idea.
* Admitely, I prefer scripting languages, avoid stored procedures, and
my working environment is nice with me.
--
Gerardo Santana