Re: Nesting Level - What should I expect when executing after prep

WE don't write the JDBC code (some of our clients use it); I just want to
know how to identify procedures that are called by one of the sp_*
procedures, or alternatively, be able to identify connections that use the
sp_* procedures to execute other procedures.

"Joe Weinstein" wrote:



joeNOSPAM@xxxxxxx wrote:

Hi. I am saying you should assume that *any* JDBC code you write
that executes a PreparedStatement (which includes CallableStatements)
will be executed via a stored procedure. If your PreparedStatement
is being asked to execute one of your stored procedures, that stored
procedure will always see the nesting level as > 1.
Your other alternative is to call your procedures with a plain
statement. You would have to send the procedure name and all
it's arguments as a single SQL string.

Joe Weinstein at BEA Systems

eg:

Statement s = c.createStatement();

boolean getResultSetNow = s.execute("EXEC my_procedure " + myFirstArg + ", " + mySecondArg);
int updateCount = -1;

while (true) { // handle all in-line results from any procedure
if (getResultSetNow) {
ResultSet r = s.getResultSet();
while (r.next()) {
// fully process result set before calling getMoreResults() again!
}
r.close();
} else {
updateCount = s.getUpdateCount();
if (updateCount != -1) { // it's a valid update count
if (you want) System.out.println("Processing an update count of " + updateCount);
}
}
if ((!getResultSetNow) && (updateCount == -1)) break; // done with loop
getResultSetNow = s.getMoreResults();
}

If any of your stored procedures return output parameters though,
then you are going to have to use a CallableStatement, and expect
that the procedure you call will find the nesting level to be higher
than if you called if from a command-runner.

Joe Weinstein at BEA Systems


.

Relevant Pages

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > The explanation for this behaviour I have found is that the JDBC ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Records lost in an ADOStoredProc
    ... Use a thread to fire off the stored procedures so that your application ... Let's suppose it updates ... > the CacheSize is set to 1; it is worse when I increase the CacheSize. ... > I don't need to show records, only to execute the store procedure. ...
    (borland.public.delphi.database.ado)
  • Re: call Stored procedure with no params
    ... All stored procedures are exposed as methods of the Connection ... This means you can execute a procedure "Fred" like this: ... so you have parameters (most SPs do). ... Where 5522.22 is a non-string parameter value. ...
    (microsoft.public.data.ado)
Loading