SQLite semi Embedded database functionality in AutoIt

[Edek]

Generally LiteX Automation may be registered from any location.

It is possible to use LiteX and standard SQLite3 UDF's together. The script below show how to do it. You do not need sqlite3.dll library in system or script directory.

It is taken from latest beta version of LiteX Automation package.

expandcollapse popup

#comments-start
    This sample demonstrates how to use
    LiteX Automation together with standard
    _SQLite3_... UDFs included into AutoIt
#comments-end

#include <File.au3>
#include <SQlite.au3>

Global Const $DB_FILE = _TempFile()
Global Const $DB_QUERY = "SELECT count(*),avg(b),total( c ) FROM Test"
Global Const $MAX_REC = 2222
Global $g_oComError

#comments-start
    Reads location of LiteX Automation library from registry.
#comments-end
Func _LiteX_Path()
    Local $sLiteXPath = RegRead("HKEY_CLASSES_ROOT\CLSID\{3E22694D-7B92-42A1-89A7-668E2F7AA107}\InprocServer32", "")
    If @error Then
        Return SetError(@error, @extended, "")
    Else
        Return SetError(0, 0, $sLiteXPath)
    EndIf
EndFunc   ;==>_LiteX_Path

#comments-start
    If LiteX Automation is registered in your system then you can use that library
    instead of standard sqlite3.dll.
    
    Instead of _SQLite_Startup use _LiteX_Startup.
    Call normal _SQLite_Shutdown() when you finish.
#comments-end
Func _LiteX_Startup()
    Local $sLiteXPath
    
    $sLiteXPath = _LiteX_Path()
    If Not @error Then
        _SQLite_Startup($sLiteXPath)
        If @error Then
            Return SetError(1, 0, False)
        Else
            Return SetError(0, 0, True)
        EndIf
    Else
        Return SetError(1, 1, False)
    EndIf
EndFunc   ;==>_LiteX_Startup

#comments-start
    Here is sample application.
#comments-end

Func com_err_func()
    Beep(400, 200)
    Dim $sMsg = "COM Error: " & Hex($g_oComError.number, 8)
    ConsoleWriteError($sMsg & @CRLF)
    $sMsg = "Source: " & $g_oComError.source
    ConsoleWriteError($sMsg & @CRLF)
    $sMsg = "Error description #1: " & $g_oComError.windescription
    ConsoleWriteError($sMsg & @CRLF)
    $sMsg = "Error  description #2: " & $g_oComError.description
    ConsoleWriteError($sMsg & @CRLF)
    If Not @Compiled Then
        $sMsg = "Line: " & $g_oComError.scriptline
        ConsoleWriteError($sMsg & @CRLF)
    EndIf
    Return SetError(1, $g_oComError, False)
EndFunc   ;==>com_err_func

$g_oComError = ObjEvent("AutoIt.Error", "com_err_func")

$sLiteXPath = _LiteX_Path()
If @error Then
    ConsoleWriteError("Fail to find LiteX Automation library." & @CRLF)
    ConsoleWriteError("Make sure that LiteX Automation is registered." & @CRLF)
    Exit 1
EndIf

ConsoleWrite("LiteX Automation library location: " & $sLiteXPath & @CRLF)
ConsoleWrite("LiteX Automation library version: " & FileGetVersion($sLiteXPath) & @CRLF)

;
; Init
;
$oConnection = ObjCreate("LiteX.LiteConnection")
If @error Then
    ConsoleWriteError("Fail to create LiteX Connection object." & @CRLF)
    Exit 10
EndIf

_LiteX_Startup()
If @error Then
    ConsoleWriteError("Fail to load LiteX Automation library." & @CRLF)
    Exit 2
EndIf

;
; Version
;
ConsoleWrite("SQLite3 library version #1: " & _SQLite_LibVersion() & @CRLF)
ConsoleWrite("SQLite3 library version #2: " & $oConnection.Version (False) & @CRLF)

;
; Structure
;
ConsoleWrite("Creating database structure." & @CRLF)
$oConnection.Open ($DB_FILE)
$oConnection.BatchExecute ("PRAGMA CODEPAGE='UTF-8';CREATE TABLE Test( a INTEGER PRIMARY KEY, b, c );CREATE INDEX idxb ON Test(b);CREATE INDEX idxc ON Test( c )")

$db = _SQLite_Open($DB_FILE)

