Hi, Mark!

Thanks for taking a look at this issue.
Just to be sure, in jaybird 2.3 octets will always be treated as bytes, right?

As i had to subclass CachedRowSetImpl to workaround the bug in this class with column names (http://tech.groups.yahoo.com/group/Firebird-Java/message/10715), i just override the getBytes method, and now i have a working CachedRowSet.

@Override
public byte[] getBytes(int columnIndex) throws SQLException {
return (byte[]) getObject(columnIndex);
}

Regards,

Fabiano

--- In Firebird-Java@yahoogroups.com, Mark Rotteveel <mark@...> wrote:
>
> On 19-7-2012 19:07, Mark Rotteveel wrote:
> > On 19-7-2012 18:04, Fabiano wrote:
> >> Hi, Mark!
> >>
> >> No, it doesn't work using octetsAsBytes
> >>
> >> I´m using this URL:
> >> jdbc:firebirdsql://localhost/test?octetsAsBytes
> >>
> >> Last time i looked at the code, this parameter had no effect.
> >> I don´t remember exactly why, but the place it was implemented was never reached when column types was CHAR/VARCHAR.
> >>
> >> I presume this is still the case.
> >>
> >> If i remember correctly, the problem was happening here (FBField.pas, line 545). I´m almost sure field.sqlsubtype is never == 1 for CHAR/VARCHAR fields, what makes isOctetsAsBytes useless in this position. It makes a long time i traced this problem, sorry if i´m mistaken.
> >
> > sqlsubtype should be 1 for OCTETS on (VAR)CHAR fields. I will check the
> > code and report back (probably this weekend).
>
> I checked it, and it does work, but only if you explicitly call
> getObject() on the ResultSet or the CachedRowSet. This also works with
> CachedRowSetImpl as it uses getObject() for all columns.
>
> Below is a small test program I wrote to demonstrates this. The output is:
>
> Test without octetsAsBytes
> octets_char is byte[] : false
> Value: test123
> octets_varchar is byte[] : false
> Value: test123
> utf8_char is byte[] : false
> Value: test123
> utf8_varchar is byte[] : false
> Value: test123
> ===================================
>
> Test with octetsAsBytes
> octets_char is byte[] : true
> Value: [B@16a5d72
> Value of array: [116, 101, 115, 116, 49, 50, 51, 32, 32, 32]
> octets_varchar is byte[] : true
> Value: [B@1d07e4
> Value of array: [116, 101, 115, 116, 49, 50, 51]
> utf8_char is byte[] : false
> Value: test123
> utf8_varchar is byte[] : false
> Value: test123
>
>
> package nl.lawinegevaar.firebird;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.util.Arrays;
>
> import javax.sql.rowset.CachedRowSet;
>
> import com.sun.rowset.CachedRowSetImpl;
>
> /**
> * Run with table structure:
> * <pre>
> * CREATE TABLE octets_table (
> * ID INT,
> * octets_char CHAR(10) CHARACTER SET OCTETS,
> * octets_varchar VARCHAR(10) CHARACTER SET OCTETS,
> * utf8_char CHAR(10) CHARACTER SET UTF8,
> * utf8_varchar VARCHAR(10) CHARACTER SET UTF8
> * );
> * </pre>
> *
> * @author Mark
> */
> public class CachedRowSetOctets {
>
> private static final String DB_URL =
> "jdbc:firebirdsql://localhost/D:/data/DB/metadata_test.fdb";
> private static final int TEST_ID = 1;
> private static final String TEST_STRING = "test123";
> private static final byte[] TEST_BYTE_ARRAY = TEST_STRING.getBytes();
>
> public static void main(String[] args) throws SQLException {
> setTestData();
>
> // test without octetsAsBytes
> System.out.println("Test without octetsAsBytes");
> testWithUrl(DB_URL + "?lc_ctype=UTF8");
> System.out.println("===================================\n");
>
> // test with octetsAsBytes
> System.out.println("Test with octetsAsBytes");
> testWithUrl(DB_URL + "?lc_ctype=UTF8&octetsAsBytes");
> }
>
> private static void setTestData() throws SQLException {
> try (Connection con = DriverManager.getConnection(DB_URL +
> "?lc_ctype=UTF8", "SYSDBA", "masterkey")) {
> try (PreparedStatement dstmt = con.prepareStatement("DELETE
> FROM octets_table WHERE id = ?")) {
> dstmt.setInt(1, TEST_ID);
> dstmt.executeUpdate();
> }
>
> try (PreparedStatement istmt = con.prepareStatement("INSERT
> INTO octets_table (id, octets_char, octets_varchar, utf8_char,
> utf8_varchar) VALUES (?, ?, ?, ?, ?)")) {
> istmt.setInt(1, TEST_ID);
> istmt.setBytes(2, TEST_BYTE_ARRAY);
> istmt.setBytes(3, TEST_BYTE_ARRAY);
> istmt.setString(4, TEST_STRING);
> istmt.setString(5, TEST_STRING);
> istmt.executeUpdate();
> }
> }
> }
>
> private static void testWithUrl(String url) throws SQLException {
> try (Connection con = DriverManager.getConnection(url,
> "SYSDBA", "masterkey")) {
> try (PreparedStatement pstmt = con.prepareStatement("SELECT
> octets_char, octets_varchar, utf8_char, utf8_varchar FROM octets_table
> WHERE id = ?")) {
> pstmt.setInt(1, TEST_ID);
> ResultSet rs = pstmt.executeQuery();
>
> CachedRowSet rowset = new CachedRowSetImpl();
> rowset.populate(rs);
>
> if (rowset.absolute(1)) {
> Object octetsChar = rowset.getObject(1);
> System.out.println("octets_char is byte[] : " +
> (octetsChar instanceof byte[]));
> System.out.printf("Value: %s%n", octetsChar);
> if (octetsChar instanceof byte[]) {
> System.out.println("Value of array: " +
> Arrays.toString((byte[])octetsChar));
> }
>
> Object octetsVarchar = rowset.getObject(2);
> System.out.println("octets_varchar is byte[] : " +
> (octetsVarchar instanceof byte[]));
> System.out.printf("Value: %s%n", octetsVarchar);
> if (octetsVarchar instanceof byte[]) {
> System.out.println("Value of array: " +
> Arrays.toString((byte[])octetsVarchar));
> }
>
> Object utf8Char = rowset.getObject(3);
> System.out.println("utf8_char is byte[] : " +
> (utf8Char instanceof byte[]));
> System.out.printf("Value: %s%n", utf8Char);
>
> Object utf8Varchar = rowset.getObject(4);
> System.out.println("utf8_varchar is byte[] : " +
> (utf8Varchar instanceof byte[]));
> System.out.printf("Value: %s%n", utf8Varchar);
>
> } else {
> System.out.println("Unexpected: no row");
> }
> }
> }
> }
> }
>
>
> Mark
>
> --
> Mark Rotteveel
>