SQLite semi Embedded database functionality in AutoIt

[ptrex]

I see that a huge number of threads and post are available relating to the use and interface of SQLite

I did some digging into the forum via the search, but never came across a user friendly solution.

The only solution so far was via DllCall.

Most important ones

http://www.autoitscript.com/forum/index.ph...ic=9296&hl=

http://www.autoitscript.com/forum/index.ph...amp;#entry61614

http://www.autoitscript.com/forum/index.ph...c=14886&hl=

Before I post my solution, I want to give some background information, to those of you who don t know SQLite.

SQLite is a freeware opensource application which has most of the features of a commercial database environment

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine

Features include:

Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.

Zero-configuration - no setup or administration needed.

Implements most of SQL92. (Features not supported)

A complete database is stored in a single disk file.

Database files can be freely shared between machines with different byte orders.

Supports databases up to 2 terabytes (241 bytes) in size.

Sizes of strings and BLOBs limited only by available memory.

Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted.

Faster than popular client/server database engines for most common operations.

Simple, easy to use API.

TCL bindings included. Bindings for many other languages available separately.

Well-commented source code with over 95% test coverage.

Self-contained: no external dependencies.

Sources are in the public domain. Use for any purpose.

That s why I think SQLite is the perfect marriage for AutoIT.

Several Wrappers are available for different languages as well as ODBC drivers.

SQLite has been integrated in several languages and platforms already like PHP, ASP, APACHE, PERL, Python, .NET, Windows CE,

Nevertheless I still feel that SQLite should become an integral part of AutoIT.

An Embedded Database functionality would boost the power of a Script tool like AutoIT !!

I hope the Developers agree with this. Lets all hope that this will come in the near future.

A good way to get started with SQLite is visiting these sites :

http://www.sqlite.org/index.html & http://www.sqlite.org/cvstrac/wiki?p=Sqlit...&1054881949

http://www.fredshack.com/docs/sqlite.html

http://www.newobjects.com/ or Google for SQLite

Read my next post for the solution on how to use SQLite in AutoIT.

This is how Native SQLite looks like in AutoIT

Included export to Excel from ListView, see thumbnail pictures. The code has been updated in the second post

Nwind.db in SQLite format to use as a test database. Removed

SQLite_Gui.au3 New version update 19/05/06 to run latest Beta syntax till 3.2.8.x.

SQLite_Gui_New.au3 New version update 18/12/07 to run latest Beta syntax 3.2.10.x and later.

TO DO List :

