This week's book giveaway is in the OCMJEA forum. We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line! See this thread for details.
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.
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.
Joined: Aug 02, 2004
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.
Joined: Aug 19, 2004
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.
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?
Joined: Aug 19, 2004
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?