Subject Re: delete with null not executed
Author hybriszero
--- In, Roman Rokytskyy <roman@...> wrote:
> If you get 0, it means that the logic in your DELETE is wrong and you
> have no entry where one of the listed fields contains NULL.
> Note, setting parameter to NULL does not tell server to ignore the
> parameter condition, but to match the record with NULL in the specified
> field.

i found that the problem is that setting a null parameter does not
match the columns with null values in it, you should use "is null".
i tought that this was handled either by the driver or the db server
but it seems it's not the case.

problem solved by using this technique:

so instead of doing
delete ... where x=?
i now do
delete ... where x=? or (?=0 and x is null)

then if i have a null parameter to pass, i also pass a 0 value to the
second parameter
if i have a non null parameter, i also pass a 1 to the second parameter

a bit more logic needed on my side but at least i saved my generic
query generator from being totally useless.

thank you anyway for your quick response