Hi Andreas, P1 : error -28910 means, that you tried to leave a db-procedure with an error with error number 0. This is not allowed. With this explanation it should be clear what happens : The lock statement failed with some $RC code. Then, in the catch block you executed a subtrans rollback which succeeded, i.e. which sets $RC to 0. Then you tried to stop with this $RC ( = 0) and got the error. Solution : save $RC into a local variable
IF $RC <> 100 THEN BEGIN rc = $RC; SUBTRANS ROLLBACK; STOP (rc, 'Unerwarteter Fehler'); END; P2 : Unfortunately I could not reproduce P2. Please send me a trace of the corresponding create dbproc statement. Q1 : It should be possible to set the isolation level inside a db-procedure via dynamic sql : VAR setStmt char(30); setStmt = 'SET ISOLATION LEVEL 2'; execute setStmt; Q2 : must be answered by an ODBC guru. Best Regards, Thomas >-----Ursprüngliche Nachricht----- >Von: Andreas Goldstein [mailto:[EMAIL PROTECTED] >Gesendet: Freitag, 30. September 2005 19:48 >An: [email protected] >Betreff: [FSID] DBProcedure & ROW-LOCK > > >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/maxdb?>[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
