SQLitening Support Forum

I have been trying to use an explicit record set number and have found a few "funny" things.

1) If you do an   slCloseSet(setnumber)   when no set is open you get a GPF.

2) There is no way (I can see) to determine if a set is currently open.

3) It appears that doing an slSEL with a set number that is currently open will not return any records (gets a -14 error).

My intent is to use multiple record sets for query's that need to be "left open" based on the program logic.
i.e. -- SELECT A, B, C from Table1 where ...  and begin processing rows from that set.  In some cases while processing that set (Table1) I will need rows from a different table.

Any assistance would be appreciated.

Thanks, Mark

Mark, you raise three valid points.  I have been the only user of my old
SQLite shell for so long that I tend to overlook certain error conditions. 
I will make the following changes to Version 2 which I plan to send to
Paul the first of next week. 

1. All commands that use SetNumber will check if the passed number is valid
   and therefore eliminate any GPF's.  Error -14 will occur if invalid.
  
2. A new command will be added -- slIsSetOpen.  It will return %True if
   set is currently open and %False if it's closed or not in the set array.

3. Yes the following will cause error -14 when the second slSel is done:
      slSel "Select * from Table1"
      slSel "Select * from Table2"

   The following will work fine:
      slSel "Select * from Table1", 0
      slSel "Select * from Table2", 1

   In the first example the second slSel is trying to reuse set number zero
   (zero is implied when ever set number is omitted) which is currently open.
   The second example uses two different set number so will work fine.

   The following will also work fine but will use slightly more memory for
   no obvious advantage.
      slSel "Select * from Table1", 83
      slSel "Select * from Table2", 47

One technique is to set up equates for each set you use and normally do
not reuse the numbers.  Makes debugging much easer also.  Each unused
set number only costs an empty entry in a Dword and String array.  Reusing
set numbers is also fine and SQLitening will complain when invalid. 
Bottom line is you can have as many unique sets open at same time as your
memory will allow. 

Hope this helps.

Fred,

Can you give me a better example of what a 'record set' actually is?  I've been playing around with creating tables and migrating data from a Tsunami file into an SQLighting DB, but the idea of the record set seems to be eluding me.

Also:  the command slBuildInsertOrUpdate doesn't appear in the DOCs nor did I see it on any of the on-line sources I looked at.  Can I assume that this will create a new row if one does not exist, otherwise the row is updated instead?  If so, I suppose the determining factor is the primary key value?

Joe let's take the easy one first.

slBuildInsertOrUpdate does not call SQLite.  It is just a coding aid to
assist you in creating the different SQL syntax used in Insert vs Update
It is normally used to build the string that is input to slExe or
slExeBind.  slBuildInsertOrUpdate is coded and commented in
SQLitening.Bas.  The same comments are in SQLitening.Txt.  I created this
routine because many times you want to update a record if its there or
insert it if it is not there.  The Update/Insert SQL syntax makes this
quite clumsy.  You must write separate code for both conditions.  Lets say
you wanted to update/insert a row that has fields F1, F2 and ID.  ID is the
key field. 
If the row is not there then you must code
   slExe "Insert Into TableA (F1,F2,ID) Values ('abc', 'def', 1)" 
If the row is there then you must code
   slExe "Update TableA Set F1='abc', F2='def' Where ID=1"
Using slBuildInsertOrUpdate, you can code as follows for either condition.
  slExe slBuildInsertOrUpdate("TableA", "abc" & $NUL & "def" & $NUL & "1", "F1,F2,ID", iif$(IsUpdate, "ID=1", "")

Now the harder subject.

A record set is a group of records from a database table.  It consist of
records and columns (fields).  I think of it an array of selected rows and
selected columns.  Almost ever data engine has the concept of a record
set.  Tsunami had something similar called indexes/keys and key paths. 
The SQL select command gives you an unlimited number of sets from a single
table.  And then there are joins.  Whereas a set in Tsunami always has all
the columns and was limited to the predefined indexes/keys.  The data
engine normally hides the details of how these sets are built and kept but
they all have some things in common. 

They have some sort of command to create a record set.  That command
returns something to identify the record set.  This returned thing can
have many names -- cursor, handle, number, object , etc.  SQLitening uses
set numbers as its "thing" for tracking record sets.  The engines provide
you with commands to navigate the record set.  Most of them provide
forward only navigation but some like Tsunami, Jet (Access), and Cheetah provide
many others.  The engines provide commands to obtain specific fields in
the current row.  And finally they provide a command to end or destroy the
set. 

Follows is how SQLite handles sets and how SQLitening helps.  SQLite has the
Prepare API which compiles a SQL Select statement and return a pointer
to the complied statement.  Then has a Step API which obtains the next
row.  Then has APIs to obtain data for a specific column number.  And
last it has a Finalize API to end the set.  Note that SQLite only
allows for forward navigation.  SQLitening uses SQLite sets differently for local vs remote.

For local the slSel does the prepare, the slGetRow does the step, the slF
commands obtain the data, and slCloseSet does the finalize (optional cause
the last slGetRow will also finalize). 

Remote works differently due to lock considerations.  slSel does the
prepare and also all the steps and finalize.  The rows are kept and
managed internally by SQLitening.  Each slGetRow gets the next row from
memory and the slCloseSet just kills the set number (optional cause the
last slGetRow does it also). 

Hope this helps.  You may have been looking for someting else.  Others may
have better ways to explain.  If you have more questions how some of the
internals work, please ask. 

Hi Joe,

I'll jump in.... A recordset is simply the records that are returned from your SQL SELECT query. When you issue a SELECT command to the server, SQLite compiles the SQL into virtual machine code that represents the various functions that need to be carried out in order to satisfy your request. The code is executed by SQLite and the results can be retrieved row by row to the client.

For example, when you issue a SELECT * FROM parts; you will get all fields from all rows returned from the "parts" table. The server executes the command and builds a temporary set of results and then feeds it back to the client in chunks as needed (whenever the client looks for the next row via "slGetRow"). For efficiency, the server will send blocks of records to the client rather than one row at a time (because the overhead of a TCP call to the server would be too much for individual rows).

So, you can think of it this way: Whenever I issue a SELECT command to the server, the server will execte the request and create a "record set" that will eventually find its way back to the client where the client processes each row as needed.

... looks like Fred replied as I was typing my feable response. 

I just finished switching all of the SQLite code in FireFly 3 over to SQLitening. It is amazing how much clearer and concise the new code is. Fred, you did a brilliant job on that code base. 

Is FF3 going to have some direct support for SQLitening?

<drool>

FF3 itself uses SQLitening to handle certain things like the Code Snippets and Code Store.

FF3 has a new Special Functions area called "FF_AppStart" that allows you to put things like your Includes and compiler directives. To add SQLitening support to FF3 you simply add something like the following to FF_AppStart

#Include "..\modules\sqlitening.inc"

So far, using SQLitening with FF3 has been extremely easy.