#INCLUDE SQLite.ahk - Functions to access SQLite3 DB's

Hello,

I found an interesting script SQlite.au3 at http://www.autoitscript.com/forum/index.php?showtopic=17099 and thought it could be interesting for the AHK community too. So I tried a "translation" and now in my opinion the important functions are realized and raw tested. I'll try to use for some productive tool, but I'd want some others to test it too and give response for missing functions and/or optimizations.

To try out you need the SQLite3.exe and SQLite3.dll (3.3.7) from http://www.sqlite.org/download.html. Both are free and don't need any installation. Once there, you should have a look on http://www.sqlite.org/lang.html and
http://www.sqlite.org/sqlite.html. This Documentation is rather brief, but I found no other.

Now, what's the script for:

The script's functions give full access on SQLite3 databases. You even can create databases - if you really need - using the _SQLite_SQLiteExe function.

Queries (SELECT statements) are supported as follows:

_SQLite_GetTable returns the full result of a query including column names in a string with column values/names separated by "|" and rows seperated by "`n".

_SQLite_Query prepares a query and creates a query handle. To get column names, you have to call _SQLite_Fetchnames, to get the values of first/next row, you have to call _SQLite_FetchData. The results are passed out in a string with "|" separated values.

All non query SQL-statements (INSERT, UPDATE, DELETE...) are executed via _SQLite_Exec.

To make testing a little bit easier I added the script SQLite_sample.ahk. It creates a small database, tries some functions on it and displays the result of a SELECT * FROM ... in a ListView.

Changelog:

2006-09-23

New global $SQLITE_s_ERROR for functions error messages instead of "ByRef $sErr" parameter in function calls.

New optional parameter $iMaxResult in _SQLite_GetTable to restrict the amount of returned rows.

SQLite_sample.ahk adapted.

2006-11-15

Adjusted code in _SQLite_GetTable to run under AHK 1.0.45.
Maybe some more changes will be needed!

SQLite.ahk - 2006-11-15

Please download the script from http://www.autohotkey.net/~dwuttke/SQLite/SQLite.ahk



Here's some sample code to show how to use the functions:

