SQLitening Support Forum

Hello All,

I wonder if SQLitening can handle parameterised Query. Something like this:


local sFrom as string
local sTo as string

CONTROL GET TEXT hDlg, FromDateid& TO sFrom
CONTROL GET TEXT hDlg, ToDateid& TO sTo

SELECT * FROM tblStoreReceives WHERE [InvoiceDate] BETWEEN sFrom AND sTo


Since SQLite don't support data navigation, would it be impractical to implement it in SQLitening? Just wondering. 'Cos Clients like to navigating their data.

Thanks guys for the great work with SQLitening.

Kind regards,

SQLitening supports paramater binding.

Quote
' The syntax and doc for slExeBind is:
'    slExeBind (rsStatement String, rsaData() String, [rsModChars String]) Long
'       Executes one statement which is normally an Insert or Update.
'       Will replace all of the ? expressions in Statement with the
'       corresponding values in the Data() array.  The first ? will be
'       replaced with Data(1), the second ? with Data(2), etc.  If there
'       are more ? expressions than entries in Data() they will be set
'       to Null.  More entries in Data() than ? expressions will be
'       ignored. The first byte in an array entry will always contain a
'       code which will control the type of bind and the encryption
'       and/or compression action. A list of these codes is detailed
'       below.  The actual data that will replace the ? expression
'       begins in position 2 of the array entry.
'         Control Codes -- Char and Hex
'            A or &H41 = Bind as Text, No Encrypt, No Compress
'            Q or &H51 = Bind as Text, Yes Encrypt, No Compress
'            a or &H61 = Bind as Text, No Encrypt, Yes Compress
'            q or &H71 = Bind as Text, Yes Encrypt, Yes Compress
'            B or &H42 = Bind as Blob, No Encrypt, No Compress
'            R or &H52 = Bind as Blob, Yes Encrypt, No Compress
'            b or &H62 = Bind as Blob, No Encrypt, Yes Compress
'            r or &H72 = Bind as Blob, Yes Encrypt, Yes Compress
'            The bit assignments in the code byte are as follows:
'               &H01 - On  = Bind as Text
'               &H02 - On  = Bind as Blob
'               &H10 - On = Encrypt
'               &H20 - On = Compress
'               &HCC - Bits are ignored
'       Sample Statement: "Update T1 set F1=?, F2=? Where rowid=1"
'       ModChars:
'         E = Return errors.
'       Returns %SQLITE3_OK (zero) if executed OK. Else, depending on ModChars,
'       will either display error and exit or will return the error number.

Check out "ExampleC.bas" that comes with the SQLitening installation (around line 128).

Quote
Since SQLite don't support data navigation...

Huh? SQLite and SQliteing both allow you to navigate through the resulting recordset. Maybe you mean something different by the term "data navigation".

Yes SQLite supports Between x And y.  So the following is valid:
Select * from T1 Where F1 is Between x and y

Paul is quoting the doc from an older version for slExeBind.  The current version is:

QuoteslExeBind                  (rsStatement String, rsBindDats String, [rsModChars String]) Long
   Statement contains one (no multiples like slExe) Insert or Update
   statement.  Will replace all of the '?' expressions in Statement with the
   corresponding BindDat entry passed in BindDats.  A BindDat entry is the
   specially formated string returned by the slBuildBindDat function.  Pass
   BindDats as multiply concatenated BindDat entries to handle multiple '?'
   expressions.  The first '?' will be replaced with the first BindDat entry,
   the second '?' with the second BindDat entry, etc.  Excess '?' expressions
   will be set to Null while excess BindDat entries will rais an error.  The
   following example will insert three columns as follows:
     ColName1 = Blob, ColName2 = Compressed Text, ColName3 = Compressed Encrypted Blob.
       slExeBind slBuildInsertOrUpdate("TableName", _
                                       "?" & $NUL & "?" & $NUL & "?" & $NUL, _
                                        "ColName1, ColName2, ColName3"), _
                                        slBuildBindDat("This is some Blob data") & _
                                        slBuildBindDat("This is some copressed Text", "TC") & _
                                        slBuildBindDat("This is a copressed and encrypted Blob", "CE")
   ModChars:
      Em = Return errors. This will override the global return errors flag.
           m is the optional message display modifier and can be:
              0 = No message is displayed.  This is the default.
              1 = Display a warning message with OK button.  Error is
                  returned when OK pressed.
              2 = Display a question message with OK and Cancel buttons.
                  If they press OK, error is returned.  If they press
                  Cancel, will exit process.
      e  = Do not return errors, display message and exit process. This
           will override the global return errors flag.
   Returns zero if processed OK. Else, depending on ModChars and the global
   return errors flag, will either display error and exit or will return
   the error number.

