SQLite – GUI interworking

As stated elsewhere, this executable has two separate command line interpreters. 

All command lines are first given to the outer interpreter which (basically) checks the first token/keyword to decide if the line is to be passed to the SQLite database machine or not.

So if for example the first keyword is select then the line is passed to the SQLite code.

In principle there is no way to pass values from the outer interpreter to the SQLite machine.


Example:

Say we want to run a query on a table

SELECT * from myTable WHERE myCol like '%Fred%' OFFSET 50

But we then want to include this query inside a function and have the Fred text and the OFFSET value 50 provided as parameters to the function.

E.g.

function myQuery ( colText , fromPos )
SELECT * from myTable WHERE myCol like '%colText%' OFFSET fromPos 
END function

Well it simply will not work. The SQL and non-SQL parts of the program are not interconnected (at this level!)


But there is one trick built-in to the executable.

There is the command SQL which will pass a text string to the SQLite machine. For example:

SQL = "SELECT * from myTable WHERE myCol like '%Fred%' OFFSET 50"

In fact the SQL command does not care how the string was created. So the following is valid.

dim Part1 = "SELECT * from myTable "
dim Part2 = " myCol like '%Fred%' OFFSET 50"
sql = part1   &   " where " &  part2

So we can now rewrite the function myQuery to be

function myQuery ( colText , fromPos )
SQL = "SELECT * from myTable WHERE myCol like '%" & colText & "%' OFFSET " & fromPos )
END function


SQLite uses " and   ' a bit differently than the outer interpreter. So it is possible to get very confused very quickly.

To make this less confusing (?) there are 3 custom commands.

1 – Any command line whose first keyword/token is > gets the following handling.

  • An internal variable, freestring, is set to be an empty string.
  • All characters after the >, up to but excluding the end of line, are copied to freestring, without any modification

2 – Any command line whose first keyword/token is >> gets the following handling.

  • All characters after the >>, up to but excluding the end of line, are appended to the end of freestring, without any modification

3- freestring is a normal variable, it is just that it is pre-declared, has global scope, and can be changed by normal commands as well as by the > and >> commands.

So we can now rewrite the function myQuery to be:

function myQuery ( colText , fromPos )
>  SELECT * from myTable WHERE myCol like '%
freestring = freestring & coltext
>>%' OFFSET
freestring = freestring &  fromPos
SQL = freestring
END function


But be careful:

  1. the text taken from the  > and >> commands starts immediately after the token, even if there is no white-space immediately after the token.
  2. passing a text string to SQLite can hit a subtle problem. What if the text contains an embedded ' then SQLite will have a problem, embedded ' should be doubled-up.
  3. if the text string is being used as a table or column name and if the text contains a ", then we have a similar problem. The " must be doubled-up.

The solution is to use the built-in sqlIdentifier and sqlString functions.

  • sqlIdentifier takes a string parameter and returns the string enclosed in a pair of " characters, and doubles-up any embedded" characters.
  • sqlIdString takes a string parameter and returns the string enclosed in a pair of ' characters, and doubles-up any embedded ' characters.

So we can make the function myQuery a bit safer by defining it as:

function myQuery ( colText , fromPos )
>  SELECT * from myTable WHERE myCol like
freestring = freestring & sqlString ( "%" & coltext & "%" )
>> OFFSET 
freestring = freestring &  fromPos
SQL = freestring
END function

So, using the trick made possible by the command SQL we can get the outer interpreter to construct a SQL command and pass it to the SQLite interpreter.


How about getting information from SQL to the outer interpreter!

Most SQLite commands that return a query type result, have the result written to the lower panel of the executable window. To do this the executable intercepts the result and directs it to the rows and columns of the lower panel.

So the executable knows how many rows and columns are in the result. These are made available as read only values qrRows and qrCols.

If the query result returns a single column in a single row, then that value is available as the read only value qrSingleValue.

So for example the result of a  SQLite query such as

Select count (*) from family where age > 20

Can be obtained by simply accessing the value qrSingleValue

Select count (*) from family where age > 20
message = qrSingleValue


The executable can intercept a query response and extract one result row at a time, and extract selected columns from that row.

Here is an example where we trigger a query request, intercept the results and access individual columns of the response.

We shall use my standard database demo as the starting point:

IF dbopen THEN
Message =" A Database is already open, save it and or close it. Then rerun this demo"
stop
end if
' we could use a menu command File/ New Memory DB to create a new database in memory, we will use a command
dbopen ("memory")
' the following are pure SQLite commands
' create a table called Family with 4 columns
' refer to the demo via menu Help / SQL / Demos / BLOBs on using the column named Pic
create table family ( Role text , Age int, Id TEXT , Pic BLOB )
insert into family values ( 'Father', 42, 0123 , NULL)
insert into family values ( 'Mother', 41, 4.5 , NULL)
insert into family values ( 'Daughter', 4, 000 , NULL)
insert into family values ( 'Son', 14, -1 , NULL)
insert into family (role, age) values ( 'Grandfather', 70)

We then add the following script snippet:

freestring = ""
withQuery ( "select age, role from family where age > 5 ")
freestring = freestring & " Age= " & wqInt(1) & " Role= " & wqText(2) & chr(0x0a)
end WithQuery
message = freestring

I have taken these two bits of code, inserted them in to the Edit Panel of the executable, pressed the Execute button, and got the following:

The results of a WithQuery are not written to the lower panel!

chr(0x0a) creates a single character string that consists of an End of Line character.

Note: the wq? commands such as wqText and wqInt take 1 based indices. The index is the position of the wanted column in the query result.

There are a few other commands similar to wqInt and wqText. Use the command  help( "wq") to get information about them.

(wqText, wqName, wqAlias, wqInt, wqDint, wqType, wqCols, wqRows)