SQLitening Support Forum

Steve Hutchesson posted some ASM code that can replace the PB JOIN$() function for certain special cases and I've incorporated it into my SQLiteningServer build and it seems to be running solidly during my initial testing.  I haven't been able to benchmark the improvement to SQLiteningServer itself, but mcat$() is running roughly 5 times faster than JOIN$() in my tests, so in theory, it should help the SQLiteningServer respond to client requests for data quicker.

I made the following changes:

First, save the following code block as mcat.inc:[code]' mcat$() developed by Steve Hutchesson:
'   Alternative to JOIN$.   (Dec. 29, 2017)
'   https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-inline-assembler/768261-alternative-to-join/page2

'

I created a distribution of the app that I've been developing for the last few years.  When I test installing the app on a new computer (ie. not my development computer), the first time the client app attempts to call a function in SQLiteningProcsT.Dll (slRunProc "TmyFunc" ...), the slRunProc call is failing on the LoadLibrary call.  LoadLibrary is not finding SQLiteningProcs.T.dll even though it's in the folder where the server app is installed.  Anyone have any ideas on what might be wrong?

I have been deep in the weeds trying to optimize my application code over the last couple of weeks.  Yesterday, I decided to have a closer look at the SQLitening code to see if I could realize any optimization gains there.  I discovered some interesting (new to me anyway) possibilities as demonstrated by this test code:#COMPILE EXE
#DIM ALL

FUNCTION PBMAIN () AS LONG

    REGISTER I AS LONG, J AS DWORD
    LOCAL lReps AS LONG
    LOCAL sText AS STRING
    LOCAL qT1 AS QUAD, qT2 AS QUAD
    LOCAL pdwValue AS DWORD PTR, pbValue AS BYTE PTR

    sText ="Some random text"

    lReps = 50000

    DO
        TIX qT1
        pbValue = STRPTR( sText)
        FOR I = 1 TO lReps
            J = @pbValue[ I MOD LEN( sText)]
        NEXT
        TIX END qT1
        TIX qT2
        FOR I = 1 TO lReps
            J = ASC( sText, (I MOD LEN( sText)) + 1)
        NEXT
        TIX END qT2
    LOOP UNTIL MSGBOX( "qT1=" + FORMAT$( qT1, "0,") + ", qT2=" + FORMAT$( qT2, "0,") + ", Do again?", %MB_YESNO) = %IDNO

    DO
        TIX qT1
        FOR I = 1 TO lReps
            J = CVDWD( sText, 5)
        NEXT
        TIX END qT1
        TIX qT2
        pdwValue = STRPTR( sText)
        FOR I = 1 TO lReps
            J = @pdwValue[ 1]
        NEXT
        TIX END qT2
    LOOP UNTIL MSGBOX( "qT1=" + FORMAT$( qT1, "0,") + ", qT2=" + FORMAT$( qT2, "0,") + ", Do again?", %MB_YESNO) = %IDNO

END FUNCTIONAccording to my testing, using a pointer to access numeric data embedded in a string is surprisingly more efficient than using PowerBASIC's CVDWD/CVBYT functions.  I can only assume this is because the built in statements are doing some error/bounds testing.

So, I tried modifying two functions in SQLiteningClient.BAS:'========================<[ Get Column Text ]>=========================
FUNCTION SQLiteGetColumnText ALIAS "SQLiteGetColumnText" (BYVAL rlSetNumber AS LONG, _
                                                          BYVAL rlCol AS LONG) EXPORT AS STRING
'   Returns the text data for passed SetNumber at passed Col
'   Row data chunk in array format:
'        1 -   4 = Set number as Long.
'        5 -   8 = Position of next row as Dword.
'        9 -  12 = Length of row data chunk.
'       13 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none

   REGISTER llColLen AS LONG, lhColPos AS DWORD   '6/23/16 Bernard Ertl

   LOCAL llSetIX AS LONG
   LOCAL llColCount AS LONG
'   LOCAL llColLen AS LONG            'Made REGISTER
'   LOCAL lhColPos AS DWORD      'Made REGISTER
   LOCAL lhEndOfRow AS DWORD

   LOCAL pbVal AS BYTE PTR, pdwVal AS DWORD PTR   '6/23/16 Bernard Ertl

   ' Find set
   IF UBOUND(tsaSets) > -1 THEN ARRAY SCAN tsaSets(), FROM 1 TO 4, = MKL$(rlSetNumber), TO llSetIX
   IF llSetIX THEN

      ' Init stuff
      '6/23/16 Bernard Ertl  -----------------
      pbVal = STRPTR( tsaSets(llSetIX))
      pdwVal = pbVal
      lhColPos = @pdwVal[ 1] + 4&
      pdwVal = pbVal + lhColPos - 5&
      lhEndOfRow = lhColPos + @pdwVal

