Issue Details (XML | Word | Printable)

Key: JDBC-271
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Mark Rotteveel
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Null value incorrectly returned with native (local) protocol

Created: 25/Aug/12 09:03 AM   Updated: 21/Feb/13 08:10 PM
Component/s: JDBC driver, JNI layer
Affects Version/s: Jaybird 2.2
Fix Version/s: Jaybird 2.2.1, Jaybird 3.0

Time Tracking:
Not Specified

Issue Links:
Relate
 


 Description  « Hide
http://stackoverflow.com/q/12106422/466862



I'm running into a bit of a situation in corner cases when binding null to a PreparedStatement with Firebird's jaybird JDBC driver. Here's a sample statement:

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

// Observe the "local" in the connection string!
Connection con = DriverManager.getConnection(
    "jdbc:firebirdsql:local:C:/data/firebird/test.db", "TEST", "TEST");

// With this connection, I'm not able to reproduce the issue:
Connection con1 = DriverManager.getConnection(
    "jdbc:firebirdsql:localhost:C:/data/firebird/test.db", "TEST", "TEST");

PreparedStatement stmt = con.prepareStatement(
  "SELECT cast(? as varchar(1)) FROM rdb$database");
stmt.setObject(1, null);
ResultSet rs = stmt.executeQuery();
rs.next();
System.out.println(rs.getString(1));
System.out.println(rs.wasNull());

The output of the above program is

>
> false

The first line being an empty string. It really should be

> null
> true

Changing this line ...

stmt.setObject(1, null);

... into any of these lines ...

stmt.setString(1, null);
stmt.setNull(1, Types.VARCHAR);

... doesn't help either. A workaround is to inline null literals in SQL statements, instead of binding them to the prepared statement. What am I missing?

Details:

    Database: Firebird WI-V2.5.1.26351
    JDBC driver: jaybird-2.2.0
    Java version: JDK 1.6.0_24
    OS: Windows 7 x64
    JDBC Connection String: See above.



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 25/Aug/12 09:07 AM
Reproducible with jdbc:firebirdsql:local:D:/data/DB/testdatabase.fdb and jdbc:firebirdsql:native://localhost/D:/data/DB/testdatabase.fdb

Mark Rotteveel added a comment - 25/Aug/12 09:37 AM - edited
If I insert into a table and read it back, then it does work:
public class BindValues2 {
private static final String CON_STRING_1 = "jdbc:firebirdsql://localhost/D:/data/DB/testdatabase.fdb";
private static final String CON_STRING_2 = "jdbc:firebirdsql:native://localhost/D:/data/DB/testdatabase.fdb";
private static final String CON_STRING_3 = "jdbc:firebirdsql:local:D:/data/DB/testdatabase.fdb";

public static void main(String[] args) throws SQLException {
try (Connection con = DriverManager.
getConnection(CON_STRING_3, "sysdba", "masterkey")) {
PreparedStatement stmt = con
.prepareStatement("INSERT INTO single_varchar (varcharcolumn) VALUES (?)");
stmt.setObject(1, null);
stmt.execute();
stmt.close();
stmt = con.prepareStatement("SELECT varcharcolumn FROM single_varchar");
ResultSet rs = stmt.executeQuery();
rs.next();
System.out.println(rs.getString(1));
System.out.println(rs.wasNull());
}
}
}

Stepping through the code I see that the JNI layer sets sqlind to -1, but it also sets sqldata to a 4 byte array.

Mark Rotteveel added a comment - 25/Aug/12 09:52 AM
Executing:
stmt = con.prepareStatement("SELECT varcharcolumn FROM single_varchar WHERE varcharcolumn = ? OR ? IS NULL");
stmt.setObject(1, null);
stmt.setObject(2, null);
ResultSet rs = stmt.executeQuery();

Also yields the expected result, which makes me suspect that it is (at least partially) a bug in Firebird when the parameter is part of the select list.

Lukas Eder added a comment - 25/Aug/12 10:12 AM
I think it is somehow related to the casting. Here's how to reproduce the issue in a similar fashion:


        con.createStatement().execute("create table single_int (i integer)");
        con.createStatement().execute("insert into single_int values (1)");
        PreparedStatement stmt = con.prepareStatement("update single_int set i = cast(? as integer)");
        stmt.setNull(1, Types.INTEGER);
        stmt.execute();
        ResultSet rs = con.createStatement().executeQuery("select * from single_int");
        rs.next();
        System.out.println(rs.getString(1));
        System.out.println(rs.wasNull());

The above update statement does not actually update a NULL value into the table, but zero.

Mark Rotteveel added a comment - 25/Aug/12 10:27 AM
Looks like you are right, changing the query from my previous comment to:
stmt = con.prepareStatement("SELECT varcharcolumn FROM single_varchar WHERE varcharcolumn = CAST(? AS VARCHAR(1)) OR CAST(? AS VARCHAR(1)) IS NULL");

Will yield no rows with the native implementation, but it will yield rows with the java implementation.

Mark Rotteveel added a comment - 29/Aug/12 06:49 PM
Firebird uses a nullable bit in the sqltype of an xsqlvar to indicate if a column or parameter is nullable or not. When the parameter is described as not nullable, then the Java implementation of the wire protocol will simply send the parameter as if it is nullable, the native client library (fbclient.dll, libfbclient.so) on the other hand does not ignore the nullable bit and if it receives an xsqlvar that is setup to contain NULL, but has the nullable bit not set, then it will send the parameter as its non-null counterpart (eg empty string for a VARCHAR, 0 for INT).

The solution to this problem is to have Jaybird flip on the nullable bit of sqltype when the parameter to be sent is NULL.

I have also filed CORE-3913 as an improvement request to have the fbclient library give precedence to the value sqlind over the nullabe bit of the sqltype when sending parameters from client to server.

Mark Rotteveel added a comment - 29/Aug/12 07:37 PM
Committed fix in trunk, will need to add additional tests and backport to 2.2.1

Lukas Eder added a comment - 29/Aug/12 07:46 PM
Awesome, thanks!

Mark Rotteveel added a comment - 22/Sep/12 12:46 PM - edited
Reopened: while testing Jaybird 2.2.1 with native protocol under Java 5 the tests fail, placing the original jaybird22.dll makes the tests pass.

Mark Rotteveel added a comment - 22/Sep/12 03:25 PM
Some additional changes I made created a buffer overflow in some cases. Reverted most of those changes and focussed on flipping the nullable bit.