Erlang ODBC parameter query with null parameters

Question

Is it possible to pass null values ​​to parameter requests? for instance

Sql = "insert into TableX values (?,?)". Params = [{sql_integer, [Val1]}, {sql_float, [Val2]}]. % Val2 may be a float, or it may be the atom, undefined odbc:param_query(OdbcRef, Sql, Params). 

Now, of course, odbc: param_query / 3 is going to complain if Val2 is undefined when trying to match sql_float, but my question is: ... Is it possible to use a parameterized query, for example:

 Sql = "insert into TableY values (?,?,?,?,?,?,?,?,?)". 

with any null parameters? I have a use case where I dump a large amount of real-time data into a database by inserting or updating. Some of the tables I'm updating have a dozen or so NULL fields, and I have no guarantee that all the data will be there.

Combining SQL for each query, checking for null values ​​seems like a complicated and wrong way to do this.

Having a parameterized query for each permutation is simply not an option.

Any thoughts or ideas would be fantastic! Thanks!

Answer 1

You can use the null atom to denote a null value. For instance:

 Sql = "insert into TableX values (?,?)". Params = [{sql_integer, [Val1]}, {sql_float, [null]}].