;
; Data phase one
;
ConsoleWrite("Building database phase 1 (UDF's)." & @CRLF)
$tBegin = TimerInit()
_SQLite_Exec($db, "BEGIN TRANSACTION")
For $i = 1 To $MAX_REC
    _SQLite_Exec($db, StringFormat("INSERT INTO Test(b,c) VALUES (%f,%d)", Random(88, 888, 1), Random(234, 444)))
Next
_SQLite_Exec($db, "COMMIT TRANSACTION")
$tDiff1 = TimerDiff($tBegin)
ConsoleWrite("Phase complete. Time " & Round($tDiff1, 3) & " ms." & @CRLF)

;
; Data phase two
;
ConsoleWrite("Building database phase 2 (LiteX Automation)." & @CRLF)
$tBegin = TimerInit()
$oConnection.Execute("BEGIN TRANSACTION")
$oStmt = $oConnection.Prepare ("INSERT INTO Test(b,c) VALUES (:one,:two)")
For $i = 1 To $MAX_REC
    $oStmt.BindParameters (Random(9, 999, 1), Random(432, 555))
    $oStmt.Execute()
Next
$oStmt.Close ()
$oConnection.Execute("COMMIT TRANSACTION")
$tDiff2 = TimerDiff($tBegin)
ConsoleWrite("Phase complete. Time " & Round($tDiff2, 3) & " ms." & @CRLF)
ConsoleWrite(StringFormat("Phase two was about %.1f times faster than phase one." & @CRLF, $tDiff1 / $tDiff2))

;
; Query using UDF's
;
ConsoleWrite("Executing query using UDF's: " & $DB_QUERY & @CRLF)
Dim $hQuery, $aRow
_SQLite_Query($db, $DB_QUERY, $hQuery)
_SQLite_FetchData($hQuery, $aRow)
_SQLite_QueryFinalize($hQuery)

ConsoleWrite("Query result #1: " & $aRow[0] & @CRLF)
ConsoleWrite("Query result #2: " & $aRow[1] & @CRLF)
ConsoleWrite("Query result #3: " & $aRow[2] & @CRLF)

;
; Query using LiteX Automation
;
ConsoleWrite("Executing query using LiteX Automation: " & $DB_QUERY & @CRLF)
$oStmt = $oConnection.Prepare ($DB_QUERY)
$oStmt.Step ()

ConsoleWrite("Query result #1: " & $oStmt.ColumnValue (0) & @CRLF)
ConsoleWrite("Query result #2: " & $oStmt.ColumnValue (1) & @CRLF)
ConsoleWrite("Query result #3: " & $oStmt.ColumnValue (2) & @CRLF)

$oStmt.Close ()
$oStmt = 0

;
; Collections
;
ConsoleWrite("Iterating records using collections. " & @CRLF)
$oStmt = $oConnection.Prepare ("SELECT a,b,c FROM TEST ORDER BY c DESC LIMIT 3555,30")
For $oColumn In $oStmt.Columns
    ConsoleWrite( "Column #" & $oColumn.Index & @TAB & $oColumn.Value & @CRLF )
Next

$nCounter = 0
For $oRow In $oStmt.Rows (True, 20) ; try True or False as first parameter
    $nCounter += 1
    $sRow = "Record #" & $nCounter & @TAB
    For $oVal In $oRow ; second collection
        $sRow &= $oVal
        $sRow &= @TAB
    Next
    ConsoleWrite(StringTrimRight($sRow, 1) & @CRLF)
Next
$oStmt.Close ()
$oStmt = 0

;
; Shutdown
;
ConsoleWrite("Shutdown." & @CRLF)
_SQLite_Close($db)
_SQLite_Shutdown()
$oConnection.Close ()

;
; Clean Up
;
FileRecycle($DB_FILE)

;
; Bye
;
ConsoleWrite("Bye." & @CRLF)
Exit 0

Edited August 30, 2007 by Edek

[piccaso]

chalup thats because the udf's use 'sqlite3_prepare_v2' but you can manually edit the include file and downgrade from 'sqlite3_prepare_v2' to 'sqlite3_prepare'.

here is a diff from that update:

Index: SQLite.au3
===================================================================
--- SQLite.au3  (revision 21)
+++ SQLite.au3  (revision 24)
@@ -1,4 +1,4 @@
-; Include Version:1.75 (29.09.06)
+; Include Version:1.76 (12.03.07)
 #include-once
 
