Hi,
While working on authorization part of Grant Revoke, I had to spend some time understanding how StatementContext gets used with triggers. I would like to share with community what my understanding of StatementContext is(this doesn't cover everything about StatementContext but something to start with). Any further discussion on this topic will clarify if I have made incorrect assumptions and in the process, may be beneficial to others on the list.
I am planning on using following acronyms
LCC LanguageConnectionContext
SC StatementContext
The JDBC connection object corresponds to a LCC. The JDBC application may have multiple open JDBC statements on a single JDBC connection. For each of those open statements, there will be a SC. LCC has a getStatementContext method in it. That method will return the currently active top SC, ie, the statement being executed in the engine at the current time. In other words, eventhough the JDBC application has multiple JDBC statements open, only one of them is actively executing in the engine at any time, and the SC related to that execution is returned by
LCC.getStatementContext.
One thing to keep in mind is if a SQL statement being executed by a JDBC statement contains server side logic (tiggers, procedures or functions), then the SC may represent any SQL statement executed by the server logic. In other words,
LCC.getStatementContext might not directly correspond to SC associated with a JDBC application SQL statement. Taking an example of nested triggers
-- create 3 tables and define triggers on 2 of them
create table mamta1t1 (c11 int)
insert into mamta1t1 values(11),(12)
create table mamta1t2 (c21 int)
insert into mamta1t2 values(21),(22)
create table mamta1t3 (c11 int)
insert into mamta1t3 values(31),(32)
create trigger tr1t1 after insert on mamta1t1 for each row mode db2sql insert into mamta1t2 values(99)
create trigger tr1t2 after insert on mamta1t2 for each row mode db2sql insert into mamta1t3 values(99)
insert into mamta1t1 values(11),(12)
create table mamta1t2 (c21 int)
insert into mamta1t2 values(21),(22)
create table mamta1t3 (c11 int)
insert into mamta1t3 values(31),(32)
create trigger tr1t1 after insert on mamta1t1 for each row mode db2sql insert into mamta1t2 values(99)
create trigger tr1t2 after insert on mamta1t2 for each row mode db2sql insert into mamta1t3 values(99)
Now, when a JDBC statement in an application executes "insert into mamta1t1 values(13)", at the beginning of this statement execution, there will be one SC(say SC1) for the sql "insert into mamta1t1 values(13)". During its execution, it is going to fire trigger tr1t1. That trigger tr1t1 is going to push its own SC (say SC2) to execute "insert into mamta1t2 values(99)". The execution of "insert into mamta1t2 values(99)" is going to cause tr1t2 to fire which in turn will push its own SC (say SC3). So, at this point, for one JDBC statement execution, there are 3 SCs pushed in Derby. And during the execution of this JDBC statement,
LCC.getStatementContext can return SC1/SC2/SC3. What exactly gets returned depends on whch one of the 3 insert statements is Derby running internally at that point.
In the end, apparently, SC is not permanently attached to a JDBC statement, They are created dynamically while a JDBC statement is running. I don't completely understand how this works. If someone is familiar with this, it will be great if they can share their knowlege (hopefully with an example JDBC code snippet).
Disclaimer - I have picked up majority of this information from of Dan's reply to a thread "Implementing Statement.cancel() " (The thread starting date in 5/2/05). I found information provided by Dan in that reply very helpful in my understanding of SC so far.
thanks,
Mamta
