Rebind for NULL values

(1) By David Ritter (dritter29) on 2021-12-20 19:16:21 [link] [source]

Hello!

I am new to SQLite programming and I have a question regarding parameter binding. Am I correct in understanding if I have a column that allows NULL values that in order to indicate a NULL value for a row I need to reset all bound parameters and rebind all columns (with the columns that need a NULL value now bound with sqlite3_bind_null instead) and then rebind again if the next row has non-null values?

Thank you for your help! Dave

(2) By Larry Brasfield (larrybr) on 2021-12-20 19:24:08 in reply to 1 [link] [source]

I suggest study of the doc on binding. In particular, the text beginning "Bindings are not cleared by ..." answers your question. Please indicate if that is unclear.

(3) By David Ritter (dritter29) on 2021-12-20 19:35:38 in reply to 2 [link] [source]

Hello Larry!

Thank you for the prompt response. Upon a fifth reading and a few examples I think I was conflating re-binding and re-preparing.

Thank you for the dope slap! Dave

(4) By Larry Brasfield (larrybr) on 2021-12-20 19:41:18 in reply to 3 [link] [source]

My reply was not intended as a "dope slap". We (mostly) try to be kind here.

I prefer a short RTM to encourage people to read the docs, which are fairly well indexed, organized, and comprehensive. My request for a report of any unclarity is sincere. (We take such reports seriously, and we often make doc changes in response to such reports.)

(5) By David Ritter (dritter29) on 2021-12-20 20:50:14 in reply to 4 [link] [source]

Heh. Sorry no offence intended. That's just me being glib.

I'm accustomed to other C database APIs (like ODBC) where there is a null indicator field used for indicating a null value is being specified for a row.

I (incorrectly) assumed that SQLite worked the same way. Since data is only inserted one row at a time and because there is no re-parse of the SQL occurring I can certainly understand how the API works to switch from indicating a null value to a non-null value.

(6) By Simon Slavin (slavin) on 2021-12-21 00:03:40 in reply to 5 [source]

You ran into the specific meaning of NULL which was used when SQL was devised. It does not match the value 'null' as used in C. So please think of NULL has having a specific useful meaning. A bit like zero being a number, but not normally seen in the 'number of items' field.

So you can bind a parameter to null in the C sense. Or you can bind the same parameter to NULL in the SQL sense. And they should do two different things.

(7) By David Ritter (dritter29) on 2021-12-21 00:17:30 in reply to 6 [link] [source]

Hi Simon,

No I'm not referring to a C null value in any sense. To date my background has been working with the C APIs for MS-SQL & DB2, Oracle and Sybase ASE.

In this case I was referring to SQLBindParameter in the ODBC API.

Specifically the last parameter StrLen_or_IndPtr which can be used to either indicate parameter length or the constant SQL_NULL_DATA which would indicate that for that indicated position in the array of items being bound that the value at that position is a DB NULL value.

Because this API allows you to indicate for a specific row and column that you are binding to a values length or NULLness I assumed that SQLite worked in the same way and was trying to reconcile the need to re-bind to insert a NULL value.

As I said, I realize I was conflating binding and preparing now and the fact that SQLite doesn't perform bulk operations like the other DB's I've worked with.

So like I said, my bad I assumed too much reading the documentation.

Apologies for the distraction all.

Thanks!

Dave