Loading ...
Sorry, an error occurred while loading the content.

Binary conversion to VARCHAR (is it expected)?

Expand Messages
  • Fabiano
    Hi, all! I´m using jaybird-2.1.6 with Firebird 2.1.4 and facing a problem with a binary column. This column is intended to store binary data, and was created
    Message 1 of 6 , Apr 13, 2011
    • 0 Attachment
      Hi, all!

      I´m using jaybird-2.1.6 with Firebird 2.1.4 and facing a problem with a binary column.

      This column is intended to store binary data, and was created this way:

      create table t (
      column CHAR(16) CHARACTER SET OCTETS
      COLLATE OCTETS);

      Now i´m selecting from this column, and Jaybird is considering it is a VARCHAR.

      The problem is that i use a CachedRowSet implementation to store the ResultSet in memory and close the connection, and the CachedRowSet implementation thinks it is a VARCHAR column when caching it, and it loses information and gives an exception when i call this column from the CachedRowSet.

      Is this expected or can be considered a bug or a candidate for improvement?

      Do someone can imagine a workaround in the SQL instruction to force this column to be treated as binary?

      Regards,

      Fabiano
    • Roman Rokytskyy
      ... Which is correct :) ... How do you try to get the column data (rs.getString() or rs.getBytes()) and what exception do you get? ... At the moment, I d say,
      Message 2 of 6 , Apr 13, 2011
      • 0 Attachment
        > This column is intended to store binary data, and was created this way:
        >
        > create table t (
        > column CHAR(16) CHARACTER SET OCTETS
        > COLLATE OCTETS);
        >
        > Now i´m selecting from this column, and Jaybird is considering it is a VARCHAR.

        Which is correct :)

        > The problem is that i use a CachedRowSet implementation to store the ResultSet in memory and close the connection, and the CachedRowSet implementation thinks it is a VARCHAR column when caching it, and it loses information and gives an exception when i call this column from the CachedRowSet.

        How do you try to get the column data (rs.getString() or rs.getBytes())
        and what exception do you get?

        > Is this expected or can be considered a bug or a candidate for improvement?

        At the moment, I'd say, it is correct behaviour, but please post the
        exception you get.

        Roman
      • Fabiano
        Here is the output of the block of code bellow. TEST 1 u8?? [117, 56, -77, -13, 62, 75, 111, 109, 46, -52, -70, -42, 111, -35, -94, 82] TEST 2 u8?? Exception
        Message 3 of 6 , Apr 14, 2011
        • 0 Attachment
          Here is the output of the block of code bellow.

          TEST 1
          u8??
          [117, 56, -77, -13, 62, 75, 111, 109, 46, -52, -70, -42, 111, -35, -94, 82]

          TEST 2
          u8??
          Exception java.sql.SQLException - Data Type Mismatch

          TEST 3
          [B@601bb1
          [117, 56, -77, -13, 62, 75, 111, 109, 46, -52, -70, -42, 111, -35, -94, 82]

          The exception when access the field from the CachedRowSet using getBytes() is "Data Type Mismatch"

          Here is the code. Hope it keeps the format...

          What i think it is happening is jaybird is treating it as VARCHAR, so it does some charset convertions and trunc it to 4 chars (as expected), but the implementation of CachedRowSet sees it is a VARCHAR and get the value using getString(), so it is losing part of the data. If a cast the field to binary (BLOB SUB_TYPE 0), jaybird treats the field as binary and the cachedrowset imports it right.

          Class.forName("org.firebirdsql.jdbc.FBDriver");

          Properties p = new Properties();
          p.setProperty("charSet", "UTF-8");
          p.setProperty("userName", "SYSDBA");
          p.setProperty("password", "masterkey");

          Connection c;
          PreparedStatement s;
          ResultSet r;
          CachedRowSet crs;

          c = DriverManager
          .getConnection("jdbc:firebirdsql:192.168.0.1:teste", p);

          // DIRECT ACCESS TO JAYBIRD RESULTSET
          s = c
          .prepareStatement("select first 1 a.id from v3$nfes_requisicoes a");
          r = s.executeQuery();
          r.next();
          try {
          System.out.println("\nTEST 1");
          System.out.println(r.getString("id"));
          System.out.println(Arrays.toString(r.getBytes("id")));
          } catch (Exception e) {
          System.out.println("Exception " + e.getMessage());
          }

          // ACCESS THROUGH CACHEDROWSET
          s = c
          .prepareStatement("select first 1 a.id from v3$nfes_requisicoes a");
          r = s.executeQuery();
          crs = new CachedRowSetImpl();
          crs.populate(r);
          crs.next();
          try {
          System.out.println("\nTEST 2");
          System.out.println(crs.getString("id"));
          System.out.println(Arrays.toString(crs.getBytes("id")));
          } catch (Exception e) {
          System.out.println("Exception " + e.getMessage());
          }

          // ACCESS THROUGH CACHEDROWSET FORCING THE FIELD TO BINARY
          s = c
          .prepareStatement("select first 1 cast(a.id as blob sub_type 0) as id from v3$nfes_requisicoes a");
          r = s.executeQuery();
          crs = new CachedRowSetImpl();
          crs.populate(r);
          crs.next();
          try {
          System.out.println("\nTEST 3");
          System.out.println(crs.getString("id"));
          System.out.println(Arrays.toString(crs.getBytes("id")));
          } catch (Exception e) {
          System.out.println("Exception " + e.getMessage());
          }

          > Which is correct :)

          I´m not so sure about this ;-)
          I think OCTETS shoud be treated as binary, as it is intended to stored unencoded bytes, like binary blobs (or am i misundertanding OCTETS purposes?)

          Regards,

          Fabiano

          > > The problem is that i use a CachedRowSet implementation to store the ResultSet in memory and close the connection, and the CachedRowSet implementation thinks it is a VARCHAR column when caching it, and it loses information and gives an exception when i call this column from the CachedRowSet.
          >
          > How do you try to get the column data (rs.getString() or rs.getBytes())
          > and what exception do you get?
          >
          > > Is this expected or can be considered a bug or a candidate for improvement?
          >
          > At the moment, I'd say, it is correct behaviour, but please post the
          > exception you get.
          >
          > Roman
          >
        • Roman Rokytskyy
          ... The main reason for OCTETS to exist is that Firebird does not perform charset translation when returning data to the client. This is not the case with
          Message 4 of 6 , Apr 14, 2011
          • 0 Attachment
            >> Which is correct :)
            >
            > I´m not so sure about this ;-)
            > I think OCTETS shoud be treated as binary, as it is intended to stored unencoded bytes, like binary blobs (or am i misundertanding OCTETS purposes?)

            The main reason for OCTETS to exist is that Firebird does not perform
            charset translation when returning data to the client. This is not the
            case with other charsets. Also, IIRC Firebird uses 0x00 as padding
            character for the CHAR(n) CHARACTER SET OCTETS columns instead 0x20 for
            other charsets.

            Bad is that CachedRowSet returns an error in the second case. Normally I
            would expect that getBytes(...) returns raw data regardless of the
            underlying data type.

            I will try your example, maybe there is some issue in our driver which
            should be fixed.

            Roman
          • Fabiano
            If you don´t have a CachedRowSet implementation, here goes one: http://www.java2s.com/Code/JarDownload/rowset.jar.zip Regards, Fabiano
            Message 5 of 6 , Apr 14, 2011
            • 0 Attachment
              If you don´t have a CachedRowSet implementation, here goes one:

              http://www.java2s.com/Code/JarDownload/rowset.jar.zip

              Regards,

              Fabiano
            • Roman Rokytskyy
              ... I check your example, it is the CachedRowSet implementation that tries to enforce the JDBC data type conversion table. Most likely they have no other
              Message 6 of 6 , Apr 16, 2011
              • 0 Attachment
                > I will try your example, maybe there is some issue in our driver which
                > should be fixed.

                I check your example, it is the CachedRowSet implementation that tries
                to enforce the JDBC data type conversion table. Most likely they have no
                other choice...

                Please create a feature request in tracker, I think I can add a property
                to tell driver to consider CHARACTER SET OCTETS as BINARY, VARBINARY or
                LONGVARBINARY. The workaround that you use with a BLOB is bad - it
                causes additional resource consumption on server and on the network level.

                Roman
              Your message has been successfully submitted and would be delivered to recipients shortly.