; ------------------------------------------------------------------------------
@@ -112,6 +112,7 @@
    25.09.06    Fixed Bug in szSring read procedure (_SQLite_GetTable*, _SQLite_QuerySingleRow, _SQLite_Escape)
    29.09.06    Faster szString Reading, Function Header corrections
    29.09.06    Changed _SQLite_Exec() Callback
+   12.03.07    Changed _SQLite_Query() to use 'sqlite3_prepare_v2' API
 #comments-end
 
 Global Const $SQLITE_OK = 0 ; /* Successful result */
@@ -675,7 +676,7 @@
; Return Value(s):  On Success - Returns $SQLITE_OK
;                  On Failure - Return Value can be compared against $SQLITE_* Constants
; @error Value(s):  -1 - SQLite Reported an Error (Check Return value)
-;                   1 - Error Calling SQLite API 'sqlite3_prepare'
+;                   1 - Error Calling SQLite API 'sqlite3_prepare_v2'
;                    2 - Call prevented by SafeMode
; Author(s):        piccaso (Fida Florian)
;
@@ -684,7 +685,7 @@
 Func _SQlite_Query($hDB, $sSQL, ByRef $hQuery)
    If Not __SQLite_hChk($hDB, $SQLITE_DBHANDLE) = $SQLITE_OK Then Return SetError(2, 0, $SQLITE_MISUSE)
    Local $iRval
-   $iRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_prepare", _
+   $iRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_prepare_v2", _
            "ptr", $hDB, _
            "str", $sSQL, _
            "int", StringLen($sSQL), _

edit: in other words, just remove '_v2' form the function parameter of this dllcall.

Edited August 29, 2007 by piccaso

[ptrex]

@chalup / Edek,

First of all mixing the SQLiteX and the SQLite.au3 together is looking for trouble !!

Best is to make a choise upfront between the two.

Even if it is possible, it is NOT recommended.

If you want the have the LiteX COM recompiled with the latest SQLite engine than it's best to send a mail to the author :

Author: Edmunt Pienkowsky.

E-Mail: roed AT net DOT eu

Regards,

ptrex

Edited December 18, 2007 by ptrex

[Edek]

Announcement.

New LiteX package was released. The main changes are in LiteX Automation library.

• Using 3.4.2 version of SQLite3 library.
• Connection object now can raise events Progress and Busy.
• Statement new collections: Columns, Rows and Parameters. Now it is possible to iterate through records using For Each statement.
• Completely reimplemented support for 64-bit integers. New LargeInteger helper class.

You can download new LiteX package here.

Update:

• Using 3.5.7 version of SQLite3 library.
• Moving homepage to assembla
• Sources now available at public SVN repository at http://svn2.assembla.com/svn/aIPblab5qr3zkdab7jnrAJ.

Edited April 25, 2008 by Edek

[ptrex]

@All

Updated the first post with an new SQLite GUI, to comply with the new AU3 syntax.

Regards,

ptrex

[Synapse]

Is there a way to get the number of results after a query was executed?

Example: If i execute a query like SELECT * FROM db-table WHERE col = 1...I would like to know before entrering the While loop how many elements from the db-table satisfy the given request

Thx in advanced

[ptrex]

@Synapse

Why would you want to know that ?

Do you expect a LOT of returns ?

In that case you can set a LIMIT statement in the query that just handles that issue.

Regards

ptrex

[Synapse]

@Synapse

Why would you want to know that ?

Do you expect a LOT of returns ?

In that case you can set a LIMIT statement in the query that just handles that issue.

Regards

ptrex

Hi ptrex...well i have a app that returns a lot of things...and if i would know the exact number of found items after a query was executed...i could create a progress bar...but in order to do that i need the number of return items so i can set the progress bars limit...btw thx for the reply..

[ptrex]

@Synapse

In order to do that you need to count the records BEFORE running the query.

This is how you could do this.

ConsoleWrite(_count() & @LF)
func _count()
    _SQlite_Query(-1, "select count(*) from aTest;" ,$hQuery)
    _SQLite_FetchData ($hQuery, $aRow)
    Return $aRow[0]
EndFunc

Then pass the output of this to your progress bar.

Then run the query.

regards

ptrex

[Synapse]

@Synapse

In order to do that you need to count the records BEFORE running the query.

This is how you could do this.

