Need help with SQLite

[Digisoul]

Hello there

I am totally stuck in my program where i am using SQLite, i am having a problem with my "search function"

here is a code example:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $aResult, $iRows, $iColumns, $iRval,$RowLimit = 499

_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

If Not _SQLite_Exec (-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

For $i = 0 To 1000
    If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Alice"&$i&"','"&$i&"');") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
Next

; Query
$iRval = _SQLite_GetTable2d (-1, "SELECT Name,Age FROM Persons WHERE Name LIKE 'Alice10%' LIMIT "&$RowLimit&";", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)


Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()

in this example i am trying to get the data from "Row 0" to "Row 499" , i tried "LIMIT" but after reading the documentation about "LIMIT"; is a keyword which can only be use to get the data from "Number of Rows", what is the proper way to restrict the search from "Row n" to "Row n" ?

I also tried "OFFSET" keyword but its use for; to specifies how many rows to skip at the beginning of the result set.

Thanks in advance.

Edited May 18, 2010 by Digisoul

[Yoriz]

Hey, gives this a try using greater than and less than against rowid.

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $aResult, $iRows, $iColumns, $iRval,$RowLimit = 499

_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

If Not _SQLite_Exec (-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

For $i = 0 To 1000
    If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Alice"&$i&"','"&$i&"');") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
Next

; Query
;~ $iRval = _SQLite_GetTable2d (-1, "SELECT Name,Age FROM Persons WHERE Name LIKE 'Alice10%' LIMIT "&$RowLimit&";", $aResult, $iRows, $iColumns)
$iRval = _SQLite_GetTable2d (-1, "SELECT Name,Age FROM Persons WHERE Name LIKE 'Alice10%' AND ROWID > 0 AND ROWID < 500" & ";", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)


Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()