SQLite NULL via variable

[plastix]

Hi all

I am probably missing the obvious here

I am reading an Access MDB into an array, then parsing it and inserting it into an SQLite DB.

I am reading the Access table into an array and setting one of the fields to 'NULL' with:

_SQL_GetTable2D(-1,"SELECT * FROM `TABLE`",$array,$row,$col)
For $i = 1 To $row
$array[$i][10] = "" ;What is SQLITE_NULL ?
Next

Certain fields are then written to SQLite via another loop...

I was hoping there was a value that I could set a field to that = SQLITE_NULL, rather than handle the NULL insertion by coding conditional queries i.e.

$query = "INSERT into TABLE (Val1,Val2,Val3) VALUES ('" & $array[$i][1] & "','" & $array[$i][2] & "',"
If $array[$i][10] = "" Then
    $query &= "NULL);"
Else
    $query &= "'" & $array[$i][10] & "');"
EndIf

TIA

[ChrisL]

in SQLITE use isNull, at least that is how I check for a Select * from Table Where Value isNull

Edited August 17, 2010 by ChrisL

[plastix]

in SQLITE use isNull, at least that is how I check for a Select * from Table Where Value isNull

Thanks ChrisL - actually, I'm trying to insert NULL into table - except the entry may not always be NULL - the value is in a variable. If the variable is "NULL", then that is a string... $variable = NULL will error out it AutoIt. I couldn't see any declared constant for this in the includes either.

[PsaltyDS]

Null is a keyword in SQLite. So if you use it literally (without quoting) in an SQLite statement it will result in an SQLite null value. So you either have to include the quoting (or lack thereof) in your array, leaving it off NULL, or interpret it when building the query string (as you have now).

Your current test is dangerous, because for example 0 = "".

Also, how would you put the string "123" in your array, and then how would that differ from the integer 123? They shouldn't be the same in your query string for insertion.

The solution seems to be to store the values in your array with the quoting already included/excluded as appropriate.

Global $array[5][11]
For $i = 0 To UBound($array) - 1
    
    ; ...
    
    $array[$i][7] = "'123'" ; String '123'
    $array[$i][8] = 123 ; Integer 123
    $array[$i][9] = "'NULL'" ; String 'NULL'
    $array[$i][10] = "NULL" ; Literal SQL NULL keyword
Next

Edited August 17, 2010 by PsaltyDS

[zorphnog]

The solution seems to be to store the values in your array with the quoting already included/excluded as appropriate.

Just to add my two cents, it is best practice to use _SQLite_Escape for all string input. Its easier than having to single quote everything and it will take care of special situations (i.e. having a ' in a string).

Global $array[5][11]
For $i = 0 To UBound($array) - 1
    
    ; ...
    $array[$i][6] = _SQLite_Escape("I'm a string with a single quote")
    $array[$i][7] = _SQLite_Escape("123") ; String '123'
    $array[$i][8] = 123 ; Integer 123
    $array[$i][9] = _SQLite_Escape("NULL") ; String 'NULL'
    $array[$i][10] = "NULL" ; Literal SQL NULL keyword
Next

Edited August 17, 2010 by zorphnog

[jchd]

The first example in your original post is wrong. Inserting an empty string is not the same than inserting null.

In SQL, NULL means the absence of value, not an empty value. The difference is important but can be difficult to materialize in some cases.

For instance, I don't know if the access ODBC driver you use actually sends you a null or an empty string in case of SQL null in a given column.

Since there is no standard way to represent NULL in an AutoIt variable, I tend to believe you simply receive an empty string.

In that case, how can you make the difference in your array, even before writing to SQLite?

Now if you insist inserting SQL nulls in some columns, you have the possibility to omit the column in question, or insert null as a keyword.

create table T (a, b, c)
insert into T (a) values (1); -- there, b and c will be null
insert into T (a, c) values (null, 'some string'); -- there, a and b will be null
insert into T values ('another string', null, null); -- b and c will be null

BTW, be aware that SQLite will allow multiple null values in primary keys (but not in integer primary keys).

@zorphnog

Thank you for rebashing this, most users don't find it useful (until they get severely bitten by their lazyness).

[plastix]

@PsaltyDS - thanks. That confirms how to declare Null literally... I would have thought that would be handled as a string/text entry.

@zorphnog - thanks for the heads up in escaping strings - I hadn't really got as far as thinking about sanitising entries etc.

@jchd - thanks. I agree - I posted this query as I wasn't sure if it was possible to use null literally - so I had been using an empty string in interim. Whatever is received from Access via ADODB is handled by Autoit as an empty string. Many thanks for suggesting leaving out fields - I hadn't thought of that...

Sorted. Thanks again to all.

[jchd]

Just a sidenote: the same issue arises with values in CSV (or TSV or ...) files. There's no "standard" way to represent NULLs (as far as there is _anything_ standard with CSVs!)

When I really need to differentiate NULLs from emtpy strings in my own applications, I use the following convention: non-nulls text values use a string delimiter, e.g. double quote while nulls don't. For non string values, the absence of any value means again NULL.

In short it means that:

"abc";;"def";123;"ghi";456.78;X'';

translates as:

"abc" NULL "def" 123 "ghi" 456.78 zero-length-blob NULL

Of course this is a proprietary convention, but it works nicely with simple code for both conversions (import/export).