Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

SQLite semi Embedded database functionality in AutoIt


  • This topic is locked This topic is locked
773 replies to this topic

#1 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 19 October 2005 - 02:03 PM

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. Let’s 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

Attached File  SQLite_Gui.au3   73.02KB   11935 downloads New version update 19/05/06 to run latest Beta syntax till 3.2.8.x.

Attached File  SQLite_Gui_New.au3   73.06KB   10124 downloads 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 by ptrex, 18 December 2007 - 12:19 PM.








#2 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 19 October 2005 - 02:06 PM

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

Plain Text         
;------------------------------------------------------------- ; 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 let’s 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 by ptrex, 18 October 2008 - 10:16 AM.


#3 flyingboz

flyingboz

    The Network Is All

  • Active Members
  • PipPipPipPipPipPip
  • 768 posts

Posted 19 October 2005 - 07:21 PM

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.
Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

#4 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 07:08 AM

@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 !!

#5 steve8tch

steve8tch

    Universalist

  • Active Members
  • PipPipPipPipPip
  • 291 posts

Posted 20 October 2005 - 08:40 AM

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.

#6 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 08:54 AM

@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 :



#7 steve8tch

steve8tch

    Universalist

  • Active Members
  • PipPipPipPipPip
  • 291 posts

Posted 20 October 2005 - 09:17 AM

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...

#8 memnon

memnon

    Adventurer

  • Active Members
  • PipPip
  • 135 posts

Posted 20 October 2005 - 09:36 AM

i think it would be really a good idea to have database functions integrated in autoit.
das beste Windows Support Forum: Windows 2000 Helpline und tschüss den WindowsfehlernProgrammieren: Autoit 3 - wer braucht noch VBS ?!Programmieren: Autoit 3 Forum?

#9 Lazycat

Lazycat

    Coding cat

  • MVPs
  • 1,174 posts

Posted 20 October 2005 - 09:41 AM

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.
Koda homepage (http://www.autoitscript.com/fileman/users/lookfar/formdesign.html) (Bug Tracker)My Autoit script page (http://www.autoitscript.com/fileman/users/Lazycat/)

#10 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 09:43 AM

@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 !!

#11 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 09:53 AM

@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 ?

#12 memnon

memnon

    Adventurer

  • Active Members
  • PipPip
  • 135 posts

Posted 20 October 2005 - 10:33 AM

as i got the time to do it, i will do it, for shure @ptrex
das beste Windows Support Forum: Windows 2000 Helpline und tschüss den WindowsfehlernProgrammieren: Autoit 3 - wer braucht noch VBS ?!Programmieren: Autoit 3 Forum?

#13 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 03:07 PM

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 :

Plain Text         
;------------------------------------------------------------- ; 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 <a href='http://www.sqlite.org/index.html' class='bbc_url' title='External link' rel='nofollow external'>http://www.sqlite.org/index.html</a> ; 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 <a href='http://republika.pl/roed/litex/' class='bbc_url' title='External link' rel='nofollow external'>http://republika.pl/roed/litex/</a>, Goed luck !!" EndFunc Func Help()     $Help = "  Is on the way, " &@CR&@CR& _             "  See Info on <a href='http://www.sqlite.org/index.html' class='bbc_url' title='External link' rel='nofollow external'>http://www.sqlite.org/index.html"</a> 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.

#14 flyingboz

flyingboz

    The Network Is All

  • Active Members
  • PipPipPipPipPipPip
  • 768 posts

Posted 20 October 2005 - 03:59 PM

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."
Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

#15 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 20 October 2005 - 04:12 PM

@flyingboz

I can only hear you repeating yourself !!

I would like to hear some more opinions too.

Bye for now.

#16 steve8tch

steve8tch

    Universalist

  • Active Members
  • PipPipPipPipPip
  • 291 posts

Posted 20 October 2005 - 09:54 PM

@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 !! :graduated:

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(B), 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. :o

#17 sykes

sykes

    Haiku Computer Operator

  • Active Members
  • PipPipPipPipPipPip
  • 422 posts

Posted 21 October 2005 - 02:25 AM

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.
We have enough youth. How about a fountain of SMART?

#18 tonedeaf

tonedeaf

    Prodigy

  • Active Members
  • PipPipPip
  • 152 posts

Posted 21 October 2005 - 06:40 AM

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 by tonedeaf, 21 October 2005 - 06:49 AM.


#19 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 21 October 2005 - 09:09 AM

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?]

#20 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 21 October 2005 - 09:59 AM

@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




1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users