How to record SQL calls when using Callable Statements?

How to record SQL calls when using Callable Statements?

Post by cormac.fol » Sat, 03 Feb 2007 21:36:14


Hi,

I'm developing a java application which uses SQL Server as the
database. I use Callable Statements to connect to the database. When
running the application I want to be able to monitor locks/process
info etc in the 'Current Activity' section in the 'Management' section
in SQL Server.

The problem is that at the moment if I look at the 'Last TSQL command
batch' in the process or lock, it does not display the sql for the
current call. It displays what seems to be the precompiled call:
#jdbc#9;1

Anyone know how to get it to display the sql?

Is it a driver issue or is there a SQL Server setting to get it to do
this?

Thanks,
Irishfeller
 
 
 

How to record SQL calls when using Callable Statements?

Post by cormac.fol » Sat, 03 Feb 2007 21:36:20

Hi,

I'm developing a java application which uses SQL Server as the
database. I use Callable Statements to connect to the database. When
running the application I want to be able to monitor locks/process
info etc in the 'Current Activity' section in the 'Management' section
in SQL Server.

The problem is that at the moment if I look at the 'Last TSQL command
batch' in the process or lock, it does not display the sql for the
current call. It displays what seems to be the precompiled call:
#jdbc#9;1

Anyone know how to get it to display the sql?

Is it a driver issue or is there a SQL Server setting to get it to do
this?

Thanks,
Irishfeller

 
 
 

How to record SQL calls when using Callable Statements?

Post by cormac.fol » Sat, 03 Feb 2007 21:57:13

Hi,

I'm developing a java application which uses SQL Server as the
database. I use Callable Statements to connect to the database. When
running the application I want to be able to monitor locks/process
info etc in the 'Current Activity' section in the 'Management' section
in SQL Server.

The problem is that at the moment if I look at the 'Last TSQL command
batch' in the process or lock, it does not display the sql for the
current call. It displays what seems to be the precompiled call:
#jdbc#9;1

Anyone know how to get it to display the sql?

Is it a driver issue or is there a SQL Server setting to get it to do
this?

Thanks,
Irishfeller
 
 
 

How to record SQL calls when using Callable Statements?

Post by Erland Som » Sat, 03 Feb 2007 22:07:37


( XXXX@XXXXX.COM ) writes:

It seems that your JDBC driver creates a temporary stored procedure. I don't
know much about Java and Callable Statements, so I cannot really much more.
Although I can't escape the comment that temporary stored procedures does
not seem a good solution for parameterised commands (which I assume that
this is all about.)

Is your JDBC driver a recent model?

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

How to record SQL calls when using Callable Statements?

Post by cormac.fol » Sat, 03 Feb 2007 22:39:21

Hi,

Thanks for response. We are using the inbuilt datasources in JRun4 to
connect to the database - so the drivers are inbuilt in the app
server. The app server is the latest version so drivers should be up
to date.

I suppose I'm wondering if it is possible to have the name of the
stored procedure I am calling in the 'Last TSQL command
batch' instead of the '#jdbc#9;1'.

Cheers,
Irishfeller
 
 
 

How to record SQL calls when using Callable Statements?

Post by Erland Som » Sun, 04 Feb 2007 08:37:37


( XXXX@XXXXX.COM ) writes:

Maybe. But you would need to talk to people who use JRun4 to find out.

However, if you are actually calling stored procedures, and JRun4
generates a temporary stored procedure, there is something fishy. The
best way to call a stored procedure is through RPC, and most client
API - to not say all - provide a means to do that. I would guess that
if JRun4 generates a temporary stored procedure, that you are in fact
composing EXEC statements. This is less efficient, and depening on how
you get parameter values into the command string, you may also be
open to SQL injection.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/