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/
aterrill@inrelation.com wrote:
>
> 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
No! That DBINFO is returning the contents of a session specific
data structure containing the SERIAL value last added by the current
session. It is NOT affected by ANY other session's activities no matter
how busy the engine becomes.
Art S. Kagel
> 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/