• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Server 2000 and JDBC,Registering cursor as OUT param

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic