Bug #13663 Incorrect datatype returned for declared datetime variable in stored procedure

• Submitted: 30 Sep 2005 16:03
• Modified: 1 Oct 2005 1:06
• Reporter: k self
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.14 BK source
• OS: Windows (Windows-XP/Linux)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[30 Sep 2005 16:03] k self

Description:
The incorrect datatype is returned from a stored procedure call when a declared variable of type datetime is returned from the stored procedure in the resultset.

The datatype is correct if the datetime field originates from a table and not a declared variable.

How to repeat:
Create the following sp:

delimiter //
create procedure Test()
begin
  declare a datetime;
  set a = now();
  select a;
end

Using this program:

import java.sql.*;

public class Test 
{
 public static void main(String[] args) throws Exception 
 {
  try
  {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=test&password=test");

            Statement stmt  = conn.createStatement();
            ResultSet lrs = stmt.executeQuery("call Test();");

            ResultSetMetaData lrsMeta = lrs.getMetaData();
            
            if(lrsMeta == null)
            {
            	System.out.println("ERROR - metadata is null");
            }
            else
            {
            	for(int i = 1;i <= lrsMeta.getColumnCount();i++)
            	{
            		System.out.println(lrsMeta.getColumnName(i));
            		System.out.println(lrsMeta.getColumnType(i));
            		System.out.println(lrsMeta.getColumnClassName(i));
            	}
            }
    		lrs.close();
            conn.close();
  }
  catch(Exception e)
  {
   e.printStackTrace();
  }
 }
}

Note the data type returned as -3 (Varbinary).

Suggested fix:
None.

[30 Sep 2005 18:58] k self

This problem also happens when using the boolean datatype. I suspect it may happen on others as well. Note that if you code the now() function in the select line, the correct datatype is returned in the resultset.

[30 Sep 2005 20:52] Mark Matthews

Not a JDBC driver issue, as it's just using whatever datatype the server is reporting to it (so thus category changed to server/stored procedures).

[1 Oct 2005 1:06] MySQL Verification Team

Same as http://bugs.mysql.com/bug.php?id=13675.