Hello,

Thank you Paul and Fred for your response.

>>Huh? SQLite and SQliteing both allow you to navigate through the resulting recordset. Maybe you mean something different by the term "data navigation".

What I meant was being able to navigate records like Cheetah does. Having something like MoveFirst, MovePrevious, MoveNext and MoveLast.

All in all I think I would use SQLitening for my next project.

Kind regards,

Take a look at the slSelAry routine.  For PowerBASIC users this will put a
record set into an array.  You can then "navigate" the array anyway you
desire.    Huge sets may be a memory probelm.

Hello Fred,

>>Take a look at the slSelAry routine.

Ok.

>>For PowerBASIC users this will put a record set into an array.

I use Powerbasic For Windows 9. Ok after putting the records in an array. You navigate using what commands? I think sample codes would in place here.

Thank you for your response.

Regards,

See the following routine in ExampleC.Bas:
'==========================<[ Query Array ]>===========================
Sub QueryArray
' Will select all the columns for all the customers returning the record
' set in a 2 dimension array.   Once we have the array we will display
' the data going forward thru the array and then display the same data
' going backward.

Hello Fred,

>>See the following routine in ExampleC.Bas:

Yeah, I have seen that. That means one is improvising here. Data Navigation is not built into neither SQLitening or SQLite. With this method one could run out of memory if the resultset is very large. Anyway it's not very wise to navigate very large records one record at a time.

Thank you for your response and the great work on SQLitening.

Best regards,

SQLite and SQLitening support forward only cursors in the recordsets. This means that there is a built in way to navigate from start to end of a recordset without any memory problems. However, if you want to move forward, backwards, start, end, then you need to save the recordset results somehow and iterate over that saved data set. Fred has chosen to use a simple array to say the results... an alternative could be to save them to a temporary disk file if memory is a problem.

Quote from: Fredrick Ughimi on June 16, 2009, 03:40:41 PMSince SQLite don't support data navigation, would it be impractical to implement it in SQLitening?

You are basically asking for SQLitening to provide functions to navigate the array returned by slSelAry.  I would think it woud be much more efficient (and flexible) for you to access the array(s) directly.

FYI:

Quote...
The problem is that the sqlite3_step() function does not step through a precomputed result set at all. A better and more realistic way to think about matters is to suppose that each prepared statement is really a computer program. You are running this program in a debugger and there is a breakpoint set on a single statement somewhere deep down inside the computation. Calling the sqlite3_step() function is like pressing the "Run" button in your debugger and thus asking the debugger to run the program until it either exits or hits the breakpoint. Sqlite3_step() returns SQLITE_ROW if it hits the breakpoint and SQLITE_DONE if it finishes. If you hit the breakpoint, you can then look at local variable in order to find the values of a "row". Then you can press the "Run" button (call sqlite3_step()) again to continue execution until the next breakpoint or until the program exits.

From this point of view (which is much closer to how SQLite works on the inside) asking for an sqlite3_step_backward() button is really like expecting your symbolic debugger to be able to run backwards or to "undo" its execution back to the previous breakpoint. Nobody reasonably expects debuggers to be able to do this, so you shouldn't expect SQLite to be able to sqlite3_step_backward() either.
...

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

Thanks guys for your response.

Still trying out some stuff with SQLitening so far so good.

Hi,

here is a little program to show how to navigate through a database.  It might be a little awkward  approach but it works.
#COMPILE EXE
#DIM ALL
#DEBUG ERROR ON
#INCLUDE "win32api.inc"
#Include "SQLitening.Inc"

%IDFIRST = 100
%IDPREVIOUS = 101
%IDNEXT = 102
%IDLAST = 103
%IDLBDATA = 110

global hDlg AS LONG
Global RecVal as string

' ========================================================================================
' Main
' ========================================================================================
FUNCTION WinMain (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS ASCIIZ PTR, BYVAL nCmdShow AS LONG) AS LONG
   DIALOG NEW 0, "SQLitening Navigate", , , 400, 240, %WS_OVERLAPPED OR %WS_THICKFRAME OR %WS_SYSMENU OR _
   %WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_VISIBLE OR %DS_CENTER TO hDlg
   ' For icon from resource, instead use something like, LoadIcon(hInst, "APPICON")
   DIALOG SEND hDlg, %WM_SETICON, %ICON_SMALL, LoadIcon(%NULL, BYVAL %IDI_APPLICATION)
   DIALOG SEND hDlg, %WM_SETICON, %ICON_BIG, LoadIcon(%NULL, BYVAL %IDI_APPLICATION)

   CONTROL ADD BUTTON, hDlg, %IDFIRST, "<<", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
   CONTROL ADD BUTTON, hDlg, %IDPREVIOUS, "<", 50, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
   CONTROL ADD BUTTON, hDlg, %IDNEXT, ">", 100, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
   CONTROL ADD BUTTON, hDlg, %IDLAST, ">>", 150, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT

   CONTROL ADD BUTTON, hDlg, %IDOK, "&Ok", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
   CONTROL ADD BUTTON, hDlg, %IDCANCEL, "&Close", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT

CONTROl add listbox, hDlg, %IDLBDATA, , 0, 20, 400, 180, %LBS_NOSEL
   DIALOG SHOW MODAL hDlg, CALL DlgProc

' Connect - adapt this to you local setting or leave it out if working locally
' slConnect "192.168.178.33"

   ' Open the sample database
   slOpen "sample.db3"

END FUNCTION
' ========================================================================================

' ========================================================================================
' Main Dialog procedure
' ========================================================================================
CALLBACK FUNCTION DlgProc() AS LONG
   LOCAL rc AS RECT

   SELECT CASE CB.MSG

      CASE %WM_INITDIALOG

      CASE %WM_SIZE
         ' Resize the two sample buttons of the dialog
         IF CB.WPARAM <> %SIZE_MINIMIZED THEN
            GetClientRect CB.HNDL, rc
            MoveWindow GetDlgItem(CB.HNDL, %IDOK), (rc.nRight - rc.nLeft) - 185, (rc.nBottom - rc.nTop) - 35, 75, 23, %TRUE
            MoveWindow GetDlgItem(CB.HNDL, %IDCANCEL), (rc.nRight - rc.nLeft) - 95, (rc.nBottom - rc.nTop) - 35, 75, 23, %TRUE
            DIALOG REDRAW CB.HNDL
         END IF

      CASE %WM_COMMAND
         SELECT CASE CB.CTL
            case %IDFIRST
call GoFirst
CASE %IDPREVIOUS
call GoPrevious
CASE %IDNEXT
call GoNext
CASE %IDLAST
call GoLast

CASE %IDOK
               IF CB.CTLMSG = %BN_CLICKED THEN
               END IF
            CASE %IDCANCEL
               IF CB.CTLMSG = %BN_CLICKED THEN DIALOG END CB.HNDL, 0
         END SELECT

      CASE %WM_DESTROY
   END SELECT

END FUNCTION
' ========================================================================================


' ========================================================================================
' Database Navigation
' ========================================================================================
function GoFirst()as long
local t as string

slOpen "sample.db3"
slSel "Select * from Parts WHERE Manuf = '3COM' ORDER BY Product LIMIT 1"
if slGetRow then
RecVal = slFN("Product")
t = slFN("Manuf") & " - " & slFN("Product")
listbox add hDlg, %IDLBDATA, t
else
listbox add hDlg, %IDLBDATA, "NO RECORDS"
end if
slCloseSet
END FUNCTION

' ========================================================================================
function GoPrevious()as long
local t as string

slOpen "sample.db3"
slSel "Select * from Parts WHERE Manuf = '3COM' AND Product < '" & RecVal & "' ORDER BY Product DESC LIMIT 1"
if slGetRow then
RecVal = slFN("Product")
t = slFN("Manuf") & " - " & slFN("Product")
listbox add hDlg, %IDLBDATA, t
else
listbox add hDlg, %IDLBDATA, "REACHED FIRST RECORD"
end if
slCloseSet
END FUNCTION

' ========================================================================================
function GoNext()as long
local t as string

slOpen "sample.db3"
slSel "Select * from Parts WHERE Manuf = '3COM' AND Product > '" & RecVal & "' ORDER BY Product LIMIT 1"
if slGetRow then
RecVal = slFN("Product")
t = slFN("Manuf") & " - " & slFN("Product")
listbox add hDlg, %IDLBDATA, t
else
listbox add hDlg, %IDLBDATA, "REACHED LAST RECORD"
end if
slCloseSet
END FUNCTION

' ========================================================================================
function GoLast()as long
local t as string

slOpen "sample.db3"
slSel "Select * from Parts WHERE Manuf = '3COM' ORDER BY Product DESC LIMIT 1"
if slGetRow then
RecVal = slFN("Product")
t = slFN("Manuf") & " - " & slFN("Product")
listbox add hDlg, %IDLBDATA, t
else
listbox add hDlg, %IDLBDATA, "NO RECORDS"
end if
slCloseSet
END FUNCTION


I'll see to make a little more universal function out of it. Shouldn't be to difficult.

You need the sample.db3 database from the package for this example. Source and Exe is in the zip.

Rolf