Bug #29684 ResultSet is Null while executing stored procedure with one OUT parameter 5.0

• Submitted: 10 Jul 2007 9:27
• Modified: 10 Jul 2007 13:09
• Reporter: sangita sharma
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Windows (ResultSet is null for Stored procedure with OUT parameter)
• CPU Architecture: Any

[10 Jul 2007 9:27] sangita sharma

Description:
Hi,

I am executing one Stored procedure which has one parameter of OUT type. In this I am setting a count from a table. Its executing and giving value from command line, but while executing it from JDBC then ResultSet is null.

I am not able to find the cause that why it's happening.

How to repeat:
This can be repeated for the same kind of senarios.

[10 Jul 2007 10:00] Sveta Smirnova

Thank you for the report.

Please provide code which demonstrates the problem.

[10 Jul 2007 10:03] sangita sharma

code for stored procedure is:

CREATE PROCEDURE stproc(OUT num INT)

BEGIN
SELECT count(*) INTO num FROM a1;

END;

Code for JDBC from which this stored procedure is being called:

import java.util.*;
import java.sql.*;
import java.sql.CallableStatement;
class resulttest 
{
	public static void main(String[] args) 
	{
		CallableStatement cstmt=null;
		
		int total=0;

		try
		{
		
	
		String url="jdbc:mysql://localhost:3306/mytest";
		
		
		//?useUnicode=yes&characterEncoding=UTF-8";
		Class.forName("com.mysql.jdbc.Driver");
				Connection con=DriverManager.getConnection(url,"root","mysql5");

		System.out.println("connected::::");
		
				cstmt = con.prepareCall("{call tt1(?)}");
				
				cstmt.registerOutParameter(1,Types.INTEGER);

				cstmt.execute();
				ResultSet rs = cstmt.getResultSet();
				System.out.println(" result set:"+rs);
				while(rs!=null)
				{
				if(rs.next())
					{
					 total = rs.getInt(1);
					System.out.println("this is:"+ total);

					}
					//rs = cstmt.getResultSet();
					//result = cstmt.getMoreResults();
				}
			
		

		}
		catch(SQLException e)
		{
			System.out.println("Exception::"+e);
			e.printStackTrace();
		}
		catch(Exception e)
		{
			System.out.println("Exception::"+e);
			e.printStackTrace();
		}
		finally{
					try{
							if(cstmt!=null)
							cstmt.close();
							}catch(Exception ex){}
				
				}
		}
	}

[10 Jul 2007 11:58] Tonci Grgin

Sangita, to me it looks like this is a duplicate of Bug#17898

[10 Jul 2007 13:09] Mark Matthews

Your stored procedure doesn't produce a result set (you SELECT into the output parameter), so why do you expect one from JDBC? 

Call .get...() on the CallableStatement itself to get the values in the output parameter.

Is there a particular bit of documentation we produce that led you to believe you get output parameters through result sets, because we need to fix it if there is such a mistake in our documentation.

[15 Aug 2007 20:53] Lou Barota

Would you be so kind as to give an example of a stored procedure that does return a result set?

[15 Aug 2007 22:10] Mark Matthews

CREATE PROCEDURE foo()	
BEGIN
SELECT 1;
END