Problem with procedure call style

I try to execute code:
...
CallableStatement prep = conn.prepareCall("{call sp_xml_preparedocument
(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
(?)}");
rem.setInt(1, prep.getInt(1));
rem.executeUpdate();
...
Java prints Exception Could not find prepared statement with handle 1.
Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
How can I fix it?
Thank You!
| From: "Yuri Shustrov" <yuri_shustrov@.mail.ru>
| Subject: Problem with procedure call style
| Date: Fri, 10 Dec 2004 16:03:46 +0300
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OBfhHir3EHA.2404@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: ns.escort-center.ru 212.176.17.195
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP14
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6541
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I try to execute code:
| ...
| CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument
| (?, ?)}");
| prep.registerOutParameter(1, java.sql.Types.INTEGER);
| prep.setString(2, "<root/>");
| prep.executeUpdate();
| System.out.println(prep.getInt(1));
| CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
| (?)}");
| rem.setInt(1, prep.getInt(1));
| rem.executeUpdate();
| ...
| Java prints Exception Could not find prepared statement with handle 1.
| Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
| How can I fix it?
| Thank You!
|
|
|
Hello Yuri,
The SQL Server 2000 Books Online topic "sp_xml_preparedocument" states the
following:
"sp_xml_preparedocument returns a handle that can be used to access the
newly created internal representation of the XML document. This handle is
valid for the duration of the connection to Microsoft SQL Server 2000,
until the connection is reset, or until the handle is invalidated by
executing sp_xml_removedocument."
Your code shows two CallableStatements being prepared and executed on the
same connection variable "conn" without closing the first
CallableStatement. If you are using "SelectMethod=direct", then this will
result in a new cloned connection. Since the handle is only valid on the
first connection, the call to sp_xml_removedocument will fail. You can
resolve this by specifying "SelectMethod=cursor" in your connection string.
Otherwise, you can still use "SelectMethod=direct" and simply modify your
code so that the first CallableStatement is closed before the second
CallableStatement is prepared. This will maintain the same underlying
connection, and so the document handle will still be valid:
CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
int handle = prep.getInt(1);
prep.close();
prep = null;
CallableStatement rem = conn.prepareCall("{call
sp_xml_removedocument(?)}");
rem.setInt(1, handle);
rem.executeUpdate();
rem.close();
rem = null;
You can verify this behavior using SQL Profiler. In the failing scenario,
you will observe two different SPIDs (two different connections).
Hope that helps!
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.