No announcement yet.

SQLite/SQLitening Programming

  • Filter
  • Time
  • Show
Clear All
new posts

    SQLite/SQLitening Programming

    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:

    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.

    SQLitening local and shared files ?


    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



      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
      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.

      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.
      '  ==========================================================================
      '  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 ]>==============================
         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)
            lsPort = "51234"
            lsPort = INPUTBOX$("Enter remote port number", gsTitle, lsPort)
            gsTitle = "SQLitening Example B -- Remote Mode (" & lsServer & "  " & lsPort & ")"
         CASE %IDYES
            ? "SERVER NOT NEEDED
            lsServer = $NUL
            gsTitle = "SQLitening Example B -- Local Mode"
            EXIT FUNCTION
         END SELECT
         ' ==========>>> 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)"
         ' 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"
         ' 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))
         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) & ")"
         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
         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
            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
      Last edited by Mike Doty; 27 Sep 2011, 08:29 AM.


        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

        Volker (Trading system development and simulation tools)


          Originally posted by Volker Butzlaff View Post
          ... 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.
          Last edited by Chris Holbrook; 27 Sep 2011, 11:23 AM.



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

            Excel VBA Course and Tutorial


              Works here (using WinRAR)

              "It was too lonely at the top".


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

                Will have a from work.

                Excel VBA Course and Tutorial