Alexey Gaidukov wrote :
>
>I have a table temp.printinfo created before in the same session.
>
>
>When I perfrom
>
>CREATE DBPROC GIS.GetPrintInfo(IN p_counter Integer)
>BEGIN
> TRY
> CREATE TABLE temp.printinfo (
> ID Integer
> );
> CATCH BEGIN
> IF ($rc = -6000) THEN
> DELETE FROM TEMP.printinfo
> ELSE
> STOP($rc, $errmsg);
> END;
> INSERT INTO temp.printinfo
> SELECT min(id) id from gis.did_log;
>END;
>
>
>
>I get
>
>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> Base table not found;-4004 POS(338) Unknown table name:PRINTINFO
>
>
>But the follwing works well
>
>
>CREATE DBPROC GIS.GetPrintInfo(IN p_counter Integer)
>BEGIN
> TRY
> CREATE TABLE temp.printinfo (
> ID Integer
> );
> CATCH BEGIN
> IF ($rc = -6000) THEN
> EXECUTE 'DELETE FROM TEMP.printinfo'
> ELSE
> STOP($rc, $errmsg);
> END;
> INSERT INTO temp.printinfo
> SELECT min(id) id from gis.did_log;
>END;
>
>
>It's very strange. Why I can't delete from temp.printinfo but I can
>insert into temp.printinfo?
>
>
>
>--
When MaxDB compiles a db-procedure the sql statements are normally
prepared in the
order of their occurrence, i.e. from top to bottom of the definition
text.
But there is one exception. In case of a catch statement the catch block
is
compiled before the try block. This means that in you example the table
temp.printinfo
doesn't exist, when the delete statement becomes prepared. This causes
error -4004.
I recommend to do without the try/catch statement :
CREATE DBPROC GIS.GetPrintInfo(IN p_counter Integer)
BEGIN
CREATE TABLE temp.printinfo (ID Integer);
IF $rc <> 0
THEN
IF ($rc = -6000) THEN
DELETE FROM TEMP.printinfo
ELSE
STOP($rc, $errmsg);
INSERT INTO temp.printinfo
SELECT min(id) id from gis.did_log;
END;
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
