wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL Server 2000 and JDBC,Registering cursor as OUT param Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Server 2000 and JDBC,Registering cursor as OUT param " Watch "SQL Server 2000 and JDBC,Registering cursor as OUT param " New topic
Author

SQL Server 2000 and JDBC,Registering cursor as OUT param

Prashant Jain
Greenhorn

Joined: Aug 19, 2004
Posts: 29
hi

I am using JDK1.4,SQL Server 2000 and Microsoft's JDBC driver.
I am stuck in accessing cursors.

The SP i wrote is

************************************************** ***
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spSelectEmployees' and type = 'P')
DROP PROCEDURE spSelectEmployees
GO
CREATE PROCEDURE spSelectEmployees @emp_cursor CURSOR VARYING OUTPUT
AS
SET @emp_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM employee

OPEN @emp_cursor
GO
************************************************** ******

The problem I face is that when i register the output param in java code,what datatype do I specify..code is written below.

calStmt = conn.prepareCall("{CALL pubs.dbo.spSelectEmployees(?)}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

calStmt.registerOutParameter(1,Types.???);-->What goes here???

java.sql.Types does not give any datatype for cursor or object.Any one knows if there is an extended type given by Microsoft Driver??

thanks in advance.
Prashant
prashantjainn@yahoo.com
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Prashant,

As far as I can see, that wouldn't fit into the JDBC architecture. You'd effectively have a ResultSet that accessed the database (network round-trips and all) every time you called rs.next(). It wouldn't be very efficient even it it were possible.

Rather than trying to use an existing SP that doesn't do what you want it to do, and crowbar it into your Java app, why don't you create a new SP that does. Headache over.

HTH

Jules
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Prashant,

Since I posted this some JDBC 3.0 methods in the API have caught my eye. I still think my suggestion above is the right way to go, but if you're unable to proceed in that direction you could try the following:

Check out the java.sql.Ref class. You could try the types java.sql.Types.REF or java.sql.Types.OTHER for your Ref Cursor parameter (not convinced either will work, however). CallableStatement then has two overloaded getRef() methods.

Note that your JDBC driver will have to support JDBC 3.0 for you to have a prayer of any of the above working.

Jules
Prashant Jain
Greenhorn

Joined: Aug 19, 2004
Posts: 29
Hi Jules!

First of all thanks a lot for the reply,I really appreciate.
The problem out here is that we are dealing with migration of an Oracle DB to SQL Server.We have a code base of almost a thousand java files.I want to retain the same way of writing the procedures because if I change them, you know whats waiting for me Still,if you could suggest me how to modify the procedure, it would be great.

I am using the JDBC driver from Microsoft and I guess that doesnt comply with JDBC3.0 specs. I tried using Types.REF and OTHER as well.
Unsupported exception greets me there

I think we will have to resort to a third party driver.Any comments/suggestions on third party drivers?I was thinking of hitting on dataconnect's driver.

thanks
Prashant
prashantjainn@yahoo.com
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I believe that one of the shortcomings (or at least peculiarities) of Oracle stored procedures (and functions and packages), at least in 8i, is that they can't return a result set. You can only do a SELECT within a cursor and, if required, return a reference to the cursor.

SQL Server does not suffer from that limitation. All you need do is to rewrite the stored procedure as follows (simplifies it a lot):

You would then call it using JDBC and just get the ResultSet from the CS as usual.

I can't really comment on the MS SQL Server JDBC drivers (never used them) but DataConnect might be my choice (for no good reason). Even if that route did work for you I still think it's the wrong choice, given the simplicity of the above code.

Out of interest, how does the existing Oracle JDBC code access the out param containing the ref cursor?

Jules
Prashant Jain
Greenhorn

Joined: Aug 19, 2004
Posts: 29
hey Julian!

I had the approach of using implicit cursors in my mind and tried it before posting the query. Yes its the cleanest way but it results in java code change. The task that I really want to avoid.

Besides,There are few stored procedures in Oracle which are returning multiple cursors.
For this- I have been able to fire mulitiple SQLs in SP in SQL Server 2000 and get resultset using cs.getMoreResults(). However there is a sequential dependency that I have because results returned are in the same order as selects fired in SP..Tight coupling!! Also if I include any DML statement between 2 SQLs in SQL Server 2000, then I can not fetch all cursors.Lets say:-

select * from <sometable1>
update <sometable2>-->(This will not let me fetch the rs from next select statement)

select * from <sometable3>
Any idea how to handle returning multiple cursors in SQL Server 2000 when the cursors arent named?

thanks in advance
Prashant
prashantjainn@yahoo.com
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Read the Javadoc for getUpdateCount() and getMoreResults() for a clear understanding. Here is some code that should do what you want to do:

I believe that in Oracle you can use OracleTypes.CURSOR for the return type. Maybe there's an equivalent in SQL Server. Check the docs.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Server 2000 and JDBC,Registering cursor as OUT param
 
Similar Threads
SQL Server 2005 & Microsoft JDBC driver
Error: SQL type is not supported by this driver
Getting result set using callablestatement??
Closing cursor in a stored procedure when invoked from a callable statement
Stored Procedure problem