'      lhColPos = CVDWD(tsaSets(llSetIX), 5) + 4
'      lhEndOfRow = lhColPos + CVDWD(tsaSets(llSetIX), lhColPos - 4)
      '6/23/16 Bernard Ertl  -----------------

      ' Locate column
      DO WHILE lhColPos < lhEndOfRow
      '6/23/16 Bernard Ertl  -----------------
'         llColLen = CVBYT(tsaSets(llSetIX), lhColPos)
         llColLen = @pbVal[ lhColPos-1]  'Base 0
      '6/23/16 Bernard Ertl  -----------------
         INCR lhColPos
         IF llColLen >= 254 THEN
            IF llColLen = 255 THEN
      '6/23/16 Bernard Ertl  -----------------
'               llColLen = CVDWD(tsaSets(llSetIX), lhColPos)
               pdwVal = pbVal + lhColPos - 1&
               llColLen = @pdwVal
      '6/23/16 Bernard Ertl  -----------------
               lhColPos = lhColPos + 4
            ELSEIF llColLen = 254 THEN
               RESET llColLen
            END IF
         END IF

         ' Check if requested column
         IF llColCount = rlCol THEN
            FUNCTION = MID$(tsaSets(llSetIX), lhColPos, llColLen)
            EXIT DO
         END IF

         ' Bump to next col
         lhColPos = lhColPos + llColLen
         INCR llColCount
      LOOP
   END IF

END FUNCTION
and:'===========================<[ Next Row ]>============================
FUNCTION SQLiteNextRow ALIAS "SQLiteNextRow" (BYVAL rhDab AS DWORD, _
                                              BYVAL rlSetNumber AS LONG) EXPORT AS LONG
'   Bumps to next row within the row data chunk which is stored in the
'   global sets array.  If no more rows in current row data chunk and
'   there are more rows at server then will request next row data chunk
'   from server.
'   Returns -1 if there is a next row or 0 if no row available any other
'   value is an error.
'   Row data chunk in array format:
'        1 -   4 = Set number as Long.
'        5 -   8 = Position of next row as Dword.
'        9 -  12 = Length of row data chunk.
'       13 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none
'   Row data chunk returned from server format:
'        1 -   4 = Length of row data chunk.
'        5 -   R = Row data chunk
'      R+1 - R+4 = Position next row data chunk as Dword, zero if none

    REGISTER lhCurRowPos AS DWORD                 '6/23/16 Bernard Ertl

   LOCAL llRC AS LONG
   LOCAL llSetIX AS LONG
'   LOCAL lhCurRowPos AS DWORD         'Made REGISTER
   LOCAL lsRowDataChunk AS STRING

   LOCAL pbVal AS BYTE PTR, pdwVal AS DWORD PTR   '6/23/16 Bernard Ertl

   ' Find set
   IF UBOUND(tsaSets) > -1 THEN ARRAY SCAN tsaSets(), FROM 1 TO 4, = MKL$(rlSetNumber), TO llSetIX
   IF llSetIX THEN
      '6/23/16 Bernard Ertl  -----------------
       pdwVal = STRPTR( tsaSets(llSetIX))
       pbVal = pdwVal

      ' Set ptr to first row or bump to next
'      lhCurRowPos = CVDWD(tsaSets(llSetIX), 5)
      lhCurRowPos = @pdwVal[ 1]         'Bytes 5-8  (or 4-7 if considering base 0)
      '6/23/16 Bernard Ertl  -----------------
      IF lhCurRowPos THEN
         ' Bump to next
      '6/23/16 Bernard Ertl  -----------------
'         lhCurRowPos = lhCurRowPos + CVDWD(tsaSets(llSetIX), lhCurRowPos) + 4
         pdwVal = pbVal + lhCurRowPos - 1&
         lhCurRowPos = lhCurRowPos + @pdwVal + 4&
         pdwVal = pbVal
      '6/23/16 Bernard Ertl  -----------------
      ELSE
         ' Set to first
         lhCurRowPos = 13&
      END IF

      ' Check for last row
      '6/23/16 Bernard Ertl  -----------------
