how to call a stored procedure that returns cursor?

Hello all,
i have a stored procedure that returns a cursor.
i'm currently facing a problem trying to guess the right
syntax for calling such procedure, since SQL Server
syntax says the return variable should be passed as
argument.
it's easy to do so from SQL Query Analyzer, just
declaring a CURSOR variable before the execute. but how
to achieve this through jdbc?
i'll deeply appreciate any help
regards,
Gerardo.
| Content-Class: urn:content-classes:message
| From: "Gerardo" <gerardo@.dangerous-minds.com>
| Sender: "Gerardo" <gerardo@.dangerous-minds.com>
| Subject: how to call a stored procedure that returns cursor?
| Date: Wed, 16 Jun 2004 14:55:31 -0700
| Lines: 16
| Message-ID: <1d6f601c453ec$a4e736a0$a101280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcRT7KTnfRizhvYZRNKIM5cfGF506A==
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6114
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello all,
|
| i have a stored procedure that returns a cursor.
|
| i'm currently facing a problem trying to guess the right
| syntax for calling such procedure, since SQL Server
| syntax says the return variable should be passed as
| argument.
|
| it's easy to do so from SQL Query Analyzer, just
| declaring a CURSOR variable before the execute. but how
| to achieve this through jdbc?
|
| i'll deeply appreciate any help
| regards,
| Gerardo.
|
Hi Gerardo,
As far as I know, your cursor variable is only useful in the context of SQL
Server. I don't think you can return the cursor variable to a datatype in
JDBC directly. What you can do is write some code (a stored procedure, for
example) that extracts the data from the cursor object into a resultset
that can then be accessed by JDBC. Below is a test procedure that returns
a cursor variable, and also a separate stored procedure that extracts data
from the cursor variable:
CREATE PROCEDURE proc_test
@.proc_test_cursor CURSOR VARYING OUTPUT
AS
SET @.proc_test_cursor = CURSOR FOR
SELECT au_lname FROM pubs.dbo.authors
OPEN @.proc_test_cursor
GO
CREATE PROC run_proc_test
AS
BEGIN
DECLARE @.test_cursor_variable CURSOR
DECLARE @.name varchar(40)
EXEC proc_test @.proc_test_cursor = @.test_cursor_variable OUTPUT
DECLARE @.message varchar(4000)
SELECT @.message = ''
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @.test_cursor_variable INTO @.name
IF (@.@.fetch_status <> -2)
BEGIN
SELECT @.message = @.message + 'au_lname: ' + @.name + ', '
END
FETCH NEXT FROM @.test_cursor_variable INTO @.name
END
SELECT @.message
CLOSE @.test_cursor_variable
DEALLOCATE @.test_cursor_variable
END
GO
Once you call run_proc_test from JDBC, you can use getString to read the
data.
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.