• Welcome, Guest. Please login.
 
September 17, 2019, 02:49:30 pm

News:

Welcome to the SQLitening support forums!


SQLite/SQLitening lesson from a n00b

Started by Bern Ertl, February 02, 2009, 11:58:24 am

Previous topic - Next topic

Bern Ertl

So I was debugging my code yesterday and learned a lesson which I thought worth sharing.  I spent 15 befuddled minutes staring at my code before figuring it out.  Maybe it will save someone else some grief if I share...

I was updating an Accounts table with connection handles for each user.  I was writing an Exit procedure (SQLiteningServerExits.DLL) to update this table and set the connection handle field to NULL when they were disconnected.  I check for error conditions at every step of the process.

The code was executing without error and yet not initializing the connection handle field:lResult = slmExe( lhDab, "UPDATE Accounts SET ConHndl = Null WHERE ConHndl = '" + PARSE$( rsData, $BS, 1) + "'")

Eventually, I realized that when I was setting the field, I was using:slExe slBuildInsertOrUpdate( "Accounts", gsConHandle, "ConHndl", "Username = '" + sUser + "'")

SQLitening/SQLite was storing the connection handle as a numeric/integer and I was specifying a string in the WHERE clause of the SET NULL statement.  SQLite processed the statement without error, but didn't find any matches.  Once I removed the single quotes from the WHERE ConHndl = statement, everything worked as expected.

It's going to take some getting used to SQLite's lack of data typing for column/field data.

Fred Meier

From http://www.sqlite.org/datatype3.html
QuoteEact value stored in SQLite3 is assigned one of the following storage classes:
   NULL. The value is a NULL value.
   INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
   REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
   TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
   BLOB. The value is a blob of data, stored exactly as it was input.

Each column created in SQLite3 is assigned one of the following affinities:
   TEXT
   NUMERIC
   INTEGER
   REAL
   NONE

The type affinity of a column is determined by the declared type of the column, according to the following rules:
   If the datatype contains the string "INT" then it is assigned INTEGER affinity.
   If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity.
   If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.
   If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity
   Otherwise, the affinity is NUMERIC.

The following program creates a one column table with affinity of NONE and
then inserts two rows.  The first row has a storage class of INTEGER
while the second has storage class of TEXT.  Run the program and you will
see that the first select will return only RowID 1 while the second will return
only RowID 2.   

Now change the affinity to other than NONE and run again, both selects will return both rows.
  slOpen "Test.Sld", "C"
   slExe "Drop Table T1; Create Table T1 (F1); Insert into T1 values (1); Insert into T1 values ('1')"
   slSel "Select rowid from T1 where F1=1"
   do while slGetRow
      ? "Where is 1 -- RowID=" & slF(1)
   loop
   slSel "Select rowid from T1 where F1='1'"
   do while slGetRow
      ? "Where is '1' -- RowID=" & slF(1)
   loop


Whats the point?  A good understanding of storage class and affinities
will assist you in understanding their affect on comparison expressions.

Bern Ertl

Another couple of lessons that might be second nature to SQL pros, but was not obvious to me:

1. If you DELETE a non-existant record (by RowID for example), SQLite returns %SQLite_OK.

2. If you UPDATE a non-existant record, SQLite returns %SQLite_OK.  You need to use slGetChangeCount to ensure that the update was successful.


Bern Ertl

3. When defining a selection with a WHERE clause, if the field could be NULL, you need to test for it separately from any specific value.  For example:

SELECT F1 FROM T1 WHERE F1 <> 3

will not return records where F1 is NULL, so you need to use the following:

SELECT F1 FROM T1 WHERE (F1 IS NULL OR F1 <> 3)

Parenthesis not really required if it's the only condition being tested, but might be necessary if you are using multiple WHERE conditions.

Bern Ertl

* bump *

This one got me again today... *sigh*   lol

Quote from: Bern Ertl on April 10, 2009, 05:58:54 pm
3. When defining a selection with a WHERE clause, if the field could be NULL, you need to test for it separately from any specific value.  ...

Bern Ertl

May 29, 2013, 02:00:47 pm #5 Last Edit: May 29, 2013, 02:07:53 pm by Bern Ertl
Well, I learned a new thing today, so thought I'd share.  I am busy writing trigger code that looks something like this:

CREATE TRIGGER fku_blah_blah BEFORE UPDATE OF Inactive ON T1 FOR EACH ROW WHEN NEW.Inactive = 1
   BEGIN
      UPDATE T3 SET F1 = 0 WHERE (SELECT T1Index FROM T2 WHERE T2.RowID = T3.T2Index) AND (
         (SELECT Inactive FROM T4 INNER JOIN T5 ON T5.T4Index = T4.RowID INNER JOIN T2 ON T5.T2Index = T2.RowID WHERE T2.T1Index = NEW.RowID) IS NULL OR
         (SELECT Inactive FROM T4 INNER JOIN T5 ON T5.T4Index = T4.RowID INNER JOIN T2 ON T5.T2Index = T2.RowID WHERE T2.T1Index = NEW.RowID) <> 1)
   END


The ( (SELECT) IS NULL OR (SELECT) <> 1 ) code has to test for both a discrete value and NULL and since it's a join of several tables, I'd really rather not have SQLite running the SELECT statement twice.  Fortunately, the IFNULL() core function provides a solution:

CREATE TRIGGER fku_blah_blah BEFORE UPDATE OF Inactive ON T1 FOR EACH ROW WHEN NEW.Inactive = 1
   BEGIN
      UPDATE T3 SET F1 = 0 WHERE (SELECT T1Index FROM T2 WHERE T2.RowID = T3.T2Index) AND
         IFNULL( (SELECT Inactive FROM T4 INNER JOIN T5 ON T5.T4Index = T4.RowID INNER JOIN T2 ON T5.T2Index = T2.RowID WHERE T2.T1Index = NEW.RowID), 0) <> 1
   END


Bern Ertl

I've been working on a scripted reporting engine that can be fed a SELECT statement and instructions for populating a grid control with the result set.  The nature of the system doesn't allow for much customization of the values in the result set, so the data returned needs to be in the final format.  Fortunately, the SELECT statement actually allows you a lot of flexibility in formatting results.  It is possible to do stuff like this:

SELECT (Name || ' - ' || IDTag || ' - Item # ' || CAST( RowID AS TEXT)) AS Person, ...

Also...

SELECT (CASE WHEN TF <= 0 THEN 'Critical' WHEN TF <= 144 THEN 'Near Critical' ELSE '' END) AS Status, ...

This isn't a ground breaking discovery, but it was new to me and I thought it was pretty cool.

Bern Ertl

Was writing some SQL code this morning and figured out the hard way that SQLite's binary operator for concatenation does not work the same as PowerBASIC's string handling.

SELECT (colA || colB) AS colAB

This works fine as long as both columns contain data.  If colB contains a NULL value, the concatenation will output a NULL value.  To "fix" this, you need to use ifnull():

SELECT (colA || ifnull(colB, '')) AS colAB