'      IF lhCurRowPos >= CVDWD(tsaSets(llSetIX), 9) + 13 THEN
      IF lhCurRowPos >= @pdwVal[ 2] + 13& THEN    'Bytes 9-12  (or 8-11 if considering base 0)

         ' Check if there are more rows available
'         IF CVDWD(tsaSets(llSetIX), lhCurRowPos) THEN
         pdwVal = pbVal + lhCurRowPos - 1&
         IF @pdwVal THEN
      '6/23/16 Bernard Ertl  -----------------

            ' Get next row data chunk
            llRC = DoRequest(%reqGetRowDataChunk, rhDab, rlSetNumber, MID$(tsaSets(llSetIX), lhCurRowPos), 0, lsRowDataChunk)
            IF llRC <> -1 THEN FUNCTION = llRC : GOTO NoMoreRowsAvailable
            tsaSets(llSetIX) = MKL$(rlSetNumber) & MKDWD$(13) & lsRowDataChunk

         ELSE

            ' No more rows available or error so empty the entry for this set and exit function returning non -1
            NoMoreRowsAvailable:
            RESET tsaSets(llSetIX)
            EXIT FUNCTION

         END IF
      ELSE

         ' Update current row position
         MID$(tsaSets(llSetIX), 5) = MKDWD$(lhCurRowPos)

      END IF

      ' We are now pointing to next row so return -1
      FUNCTION = -1

   END IF

END FUNCTIONThese two functions are used by slGetRow, slF (and slFX, slFN, slFNX) to retrieve data from the database.  I tested the modified SQLIteningClient.DLL against the original using a test application that reads 20 columns for 3,000 rows of data (ie. 20x slF calls for 3,000 rows = 60,000 data elements read in remote mode).  In my testing, the modified SQLIteningClient.DLL ran an average of 7.6% faster.

It looks to me like the two functions I modified are doing their own bounds checking, so I think the modifications should be safe. 

If you use slSelStr to return more than one value and don't explicitly set the Q modchar, it uses $nul for all delimiters instead of $BS and $VT as the docs say.  Fortunately, it's real simple to fix.

In SQLitening.BAS, in slSelStr(), insert just before the END IF statement:

~~~~~~~~~~~~~~~~
ELSE
   llBS = 8
   llVT = 11
~~~~~~~~~~~~~~~~

Here's a summary of the suggestions for updating the SQLitening code base (that I'm aware of).  Some might already be incorporated (shouldn't take but a second to confirm)...

Fix for issue with slDisconnect causing lock ups
SQLiteningClient.Bas, add RESET thMutex statement:

SUB SQLiteDisconnect ALIAS "SQLiteDisconnect" EXPORT
  ' Tell ImHere thread to die
  @tlpKillImHere = 1
  reset tlpKillImHere  ' Disconnect and close Tcp socket

  DoRequest %reqDisconnect, 0, 0, "", 0
  TCP CLOSE thSocket
  CloseHandle thMutex
  RESET thMutex
END SUB

Solution discussed here:  http://www.sqlitening.com/support/index.php?topic=9420.0

~~~

Documentation update:  http://www.sqlitening.com/support/index.php?topic=9162.msg24023#msg24023

~~~

slConnect wsOutData pararmeter fix:  http://www.sqlitening.com/support/index.php?topic=9079.0

~~~

Expose new function slKill:  http://www.sqlitening.com/support/index.php?topic=9087.0

~~~

slSetNamedLocks error handling update:  http://www.sqlitening.com/support/index.php?topic=9391.msg24774#msg24774

~~~

(Proposed / solution not detailed) Update Rijndael encryption:  http://www.sqlitening.com/support/index.php?topic=9406.0

Note:  if there is any difference in the encryption (from existing routine to potential replacement), it could break existing apps so this would need to be something optional unless fully tested)

~~~


If I missed something, please add to the list.

I have a section of code that is reading data from a remote db and populating a grid control.  The performance is not where I want it to be currently, so I'm tweaking things trying to optimize it.

This section of code is "generic" in that it can be called from different points with different criteria on what fields (db columns) need to be read/loaded.  I'm using a bit array to designate which fields/columns should be included.  The code builds the SELECT statement based upon which bits/columns are true/selected.

As such, the field position for a given column may change depending upon the selection criteria.  I was using slFN with column names to keep the code simple and clear.

But when I added a bunch of code to index the positions of each selected column and then use slF instead of slFN, I saw a roughly 30% speed increase over my modest test data.

