Alexey Gaidukov wrote : > >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] > > Whenever your db-procedure contains a static sql statement (let's say S) referencing a TEMP table, a create statement for that table must be contained in that db-procedure and the create statement must have been evaluated before S is prepared by the compiler. Alternatively you can use dynamic sql statements referencing the TEMP table.
Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
