Select Using PreparedStatement returns erroneous data

[Leslie Lowry]

OS - Red Hat Linux 8.0
kernel 2.4.20
Java - 1.4.2
MySQL Connector/J Driver - 3.1.10
MySQL version 4.1.7 standard

Within a java application I am attempting to run a PreparedStatement which selects from a table and I am getting false results.

First I run the simple select from the mysql client in command line, outside of the java application entirely.

SELECT id, last_name FROM person WHERE id = 1035;

And I can see the result returned for the id value is 1035 as it should be.

Then I run a straight select query in the java application and again retrieve the correct result for id, where id = 1035.

But when I attempt to use the same select statement within a prepared statement by setting the id value to 1035 in the where clause using preparedstatement.setInt( 1, 1035), a false result for the id value is returned with the result set.

Below is the file I have used to run my test.

/* 
 * use database test;
 * 
| person | CREATE TABLE `person` (
  `id` smallint(4) unsigned NOT NULL default '0',
  `first_name` char(40) NOT NULL default '',
  `last_name` char(50) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

INSERT INTO person (id, first_name, last_name) VALUES
 (1035, 'scott', 'banks'), (48, 'fred', 'furlough'), (200, 'nancy', 'dreeson');
 *
**/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class StatementTest {

    private static final String STATEMENT 
    	= "SELECT id, last_name FROM person WHERE id = 1035";
    private static final String PREPARED_STATEMENT 
    	= "SELECT id, last_name FROM person WHERE id = ?  ";
    	
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306";
    private static final String DATABASE = "test";
    private static final String USER = "foo";
    private static final String PASSWORD = "bar";
    
    private Connection con = null;
    private Statement stmt = null;
    
    public StatementTest() {
        try {
            runSimpleStatement();
             runPreparedStatement();
             } catch( Exception e ) {
                 e.printStackTrace();
             }
    }
    
    public static void main( String[] args ) {
        new StatementTest();
        
        /* OUTPUT
        Running simple statement
         >> The id value = 1035
         >> banks
        Running prepared statement
         >> The id value = 66571
         >> banks
       */
    }
    
    private void runSimpleStatement() throws Exception {
        System.out.println( "Running simple statement" );
        ResultSet rs = executeQuery( STATEMENT );
        while ( rs.next() ) {
            System.out.println( " >> The id value = " + rs.getInt( "id" ) );
            System.out.println( " >> " + rs.getString( "last_name" ) );
         }
         closeConnection();
    }
    
    private void runPreparedStatement() throws Exception {
        System.out.println( "Running prepared statement" );
        PreparedStatement ps = createPreparedStatement( PREPARED_STATEMENT );
        ps.setInt( 1, 1035 );
        ResultSet rs = executeQuery( ps );
        while ( rs.next() ) {
           System.out.println( " >> The id value = " + rs.getInt( "id" ) );
           System.out.println( " >> " + rs.getString( "last_name" ) );
        }
         closeConnection();
   }
   
   private ResultSet executeQuery( String query ) {
       ResultSet rs = null;
		try {
			if ( openConnection() ) {
				Statement stmt = con.createStatement();
				rs = stmt.executeQuery( query );
			}
		} catch( Exception e ) {
			e.printStackTrace();
		} 
		return rs;
    }
    
    private ResultSet executeQuery( PreparedStatement statement ) {
        ResultSet rs = null;
		try {
			if (openConnection()) {
				rs = statement.executeQuery();
			}
			return rs;
		} catch( Exception sle ) {
			sle.printStackTrace();
		}
		return rs;
	}
	
	PreparedStatement createPreparedStatement(String query) throws Exception {
		PreparedStatement pstmt = null;
		if (openConnection())
			pstmt = con.prepareStatement(query);
		return pstmt;
	}
 
    boolean openConnection() {
	try {
		if ( con == null ) {
			StringBuffer sb = new StringBuffer( URL );
			sb.append("/").append( DATABASE );
			Class.forName(DRIVER).newInstance();
		            con = DriverManager.getConnection( sb.toString(), 
		            									USER, PASSWORD );
		}
		
		if ( con.isClosed() ) {
			StringBuffer sb = new StringBuffer(URL);
			sb.append("/").append( DATABASE );
			con = DriverManager.getConnection( sb.toString(), USER, PASSWORD );
		}
	} catch( Exception e ) {
	    e.printStackTrace();
	}
	
	if (con != null) return true;
	else return false;
    }
    
    private void closeConnection() {
		try {
			if ((con != null) && (!con.isClosed())) {
				con.close();
				con = null;
			}
		} catch( Exception e ) {
              e.printStackTrace();
		}
	}
 }