Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-236

Using a parametrized query with a smallint -1 does return the unsigned value

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 1.3.3
    • 1.4.0
    • Other
    • None

    Description

      Consider following testcase

      /*
       
      CREATE DATABASE test;
      CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
      GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test';
      flush PRIVILEGES;
      */
       
      import java.sql.*;
       
      import static org.junit.Assert.assertEquals;
       
       
      public class Test {
       
          @org.junit.Test
          public void test() throws Exception {
              Class.forName("org.mariadb.jdbc.Driver");
       
              String createStmt = "CREATE TABLE table1(" +
                      "`id` int(11) NOT NULL AUTO_INCREMENT, value1 int default NULL, value2 smallint(6) default null, " +
                      "PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=56840 DEFAULT CHARSET=utf8";
       
              try (Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost/test", "test", "test")) {
                  try {
       
                      connection.prepareStatement(createStmt).execute();
       
                      PreparedStatement prepareStatement = connection.prepareStatement("INSERT INTO table1(value1, value2) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
                      prepareStatement.setObject(1, -1, java.sql.Types.INTEGER);
                      prepareStatement.setObject(2, -1, java.sql.Types.INTEGER);
                      prepareStatement.execute();
                      ResultSet rs = prepareStatement.getGeneratedKeys();
      //                    if (rs.next()) {
      //                        System.out.println(rs.getLong(1));
      //                    }
                      
                      try (PreparedStatement statement = connection.prepareStatement("SELECT value1, value2 from table1")) {
                          statement.executeQuery();
                          ResultSet resultSet = statement.getResultSet();
                          while (resultSet.next()) {
                              assertEquals(new Integer(-1), new Integer(resultSet.getInt("value1")));
                              assertEquals(new Integer(-1), new Integer(resultSet.getShort("value2")));
                              assertEquals(new Integer(-1), new Integer(resultSet.getInt("value2")));
                          }
                      }
       
                      try (PreparedStatement statement = connection.prepareStatement("SELECT value1, value2 from table1 where value1 = ?")) {
                          statement.setInt(1, -1);
                          statement.executeQuery();
                          ResultSet resultSet = statement.getResultSet();
                          while (resultSet.next()) {
                              assertEquals(new Integer(-1), new Integer(resultSet.getInt("value1")));
                              assertEquals(new Integer(-1), new Integer(resultSet.getShort("value2")));
                              assertEquals(new Integer(-1), new Integer(resultSet.getInt("value2")));
                          }
                      }
                  } finally {
                      connection.prepareStatement("drop table table1").execute();
                  }
              }
          }
      }

      Executing the select statement with the "?" does return the unsigned value of -1 from value2 (smallint column).
      It has sth to do with the using of MariaDbServerPreparedStatement which is used by the paramterized query and the setting of the binaryFlag which is used in MariaDBValueObject.getInt()
      which is set to false their. So the Value is returned by getSmallInt. In MariaDbClientPreparedStatement the binaryFlag is set to true, so the value is returned via parseIn in MariaDBValueObject and this returns the correct (signed) value.

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            framas Frank S
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.