Thursday, May 8, 2008

How to log JDBC Calls to a Stored Procedure

When debugging nasty problems, JDBC developers want to see logs. Of course we can log on the Java code side. A lot of times it is still not good enough. What if the problem is from the JDBC driver? We want to see what is really going on inside a stored procedure and what exactly do the input parameters or the complicated query statement look like.

One simple approach is to insert the statement as a string into a log table. The following is an example in the syntax of SQL Server:
declare @stmt1 nvarchar(2000)
declare @stmt2 nvarchar(2000)

select @stmt1 = '...'
select @stmt2 = '...'

if ( @input is not null ) begin
select @stmt1 = @stmt1 + '...'
select @stmt2 = @stmt2 + '...';

select @stmt1 = @stmt1 + @stmt2
exec sp_executesql @stmt1
insert into LogTable values (@stmt1, CURRENT_TIMESTAMP)

In a conclusion, the trick is to write the statement as a string, insert it into a table before execution.

1 comment: