;-------------------------------------------------------------
; 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
SQLite_Gui.au3 73.02K
11108 downloads New version update 19/05/06 to run latest Beta syntax till 3.2.8.x.
