SQLite/SQLitening Programming

[Martin Francom]

I have recently posted a sample program to show some techniques in programming with SQLite/SQLitening. I hope it is helpful. It is a FF3 project.
The link is:

http://www.powerbasic.com/support/pbforums/showthread.php?t=41953

Rolf Brandt, wrote the SQLite/SQLItening portion of the program. The program has been a way for me to learn SQLITE programming. And this sample program has been very helpful. Thank you, Rolf.

I would like any comments, or suggestions for improvement. Also, eventually I would like to see how additional tables are added how to interrelate them with JOIN statement. If you want to make modifications and repost the sample program please do.

[Hubert Brandel]

SQLitening local and shared files ?

Hi,

how are your expirience with SQLitening at all ?
Does the LOCAL Version allow to share a database or can only the lokal PC connect to it ? I know that normaly the client / server version should be used, but I can't install a service on those PC

[Mike Doty]

The server is not required for multiuser access

Yes, multiple processes may access without needing to install a server.
The server is installed as a service, as you know, and does not need to be present.

For others reading this:
Coding is identical for local and client/server. If client server is needed, just
add an IP address, set a port and start the server. If using the server, the
allowed users and other settings must be set in the file SqliteningServer.Cfg.


SQLitening is one of the best programs I have seen for developing applications.
Four example programs come with SQLitening and all source is included.
ExampleA.bas, ExampleB.bas, ExampleC.Bas, ExampleD.bas.

I had nothing to do with the developement of this product
and I believe I was first introduced by one of the moderators Paul Squires.
Fred Meier is the developer at http://www.sqlitening.com
Thank you Fred, Paul and anyone else involved!

The code below just remarks out some lines of ExampleB.bas to eliminate message boxes and run local
without asking any questions. Also, remarked out the drop tables so the databases increase in size.
Without the drop the tables the 2 tables remain the same size.

I created a batch file to brute force inserts by multiple processes.

ExampleA.Bas may require the server so I used ExampleB.bas, here.

easy.bat

exampleb
exampleb
exampleb
dir Exampleb*.sld

'  ==========================================================================
'  |                                                                        |
'  | SQLitening Example B                                                   |
'  |                                                                        |
'  ==========================================================================
'  This meaningless program will create two new databases.  Insert a
'  row into the first (attached) one and then get it back comparing
'  to be sure its the same.  This demo's encryption and compression
'  using the slExeBind and slBuildInsertOrUpdate commands.
'  It will then insert 50,000 records twice.  Once using slExeBind and
'  then a second time using slExe.  slExeBind will be faster.
'
'  ==========================================================================
'  PUBLIC DOMAIN SOFTWARE
'  The author or authors of this code dedicate any and all
'  copyright interest in this code to the public domain.
'  Anyone is free to copy, modify, publish, use, compile,
'  sell, or distribute the original code, either in source
'  code form or as a compiled binary, for any purpose,
'  commercial or non-commercial, and by any means.
'  Fred Meier - July 2011
'  ==========================================================================
#COMPILE EXE "..\Bin\ExampleB.Exe"
#DIM ALL
#INCLUDE "..\Inc\SQLitening.Inc"
'============================<[ Globals ]>=============================
GLOBAL gsTitle AS STRING
'==============================<[ Main ]>==============================
FUNCTION PBMAIN()
   LOCAL llDo AS LONG
   LOCAL lsServer AS STRING
   LOCAL lsPort AS STRING
   LOCAL ldTime1, ldTime2 AS DOUBLE
   DIM lsaRows() AS STRING
' ==========>>>    Ask if they want to run local or remote
   gsTitle = "SQLitening Example B -- Local Mode"
   lsServer = $NUL
   #IF 0
   SELECT CASE MSGBOX("Do you want to run Local or Remote mode?" & $CR & $CR & "Yes = Local             No = Remote", %MB_YESNOCANCEL, "SQLitening Example C")
   CASE %IDNO
      ? "LOCAL HOST"
      lsServer = "LocalHost"
      lsServer = INPUTBOX$("Enter remote server name", gsTitle, lsServer)
      IF ISFALSE LEN(lsServer) THEN EXIT FUNCTION
      lsPort = "51234"
      lsPort = INPUTBOX$("Enter remote port number", gsTitle, lsPort)
      IF ISFALSE LEN(lsPort) THEN EXIT FUNCTION
      gsTitle = "SQLitening Example B -- Remote Mode (" & lsServer & "  " & lsPort & ")"
   CASE %IDYES
      ? "SERVER NOT NEEDED
      lsServer = $NUL
      gsTitle = "SQLitening Example B -- Local Mode"
   CASE %IDCANCEL
      EXIT FUNCTION
   END SELECT
   #ENDIF
   ' ==========>>> SQLitening -- Connect
   ' Will connect to a SQLitening server.  This is only required if
   ' you want to run in remote mode. Do not call this routine or set
   ' Server to $NUL if you want to run in local mode. If Server is
   ' omitted or empty it will default to LocalHost.
&aõ H‚bsp;  slConnect lsServer, VAL(lsPort)
   ' Create new database
   slOpen "ExampleB_New1.Sld", "C"
   'slExe "Drop Table If Exists T1"
   slExe "Create Table If Not Exists T1 (F1, F2, F3, F4)"
   slClose
   ' Create second new database and attact the first
   slOpen "ExampleB_New2.Sld", "C"
   'slExe "Drop Table If Exists T1"
   slExe "Create Table If Not Exists T1 (F1, F2, F3)"
   slAttach "ExampleB_New1.Sld", "One"
   ' Insert some rows with encrypted and compressed fields into the first database
   slSetProcessMods "K1234567890123456"
   slExeBind slBuildInsertOrUpdate("One.T1", "?" & $NUL & "?" & $NUL & "?" & $NUL & "Just some data"), _
                                    slBuildBindDat("Encrypt and Compress as Text", "TNC") & _
                                    slBuildBindDat("Encrypt and Compress as Blob", "BNC") & _
                                    slBuildBindDat("Neither as Text", "T")
   ' Now we will get the fields and make sure they equal the original
   slSel "Select * from One.T1"
   DO WHILE slGetRow
      IF slFX(1, "DU")  <> "Encrypt and Compress as Text" THEN MSGBOX "Field 1 is different"
      IF slFX(2, "DU") <> "Encrypt and Compress as Blob" THEN MSGBOX "Field 2 is different"
      IF slF(3) <> "Neither as Text" THEN MSGBOX "Field 3 is different"
   LOOP
   ' Now we will use slExeBind to do a large number of inserts.
   ' Using slExeBind this way tells SQLite3 to only prepare/compile
   ' the SQL statement one time and then insert each row re-using the
   ' prepared/compiled statement.  We also load all of the BindDat's
   ' into an array and then use the join$ command to build a single
   ' string --- much faster than concatenating each string. These two
   ' techniques are a way to insert or update many records very fast.
   ldTime1 = TIMER
   REDIM lsaRows(1 TO 100000)
   FOR llDo = 1 TO UBOUND(lsaRows)
      lsaRows(llDo) = slBuildBindDat(FORMAT$(llDo))
   NEXT
   slExe "Begin"
   slExeBind "Insert into T1 values(?, 'X', ?)", JOIN$(lsaRows(), ""), "V2"
   slExe "End"
   ldTime1 = TIMER - ldTime1
   REM msgbox "We inserted " & format$(ubound(lsaRows)/2, "#,###") & " records in just" & format$(ldTime1, "* #.000") & " seconds using slExeBind.", , gsTitle
   ' Now we will use slExe to do a large number of inserts.  This
   ' will be slower than using slExeBind.
   ldTime2 = TIMER
   REDIM lsaRows(1 TO 50000)
   FOR llDo = 1 TO UBOUND(lsaRows)
      lsARows(llDo) = "Insert into T1 values(1" & FORMAT$(llDo) & ", 'X', 2" & FORMAT$(llDo) & ")"
   NEXT
   slExe "Begin;" & JOIN$(lsaRows(), ";") & ";End"
   ldTime2 = TIMER - ldTime2
   REM msgbox "We inserted " & format$(ubound(lsaRows), "#,###") & " records in " & format$(ldTime2, "* #.000") & " seconds using slExe.", , gsTitle
   REM msgbox "Notice that slExeBind was " & format$((ldTime2 - ldTime1) / ldTime2 * 100, "00.00") & "% faster then slExe.", , gsTitle
   ' ==========>>>    Done
   slClose
   IF lsServer = $NUL THEN
      REM if msgbox("End of SQLitening Example B.  Do you want to delete the two databases used in this example?", %MB_YESNO, gsTitle) = %IDYES then
      REM    kill "ExampleB_New1.Sld"
      REM    kill "ExampleB_New2.Sld"
      REM end if
   ELSE
      MSGBOX "End of SQLitening Example B.   The following two files were created in the same folder as the service which is running on your server.  You will want to delete them manually." & $CR & $CR & _
             "ExampleB_New1.Sld" & $CR & "ExampleB_New2.Sld", ,gsTitle
   END IF
   BEEP
END FUNCTION

[Volker Butzlaff]

SQLite and SQLitening...

... in addition to your samples I'll mention the incredible "inMemory" option of SQLite:

slOpen ":memory:"

So you can use also standard SQL commands for extreme fast analysis procedures in your code without using physical DB access (if that fits for you) and without additional programming.

I've build a little freeware analysis tool for stock prices, indexes etc. using that feature in connection with PB/Win and SQLitening:

Zen Analyzer v1.2

http://www.zentrader.de/html/support1.html

bye,
Volker

[Chris Holbrook]

... in addition to your samples I'll mention the incredible "inMemory" option of SQLite

There is an example of its use in the Source Code Forum here.

[Steve Pardoe]

Martin,

That zip file (of the FF address book example) appears to be corrupted. Anyone else had that problem ?

Steve

[Bob Houle]

Works here (using WinRAR)

--Bob

[Steve Pardoe]

Thanks for that Bob. With WinZip I get pretty much all files failed CRC32 check.

Will have a from work.

Steve