sql server callable statement result set issue..

[Vijay Vaddem]

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

          CallableStatement cStatement = con.prepareCall("{? = call dt_getxxx(?,?)}");
            cStatement.registerOutParameter(1, Types.VARCHAR);
            cStatement.setString(2, "abc");
            cStatement.setString(3, "def");
            boolean success = cStatement.execute();
            System.out.println("Query Succeeded??:"+success);
            ResultSet rs = (ResultSet) cStatement.executeQuery();
            while(rs.next())
            {
                String values = (String) rs.getString(1);
                System.out.println("Records:"+values);
                
            }

TIA,

Vijay
[ January 12, 2005: Message edited by: Vijay Vaddem ]

[Jeremy Wilson]

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.

[Avi Abrami]

Vijay,
This line of your code:

cStatement.registerOutParameter(1, Types.VARCHAR);

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]

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

             int rsIndex = 0;
            while(true)
            {
                if(! success )
                {
                     int updateCount = cStatement.getUpdateCount();
                     if(updateCount == -1)
                     {
                        System.out.println("\nDone handling all results.");
                        break;
                     }
                     System.out.println("updated :"+updateCount);
                }
                else
                {
                     ResultSet rs2 = cStatement.getResultSet();
                     ResultSetMetaData rsmd = rs2.getMetaData();
 
                     System.out.println("Got ResultSet[" + (++rsIndex) + "]: " 
                                              + rsmd.getColumnCount() + " columns.");
                     System.out.println("Column name :"+rsmd.getColumnName(1));
                     System.out.println("Column label :"+rsmd.getColumnLabel(1));
                     System.out.println("Column type :"+rsmd.getColumnTypeName(1));
                     while(rs2.next())
                     {
                         System.out.println("Record:"+rs.getString(1));
                     }
                     rs.close();
                }
                if(!cStatement.getMoreResults()) 
                    break;
            }
 

[Vijay Vaddem]

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