When is a new StatementContext pushed?

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

Hi,

I am curious if there is some rule about when does the Derby engine push a
new StatementContext? I have tried couple examples and noticed few cases
where a new StatementContext is pushed and later popped.
1)When a user gives a sql statement and if it is not in the cache, then
during the compilation phase, a StatementContext is pushed and at the end of
compilation, the StatementContext is popped. When the execution phase starts
for the statement, a new StatementContext is pushed and at the end of
execution, the StatementContext is popped.
2)When the user moves in a JDBC ResultSet with next/first/last etc, a
StatementContext is pushed and popped around the ResultSet movement code.
3)If a trigger is defined on a dml statement, trigger execution has its own
StatementContext.

My question is when is there a need to push a new StatementContext rather
than using an existing one?

thanks,
Mamta

Re: When is a new StatementContext pushed?

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

Thank you, Dan. Very helpful information.

On 3/23/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
>
> > Hi,
> >
> > I am curious if there is some rule about when does the Derby engine push
> > a new StatementContext? I have tried couple examples and noticed few
> > cases where a new StatementContext is pushed and later popped.
> > 1)When a user gives a sql statement and if it is not in the cache, then
> > during the compilation phase, a StatementContext is pushed and at the
> > end of compilation, the StatementContext is popped. When the execution
> > phase starts for the statement, a new StatementContext is pushed and at
> > the end of execution, the StatementContext is popped.
> > 2)When the user moves in a JDBC ResultSet with next/first/last etc, a
> > StatementContext is pushed and popped around the ResultSet movement
> code.
> > 3)If a trigger is defined on a dml statement, trigger execution has its
> > own StatementContext.
> >
> > My question is when is there a need to push a new StatementContext
> > rather than using an existing one?
>
> I believe StatementContext's are there to mainly handle statement
> cleanup. Statements in SQL are atomic, either all the statement occurs
> or none of it. Thus an INSERT statement that inserts five rows but fails
> on the third due to a unique constraint violation, needs to rollback and
> cleanup all the effects of that statement.
>
> If a statement calls another statement, e.g. a function call from an
> INSERT statement that performs server side JDBC then a statement level
> exception in the server-side JDBC needs to only rollback the changes and
> cleanup from the effect of the server-side JDBC statement, it must not
> cleanup the outer INSERT statement.
>
> In the Java method for the function, the application code can either
> catch the exception and continue, or allow the exception to be thrown
> into the databsae engine. In the first case the outer INSERT statement
> needs to continue successfully, hence the limiting of the cleanup to the
> server-side statement. In the second case the INSERT must be also rolled
> back due to the exception, but now it's handled as a separate statement
> level cleanup of the INSERT statement.
>
> So I think the current simple model is that each statement pushes a
> StatementContext when it is executing so that it can be rolled back
> individually, even though in some cases (a trigger) the failure of the
> inner statement may also cause the outer statement to be rolled back as
> well.
> Not sure how this actually works for a trigger.
>
> The alternate would be to have nesting counts within a StatementContext,
> which would also require (possibly) StatementContexts to be owned by
> statements. Currently StatementContexts are just temporarily owned by
> the statement while it is executing.
>
> HTH,
> Dan.
>
>

Re: When is a new StatementContext pushed?

[Daniel John Debrunner <dj...@apache.org>]

Mamta Satoor wrote:

> Hi,
>  
> I am curious if there is some rule about when does the Derby engine push
> a new StatementContext? I have tried couple examples and noticed few
> cases where a new StatementContext is pushed and later popped.
> 1)When a user gives a sql statement and if it is not in the cache, then
> during the compilation phase, a StatementContext is pushed and at the
> end of compilation, the StatementContext is popped. When the execution
> phase starts for the statement, a new StatementContext is pushed and at
> the end of execution, the StatementContext is popped.
> 2)When the user moves in a JDBC ResultSet with next/first/last etc, a
> StatementContext is pushed and popped around the ResultSet movement code.
> 3)If a trigger is defined on a dml statement, trigger execution has its
> own StatementContext.
>  
> My question is when is there a need to push a new StatementContext
> rather than using an existing one?

I believe StatementContext's are there to mainly handle statement
cleanup. Statements in SQL are atomic, either all the statement occurs
or none of it. Thus an INSERT statement that inserts five rows but fails
on the third due to a unique constraint violation, needs to rollback and
cleanup all the effects of that statement.

If a statement calls another statement, e.g. a function call from an
INSERT statement that performs server side JDBC then a statement level
exception in the server-side JDBC needs to only rollback the changes and
cleanup from the effect of the server-side JDBC statement, it must not
cleanup the outer INSERT statement.

In the Java method for the function, the application code can either
catch the exception and continue, or allow the exception to be thrown
into the databsae engine. In the first case the outer INSERT statement
needs to continue successfully, hence the limiting of the cleanup to the
server-side statement. In the second case the INSERT must be also rolled
back due to the exception, but now it's handled as a separate statement
level cleanup of the INSERT statement.

So I think the current simple model is that each statement pushes a
StatementContext when it is executing so that it can be rolled back
individually, even though in some cases (a trigger) the failure of the
inner statement may also cause the outer statement to be rolled back as
well.
Not sure how this actually works for a trigger.

The alternate would be to have nesting counts within a StatementContext,
which would also require (possibly) StatementContexts to be owned by
statements. Currently StatementContexts are just temporarily owned by
the statement while it is executing.

HTH,
Dan.