Bug #2787 rs.getString skips leading zeroes for a zerofill column with PreparedStatement

• Submitted: 14 Feb 2004 1:51
• Modified: 1 Mar 2004 8:46
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 3.1
• OS: Any (Any)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[14 Feb 2004 1:51] [ name withheld ]

Description:
If a zerofill int column has the value of 14 for example,
rs.getString() returns '00000014' when using Statement and
returns '14' when using PreparedStatement.

A fact that might help searching the origin of the bug:
if I specify the query when calling executeQuery method of the PreparedStatement ("rs=pstmt.executeQuery(qry);") it works well.

Environment:
- MySQL server 4.1.2-alpha-nightly-20040211 on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040211-bin.jar (on Win98) 
- JSDK 1.4.2 b-28 (on Win98)
- Apache Tomcat 4.1.29 (on Win98)
    

How to repeat:
The script:
create table test5
(
  id int(8) unsigned zerofill
);
insert into test5 values (14);
commit;

The code:
  String qry="select id from test5";
   
  try
  {
    java.sql.PreparedStatement pstmt=conn.prepareStatement(qry);
    java.sql.Statement stmt=conn.createStatement();

    java.sql.ResultSet rs=stmt.executeQuery(qry);
    rs.next();
    out.println(rs.getString(1));
    rs.close();

    out.println("<br>");

    rs=pstmt.executeQuery();
    rs.next();
    out.println(rs.getString(1));
    rs.close();

    stmt.close();
    pstmt.close();
  }
  catch (java.sql.SQLException sqlEx)
  {
    // handle any errors 
    out.println("Error."); 
    out.println(" SQLException: " + sqlEx.getMessage()); 
    out.println(" SQLState:     " + sqlEx.getSQLState()); 
    out.println(" VendorError:  " + sqlEx.getErrorCode());
  }

[14 Feb 2004 6:53] Mark Matthews

The issue arises from the fact that the server is returning this value as a binary-encoded integer because of the prepared statement, without returning any information about the zerofill attribute of the value, so the driver can not determine this. 

When you use a regular statement (e.g. via executeQuery(sql)), the server returns _all_ values as strings.

[14 Feb 2004 7:55] Mark Matthews

After further examination, I believe I can detect this in the driver. 

This will be fixed for Connector/J 3.1.2.

[1 Mar 2004 0:25] [ name withheld ]

I've checked against mysql-connector-java-3.1-nightly-20040229.
It seems to be corrected.
You can close this bug.