I think the following will be the best variant.
CREATE DBPROC GIS.notePrintedRes(IN count_list varchar) as
BEGIN
IF false THEN BEGIN
CREATE TABLE TEMP.INTS (
element Integer
);
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 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]