This is just an FYI.  I knew that slFN added some extra processing to find the column position, but I didn't expect it to be that significant.

Hi folks, been a while since I was active here.

I've got a SQLiteningProcs DLL with a function that is doing some heavy calculations.  While testing it, I keep getting an error -18 while the routine is calculating.  Best I can tell, the (slRunProc) function is still in the process of it's calculations (no error conditions or endless loops). 

This looks like a timeout issue, but I did set ConnectionTimeout = -1 in SQLiteningServer.CFG. 

I'm not sure how to proceed in troubleshooting this.  Any ideas?

A long time ago, I had asked Fred about the possibility of adding a function to kill an active connection.  I'm developing an application that uses a login system to manage licenses and I wanted to implement an administrator function that would allow an admin to manage access to the system (including logging out / disconnecting users when required). 

The SQLitening Server Admin program has this function.  There are a couple of functions implemented in SQLIitening that are "admin" only - not exposed through the include file for the client side.

It's actually not too difficult to expose these admin functions.  Here's what I did to implement slKill in case it is of use to anyone else:

In SQLitening.INC, at the end, add:DECLARE FUNCTION slKill LIB "SQLitening.Dll" ALIAS "slKill" ( BYVAL rlTcpFileNumber AS LONG) AS LONG

In SQLitening.BAS, declares section, add:DECLARE FUNCTION UsingKill  CDECL( BYVAL rlTcpFileNumber AS LONG) AS LONG

In SQLitening.BAS, after slDisconnect, add:FUNCTION slKill ALIAS "slKill" ( BYVAL rlTcpFileNumber AS LONG) EXPORT AS LONG
'   Posts a request to kill another connection.  The other connection won't
'   be killed until it attempts a communication with the server.

   LOCAL lhRutAddr AS DWORD

   'Init Stuff
   RESET tlLastError

   'Disconnect from server
   irGetRutAddress "SQLiteKill", lhRutAddr
   CALL DWORD lhRutAddr USING UsingKill( rlTcpFileNumber) TO tlLastError

   FUNCTION = tlLastError

END FUNCTION

In SQLiteningClient.BAS, after SQLiteDisconnect SUB, add:FUNCTION SQLiteKill ALIAS "SQLiteKill" ( BYVAL rlTcpFileNumber AS LONG) EXPORT AS LONG

   'Post kill request
   FUNCTION = DoRequest( %reqAdmin, 3, 0, MKDWD$( rlTcpFileNumber), 0)

END FUNCTION

~~~

To use this, you will need to track the Tcp file numbers for the active connections in a database table.  I'm adding records to a master/admin table when users log in and using SQLIitening server exits to clean up when they disconnect.

Finally got around to investigating the issue I was having with strange behavior for a log in system that I implemented using SQLitening's server exits and a table to monitor active connections.  SQLitening.Txt states:

QuoteslConnect                  ([rsServer String, rlPort Long, rsModChars String, wsOutData String]) Long
...
   OutData, if parm is passed and if return is zero, will contain the
   following values delimited by the $BS character.  There is only one
   value being returned now but others may be added in future so use the
   parse$ command.
      1 = The unique TcpFileNumber assigned by the server to this connection.  This
          can be used whenever a unique number for a connection is needed.  This
          same value is passed to ceratin exits.
...

My code had:

lResult = slConnect( "LocalHost", 0, "E2", sConHandle)
<check lResult>
sConHandle = PARSE$( sConHandle, $BS, 1)

I was expecting sConHandle to be '1' or '4' or something like that.  It's always returning an empty string though.

I checked SQLiteningServer.BAS : ProcessRequest() and the %reqConnect handler is not adding the required data (for bytes 7-?).

I made the following modifications to SQLiteningServer.BAS : ProcessRequest() and it is now functioning as expected:   case %reqConnect
'      wsDataOut = mki$(0) & mkdwd$(Connect_(rsDataIn))   'Original code

      wsDataOut = mki$(0) & mkdwd$(Connect_(rsDataIn)) & FORMAT$( rlTcpFileNumber)  'Modified to return connection handle


Now that Fred is no longer supporting/developing SQLitening, I thought it might be a good idea for those of us using it to discuss a framework for it's future. 

First of all, does anyone know about the disposition of this website?  Paul, do you own the domain name in addition to hosting the site itself?  If not, we should register a backup domain and have a migration plan for when the domain expires (in case it gets acquired by some disinterested third party).  Unless someone in Fred's family is willing to either maintain the domain or donate/sell/transfer it.

I'm not used to developing open source with code bases, etc.  I am totally unfamiliar with how updates and modifications are integrated from multiple developers working with little coordination.  However, I expect to be working on the dropped connections issue in the near future if one of you don't solve it first.  If anyone has any experience or ideas how best to manage updates to the code base when one of us improves it, please share before I (or someone else) does a lot of work that becomes hard to integrate with work done by someone else.

I noticed that he hasn't visited the forum since January 9.  Hopefully, he's just enjoying a nice long vacation...

When writing Procs DLL functions for the SQLitening remote server, it can be difficult to debug the code when you can't easily see what is happening in your code.  I've been using Patrice's zdebug utility for this purpose and it works great:

http://www.jose.it-berater.org/smfforum/index.php?topic=3092.0

Cheers.

I've got a table with records that reference another table:

CREATE TABLE MyTable ( RowID INTEGER PRIMARY KEY,
                        DateData NOT NULL,
                        Data2,
                        FKey INTEGER NOT NULL REFERENCES AnotherTable( RowID) ON DELETE CASCADE,
                        UNIQUE ( DateData, FKey));

Let's say the table has 3 rows in it with FKey = 4.  I want to copy/insert those 3 rows into the same table, but with a new FKey ( = 7 for example). 

I know I can use a select statement to iterate through the 3 rows explicitly in my code and perform 3 separate inserts to accomplish my goal, but I was wondering if there was any way to achieve the same result with a single SQL statement?

I've got a routine that creates a two dimensional byte array and fills it with data.  Both dimensions to the array can vary from one calculation to the next.  I want to store the calculated array in my database until I need to calculate a new one.  The byte array could get large (say 25,000,000 records) in practical use. 

I was thinking it would be best to treat the array as a single blob of binary data.  Is this the correct approach?X = 5000
Y = 5000
sBuffer = STRING$( X * Y, $NUL)
DIM bLG( X, Y) AS BYTE AT STRPTR( sBuffer)

... <fill array with data> ...

slExeBind slBuildInsertOrUpdate( "T1",  "?", "bLG", "RowID=1"), slBuildBindDat( sBuffer, "C")

Add this to a SQLiteningProcs[Letter] file:UNION Quad_FileTime
   Q AS QUAD
   F AS FILETIME
END UNION

FUNCTION qFormat( BYVAL q AS QUAD) AS STRING

   IF q > 100000&& THEN
       FUNCTION = FORMAT$( q \ 100000&&) + FORMAT$( q MOD 100000&&)
   ELSE
       FUNCTION = FORMAT$( q)
   END IF

END FUNCTION

SUB  sqlFnGetTimeStamp CDECL ( BYVAL hContext AS DWORD, BYVAL iArgs AS LONG, BYVAL pArgv AS DWORD PTR) EXPORT

   'Returns FORMAT$( FILETIME) = current server date/time

   LOCAL sResult AS STRING
   LOCAL ST as SYSTEMTIME
   LOCAL CurTime AS Quad_FileTime

   GetLocalTime ST
   SystemTimeToFileTime ST, CurTime.F

   sResult = qFormat( CurTime.q) + $NUL

   sqlite3_result_text hContext, BYVAL STRPTR( sResult), -1, %SQLITE_TRANSIENT

END SUB

FUNCTION ICF ALIAS "ICF" (BYVAL rhDab AS DWORD, BYVAL rlTcpFileNumber AS LONG, blParm1 AS LONG, blParm2 AS LONG, bsParm3 AS STRING, bsParm4 AS STRING) EXPORT AS LONG

   'ICF = Initialize Custom Functions

   'In = None

   'Out:  FUNCTION = 0 for success, <>0 if error
   '         blParm1 = where in execution path SQLite error occured (if any)
   '         bsParm3 = error message if error occurred

   LOCAL lResult AS LONG, zText AS ASCIIZ * 32

   zText = "ServerTimeStamp"
   lResult = sqlite3_create_function( rhDab, zText, 0, %SQLITE_UTF8, 0, CODEPTR( sqlFnGetTimeStamp), 0, 0)

   IF ISTRUE lResult THEN
      bsParm3 = "Failed to create ServerTimeStamp"
      blParm1 = 500  'Return "error line number"
      FUNCTION = lResult 'Set return code
      EXIT FUNCTION
   END IF

END FUNCTION

