CODEWITHSUNDEEP
javasql-server-2008jdbc
Mukesh Yadav
Mukesh Yadav

Reputation: 1

sql server 2008 ,PreparedStatement jdbc to fetch multiple queries

gives error : com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

StoredProcedure

CREATE  PROCEDURE getDetails(@n varchar(50) OUT,@c varchar(50) OUT,@i INT OUT)
as
BEGIN 
SELECT @n=product.name,@c=product.code,@i=product.id FROM  product;
END;

java code

 try
    {
            Connection con =LoginConnection.getConnection();
    CallableStatement stmt=con.prepareCall("{call getDetails(?,?,?)}");

    stmt.registerOutParameter(1, Types.VARCHAR);
            stmt.registerOutParameter(2, Types.VARCHAR);
            stmt.registerOutParameter(3, Types.INTEGER);
            ResultSet resultSet = stmt.executeQuery();
            while(resultSet.next())
            {
               System.out.println("value 1:"+resultSet.getString(1));
               System.out.println("value 2:"+resultSet.getString(2));
               System.out.println("Value 3:"+resultSet.getInt(3));
            }

         con.close(); 
    }
    catch(Exception e)
    {
           System.out.println("ex:"+e); 
    }

Upvotes: 0

Views: 130

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

The statement did not return a result set.

That is true. The procedure

CREATE PROCEDURE getDetails
(
    @n varchar(50) OUT,
    @c varchar(50) OUT,
    @i INT OUT
) AS 
BEGIN 
    SELECT @n=product.name,@c=product.code,@i=product.id FROM product; 
END;

will not return a resultset. It will only return the three scalar OUT parameter values from a single row of the table (the last row returned by the SELECT statement). If you want the stored procedure to return a resultset then

ALTER PROCEDURE getDetails
AS 
BEGIN 
    SELECT product.name, product.code, product.id FROM product; 
END;

and use this Java code

CallableStatement stmt = con.prepareCall("{call getDetails}");
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()) {
    System.out.println("ProductName: " + resultSet.getString(1));
    System.out.println("ProductCode: " + resultSet.getString(2));
    System.out.println("  ProductID: " + resultSet.getInt(3));
}

Upvotes: 1

Related Questions