Jump to content
Sign in to follow this  
plastix

SQLite NULL via variable

Recommended Posts

plastix

Hi all

I am probably missing the obvious here Posted Image

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

Share this post


Link to post
Share on other sites
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. Posted Image

Share this post


Link to post
Share on other sites
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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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 by zorphnog

Share this post


Link to post
Share on other sites
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).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×