SQLite_GUI (uses LiteX ActiveX Component, see post #2 for more information)

Bugs

- Excel Object Jumps when windows resizes (and clicking in the Excel object) the 1st time.

(Work around found see post #165)

Fixes

- Store SQL,VIEWS, TRIGGERS and Functions in a SYSTEM Table. Should be all in 1 table z_SQL_Log.

Add field TYPE to filter.

- The "FileOpenDialog" dialog should be able to select other formats like .DB3 or *.*

- Datatype VARCHAR (size) does not work yet

Features to Add

- Option to use the ListView instead of Excel Com should be added

- Add TABLE for storing TEMPLATE qry STATEMENTS.

- Add SQLite ANALYZE function

- Activate rename field name feature

- Include the ATTACH DB function

- Include the ROLLBACK (=undo) feature

- Create a Sub GUI for working with FUNCTIONS and ExpressionS in SQL

- Assign Function key F9 to execute a Query & ctrl+Tab for tab swithing

- Load Database in memory to increase speed and performance

TO DO's SQLite UDF :

SQLite.au3 UDF (uses DLL call to access native SQLite)

Bugs

- DLL Call _SQLite_Get_Tables() give errors in line 432 when running.

SQLite.Zip Latest version

SQLite.au3 & downloads are found in there (for people that dont want to read all posts...)

SQLite Downloads

Edited December 18, 2007 by ptrex

[ptrex]

Update LiteX to SQLite version 3.5.7

My solution uses the LiteX COM wrapper of http://www.assembla.com/wiki/show/litex

Download the zip file extract and copy the DLL's to your c:\windows\system32 dir.

Use the command "regsvr32 sqlite3.dll". or REregister after an upgrade

I choose this COM Wrapper, because it has nice VB Script examples, which are easy to port to AutoIT (But there are other wrappers available see my previous post).

Together with the COM ability (provided by SvenP) in all the Beta versions of AutoIT, you can have a semi embedded database functionality in AutoIT.

I deliberately used a lot of VB Script commands in the AU3 script. This way you can find back the relation to the original VB Script (available in the downloaded zip file)

This is the code

expandcollapse popup

;-------------------------------------------------------------
; PTREX dd 20/10/2005
;
; SQLite3 database Integration in AutoIt
; Creates a DB creates a Table and fields using SQL statements
; Reads the data into the ListView
; Exports the data to into .TXT file
;
; IMPORTANT : 
; Make sure you have the SQLite3.DLL installed and registered
; Sqlite homepage http://www.assembla.com/wiki/show/litex
; Sqlite example using LiteX ActiveX wrapper
;
;------------------------------------------------------------

; Includes
#include <GuiConstants.au3>
#include <Array.au3>
#include <GuiListView.au3>
#include <GuiTab.au3>
#NoTrayIcon

; Declare Vars
Dim $Info, $Help
Dim $oMyError 
Dim $HexNumber
Dim $WshShell 
Dim $Fso, $MyExcel
Dim $oDb
Dim $sDb
Dim $sFile
Dim $f
Dim $oStmt
Dim $Path
Dim $Dbfile
Dim $Txtfile
Dim $Count, $Combo1Item
Dim $Param
Dim $Array

; Initialize SvenP 's  error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; Declare Objects
$WshShell =  ObjCreate( "WScript.Shell" )
$Fso = ObjCreate( "Scripting.FileSystemObject" )
$oDb = ObjCreate( "LiteX.LiteConnection" )
$oExcel = ObjCreate("OWC10.Spreadsheet")
If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003    
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC9.spreadsheet"); Office 2000
EndIf

MsgBox(0,"Feedback", "Hello from SQLite version" & $oDb.Version & "!")

$Path = $Fso.GetSpecialFolder(2).Path;Fso.GetSpecialFolder, 2 is Tempfolder
$Dbfile = "sqlite3.db"
$Txtfile ="Table.txt"

; Check if file exist else delete Db file
If FileExists($Path&"\"&$Dbfile) Then
    $Fso.DeleteFile($Path&"\"&$Dbfile)
;FileDelete($Path&"\"&$Dbfile)
endif

; Check if file exist else delete Db file
If FileExists($Path&"\"&$Dbfile) Then
$Fso.DeleteFile($Path&"\"&$Dbfile)
endif

; Main GUI
; ---------
$Gui = GuiCreate("SQLite Native Database Integration v1.2", 975, 571,(@DesktopWidth-797)/2, (@DesktopHeight-571)/2 , _ 
$WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

$Helpmenu = GUICtrlCreateMenu ("?")
$Helpitem = GUICtrlCreateMenuitem ("Help",$Helpmenu)
$Infoitem = GUICtrlCreateMenuitem ("Info",$Helpmenu)

$Tab=GUICtrlCreateTab (8,30, 962,520)
GUICtrlSetResizing ($Tab,$GUI_DOCKAUTO)

; Tab1
$Tab1=GUICtrlCreateTabitem ("View Data")
$ListView = GUICtrlCreateListView("Icon| Col a| Col b|Col c| Col c| Col e| Col f| Col f| Col g| Col h| Col i| Col j| Col h| Col l| Col m" _
, 10, 60, 957, 483,-1,$LVS_EX_HEADERDRAGDROP+$LVS_EX_FULLROWSELECT);Drag&Drop Columns
GUICtrlSetResizing ($Listview,$GUI_DOCKAUTO)
GUICtrlSetState($ListView,$GUI_FOCUS)
GUICtrlSetImage ($ListView, "mmcndmgr.dll",96)                   ;Set Icons for records
;_GUICtrlListViewSetColumnWidth ($listview, 0,90)                 ;Set Column with

$Tab1combo1=GUICtrlCreateCombo ("# Records", 10,6,60,40)
GUICtrlSetData(-1,"100|150|300|500|1000|2000|3000|4000", "100")     ;Set default 100
GUICtrlCreateLabel("Select # of Records to display",80,10)

$CreateDB = GuiCtrlCreateButton("Create DB", 350, 6, 90, 30)
GUICtrlSetTip($CreateDB,"Creates a DB, Talbles and inserts Values")

$Readdata = GuiCtrlCreateButton("Read Data", 450, 6, 90, 30)
GUICtrlSetTip($Readdata,"Read the data from the database into the ListView")

$Export_xls = GuiCtrlCreateButton("Export to XLS", 752, 6, 90, 30)
GUICtrlSetTip($Export_xls,"Export the data to a XLS file")

$Export_txt = GuiCtrlCreateButton("Export to TXT", 852, 6, 90, 30)
GUICtrlSetTip($Export_txt,"Export the data to a Text file")

;TAb2
; Tab1
$Tab1=GUICtrlCreateTabitem (" Excel ")
$GUI_ActiveX = GUICtrlCreateObj ($oExcel, 10, 60 , 955 , 480)
GUICtrlSetResizing (-1,$GUI_DOCKAUTO)
GUICtrlSetStyle ($GUI_ActiveX, $WS_VISIBLE )                    ;Show XLS Object on tab2



; GUI handling
; ------------
GuiSetState()

Dim $B_DESCENDING[_GUICtrlListViewGetSubItemsCount ($listview)];Used by Sort Click Header

While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    Case $msg = $Tab1combo1
        GUICtrlRead($Tab1combo1)
        $Combo1Item=Number(GUICtrlRead($Tab1combo1)-1)
        MsgBox(0,"# of Records","The # of records are : "&$Combo1Item+1)
    Case $msg = $CreateDB
         CreateDb()
    Case $msg = $Readdata
         _LockAndWait()
         Getdata()
         _ResetLockWait()
    Case $msg = $Export_txt
        Export2Txt()
    Case $msg = $Export_xls
        Export2Xls()
    Case $msg = $Listview                                         ;Sort the list by the column header clicking
        _GUICtrlListViewSort($listview, $B_DESCENDING, GUICtrlGetState($Listview))
    Case $msg = $infoitem
        Info()
        Msgbox(0,"Info",$Info)
    Case $msg = $Helpitem
        Help()
        Msgbox(0,"Help",$Help)
    EndSelect
WEnd

If FileExists($Path&"\"&$Dbfile) Then
;_GUICtrlListViewDeleteAllItems ($listview)
$oStmt.Close();
$oDb.Close();    
$Fso.DeleteFile($Path&"\"&$Dbfile)
endif
Sleep(500);
If FileExists($Path&"\"&$Txtfile) Then
$Fso.DeleteFile($Path&"\"&$Txtfile)
EndIf

MsgBox(0,"Finish","Test completed." & @CRLF &  @CRLF &"Did you notice the speed of all of this !!" )

$WshShell = ""
$Fso = ""
$oDb = ""
$oXLDummy=""
Exit

; Functions
; ----------
; Create DB, Tables and values
Func CreateDb() 
If FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database was already created",3)
Else
    $sDb = $Fso.BuildPath( $Fso.GetSpecialFolder(2).Path, "sqlite3.db"); Create the DB file
    $oDb.Open($sDb);Open the DB file

; Create table using the powerfull SQLite syntax
    $oDb.Execute( "CREATE TABLE Test( a INTEGER PRIMARY KEY, b TEXT COLLATE unaccented, c INTEGER, d FLOAT )" )
    $oDb.Execute( "CREATE INDEX b ON test(b ASC)" )
    $oDb.Execute( "BEGIN TRANSACTION" )
; Insert test values in the DB
    $Combo1Item=Number(GUICtrlRead($Tab1combo1)-1)
For $i=0 To $Combo1Item
    $oDb.Execute( "INSERT INTO test( b, c, d ) VALUES (?,?,:three)","Data:"&$i, Random(-10, 10, 1), random(1, 10) )
Next
$oDb.Execute( "COMMIT TRANSACTION" )
Endif
    MsgBox(0,"Feedback","Database & Tables created, Data inserted, "&@LF&@LF& "Realy Fast, Don' t you think !!",4)
EndFunc

; Read data from Database into Listview
Func Getdata()
If not FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database has not been created yet !!",3) 
Else
    GUISetState(@SW_LOCK)
_GUICtrlListViewDeleteAllItems ($listview)
; Read lines of the database by using SQL & Notice : You can use the Column indexes or names as reference
$oStmt = $oDb.Prepare( "SELECT a , unaccent(b), c , d FROM test ORDER BY a" )
; $Param = $oStmt.ParameterCount()
while $oStmt.step() = 0
    $Data = ("|"&$oStmt.ColumnValue(0) &"|"& $oStmt.ColumnValue(1) &"|"& $oStmt.ColumnValue(2 )&"|"& $oStmt.ColumnValue(3))
    $dataCol=GUICtrlCreateListViewItem($data,$listview)
WEnd
$oStmt.Close();
EndIf
GUISetState(@SW_UNLOCK)
EndFunc

Func Export2Xls()                                                    ;Read from ListView
        GUISetState(@SW_LOCK)
        Local $a_list, $x
            For $x = 0 To _GUICtrlListViewGetItemCount($listview) - 1
                If IsArray($a_list) Then
                    ReDim $a_list[UBound($a_list) + 1]
                Else
                    Dim $a_list[1]
                EndIf
                $a_list[UBound($a_list) - 1] = _GUICtrlListViewGetItemText ($listview, $x)
            Next
            
            For $x = 0 To UBound($a_list) -1
                $string = StringSplit($a_list[$x], "|", 0)
              ;MsgBox(0,"a_list[" & $x+1 & "]",$a_list[$x])
                For $a=0 To _GUICtrlListViewGetSubItemsCount($listview)-1                    
                        If IsObj($oExcel) Then
                        With $oExcel
                        .cells($x+1,$a+1).value = $string[$a]
                        EndWith
                    Else
                        MsgBox(0,"Reply","Not an Object",4)
                    EndIf
                Next
            Next
        GUISetState(@SW_UNLOCK)
EndFunc

; Export Data to .TXT file
Func Export2Txt()
If not FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database has not been created yet !!",3) 
Else
 WriteFile()
EndIf
EndFunc

Func Writefile()
; Show the test data from the DB in a txt file.
 If FileExists($Path&"\"&$Txtfile) Then
 $Fso.DeleteFile($Path&"\"&$Txtfile)
EndIf 
$sFile = $Fso.BuildPath( $Fso.GetSpecialFolder(2).Path, $Txtfile )
$f = $Fso.CreateTextFile( $sFile, 1, 1 )

; Write the first 2 lines of the file
$f.WriteLine("Hello from LiteX Example in AutoIT " & @CRLF & $oDb.Path & " !" & @CRLF)

; Write the next lines of the file by fetching the data using SQL & Notice : You can use the Column indexes or names as reference
$oStmt = $oDb.Prepare( "SELECT a , unaccent(b), c , d FROM test ORDER BY c, b DESC" )
; $Param = $oStmt.ParameterCount()
while $oStmt.step() = 0
    $f.WriteLine($oStmt.ColumnValue("a") &@TAB& $oStmt.ColumnValue(1) & @TAB & @TAB & $oStmt.ColumnValue("c") & @TAB & $oStmt.ColumnValue(3))
    $Count = $oStmt.ColumnCount()
WEnd
    $f.WriteLine(@CRLF & "The number of Columns in the database are : " & $Count )
$f.Close();    
$WshShell.Run( $sFile )
Sleep(1000);
$Fso.DeleteFile( $sFile );
EndFunc

Func _LockAndWait()
    Local $Cursor_WAIT
    GUISetState(@SW_LOCK)
    GUISetCursor($Cursor_WAIT, 1)
EndFunc

Func _ResetLockWait()
    local $Cursor_ARROW
    GUISetState(@SW_UNLOCK)
    GUISetCursor($Cursor_ARROW, 1)
EndFunc

Func _Dot2Comma($number)
    Select
    Case StringInStr ($number, ".")
        $number = StringReplace ($number, ".", ",")
        Return $number
    Case StringInStr ($number, ",")
        $number = StringReplace ($number, ",", ".")
        Return $number
    EndSelect
EndFunc


; Menu functions
Func Info()
    $Info = "Sytem Requirements :"&@CR&@CR& _
            "- In order to use this application you need to have the Sqlite3.dll"&@CR&@CR& _
            "- Copy the DLL to the Windows\System32 Directory."&@CR& _
            "  Register the DLL using the Command REGSVR32 'c:\Windows\system32\sqlite3.dll'."&@CR& _
            "  Download is available at http://republika.pl/roed/litex/, Good luck !!"
EndFunc

Func Help()
    $Help = "  Is on the way, " &@CR&@CR& _
            "  See Info on http://www.sqlite.org/index.html"
EndFunc

; This is SvenP's custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1); To check for after this function returns
Endfunc

I hope you all appreciate my proclamation for getting SQLite integrated/Embedded in AutoIT.

And I hope a lot of you share the same idea, so lets hear you votes.

This example now shows an integrated EXCEL object, which can be used to export data form the LV to EXCEL

Give it a try.

Edited October 18, 2008 by ptrex

[flyingboz]

I don't believe that we need the overhead of a database engine as part of a standard au3 install / compile -

if you need it, use a COM or DLL capable wrapper.

I do think , however, that these things would fit well in the proposed plugin schema.

[ptrex]

@flyingboz

Thanks for your feedback.

I understand your approach that integrating it in the distribution would be a little too much.

But did you look at it from all the perspectives ?

How can a less than 200 K large dll, be of any overhead.

It contains +/- 15 additional functions.

By the way did bother to have a look at thoose ? http://republika.pl/roed/litex/

This would expose all the native SQL, features from within the AutoIt.

Reasons to include it are:

When the SQL(ite) is natively included it would not have to do any compromises on SPEED, when running Queries. Which is always the case with COM and ODBC.

Less coding is needed in the script, No COM or ODBC connection settings have to be specified in the script.

This means less CreateObj statements and variables to reach COM objects.

More readable and less complex to write any scripts for SQL database connectivity.

etc ...

From a technical and user friendly point of view, there are a lot of pro's, to go for a native support of SQLite.

But I will leave it to the developers and the database users, to vote for, or against the native SQL(ite).

As you indicated, including this through the addin or plugin, might be a good compromise.

Anyhow I can't estimate what the effects are on speed, and other technical consequenses.

The only thing which is clear is that a lot of users would appreciate having SQL and Database support available.

Let's hear your votes !!

[steve8tch]

I know nothing of databases - I would like to hear the views of others who do use databases of different sorts. What I do know is this - Any new scripting done for Windows is done with AutoIT. The need to pull in other scripting languages - like Kix - is getting less and less. Its reliability is more or less proven (I have AutoIt scripts working with production machinery 24hrs a day) - The next area I need to look into is updating loads of smallish Access dbs - for some reason they are trying to move off Access (and onto some supported freeware !!!)- so at some point some alternatives need to be suggested - if a db was supported from within AutoIt then certainly I would want to look at it.

[ptrex]

@steve8tch

Thanks for the feedback.

Steve did you try my example.

It does not need any installation files what so ever (execpt the 1 .dll to register see post #2)

It is lightning fast and very stable.

Please try and evaluated yourself, I can help if problem arise while testing.

To get you get starting right away I will add the DLL in this post.

Let us know how you feel about this after the test.

Here is the DLL :

[steve8tch]

Thankyou for the reply.

I have downloaded the dll and will try and look at it soon. Work is prety hectic just now - lots of NT4 boxes controlling production equipment - No vendor support and NT4 is "Persona Non Grata". - life in IT...

[memnon]

i think it would be really a good idea to have database functions integrated in autoit.

[Lazycat]

Thanks for that, this is the good solution. But I still don't think that it's good idea integrate whole thing in the Autoit - compiling SQLite sources give about 140 kb overhead (this is compressed size, whole Autoit now is close to the same size...) for functions, that will use only small part of users.

This way (via COM) it works just fine. Who need it can easily download and install this wrapper. But in terms of speed maybe plugin will be also good solution.

[ptrex]

@memnon

Thanks for sharing you ideas.

Did you try my example ?

It is as simple as it can be. Download the DLL posting in here.

Use the Code and instructions in Post #2.

No Risks involved !!

The database and Reports are deleted when the script is finished.

Run it and lat us know your experience !!

[ptrex]

@Lazycat

Thanks for you reply as well.

I' ve seen that you were involved in previous SQLite Threads before as well. So your opion realy counts.

I see that it will double the size of the (compressed) AutIT application.

But still this would bring it to 116 Kb + 186 Kb = 302 Kb.

Still reasonable small size, considering the extra benifits it brings.

Than again incorporating existing code into the AutoIT code does not bring a lot of development overhead with it, does it ?

But I am not a developer, so your opion might be more relevant than mine.

Anyhow if we would take the route of using it as a plug in.

Are there any developers around who are willing to bring this to live ?

[memnon]

as i got the time to do it, i will do it, for shure @ptrex

[ptrex]

Hello All,

I' ve made a quick and dirty GUI frontend to the Native SQLite database Example.

This is how it looks now :

This is the code for it :

expandcollapse popup

;-------------------------------------------------------------
; PTREX dd 20/10/2005
;
; SQLite3 database Integration in AutoIt
; Creates a DB creates a Table and fields using SQL statements
; Reads the data into the ListView
; Exports the data to into .TXT file
;
; IMPORTANT : 
; Make sure you have the SQLite3.DLL installed and registered
; Sqlite homepage http://www.sqlite.org/index.html
; Sqlite example using LiteX ActiveX wrapper
;
;------------------------------------------------------------

; Includes
#include <GuiConstants.au3>
#include <Array.au3>
#include <GuiListView.au3>
#include <GuiTab.au3>
#NoTrayIcon

; Declare Vars
Dim $Info, $Help
Dim $oMyError
Dim $HexNumber
Dim $WshShell 
Dim $Fso
Dim $oDb
Dim $sDb
Dim $sFile
Dim $f
Dim $oStmt
Dim $Path
Dim $Dbfile
Dim $Txtfile
Dim $Count
Dim $Param

; Initialize SvenP 's  error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; Declare Objects
$WshShell =  ObjCreate( "WScript.Shell" )
$Fso = ObjCreate( "Scripting.FileSystemObject" )
$oDb = ObjCreate( "LiteX.LiteConnection" )
MsgBox(0,"Feedback", "Hello from SQLite version" & $oDb.Version & "!")

$Path = $Fso.GetSpecialFolder(2).Path;Fso.GetSpecialFolder, 2 is Tempfolder
$Dbfile = "sqlite3.db"
$Txtfile ="Table.txt"

; Check if file exist else delete Db file
If FileExists($Path&"\"&$Dbfile) Then
$Fso.DeleteFile($Path&"\"&$Dbfile)
endif

; Main GUI
; ---------
$Gui = GuiCreate("SQLite Database Integration v1.0", 975, 571,(@DesktopWidth-797)/2, (@DesktopHeight-571)/2 , _ 
$WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

$Helpmenu = GUICtrlCreateMenu ("?")
$Helpitem = GUICtrlCreateMenuitem ("Help",$Helpmenu)
$Infoitem = GUICtrlCreateMenuitem ("Info",$Helpmenu)

$Tab=GUICtrlCreateTab (8,30, 962,520)
GUICtrlSetResizing ($Tab,$GUI_DOCKAUTO)

; Tab1
$Tab1=GUICtrlCreateTabitem ("View Data")
$ListView = GUICtrlCreateListView("Col a| Col b|Col c| Col c| Col e| Col f| Col f| Col g| Col h| Col i| Col j| Col h| Col l| Col m" _
, 10, 60, 957, 483,-1,$LVS_EX_HEADERDRAGDROP+$LVS_EX_FULLROWSELECT);Drag&Drop Columns
GUICtrlSetResizing ($Listview,$GUI_DOCKAUTO)
GUICtrlSetState($ListView,$GUI_FOCUS)
GUICtrlSetImage ($ListView, "mmcndmgr.dll",96)                  ;Set Icons for records
_GUICtrlListViewSetColumnWidth ($listview, 0,90)                ;Set Column with

$Tab1combo1=GUICtrlCreateCombo ("# Records", 10,6,60,40)
GUICtrlSetData(-1,"100|150|300|500|1000|2000|3000|4000", "100")     ;Set default 100
GUICtrlCreateLabel("Select # of Records to display",80,10)

$CreateDB = GuiCtrlCreateButton("Create DB", 650, 6, 90, 30)
GUICtrlSetTip($CreateDB,"Creates a DB, Talbles and inserts Values")

$Readdata = GuiCtrlCreateButton("Read Data", 750, 6, 90, 30)
GUICtrlSetTip($Readdata,"Read the data from the database into the ListView")

$Export_txt = GuiCtrlCreateButton("Export to TXT", 852, 6, 90, 30)
GUICtrlSetTip($Export_txt,"Export the data to a Text file")

    
; GUI handling
; ------------
GuiSetState()

Dim $B_DESCENDING[_GUICtrlListViewGetSubItemsCount ($listview)] ;Used by Sort Click Header

While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    Case $msg = $CreateDB
         CreateDb()
         Case $msg = $Readdata
         Getdata()
    Case $msg = $Export_txt
        Export2Txt()     
    Case $msg = $Listview                                       ;Sort the list by the column header clicking
        _GUICtrlListViewSort($listview, $B_DESCENDING, GUICtrlGetState($Listview))
    Case $msg = $infoitem
        Info()
        Msgbox(0,"Info",$Info)
    Case $msg = $Helpitem
        Help()
        Msgbox(0,"Help",$Help)
    EndSelect
WEnd

If FileExists($Path&"\"&$Dbfile) Then
_GUICtrlListViewDeleteAllItems ($listview)
$oStmt.Close();
$oDb.Close();   
$Fso.DeleteFile($Path&"\"&$Dbfile)
endif
Sleep(1000);
If FileExists($Path&"\"&$Txtfile) Then
$Fso.DeleteFile($Path&"\"&$Txtfile)
EndIf

MsgBox(0,"Finish","Test completed." & @CRLF &  @CRLF &"Did you notice the speed of all of this !!" )

$WshShell = ""
$Fso = ""
$oDb = ""

Exit

; Functions
; ----------
; Create DB, Tables and values
Func CreateDb () 
If FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database was already created",3)
Else
    $sDb = $Fso.BuildPath( $Fso.GetSpecialFolder(2).Path, "sqlite3.db"); Create the DB file
    $oDb.Open($sDb);Open the DB file

; Create table using the powerfull SQLite syntax
    $oDb.Execute( "CREATE TABLE test( a INTEGER PRIMARY KEY, b TEXT COLLATE unaccented, c INTEGER, d FLOAT )" )
    $oDb.Execute( "CREATE INDEX b ON test(b ASC)" )
    $oDb.Execute( "BEGIN TRANSACTION" )
; Insert test values in the DB
for $i=0 To 99
    $oDb.Execute( "INSERT INTO test( b, c, d ) VALUES (?,?,:three)","Data:"&$i, Random(-10, 10, 1), random(1, 10) )
Next
$oDb.Execute( "COMMIT TRANSACTION" )
Endif
    MsgBox(0,"Feedback","Database & Tables created, Data inserted, "&@LF&@LF& "Realy Fast, Don' t you think !!",4)
EndFunc

; Read data from Database into Listview
Func Getdata()
If not FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database has not been created yet !!",3) 
Else
; Read lines of the database by using SQL & Notice : You can use the Column indexes or names as reference
$oStmt = $oDb.Prepare( "SELECT a , unaccent(b), c , d FROM test ORDER BY a" )
; $Param = $oStmt.ParameterCount()
while $oStmt.step() = 0
    $Data = ("|"&$oStmt.ColumnValue(0) &"|"& $oStmt.ColumnValue(1) &"|"& $oStmt.ColumnValue(2 )&"|"& $oStmt.ColumnValue(3))
    $dataCol=GUICtrlCreateListViewItem($data,$listview)
WEnd
$oStmt.Close();
EndIf
EndFunc

; Export Data to .TXT file
Func Export2Txt()
If not FileExists($Path&"\"&$Dbfile) then
    MsgBox(0,"Feedback","Database has not been created yet !!",3) 
Else
 WriteFile()
EndIf
EndFunc

Func Writefile()
; Show the test data from the DB in a txt file.
 If FileExists($Path&"\"&$Txtfile) Then
 $Fso.DeleteFile($Path&"\"&$Txtfile)
EndIf 
$sFile = $Fso.BuildPath( $Fso.GetSpecialFolder(2).Path, $Txtfile )
$f = $Fso.CreateTextFile( $sFile, 1, 1 )

; Write the first 2 lines of the file
$f.WriteLine("Hello from LiteX Example in AutoIT " & @CRLF & $oDb.Path & " !" & @CRLF)

; Write the next lines of the file by fetching the data using SQL & Notice : You can use the Column indexes or names as reference
$oStmt = $oDb.Prepare( "SELECT a , unaccent(b), c , d FROM test ORDER BY c, b DESC" )
; $Param = $oStmt.ParameterCount()
while $oStmt.step() = 0
    $f.WriteLine($oStmt.ColumnValue("a") &@TAB& $oStmt.ColumnValue(1) &@TAB & @TAB& $oStmt.ColumnValue("c") &@TAB& $oStmt.ColumnValue(3))
    $Count = $oStmt.ColumnCount()
WEnd
    $f.WriteLine(@CRLF & "The number of Columns in the database are : " & $Count )
$f.Close(); 
$WshShell.Run( $sFile )
Sleep(1000);
$Fso.DeleteFile( $sFile );
EndFunc

; Menu functions
Func Info()
    $Info = "Sytem Requirements :"&@CR&@CR& _
            "- In order to use this application you need to have the Sqlite3.dll"&@CR&@CR& _
            "- Copy the DLL to the Windows\System32 Directory."&@CR& _
            "  Register the DLL using the Command REGSVR32 'c:\Windows\system32\sqlite3.dll'."&@CR& _
            "  Download is available at http://republika.pl/roed/litex/, Goed luck !!"
EndFunc

Func Help()
    $Help = "  Is on the way, " &@CR&@CR& _
            "  See Info on http://www.sqlite.org/index.html"
EndFunc

; This is SvenP's custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"     & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc

Have fun with it.

[flyingboz]

I understand your approach that integrating it in the distribution would be a little too much.

But did you look at it from all the perspectives ?

I looked at it from my perspective as a long time au3 user and professional programmer,

as you are looking at this issue from yours. If you can't understand that others may disagree with your opinion on this matter for rational reasons (we don't need a mandatory database tax on script size that provides hooks to a specific database engine with small market share when there are excellent, proven alternatives available) , the issue w/ open-mindedness is not mine.

How can a less than 200 K large dll, be of any overhead.

Compare it on a per centage basis with the current size of a compiled app. Pretty significant.

It contains +/- 15 additional functions.

So what? If you don't need them, you don't need them. And we don't NEED them. If you WANT them,

as you have amply demonstrated with your sample code, you can have them.

It is true that if a plugin, COM, or DLL solution is used, it must be initialized - but only if you

need the functionality. It is also true that database initialization must occur regardless of the method

used, a very questionable argument for the plus column.

Performance can be tested quite easily. Have you tested any of your implementations using COM or

ODBC methodologies against your database engine of choice. My experience with the speed of DLL and COM methods has always been "more than fast enough".

Whether or not the code is easier to read depends largely on the programmer and his background, again

a very dubious argument for the plus column.

The only thing which is clear is that a lot of users would appreciate having SQL and Database support available.

It is available! Functioning database integrated scripts are working in the real world as we speak!Incorporating direct language function support for a specific small market database engine is, in my opinion, contrary to the principles of au3 being the best general purpose scripting language available at any price. The forums contain many examples of database connections to Excel, Access, MySQL, MS SQL. Any database engine that has a callable DLL, supports ODBC, or is COM-enabled is accessible via standard and supported methods. While I have not seen it discussed, it should also be possible to perform database operations via the TCP* functions. Let's not forget, too, that if we add support for one of the database engines, the next thing we're going to get to hear on the forums is "Why not native support for [insert next database zealot's favorite database engine] here."

[ptrex]

@flyingboz

I can only hear you repeating yourself !!

I would like to hear some more opinions too.

Bye for now.

[steve8tch]

@ptrex

Downloaded it and ran it - I think it great running examples when all the work has been done for you and it just works !!

I may be wrong - but in terms of database speed - it seems that all the work is being done by the following line

line 200 "$oStmt = $oDb.Prepare( "SELECT a , unaccent(, c , d FROM test ORDER BY c, b DESC" )"

Looking at some of the discussion above - would this line be executed any quicker if it were an inbuilt function ?

As I intimated above - I know nothing about databases - but soon I will need to know enough to make some recommendations - so I am keen to learn. I think I will be looking for something that has an easy initial learning curve - to achieve some basic tasks but is capable of more when I learn more and when I get up to speed with it. The another part of the decision for me is obviously designing front end GUI and reports GUIs to work seemlessly - you have shown how easy that can be - thankyou for that.

So from a complete db newbie - I would AutoIt to do some "hand holding"- whether this is in the form of a well written 'official' UDF - or pluggin I do not know. It seems to me that for what I am going to use it for speed will not be the big issue - but then I am not using Autoit at the back end of a Web server (I am not too sure that's AutoIt's primary design function) and I would rather ease of use and functionality ahead of speed.

So ,@ptrex , I don't know whether this contributes or not. I would like to see db support, and I am keen that you persue the topic - and I would certainly like some 'supported' outcome - (even if this were an officially supported UDF - btw - the reason I keep mentioning official UDF - is that these are downloaded and installed as part of the AutoIt installation - I don't then have to go and find a UDF I was using on one computer when I sit down at another!! - and its great to have all the syntax displayed in the help file)

SO keep up the good work - keep posting code (that way I can learn how this db stuff works !!) - thankyou for your efforts.

[sykes]

I create and use many different scripts that connect to various databases on a daily basis. Having said that I see no need to increase the size of AutoIt to satisfy the needs of a very low percentile of AutoIt users. You say that it would add +/- 15 functions, but is that worth roughly doubling the size of AutoIt to have embedded database functionality? IMHO it does not.

+/- 15 functions for 186 KBs of filesize (which would only be used by a few programmers) leaves the rest of us with 186 KBs of dead weight.

With a moderate amount of code you can do anything you need to with databases without adding this feature. The whole purpose of AutoIt was to have a strong, fast and reliable method to automate windows tasks. In version 2 we saw file sizes under 65k for a compiled script. With version 3 we saw a trade-off of more functionality for a larger compiled script size. There has to be a stopping point somewhere. We don't want AutoIt to become the bloated beast that VB is.

Allthough I think that embedding this feature is not needed, I do believe that having it as a Plug-In would be great for those that would use it.

[tonedeaf]

Hi ptrex,

I can see that having database functionality with AutoIt is very useful.

I think the reason you want AutoIt to support the SQLlite natively, is that you prefer accessing the DLL directly instead of registering a wrapper and accessing it through COM to have the obvious enhancements of speed (COM is definitely slower than accessing the DLL functions directly.) This would matter a lot for a database engine as the size of data accessed would greatly affect the speed.

But thats what the Plugin functionality is for:

Ever wanted to write your own C/C++ functions in a DLL and have AutoIt treat it as just another builtin function without the horrors of DllCall?

http://www.autoitscript.com/forum/index.php?showtopic=15279

So if the plugin functionality works like it is supposed to, it would provide the speed and the size of your executable would remain the same (you would need to provide the DLL with the executable to the users).

I definitely would vote for SQL Lite support as plugin. Infact it probably may already be working with the current beta. I would suggest you to try to implement your current program using the plugin functionality and report any bugs/suggestions to developers.

Edited October 21, 2005 by tonedeaf

[randallc]

So ptrex,

Did you mean "Func Writefile()" as "reading Listview to Text file"?

It looks like you are reading the database to the text file here?.....

Randall

[PS can I use your GUI to look up data in my work SQLserver if I know the location/ server/ table etc? - If so, how?]

[ptrex]

@randallc

Yes I meant the function Writefile() mentioned in post #13

http://www.autoitscript.com/forum/index.ph...ndpost&p=118152

I must be easy to make it write to an EXCEL file using one of you UDF's.

If I have time (or you have) I will try to create an example.

This example could than be used to create a generic UDF for Exporting data from a ListView to Excel.

Powerfull don't you think ?

Your question about using my Gui to read an MS SQL database, I must say that this is not possible because that is what this whole discussion is about. I wanted to show a native database example. Different frpm the traditional approaches. Where my Example is not depending on any familiar commercial databases.

To get your need fulfilled you will have to use the traditional ODBC solution see here for more info

http://www.autoitscript.com/forum/index.ph...hlite=odbcquery