RE: DBINFO('sqlca.sqlerrd1') -- transaction based?

What is the logging status of the database containing 'sequence'? If there is no transaction logging, then BEGIN WORK and COMMIT WORK will fail. -----Original Message----- From: aterrill@inrelation.com [mailto:aterrill@inrelation.com] Sent: Friday, January 12, 2001 5:22 PM To: informix-list@iiug.org Subject: UDR: DBINFO('sqlca.sqlerrd1') -- transaction based? Hello, I am trying to write a UDR that is a sequence number generator: uses a separate table to hold sequence values. Does the DBINFO function in Informix need to be transaction based to be used properly? This seems to make sense to me that you would need to execute this as a transacation to make this work properly. Otherwise you might get an incorrect value back in high load conditions? However, is this true even for UDR functions? Are UDRs inherently transaction based? If I add BEGIN WORK / COMMIT WORK to the following function I get a java.sql.SQLException: Transaction not available error. Any suggestions? Is this a good way to tackle this problem? CREATE FUNCTION serial_udr ( ) RETURNING INT8; DEFINE id INT8; INSERT INTO sequence (timestamp) VALUES (CURRENT); LET id = DBINFO('sqlca.sqlerrd1'); RETURN id; END FUNCTION; Thanks for the help, Alexander Terrill inrelation.com Sent via Deja.com http://www.deja.com/

John Carlson wrote: > > What is the logging status of the database containing 'sequence'? If there > is no transaction logging, then BEGIN WORK and COMMIT WORK will fail. I think the question is more about whether the DBINFO('sqlca.sqlerrd1') information would ever be 'corrupted' by a second user on the system also doing something between the time the INSERT sets the value and the DBINFOR function returns it. If that is what is being asked, then the answer is that DBINFO is session-based; the operations on it are private to a session. This is more or less the same as transaction based, except that the session concept applies in databases without transaction logs, but transactions don't. Inside a database with transactions, the values returned by DBINFO('sqlca.sqlerrd1') are correct for the transaction. > -----Original Message----- > From: aterrill@inrelation.com [mailto:aterrill@inrelation.com] > Sent: Friday, January 12, 2001 5:22 PM > > I am trying to write a UDR that is a sequence number generator: uses a > separate table to hold sequence values. Does the DBINFO function in > Informix need to be transaction based to be used properly? This seems > to make sense to me that you would need to execute this as a > transacation to make this work properly. Otherwise you might get an > incorrect value back in high load conditions? However, is this true > even for UDR functions? Are UDRs inherently transaction based? If I > add BEGIN WORK / COMMIT WORK to the following function I get a > java.sql.SQLException: Transaction not available error. Any > suggestions? Is this a good way to tackle this problem? > > CREATE FUNCTION serial_udr ( ) > RETURNING INT8;> DEFINE id INT8; > > INSERT INTO sequence (timestamp) VALUES (CURRENT);> LET id = DBINFO('sqlca.sqlerrd1'); > RETURN id; > > END FUNCTION; In this sample code, there is no problem. If there was a call to a stored procedure between the INSERT and the DBINFO, then all bets would be off; you'd have to analyse the stored procedure and what it does. But there's no way two different users can interfere with each other. Don't forget to empty the sequence table sometimes. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"