Andreas Goldstein > > Hi Elke, > > it seems that the Mysql-Mailinglist blocks GMX-Adresses for any reason, so > I > can not send my questions to the list. :( > > Maybe you can help me on my question concerning CURRVAL in DB-procedures. > > Thanks a lot!
I return this to the mailing list, as private communication should only be used if data or very special info is sent in support cases. We use a closed mailing list. Therefore you need to register to the list before being able to ask questions and send them to the list. > > > Hi. This is the qmail-send program at mail.gmx.net. > I'm afraid I wasn't able to deliver your message to the following > addresses. > This is a permanent error; I've given up. Sorry it didn't work > out. > > <[email protected]>: > 213.136.52.31_does_not_like_recipient./Remote_host_said:_550_http://dsbl.o > rg/listing?213.165.64.20/Giving_up_on_213.136.52.31./ > > --- Below this line is a copy of the message. > > Return-Path: <[EMAIL PROTECTED]> > Received: (qmail 24434 invoked by uid 0); 8 Oct 2005 11:27:29 -0000 > Received: from 84.178.196.54 by www70.gmx.net with HTTP; > Sat, 8 Oct 2005 13:27:29 +0200 (MEST) > Date: Sat, 8 Oct 2005 13:27:29 +0200 (MEST) > From: "Andreas Goldstein" <[EMAIL PROTECTED]> > To: [email protected] > MIME-Version: 1.0 > Subject: [FSID] How to use CURRVAL inside DB-procedures? > X-Priority: 3 (Normal) > X-Authenticated: #7433130 > Message-ID: <[EMAIL PROTECTED]> > X-Mailer: WWW-Mail 1.6 (Global Message Exchange) > X-Flags: 0001 > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi Listmembers, > > I have a DB-procedure similar to > > CREATE DBPROC WWIND.BESTELLUNGNEU (IN KDNR FIXED(10), OUT BESTNR > FIXED(10)) > AS > LOCK (WAIT) TABLE WWIND.BESTELLUNGEN IN EXCLUSIVE MODE; > INSERT INTO WWIND.BESTELLUNGEN (KDNR, DATUM, ...) VALUES (:KDNR, > CURDATE(), > ...); > SELECT WWIND.BESTELLUNGEN.CURRVAL FROM DUAL; > FETCH INTO :BESTNR; > > > On this I have two questions: > > 1.) Schema of DUAL > Trying to commit that procedure results in an error because the table DUAL > is not referenced with a schema. How can this be done inside a procedure? > I > tried WWIND.DUAL and DOMAIN.DUAL but this results in an 'Unknown Table > DUAL'-Error. > Sysdba.dual will help > 2.) LOCK > Is it necessary to lock the table when using the DB in a multuser > environment? Or will DUAL keep the last given sequence-number for each > DB-session exclusive? > > What I mean is something like this: > > Time | t1 t2 t3 > -------+------------------------------ > User A | INSERT CURRVAL = ? > User B | INSERT CURRVAL = ? > > Lets say the INSERT-Operation of user A causes a sequence-value of 10 and > the operation of user B causes 11. > Without locking the table what values have CURRVAL at t3 for the Users? > As written in http://dev.mysql.com/doc/maxdb/en/1b/2e97e1b42a11d2a97100a0c9449261/frameset.htm CURRVAL is known for the CURRENT session --> no lock needed. But CURRVAL can only be used after having used NEXTVAL for a sequence within the current session. And in the given info I do see neither a serial nor a sequence nor a NEXTVAL used. Maybe the table definition would have told us about serial-usage. Elke SAP Labs Berlin > > Thanks for help & comments > > Best regards > > -- > Highspeed-Freiheit. Bei GMX supergünstig, z.B. GMX DSL_Cityflat, > DSL-Flatrate für nur 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]
