Дмитрий Русанов wrote
>
>
>
>
>
> DROP table test_a
> //
> create table test_a
> (
> a int not null,
> b int not null
> )
> //
> insert test_a (a, b) values (1, 1)
> //
> insert test_a (a, b) values (2, 1)
> //
> insert test_a (a, b) values (3, 1)
> //
> drop table test_b
> //
> create table test_b
> (
> a int not null,
> b int not null
> )
> //
> insert test_b (a, b) values (1, 1)
> //
> insert test_b (a, b) values (2, 1)
> //
> insert test_b (a, b) values (3, 1)
> //
>
> DROP FUNCTION DBADMIN.getTest
> //
> CREATE FUNCTION DBADMIN.getTest
> ( a INT
> )
>
> RETURNS INT
>
> AS
>
> VAR id INTEGER;
>
> BEGIN
>
> TRY
>
> SELECT b
> FROM
> dbadmin.test_a
> WHERE
> a = :a;
>
> FETCH INTO :id;
>
> CATCH
> IF $rc <> 100 THEN
> STOP($rc, $errmsg);
>
> RETURN id;
>
> END;
> //
>
> DROP DBPROC dbadmin.GetList
>
> //
>
> CREATE DBPROC dbadmin.GetList
> RETURNS CURSOR AS
> BEGIN
> TRY
> DECLARE :$CURSOR CURSOR FOR
> SELECT a, b FROM dbadmin.test_a where b =
> DBADMIN.getTest(a)
> UNION ALL
> SELECT a, b FROM dbadmin.test_b where a =
> DBADMIN.getTest(a);
> CATCH
> IF $RC <> 100 THEN
> STOP($RC, $ERRMSG);
> END;
> //
>
Hallo,
1. close the resultset in the function.
But pay attention that this will be done in all 3 cases:
a) no error during select and fetch --> CLOSE; behind FETCH
b) error --> jumping into CATCH.
if you write the CLOSE as the first statement in CATCH, then both
errorcases
(= 100 and <> 100) can be handled, but you have to save the $rc and
$ERRMSG of the command
causing the jump into CATCH before CLOSE and use these saved values in
STOP
or
you use CLOSE behind STOP (called with error 100) + does the saving,
CLOSE only in case <> 100
2. name the cursor in the dbproc:
$CURSOR = 'MYTEST_CURSOR'; (or a better name, telling what you select
there)
Elke
SAP Labs Berlin
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
