File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes sql server callable statement result set issue.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql server callable statement result set issue.." Watch "sql server callable statement result set issue.." New topic
Author

sql server callable statement result set issue..

Vijay Vaddem
Ranch Hand

Joined: Feb 13, 2004
Posts: 243
Hi,.

i have the following code which is calling a stored procedure in
sql server database...

this procedure takes 2 parameters and outputs a set of records with one column of type varchar....

i'm unable to get the result ......... its returning 0 records......when
calling from the program, if i run the procedure in sql server, its working
fine...

please let me know where im missing....

i did some amount of search in this forum before posting this question... without any luck...



TIA,

Vijay
[ January 12, 2005: Message edited by: Vijay Vaddem ]
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
Output parameters are only used in the parameter list of the stored procedure. Because of this you only get a single value for each stored procedure call. Sounds like what you are doing is returned as the result set. I do not believe you are using the output parameter correctly. Could you provide an example of the sql server stored procedure you are calling.


Jeremy Wilson
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Vijay,
This line of your code:

indicates that the stored procedure you are calling, returns a single VARCHAR value. However, according to your description, I understand that the stored procedure returns a result set. Therefore, I believe that the above line (of your code) is incorrect, and hence causing your problem.

I don't use SQL Server, so I can't tell you what the correct type should be. But I am almost certain that there must be some sample code or documentation available from the Microsoft Web site that will show you the correct type to use.

Good Luck,
Avi.
Vijay Vaddem
Ranch Hand

Joined: Feb 13, 2004
Posts: 243
i have spent nearly 2 days in searching for this..... didnt help...

im still trying....

when i use ResultSetMetaData to get the meta data information, it is working fine and giving me the name of the column
and its type ( as a varchar).....


here is its code.......

Vijay Vaddem
Ranch Hand

Joined: Feb 13, 2004
Posts: 243
I do not believe you are using the output parameter correctly. Could you provide an example of the sql server stored procedure you are calling.


Yes, here it is.....

create procedure getXXX
@e_id varchar(250),
@name varchar(250)
as

DECLARE @p2_output varchar(250)
declare @initvalue numeric
declare @tab1 table (childlist varchar(250))
set @p2_output = ','

declare getParentsCursor cursor local
for select e_id from tableXXX where e_name = @e_name and name = @name

open getParentsCursor

fetch next from getParentsCursor into @initvalue


while @@fetch_status = 0
begin
EXECUTE getChildren @initvalue, @p2_output OUTPUT
insert into @tab1 values(@p2_output)
fetch next from getParentsCursor into @initvalue
end

close getParentsCursor
deallocate getParentsCursor

SELECT * from @tab1

GO
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql server callable statement result set issue..
 
Similar Threads
Call to Stored Proc Returns 1st Row of RS into callableStmt
What is -Xlint in compiling java program?why this is done?
CallableStatement PostgreSQL Error
Insert into MS Access with PreparedStatement
Not able to read ResultSet from as400 stored procedure