art@nwn.org

I Think your making life quite complicated for yourself. I believe it would work, but you would have locking problems. eg a user gets the last value, goes on lunch, and nobody can use your table until that person is back. You can do the lot in one stored procedure. Here is how I generate ISBNs. TABLOCKX insures there is an exclusif lock on the table until the commit is issued. 'You do not need to change the Isolation level 'You do not need to change the Auto commit option, use default for both. go Create procedure SP_ISBN_NEXT AS Begin transaction --Need some working variables Declare @Current_ISBN char(13), @Next_ISBN char(13), @temp1_Char_1 char(5) ,@temp_int_1 integer, @temp_int_2 integer --Get last ISBN Select @Current_ISBN = ISBN from tblISBN_NEXT TABLOCKX --Bump' it Select @temp1_Char_1 = convert(char(5), convert(int, substring(@Current_ISBN ,7,5)) + 1) select @temp_int_2 = convert(int, substring( @Current_ISBN ,1,1)) * 1 + convert(int, substring( @Current_ISBN ,3,1)) * 2 + convert(int, substring( @Current_ISBN ,4,1)) * 3 + convert(int, substring( @Current_ISBN ,5,1)) * 4 + convert(int, substring( @temp1_Char_1 ,1,1)) * 5 + convert(int, substring( @temp1_Char_1 ,2,1)) * 6 + convert(int, substring( @temp1_Char_1 ,3,1)) * 7 + convert(int, substring( @temp1_Char_1 ,4,1)) * 8 + convert(int, substring( @temp1_Char_1 ,5,1)) * 9 select @temp_int_2 = @temp_int_2 - convert(int,@temp_int_2 / 11) * 11 --Work out check digit select @Next_ISBN = substring(@Current_ISBN,1,6) IF @Temp_int_2 = 10 BEGIN select @Next_ISBN = @Next_ISBN + @temp1_Char_1 + '-' + 'X' END ELSE BEGIN select @Next_ISBN = @Next_ISBN + @temp1_Char_1 + '-' + convert(char(1),@temp_int_2 ) END 'Update table with new value Update tblISBN_NEXT set ISBN = @Next_ISBN Commit Transaction --In my case I need to check that the ISBN has not already been used/generated by some other (Mainframe) system IF (Select Count(*) from tblProduct where ISBN = @Next_ISBN) > 0 BEGIN Declare @Message varchar(255) select @Message = @Next_ISBN + ' is already in use.' RAISERROR(@Message,1,2) WITH SETERROR END ELSE BEGIN --If OK return that value to the Client select @Next_ISBN END go grant execute on SP_ISBN_NEXT to grp_admin -- Fr'd'ric. fredb@macmillan.co.uk Art Zemon <art@nwn.org> wrote in article <3484EEEB.21D59385@nwnREMOVE_ME.org>... > Hello, > > I am fairly new to ODBC a.k.a. Informix-CLI programming and I would like > someone to confirm for me that my assumptions are correct. > > I have a table which contains one record: a serial number. Many > concurrently executing instances of my application need to read the record, > bump the value of the serial number (keep and use the new value), and store > the updated value back into the database... all within the safety of a > transaction. Obviously it is critical that no additional applications even > read the serial number in the middle of the read-update transaction. > > I think that what I need is "pessimistic locking." I.e., I want to place an > exclusive lock on the record before the read and not release it until I > commit or rollback the transaction. If the record is locked then I want to > wait for it to be unlocked before reading and proceeding into the critical > region. > > From my reading of the ODBC docs, I think that what I want to do is > something like this: > > // establish a connection to the database > SQLSetConnectOption(..., SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); > SQLSetConnectOption(..., SQL_TXN_ISOLATION, SQL_TXN_SERIALIZABLE); > > SQLAllocStmt(..., &selectStatement); > SQLAllocStmt(..., &updateStatement); > SQLSetCursorName(selectStatement, "c1", ...); > > SQLExecDirect(selectStatement, > "select value from Serial_Number where sn_key = 1", ...); > SQLBindCol(...); > SQLFetch(...); > > // bump the value of the serial number > > SQLExecDirect(updateStatement, > "update Serial_Number set value=? where current of c1", ...); > > SQLTransact(..., SQL_COMMIT); > > Am I right? If not, what is the correct way to do what I want to do? When > all is said and done, this should run on SQL Server and Informix (and > Oracle). > > Thanks, > -- Art Z. > > -- > ******************************************** > Pigasus Software, Inc. > http://pigasus1.brick.net/ > PGP key at http://pigasus1.brick.net/pgp/art.html >