Sign in to follow this  
Followers 0
Digisoul

Need help with SQLite

2 posts in this topic

#1 ·  Posted (edited)

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 by Digisoul

73 108 111 118 101 65 117 116 111 105 116

Share this post


Link to post
Share on other sites

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

#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 ()

GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0