Some information on how StatementContext is used in Derby.

[Mamta Satoor <ms...@gmail.com>]

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)

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