ConsoleWrite(_count() & @LF)
func _count()
    _SQlite_Query(-1, "select count(*) from aTest;" ,$hQuery)
    _SQLite_FetchData ($hQuery, $aRow)
    Return $aRow[0]
EndFunc

Then pass the output of this to your progress bar.

Then run the query.

regards

ptrex

Thx ptrex

[quake101]

Hello,

I think using sqlite is a great idea for simple database projects and I plan on using this on my first autoit script. I do have a few questions as I'm still new to autoit.

1) The litex website seems to be down. http://republika.pl/roed/litex/ anyone have the latest package for autoit v3.2.12.0 ?

2) What all do I need to make an sqlite database?

3) Does the current version of autoit still work with this?

[ptrex]

@quake101

I don't know what happened to LiteX and if it will come online again ?

It might be better to use the native SQLite support in AU3.

Open the help file a look for SQLite.

regards,

ptrex

[quake101]

@quake101

I don't know what happened to LiteX and if it will come online again ?

It might be better to use the native SQLite support in AU3.

Open the help file a look for SQLite.

regards,

ptrex

Oh wow, didn't notice that. Thanks! ;D

[dmob]

Hi, I am new with AutoIt and I like it very much. About the SQL UDF, I would like know how I can read the last record in the database without reading each record. I am using this code and I think there is a better way. pardon me if I am not using the right forum/format.

CODE

Local $TxList, $lNo

_SQlite_Query($DataBase, "SELECT * FROM Daily ORDER BY TxNo ;", $Query)

While _SQLite_FetchData($Query, $TxList) = $SQLITE_OK

$lNo = $TxList[0]

WEnd

_SQLite_QueryFinalize($Query)

Return($lNo)

[ptrex]

@dalisuit

You can select the last row ID like this :

opt("MustDeclareVars", 1)
#include <SQLite.au3>
Local $hQuery, $aRow, $sMsg,$d
$d = _SQLite_Startup ()
$d = _SQLite_Open () ; open :memory: Database
$d = _SQLite_Exec (Default, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
$d = _SQlite_Query (-1, "SELECT ROWID FROM aTest Order by ROWID DESC limit 1;", $hQuery) ; the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg &= $aRow[0]
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;") ; Remove the table
ConsoleWrite($sMsg & @CR)
_SQLite_Close()
_SQLite_Shutdown()

And then after this selec the row where ROWID is eqaul to this ROWID number.

regards

ptrex

[dmob]

Thanks, ptrex, works like a charm.

I have another question: I use three different queries on the same database. I kept getting an error msg (when I exit my script) that the database cannot close 'coz of unfinalised queries. I then finalised after every query and the error was no more.

1. Do I need to issue an _SQLite_QueryFinalize after each and every _SQLite_Query/ _SQLite_FetchData

call or can I finalise them when I exit the script?

2. Do I need to finalise each query individually, or one finalise does all queries?

3. If I reuse a query with different values do I need to finalise the previous query or just go ahead with new query?

Pardon me if I seem ignorant, but this my very first attempt at AutoIt and SQL, inspired by your UDF and a script I found in this forum.

All help will be appreciated much.

[ptrex]

@dalisuit

This is how you can do multiple QRS.

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $hQuery,$aRow,$iRows,$iCols,$aResult
_SQLite_Startup()
_SQLite_Open()
_SQLite_Exec(-1,"Create table tblTest (a int,b,c single not null);" & _
                "Insert into tblTest values (1,2,3);" & _
                "Insert into tblTest values (4,5,6);")

; Query 1
_SQLite_Query(-1,"Select RowID,a From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " a: " & $aRow[1] & @LF)
WEnd

; Query 2
_SQLite_Query(-1,"Select RowID,b From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " b: " & $aRow[1] & @LF)
WEnd

; Query 3
_SQLite_Query(-1,"Select RowID,c From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " c: " & $aRow[1] & @LF)
WEnd

regards

ptrex

[dmob]

My main question was about the query finalise.

[ptrex]

@dalisuit

Sorry for the late reply. I didn't see your last post untill today.

The _SQLite_QueryFinalize only needs to be called when you want to stop a previous called _SQLite_Query().

See help text :

_SQLite_QueryFinalize Finalizes an _SQLite_Query() based query. The query is interrupted.

Regards

ptrex

[dmob]

thanks, ptrex.

I had already decided to finalise after every query.

I have used your UDF successfully in this liitle application in this post

#535174.

you rock, man

Edited June 12, 2008 by dalisuit