RE: how to call a stored procedure that returns cursor??

• Previous message: Carb Simien [MSFT]: "RE: JDBC Driver Error for method ResultSet.previous"
• In reply to: Gerardo: "how to call a stored procedure that returns cursor??"
• Messages sorted by: [ date ] [ thread ]

Date: Mon, 21 Jun 2004 22:09:23 GMT

--------------------
| 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.

• Previous message: Carb Simien [MSFT]: "RE: JDBC Driver Error for method ResultSet.previous"
• In reply to: Gerardo: "how to call a stored procedure that returns cursor??"
• Messages sorted by: [ date ] [ thread ]