This is in files in src/templates/velocity/java5/pertable.
There is a discrepancy between the routines fillWhere and fillPreparedStatement in the case of NULL values.
fillWhere looks to see whether there is a null value, and either
puts in <column> IS NULL or <column> = ?. BUT fillPreparedStatement
then looks again to see if it is NULL and if so does a setNull, otherwise it does a set<Type>. But the setNull will not work as there is no ? to match against.
In this case it is fillWhere that needs to change, as in the case of insert fillPreparedStatement is used without fillWhere and the column
name is inserted into the column list so setNull or set<Type> is needed.
Basicaly the test for null needs to be removed from fillWhere so that it always puts in the ? regardless of whether there is a NULL value.
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You are right about the discrepancy but I am not sure not to introduce another kind of bug by applying your suggestion.
What would then happen when using fillWhere to select beans (through loadUsingTemplate(), deleteUsingTemplate() or countUsingTemplate() methods) whose <column> must be NULL?
Or I may put it the other way around: it may be that this bug was introduced when trying to make fillWhere select the columns with NULL values in them.
If I remember well, it is not possible to select by making the selection as <column> = ? setting a parameter as NULL through setNull(). Am I wrong?
May it be an option to make fillPreparedStatement kind of "skip" the null columns when selecting and set the null when inserting or updating? Would that make sense?
Thanks for your help,
Alain.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes I think you are right, in which case we need an extra parameter on fillPreparedStatement to say whether we should be using setNull of ignoring NULL values. A simple boolean should suffice. As this is a protected method the change in signature should not be too much of a problem.
The problem as I understand it is not actually the null value, is that the = would have to change to IS, as null values are never equal to anything, even another null, and that is explicit in the SQL standard. So for a select a setNull would have to change the operator and I think they thought was not a nice thing to do under the covers.
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is in files in src/templates/velocity/java5/pertable.
There is a discrepancy between the routines fillWhere and fillPreparedStatement in the case of NULL values.
fillWhere looks to see whether there is a null value, and either
puts in <column> IS NULL or <column> = ?. BUT fillPreparedStatement
then looks again to see if it is NULL and if so does a setNull, otherwise it does a set<Type>. But the setNull will not work as there is no ? to match against.
In this case it is fillWhere that needs to change, as in the case of insert fillPreparedStatement is used without fillWhere and the column
name is inserted into the column list so setNull or set<Type> is needed.
Basicaly the test for null needs to be removed from fillWhere so that it always puts in the ? regardless of whether there is a NULL value.
David
You are right about the discrepancy but I am not sure not to introduce another kind of bug by applying your suggestion.
What would then happen when using fillWhere to select beans (through loadUsingTemplate(), deleteUsingTemplate() or countUsingTemplate() methods) whose <column> must be NULL?
Or I may put it the other way around: it may be that this bug was introduced when trying to make fillWhere select the columns with NULL values in them.
If I remember well, it is not possible to select by making the selection as <column> = ? setting a parameter as NULL through setNull(). Am I wrong?
May it be an option to make fillPreparedStatement kind of "skip" the null columns when selecting and set the null when inserting or updating? Would that make sense?
Thanks for your help,
Alain.
Yes I think you are right, in which case we need an extra parameter on fillPreparedStatement to say whether we should be using setNull of ignoring NULL values. A simple boolean should suffice. As this is a protected method the change in signature should not be too much of a problem.
The problem as I understand it is not actually the null value, is that the = would have to change to IS, as null values are never equal to anything, even another null, and that is explicit in the SQL standard. So for a select a setNull would have to change the operator and I think they thought was not a nice thing to do under the covers.
David