FBClient: non-nullable parameter with null value should be sent as nullable [CORE3913] #4248
Comments
|
Commented by: @mrotteveel |
Modified by: @mrotteveelComponent: API / Client Library [ 10040 ] |
Modified by: @mrotteveeldescription: In Firebird 2.5.0 and 2.5.1 (and probably earlier), parameters are described with the nullability of their columns. If a user of the fbclient library sets everything correct for this parameter in the xsqlvar to send a NULL value (eg sets sqlind to -1), but does not enable the nullable bit of sqltype, then the fbclient will ignore the sqlind value and send the parameter as if it was a NON NULL value (eg empty string for (VAR)CHAR, 0 for INT, etc). I think it would be better if - for sending parameters from client to server - the sqlind value is leading, not the nullability flag of sqltype (the server doesn't care, as this is exactly what the pure ava implementation of Jaybird does). The current parameter description can lead to situation where you think you are using NULL, where in reality you are using the NOT NULL counterpart. For example: Now we try to insert using: With the native client and doing everything in the xsqlvar except setting the nullable bit of sqltype will result in the empty string being inserted instead of getting GDS Exception. 335544347. validation error for column VARCHARCOLUMN, value "*** null ***" as the client library will send the value as if it is not null. => In Firebird 2.5.0 and 2.5.1 (and probably earlier), parameters are described with the nullability of their columns. If a user of the fbclient library sets everything correct for this parameter in the xsqlvar to send a NULL value (eg sets sqlind to -1), but does not enable the nullable bit of sqltype, then the fbclient will ignore the sqlind value and send the parameter as if it was a NON NULL value (eg empty string for (VAR)CHAR, 0 for INT, etc). I think it would be better if - for sending parameters from client to server - the sqlind value is leading, not the nullability flag of sqltype (the server doesn't care, as this is exactly what the pure Java implementation of Jaybird does). The current parameter description can lead to situation where you think you are using NULL, where in reality you are using the NOT NULL counterpart. For example: Now we try to insert using: With the native client and doing everything in the xsqlvar except setting the nullable bit of sqltype will result in the empty string being inserted instead of getting GDS Exception. 335544347. validation error for column VARCHARCOLUMN, value "*** null ***" as the client library will send the value as if it is not null. |
Modified by: @mrotteveel |
|
Commented by: @aafemt No way. Data access components must throw an exception when user is trying to assign NULL to non-nullable parameter. |
|
Commented by: @mrotteveel I get your point. I forgot that sqlind is a pointer. I however disagree with your statement that the access component should throw an exception when assigning NULL to a non-nullable parameter. There are sufficient use cases where you as an end-user of the access component want to be able to set null to a non-nullable parameter. For example having a NOT NULL column with a trigger which will - for example - assign a generated value if the column is NULL. Maybe instead Firebird should describe parameters as always being nullable, but that would prevent clientside decisions based on the nullability. |
Modified by: @mrotteveelpriority: Major [ 3 ] => Minor [ 4 ] |
Submitted by: @mrotteveel
Relate to JDBC271
Votes: 1
In Firebird 2.5.0 and 2.5.1 (and probably earlier), parameters are described with the nullability of their columns. If a user of the fbclient library sets everything correct for this parameter in the xsqlvar to send a NULL value (eg sets sqlind to -1), but does not enable the nullable bit of sqltype, then the fbclient will ignore the sqlind value and send the parameter as if it was a NON NULL value (eg empty string for (VAR)CHAR, 0 for INT, etc).
I think it would be better if - for sending parameters from client to server - the sqlind value is leading, not the nullability flag of sqltype (the server doesn't care, as this is exactly what the pure Java implementation of Jaybird does).
The current parameter description can lead to situation where you think you are using NULL, where in reality you are using the NOT NULL counterpart.
For example:
CREATE TABLE SINGLE_VARCHAR
(
VARCHARCOLUMN Varchar(1) NOT NULL
);
Now we try to insert using:
INSERT INTO SINGLE_VARCHAR(VARCHARCOLUMN) VALUES (?)
With the native client and doing everything in the xsqlvar except setting the nullable bit of sqltype will result in the empty string being inserted instead of getting GDS Exception. 335544347. validation error for column VARCHARCOLUMN, value "*** null ***" as the client library will send the value as if it is not null.
The text was updated successfully, but these errors were encountered: