• 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 callable statement result set issue..

 
Ranch Hand
Posts: 243
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 243
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 243
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
reply
    Bookmark Topic Watch Topic
  • New Topic