|
[
Permalink
| « Hide
]
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
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. 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. 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. 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. 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. Committed fix in trunk, will need to add additional tests and backport to 2.2.1
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.
Some additional changes I made created a buffer overflow in some cases. Reverted most of those changes and focussed on flipping the nullable bit.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Major
Null value incorrectly returned with native (local) protocol
