How to access a database 'transaction id' from an H2 trigger?

[chenson42]

I am currently implementing an H2 'database dialect' for SymmetricDS
(www.symmetricds.org). I am trying to figure out the best way to
access a 'transaction id' from an H2 trigger. The following is the
Java code I have come up with. Does anybody have suggestions for
improvements (or more likely can they poke holes in my approach)?

protected String getTransactionId(Connection c) {
JdbcConnection con = (JdbcConnection) c;
Session session = (Session) con.getSession();
return StringUtils.leftPad(Integer.toString(session.getId
()), 3, "0") + "-"
+ session.getFirstUncommittedLog() + "-" +
session.getFirstUncommittedPos();
}

[Thomas Mueller]

Hi,

There is currently no good way to do that. How unique does the
transaction id need to be? I mean, is it a problem if the id starts
from 0 when you restart the application? Is there a sort requirements
(larger transaction ids means later)?

Your solution doesn't work for remote connections. I suggest I add a
SQL system function that returns the current transaction id. What is
your preferred function name?

Is a String good enough, or is a byte array better? How fast does it
need to be?

Regards,
Thomas

[chenson42]

A function would be perfect! A String would work for SymmetricDS.
The value needs to be unique across database restarts. The faster the
better. We use it to track changes that need to be 'synchronized'
atomically. I am really looking for the equivalent of
DBMS_TRANSACTION.local_transaction_id(false) on Oracle. I would think
the name could be as simple as TRANSACTION_ID() ??

Thank you.

On Feb 6, 2:10 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Kris]

Hi,

I was wondering what is the possibility of getting the TRANSACTION_ID
() put into H2.
I would like to use SymmetricDS with my H2 database. It would be
appreciated if you
have a opportunity to put this in H2.

Thanks,
Kris

[chenson42]

Do you have a timeline when this feature might be available? Do I
need to make a feature request in the bug tracker on the project
site?

Thanks again, Chris

On Feb 6, 3:04 pm, chenson42 <chenso...@gmail.com> wrote:
> A function would be perfect!  A String would work for SymmetricDS.
> The value needs to be unique across database restarts.  The faster the
> better.  We use it to track changes that need to be 'synchronized'
> atomically.  I am really looking for the equivalent of
> DBMS_TRANSACTION.local_transaction_id(false) on Oracle.  I would think
> the name could be as simple as TRANSACTION_ID() ??
>
> Thank you.
>
> On Feb 6, 2:10 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
> wrote:
>
> > Hi,
>
> > > I am currently implementing an H2 'database dialect' for SymmetricDS
> > > (www.symmetricds.org).    I am trying to figure out the best way to

> > > access a 'transactionid' from an H2 trigger.  The following is the

> > > Java code I have come up with.  Does anybody have suggestions for
> > > improvements (or more likely can they poke holes in my approach)?
>
> > >    protected String getTransactionId(Connection c) {
> > >            JdbcConnection con = (JdbcConnection) c;
> > >            Session session = (Session) con.getSession();
> > >            return StringUtils.leftPad(Integer.toString(session.getId
> > > ()), 3, "0") + "-"
> > >                    + session.getFirstUncommittedLog() + "-" +
> > > session.getFirstUncommittedPos();
> > >    }
>
> > There is currently no good way to do that. How unique does the

> >transactionidneed to be? I mean, is it a problem if theidstarts

> > from 0 when you restart the application? Is there a sort requirements

> > (largertransactionids means later)?

>
> > Your solution doesn't work for remote connections. I suggest I add a

> > SQL system function that returns the currenttransactionid. What is

[Kris]

At this point, I am very flexible. I am taking the next 2 weeks for
learning about SymmetricDS and to interface it into my new project.

[Thomas Mueller]

Hi,

> A function would be perfect!  A String would work for SymmetricDS.

I'm currently implementing such a function. I have a question: if
there is currently no uncommitted change (that is, a transaction is
not started, or one is started, but no changes have been made so far),
is it OK if NULL is returned? My plan is to return:

- NULL if there is no uncommitted change currently
- Otherwise the position of the first uncommitted change in the
transaction log file, and session id. Format:
<logFileId>-<position>-<sessionId>.

TRANSACTION_ID() returns a VARCHAR

Returns the current transaction id for this session. This method returns NULL
if there is no uncommitted change, or if the the database is not persisted.
Otherwise a value of the following form is returned:
logFileId-position-sessionId.
The value is unique across database restarts (values are not re-used).

Example:
CALL TRANSACTION_ID()


Regards,
Thomas

[chenson42]

That sounds perfect.

On Mar 3, 3:42 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>