Passing nulls to Named Query when testing

I have an SQL table which has nullable numerical fields (and none of the fields have default values) , and I want to use a Named Query to update it. Basically a simple:

UPDATE TableName
SET
FieldName1 = :FieldValue1,
FieldName2 = :FieldValue1,
FieldName2 = :FieldValue1
WHERE keyField=:KeyVal

When testing the query from the Name Query testing panel I only filled out the parameters for the KeyVal and one of the numerical value fields. But when I browsed the table directly from the DB I saw that all the numerical fields for which I did not explicitly supply a value had a value of zero.

How do I fill out parameters in a Named Query test so that null (or python None) values get correctly propagated to the DB table as nulls?

Or is this just a side effect of the testing panel?

And if I call the query with a dictionary like { ‘FieldValue1’ : None, ‘FieldValue1’ : None } etc, will that propagate the null values?

Answering my own question. The setting of numerical values to zero seems to be an artifact of the Named Query testing panel. When I called the query via system.db.runNamedQuery and only provided some of the parameters, the rest of the numerical fields ended up with nulls.

4 Likes

I’m not aware of setting a value to null under the testing panel, it seems like it wants to do 0 for an integer if you clear it, which would not be the same as null.

Does anyone have any good suggestions?

As far as I know, the query testing tab will not allow parameters to be null. To test it, you would have to use the script console, or test it in the project. I’ve run into this as well when writing queries with parameters controlling filtering.