G'day,
Here's a block of code from an R:BASE app
that a WiTango competent person might be
able to refine to suit the purpose...
The next row number for various tables is
stored in the table NextRowNumber, the code
prevents the same number from being used
more than once.
$COMMAND
SetRowID
-- Gets next row number to add row to table and
-- increments the next row number.
-- Called by:
-- inserting rows into tables in NextRowNumber
*( Requires var
vDBTableName
)
-- NOTE This code adapted from e-mail posting by Bill Perry
-- Created by Tom Grimshaw 26-12-1997
-- Modification history
-- 09-06-1998 Altered code to accept passed table name and reflect change
-- of column names
-- 25-08-1998 Removed to GlobBlok.apx file
--
SET ERROR VARIABLE vError
SET VARIABLE vNextID INTEGER = NULL
WHILE #TIME IS NOT NULL THEN
SELECT NextRowNumb INTO +
vNextID IND vi1 +
FROM NextRowNumber +
WHERE DBTableName = .vDBTableName
UPDATE NextRowNumber SET +
NextRowNumb = (NextRowNumb + 1) +
WHERE DBTableName = .vDBTableName +
AND NextRowNumb = .vNextID
IF vError = 0 THEN
BREAK
ENDIF
ENDWHILE
CLEAR VAR vError
*( Alternative code to use if no missing numbers required
WHILE #PI IS NOT NULL THEN
SELECT MIN(ID) INTO vID FROM cachetable WHERE ID > 0
IF SQLCODE <> 0 OR vID = 0 THEN -- NOTHING IN CACHE
SELECT ID INTO vID FROM tablename -- grab the current value
UPDATE tablename SET ID = (ID+1) WHERE ID = .vID -- attempt to update
IF SQLCODE = 0 THEN -- id in table not changed by another user
SET VAR vID = (.vID + 1) -- update variable to match new value
BREAK -- exit inner while with new value
ENDIF
ELSE -- NUMBERS IN CACHE
UPDATE cachetable SET vID = 0 WHERE ID = .vID
IF SQLCODE = 0 THEN -- No one else got number
DEL ROWS FROM cachetable where ID = 0 -- DROP USED NUMBER
BREAK -- exit outer while with value from cache
ENDIF
ENDIF
ENDW
)
RETURN
At 09:52 01/05/02 -0400, you wrote:
>Wrapping an insert and a "select @@identity" into a transaction does not
>guarantee that other threads (or sessions) will not be processed in between.
>It only helps you treat the entire block as an atomic statement that gets
>processed entirely or not at all.
>
>On Wednesday 01 May 2002 09:23, Alexander Zatko wrote:
> > My Tafs are full of insert actions followed by @@IDENTITY gleaning result
> > action, but these are wrapped into transaction actions. Up until reading
> > this thread I thought that this setup is sufficient to get me the correct
> > record identity. Should I change anything in my setup?
> >
> > > -----Original Message-----
> > > From: Scott Cadillac [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, May 01, 2002 12:36 AM
> > > To: Multiple recipients of list witango-talk
> > > Subject: Re: Witango-Talk: Identity after Insert
> > >
> > >
> > > Thank you Klaus, for clearing that up in simple terms.
> > >
> > > Although I don't use @@IDENTITY very much, I find it a very
> > > useful function
> > > but I admit I've wondered about this point. I've read the SQL On-line
> > > documentation, but frankly it didn't clarify things when I
> > > tried to match up
> > > SQL-Server's explanation of a Session and what we understand
> > > is a Witango
> > > Thread.
> > >
> > > Are they the same thing? My guess is that they are not - correct?
> > >
> > > Does a single SQL 'Session' count the same as a single SQL
> > > 'Seat License'?
> > >
> > > Presumably a Session can contain multiple Threads, when hooked up to a
> > > web-application like Witango?
> > >
> > > Although I primarily use SQL-Server for all my work, I freely
> > > admit to being
> > > more of an Application Programmer than a Database Programmer.
> > > Once upon a
> > > time I used to have a DBA on staff - but down-sizing has left
> > > me as the sole
> > > Developer here to cover all the bases.
> > >
> > > I know we've covered this topic in the past, but I could use
> > > a refresher if
> > > anyone has some comments.
> > >
> > > Thank you. Cheers...
> > >
> > >
> > > Scott Cadillac
> > > http://xml-extra.net
> > > [EMAIL PROTECTED]
> > >
> > >
> > > ----- Original Message -----
> > > From: "Klaus Sonnenleiter" <[EMAIL PROTECTED]>
> > > To: "Multiple recipients of list witango-talk"
> > > <[EMAIL PROTECTED]>
> > > Sent: Tuesday, April 30, 2002 6:29 PM
> > > Subject: Re: Witango-Talk: Identity after Insert
> > >
> > > > Brad,
> > > >
> > > > Sounds like you're using either MS-SQL or Sybase. Either
> > >
> > > way you don't
> > > have
> > >
> > > > to run a select after the insert. Both have a built-in variable for
> > > > auto-increment fields (@@identity) and you can either
> > >
> > > return it in your
> > >
> > > > stored procedure or from the next Tango action.
> > > >
> > > > The only problem with doing this in a Tango action is that
> > >
> > > there is no
> > > real
> > >
> > > > guarantee that you're seeing the correct identity field.
> > >
> > > For example, if
> > > you
> > >
> > > > are using two consecutive Tango actions, you could run into
> > >
> > > a situation
> > > where
> > >
> > > > two threads are doing an insert and the first one gets the
> > >
> > > identity field
> > >
> > > > only after the second insert - guess what you're getting
> > >
> > > for @@identity?
> > >
> > > > Yep... ;-) Remember, this has nothing to do with Tango -
> > >
> > > there is only one
> > >
> > > > identity variable and it will always be set ot the last
> > >
> > > identity field
> > >
> > > > inserted.
> > > >
> > > > Klaus
> > > >
> > > > On Tuesday 30 April 2002 08:37, Brad Robertson wrote:
> > > > > I am currently using a Direct DBMS action in wTango that
> > >
> > > calls a stored
> > >
> > > > > procedure to insert a record and then return the new
> > >
> > > identity of that
> > >
> > > > > field, it seems that works better than a insert then
> > >
> > > search action to
> > > get
> > >
> > > > > the identity field. I was wondering if 5.0 or later will have the
> > >
> > > ability
> > >
> > > > > to grab this field on insert, or if anybody has a less
> > >
> > > tedious way of
> > > doing
> > >
> > > > > this...
> > > > >
> > > > > Brad
> > > >
> > > > ----------------------------------------
> > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> > > > Content-Transfer-Encoding: quoted-printable
> > > > Content-Description:
> > > > ----------------------------------------
> > >
> > > ______________________________________________________________
> > > __________
> > >
> > > > TO UNSUBSCRIBE: send a plain text/US ASCII email to
> > >
> > > [EMAIL PROTECTED]
> > >
> > > > with unsubscribe witango-talk in the message body
> > >
> > > ______________________________________________________________
> > > __________
> > > TO UNSUBSCRIBE: send a plain text/US ASCII email to
> > > [EMAIL PROTECTED]
> > > with unsubscribe witango-talk in the message body
> >
> > ________________________________________________________________________
> > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> > with unsubscribe witango-talk in the message body
>________________________________________________________________________
>TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the
intended recipient and may be privileged. If you have received this email
inadvertently or you are not the intended recipient, you may not
disseminate, distribute, copy or in any way rely on it. Further, you should
notify the sender immediately and delete the email from your computer.
Whilst we have taken precautions to alert us to the presence of computer
viruses, we cannot guarantee that this email and any files transmitted with
it are free from such viruses.
________________________________________________________________________
TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
with unsubscribe witango-talk in the message body
