Hi Listmembers,
I have some problems with the following DBProcedure:
01 CREATE DBPROC WWIND.DELKUNDE(IN KDNR FIXED(10), OUT ANZBEST INTEGER)
02 AS
03 VAR BESTNR FIXED(10);
04 SET ANZBEST = 0;
05 SUBTRANS BEGIN;
06 TRY
07 /* LOCK (WAIT) ROW WWIND.KUNDEN KEY KUNDENNR = :kdnr IN EXCLUSIVE
MODE; */
08 DECLARE BNRN CURSOR FOR
09 /* SELECT Bestellnr FROM WWIND.BESTELLUNGEN WHERE Kundennr = :kdnr
WITH LOCK EXCLUSIVE;*/
09B SELECT BESTELLNR FROM WWIND.BESTELLUNGEN WHERE KUNDENNR = :kdnr;
10 WHILE $RC = 0 DO BEGIN
11 FETCH NEXT BNRN INTO :bestnr;
12 /* DELETE FROM WWIND.BESTELLDETAILS WHERE BESTELLNR = :bestnr; */
13 /* DELETE FROM WWIND.BESTELLUNGEN WHERE BESTELLNR = :bestnr; */
14 SET ANZBEST = ANZBEST + 1;
15 END;
16 CATCH
17 BEGIN
18 IF $RC <> 100 THEN
19 BEGIN
20 SUBTRANS ROLLBACK;
21 STOP ($RC, 'Unerwarteter Fehler');
22 END;
23 END;
24 /* DELETE FROM WWIND.KUNDEN WHERE KUNDENNR = :kdnr; */
25 CLOSE BNRN;
26 SUBTRANS END;
Problems:
=========
P1) Line 07:
-----------
When uncomment the LOCK-Assignment I can store the DBProc in the DB,
but when I call it by 'CALL WWIND.KUNDE(16, :2)' I get the follwing error:
--> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
--> General error;-28910 STOP(0) not allowed
--> call wwind.delkunde(16, :2)
What is error -28920 (it is not stated in the reference!)?
P2) Line 09 and 09B:
-------------------
Line 09B is only for testing purposes as line 09 will cause the following
error when saving the procedure to the DB:
--> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
--> General error;-4000 POS(425) Unknown result table
--> CREATE DBPROC WWIND.DELKUNDE(...
--> ...
--> FETCH NEXT BNRN INTO :bestnr;
-----
What is wrong here?
Using line 9B instead of 9 the cursor works well, so it seems to have to do
with the lock-option in line 9?
Line 12, 13 and 24 are commented for testing purposes only.
I know, that the delete-operations implemented by the DBProc can be
by using the delete-rules in the referential constraints, but I will
do this by a DBProcedure for other reasons!
Questions:
==========
Q1) Setting the isolation level
-------------------------------
Can I set the isolation level inside a DBProcedure?
Q2) ODBC-Connection-String
--------------------------
Where can I find a complete documentation of MaxDB ODBC-Connection-Strings?
At the moment I use something like this:
'DRIVER={MAXDB};SERVER=127.0.0.1;DATABASE=WWIND;UID=MONA;PWD=RED'
I am sure there are more possible options and parameters.
Thanks for help and comments!
Best Regards
--
GMX DSL = Maximale Leistung zum minimalen Preis!
2000 MB nur 2,99, Flatrate ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