;===============================================================================
; Script Function:  Sample script for SQLite.ahk
; AHK Version:      1.0.44.13
; Language:         English
; Platform:         WinXP
; Author:           nick
; Version:          0.00.01/2006-09-23/nick
;===============================================================================
; Autoexec Section
;===============================================================================
; AHK Settings
;===============================================================================
#NoEnv
#SingleInstance force
#Persistent
SetWorkingDir %A_ScriptDir%
SetBatchLines -1
SetWinDelay -1
;===============================================================================
; Includes
;===============================================================================
#Include %A_ScriptDir%
#Include SQLite.ahk
;===============================================================================
; Variables
;===============================================================================
$iRC := 0
$sErr := ""
;===============================================================================
;   Use SQLITE3.EXE - Create a Database and Table
;===============================================================================
$sDBFileName := "TEST.DB"
$sCSVFileName := "TEST.CSV"
$sDBFileName := "TEST.DB"
$sCSVFileName := "TEST.CSV"
$sInput =
(Ltrim
   CREATE TABLE Test (Name, Fname, Phone, Room);
   .separator \t
   .import '%$sCSVFileName%' Test
)
$sOutput := ""
IfExist %$sDBFileName%
{
   FileDelete %$sDBFileName%
}
IfExist %$sCSVFileName%
{
   FileDelete %$sCSVFileName%
}
Loop 99
{
   FileAppend,
   (LTrim Join
      Name%A_Index%%A_Tab%Fname%A_Index%%A_Tab%
      Phone%A_Index%%A_Tab%Room%A_Index%`n
   ),%$sCSVFileName%
}
$iRC := _SQLite_SQLiteExe($sDBFileName, $sInput, $sOutput)
If ($iRC <> 0)
{
   MsgBox Fehler bei SQLITEEXE: %$iRC%`n%$SQLITE_s_ERROR%`n%$sOutput%
   ExitApp
}
;===============================================================================
;   Use SQLITE3.DLL - Query the Database
;===============================================================================
$iCols := 0
$iRows := 0
$sRC := ""
$sResult := ""
$iRC := _SQLite_Startup()
If ($iRC <> 0)
{
   MsgBox Fehler bei STARTUP: %$iRC%
}
$sRC := _SQLite_LibVersion()
If ($SQLITE_s_VERSION <> $sRC)
{
   MsgBox,
   (LTrim
      SQLite Scriptversion = %$SQLITE_s_VERSION%
      SQLite DLL version   = %$sRC%
   )
}
$iRC := _SQLite_OpenDB($sDBFileName)
If ($iRC <> 0)
{
   MsgBox Fehler bei OPENDB: %$iRC%
}
$sSQL := "INSERT INTO Test VALUES('Name111', 'Fname111', 'Phone111', 'Room111');"
$iRC := _SQLite_Exec($SQLITE_h_DB, $sSQL)
If ($iRC <> 0)
{
   MsgBox Fehler bei EXEC: %$iRC%`n%$SQLITE_s_ERROR%
}
$iRC := _SQLite_LastInsertRowID(-1, $iRows)
If ($iRC <> 0)
{
   MsgBox Fehler bei EXEC: %$iRC%`n%$SQLITE_s_ERROR%
}
Msgbox Last inserted Row = %$iRows%
$sSQL := "SELECT * FROM Test;"
$i := A_TickCount
$iRC := _SQLite_GetTable($SQLITE_h_DB, $sSQL, $sResult, $iRows, $iCols)
If ($iRC <> 0)
{
   MsgBox Fehler bei GETTABLE: %$iRC%
}
MsgBox % A_TickCount - $i
Msgbox Zeilen : %$iRows%, Spalten : %$iCols%
/*
$iRC := _SQLite_Query($SQLITE_h_DB, $sSQL)
If ($iRC <> 0)
{
   MsgBox Fehler bei QUERY: %$iRC%
}
$iRC := _SQLite_FetchNames(-1, $sResult)
If ($iRC <> 0)
{
   MsgBox Fehler bei FETCHNAMES: %$iRC%
}
$sResult = %$sResult%`n
MsgBox %$sResult%
$iRC := _SQLite_FetchData(-1, $sResult)
If ($iRC <> 0)
{
   MsgBox Fehler bei FETCHNAMES: %$iRC%
}
MsgBox %$sResult%
$iRC := _SQLite_QueryFinalize(-1)
If ($iRC <> 0)
{
   MsgBox Fehler bei FINALIZE: %$iRC%
}
*/
; Show the Result in Listview
Gosub SHOW_LISTVIEW
;  Wait for GUiClose
WinWait, SQLite Result - Close with ESC, , 20
IfWinExist, SQLite Result - Close with ESC
{
   WinWaitClose, SQLite Result - Close with ESC
}
Sleep 100
; Now GUI is closed
$iRC := _SQLite_CloseDB(-1)
If ($iRC <> 0)
{
   MsgBox Fehler bei CLOSEDB: %$iRC%
}
$iRC := _SQLite_ShutDown()
If ($iRC <> 0)
{
   MsgBox Fehler bei SHUTDOWN: %$iRC%
}
ExitApp
;===============================================================================
;   Gui Subs
;===============================================================================
SHOW_LISTVIEW:
$LV =
$LVCOUNT := $iRows + 1
Loop, Parse, $sResult, `n
{
   If (A_Index = 1)
   {
      Gui, Add, ListView, r20 w420 Grid Count%$LVCOUNT%, %A_LoopField%
   }
   Else
   {
      StringSplit, $LV, A_LoopField, |
      LV_ADD("", $LV1, $LV2, $LV3, $LV4)
   }
}
LV_ModifyCol(1, 100)
LV_ModifyCol(2, 100)
LV_ModifyCol(3, 100)
LV_ModifyCol(4, 100)
Gui, Show, Autosize Center, SQLite Result - Close with ESC
Return
;===============================================================================
GuiClose:
GuiEscape:
Gui, Destroy
Return