Re: AW: Compile DBPROC error message if there is statement 'DELETE FROM TEMP.table'

[Alexey Gaidukov]

I wrote

CREATE TABLE TEMP.INTS (
      element Integer
);


CREATE DBPROC GIS.SPLIT_INT(IN str varchar) as
VAR
i1 smallint;
i2 smallint;
el Integer;
BEGIN
   TRY
       CREATE TABLE TEMP.INTS (
           element Integer
       );
   CATCH BEGIN
       IF ($rc = -6000) THEN
           EXECUTE 'DELETE FROM TEMP.INT'
       ELSE
           STOP($rc, $errmsg);
   END;
   SET i1 = 0;
   SET i2 = 1;
   WHILE i2 > 0 do BEGIN
       SET i2 = index(str,',',i1+1,1);
       if i2 > 0 then BEGIN
           SET el = num(substr(str, i1+1,i2-i1-1));
           insert into TEMP.INTS values (:el);
       END
       else BEGIN
           SET el = num(substr(str, i1+1));
           if length(el)>0 then
               insert into TEMP.INTS values (:el);
       END;
       SET i1 = i2;
   END;
END;


CREATE DBPROC GIS.notePrintedRes(IN count_list varchar) as
BEGIN
   CALL split_ints(:count_list);
   update gis.did_doc set is_print=true where counter in (select 
element from TEMP.INTS) and forma is not null;
END;



Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
Base table not found;-4004 POS(173) Unknown table name:INTS



As I understand I should in each DBPROC where I want to use split_ints 
write something like the following?


CREATE DBPROC GIS.notePrintedRes(IN count_list varchar) as
BEGIN
   TRY
       CREATE TABLE TEMP.INTS (
           element Integer
       );
   CATCH BEGIN
   END;
   CALL split_ints(:count_list);
   update gis.did_doc set is_print=true where counter in (select 
element from TEMP.INTS) and forma is not null;
END;


Anhaus, Thomas пишет:

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]