In your application:...
   'Register functions and keep them persistent ...
   lResult = slRunProc( "[letter]ICF", lReturn1, lReturn2, sBuffer1, sBuffer2, "Eu")
...
   'Application Code
...
   'Unload custom functions when no longer needed
   slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"
...

and finally, in your SQL wherever date time stamping is required...CREATE TABLE IF NOT EXISTS TransactionLog (RowID INTEGER PRIMARY KEY, SQL_Statement, User, TimeStamp);
CREATE TRIGGER fki_TransactionLog_Update_TimeStamp AFTER INSERT ON TransactionLog FOR EACH ROW
   BEGIN
      UPDATE TransactionLog SET TimeStamp = ServerTimeStamp() WHERE RowID = NEW.RowID;
   END;

I'm not sure if this is possible or not, but thought I'd ask the SQL/SQLite gurus here...

I've got a table that includes two columns A and B filled with integer values like so (in ascending order on Column A):

Column A	Column B
1	2
2	25
3	4
4	26
5	6
...	...
25	3
26	5
...	...

I'd like to retrieve records from the table sorted by the minimum value across both columns A and B with column B taking precedence.  Like so:

Column A	Column B
1	2
2	25
25	3
3	4
4	26
26	5
5	6
...	...

It doesn't look like the ORDER BY clause in a SELECT statement gives me any means to achieve this.  Or does it?  Any other elegant solution available?

I'm probably missing something obvious and fundamental, but I just tried creating a Thread Function for the sole purpose of calling a SQLitening Proc and it crashes my app when it tries to call slRunProc. 

My app establishes a connection and opens a database.  Then it creates a new dialog.  Previously, in the process of initializing the new dialog, it called slRunProc.  But this caused a noticable delay as the client app waited on the server to finish the processing of the slRunProc function.  So I tried creating a thread just to call slRunProc, but it ends up crashing the app.

Ideas?

Hi folks.  I've been busy with other projects for a while and I'm just now getting back to my SQLitening project.  I've downloaded the latest (1.6.1) version of SQLitening and read a bit through the release notes.  Is the ping system ("ImHere") for detecting dropped connections working good now?  If the ping system detects a dropped connection, does it fire the "Disconnect" server exit (assuming the system was compiled with that option enabled)?

Maybe it's by design, but the SQLitening.txt file doesn't mention anything about such a restriction.  I was trying to use CHR$(0) as the record delimeter.

I started using SQLitening/SQLite before SQLite offered native support for foreign keys.  As a consequence, I've got a ton of trigger code implemented to handle referential integrity for my foreign keys.  Today, I downloaded the latest SQLite DLL and started experimenting with the native support in the hopes I could eliminate all that trigger code.

I wrote the following test program:#COMPILE EXE
#DIM ALL

#INCLUDE ONCE "SQLitening.Inc"

FUNCTION PBMAIN () AS LONG

LOCAL I AS LONG, J AS LONG

LOCAL lSetNum AS LONG, lResult AS LONG
LOCAL sBuffer AS STRING, sTemp AS STRING

slOpen "fk-test.pdb", "C"

slExe "PRAGMA foreign_keys = ON; PRAGMA recursive_triggers = OFF;"

slExe "CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE, Tag, Desc, Prev_Pct_Comp, Cur_Pct_Comp, Prev_Mhs, Cur_Mhs, Inactive, DisplayOrder);" + _
"CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES JobPlans(RowID) ON DELETE CASCADE, " + _
  "UID UNIQUE NOT NULL, Desc, DisplayOrder, Type, Scope, OrigDur, CurDur, EstConf, OptimisticDur, PessimisticDur, PerComp DEFAULT 0, UID_Preds, UID_Succs, LogicProcessed);"


   slExe slBuildInsertOrUpdate( "JobPlans", "345351", "Name")

MSGBOX "Here we go..."

'   slExe slBuildInsertOrUpdate( "Tasks", "1" + $NUL + "ZZZ", "JobPlan_ID, UID")
slExe "INSERT INTO Tasks(JobPlan_ID,UID)VALUES(1,'ZZZ')"

MSGBOX "Done"

END FUNCTIONIt gives me an SQLite error stating foreign key mismatch.  When I open the fk-test.pdb database in SQLite Expert and execute the insert on the Tasks table (with the pragma set), it works without throwing an exception (unless I change the value for the JobPlan_ID field in which case it throws the exception as expected).

Any idea why the insert fails in my code (using SQLitening)?