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