SQLite semi Embedded database functionality in AutoIt

[ptrex]

@elieleroy2002

Thanks for the feedback.

More and more languages are moving toward the Native LinQ.

(this means Native Language Integrated Query).

At the moment this is a hot topic at Microsoft.

They have a project going where they've extended C# and Visual Basic, with native language syntax for queries and provides class libraries to take advantage of these capabilities.

These extensions are ready to download.

For more info look here :

LinQ

I hope we can see the same trend in AutoIt.

[blitzkrg]

ptrex i support your efforts.. i too would like to see some type of sqlite support.

although i am a novice developer, could we use something like the pluginopen function

i.e.

$blah = PluginOpen("sqlite.dll")

$blah2 = some fuction of the dll to run against the sqlite db files

etc etc

again i'm not much of a developer, but would something like that be possible?

i know that's not the tight integration your hoping for, but i think it would be ok with doing a fileinstall on the dll file

[ptrex]

@blitzkrg

Thanks for support.

I can follow your logic. (I was hoping someone could tell us if it is possible.)

My knowledge doesn't go that far either. Connecting using COM and ActivX is as far as I can go, for now.

I have been following the DllCal posts for a while. And I could grasp that most of the DllCall's are using Windows DLL's. Because that's where some documentation is availble for on the internet.

I could post a question in the Developers area, to ask them if it makes sence to walk this route.

And investigate if doing a DllCall against a NON Windows DLL like SQLite is worthwhile doing.

[dhack]

Ptrex,

I'm thinking of this:

a way to design a *simple* db in access and Export it to AutoIT.

Here's what i propose:

I believe i can make a function to export access forms to autoit (code will be generated to bind

fields to data)

i can export tables to CSV files. (these could be autoimported into an SQLite db, or --

be setup as a Microsoft Text Driver DSN, where we could query via ODBC those files

i can export the access queries' SQL statements, which could be used

(in the event the queries reference a Form's fields, we do this: before the query, we

export the forms variables to a table, -- *then*, the form would behave like a table, so you

could reference it and the SQL statements would work

Modules are just right out, because it would require a VBA to autoit converter,.. however,

we could import the code and comment it..

Reports, could be done.. but that's for later.

Macros.. again.. too large a scope.. but could be imported with comments..

What i'm trying to achieve is this.. Setup the data and queries and forms in access *without* much

VBA behind it.. Then, the function i'm working on could export the Tables, Queries and Forms to

AutoIT with basic functionality -- IE, browse records with a form, execute a query...

What are your thoughts-- my main concern is setting up the datasource.. getting the data from access to a form that AutoIT can use.. Ideas?

Also, can i *just* use Sqlite.dll without using the scripting host object and wshell object?.. then, i can establish the code that will import the data into a SQLdb

Damon

My solution uses the LiteX COM wrapper of http://republika.pl/roed/litex/

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

Use the command regsvr32 sqlite3.dll.

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

; ----------------------------------------------------------------------------
;
; Sqlite homepage http://www.sqlite.org/index.html
; Sqlite example using LiteX ActiveX wrapper
; More info http://republika.pl/roed/litex/index.html#stmt_mtd_step
; LiteX simple test, create a database, create a table and export test values
;
; ----------------------------------------------------------------------------
#include <bk-logfile.au3>

;Declare Vars
Dim $oMyError
Dim $HexNumber
Dim $WshShell 
Dim $Fso
Dim $oDb
Dim $sDb
Dim $sFile
Dim $f
Dim $oStmt
Dim $Path
Dim $Dbfile
Dim $Txtfile

;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
$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" )

;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" )
while $oStmt.step() = 0
    $f.WriteLine($oStmt.ColumnValue("a") &@TAB& $oStmt.ColumnValue(1) &@TAB& $oStmt.ColumnValue("c") &@TAB& $oStmt.ColumnValue(3))
WEnd

$f.Close();
$oStmt.Close();
$oDb.Close();
$Fso.DeleteFile( $sDb );

$WshShell.Run( $sFile );
Sleep(2000);
$Fso.DeleteFile( $sFile );

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

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

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

[ptrex]

@dhack

I will come back to this on Monday. OK for you ?

[dhack]

sure..

@dhack

I will come back to this on Monday. OK for you ?

[ptrex]

@dhack

First of all thanks a lot for the interest in SQlite !!

I saw your MDB forms export to AutoIT, this is a good job.

As far as I see the approach, It it best to do this all in different steps.

Creating a utillity to import MDB databases is not needed. Because it already exists

Please give it a try and see if it works on you databases.

The tool provides a SQlite Nwind.db converted from Access Nwind.mdb as an example.

2. SQLiteImport

A tiny utility which helps importing Access databases (in fact any database accessible by ADO) to an SQLite database. The sample is very simple; feel free to extend it (add more datatypes support, create indexes etc.)

You will also need ADO 2.5 or better.

source: http://www.terrainformatica.com/sqlitedb/

This answers your question, how to get the data from MDB to DB.

Next step is when a database is converted for MDB to DB, is getting the QUERIES moved over.

I think if you are able to get the Queries exported, best is to store the queries in the DB tables.

These could then be called later on by the GUI AutoIT Forms.

Reports is for later. I agree

Modules and Macro''s out of the Scope. I agree.

Your question if you could use only Sqlite.dll.

Yes you can. But it requires a different approach. Sqlite.dll goes together with a command line utility, that can intercept any SQL statement.

This means if you sent the CREATE table statement to that utility, after you create the DB. The table will be created. etc.

I thinks this approach is the only good one, until someone creates a PlugIn for AutoIt.

See here for an example of the approach:

http://www.autoitscript.com/forum/index.ph...wtopic=8684&hl=

I think this could be a very interesting project !!

I hope this info helps you out. If you need more info let me know.

Regards,

[dhack]

Ptrex,

thanks!

I believe I can get the queries transferred over no problem. I think just using the SQlite.dll is fine.. i don't think it will *require* the command line tools.. you could just make the calls to the DLL and be fine.

I'll make an Access function to export queries to a table then using that tool you mentioned, we could export

the tables (including the table with the query definitions) into a database we can read with AutoIT

Damon

@dhack

First of all thanks a lot for the interest in SQlite !!

I saw your MDB forms export to AutoIT, this is a good job.

As far as I see the approach, It it best to do this all in different steps.

Creating a utillity to import MDB databases is not needed. Because it already exists

Please give it a try and see if it works on you databases.

The tool provides a SQlite Nwind.db converted from Access Nwind.mdb as an example.

This answers your question, how to get the data from MDB to DB.

Next step is when a database is converted for MDB to DB, is getting the QUERIES moved over.

I think if you are able to get the Queries exported, best is to store the queries in the DB tables.

These could then be called later on by the GUI AutoIT Forms.

Reports is for later. I agree

Modules and Macro''s out of the Scope. I agree.

Your question if you could use only Sqlite.dll.

Yes you can. But it requires a different approach. Sqlite.dll goes together with a command line utility, that can intercept any SQL statement.

This means if you sent the CREATE table statement to that utility, after you create the DB. The table will be created. etc.

I thinks this approach is the only good one, until someone creates a PlugIn for AutoIt.

See here for an example of the approach:

http://www.autoitscript.com/forum/index.ph...wtopic=8684&hl=

I think this could be a very interesting project !!

I hope this info helps you out. If you need more info let me know.

Regards,

[ptrex]

@dhack

Thanks that's looks great.

If can run it using the sqlite.dll only, let me kow and how. Because I can't.

At least I am talking about the original DLL from the SQLite homepage.

Not the one from where you can download the import tool, or wrapper DLL I used in my example

Can you run your FORMS export tool on the Nwind.mdb ?

If yes, please attached to converted AutoIT Forms, than I can have a look on how this would fit into the SQLite database.

Thanks

[piccaso]

If can run it using the sqlite.dll only, let me kow and how. Because I can't.

sqlite3_exec uses callback

so you need something in between that can handle callback's (because autoit cant)

[ptrex]

@piccaso

Thanks. This is what I thought as well.

A PlugIn for AutiIT would be the final solution.

In the meantime we will have to settle with an ActiveX wrapper, or the command line tool of SQLite.

[blink314]

ActiveX? You mean LiteX? Is the Command line tool much different? Does it allow you to use the latest version of SQlite without having to depend on a third party to recompile the wrapper? The reason I am asking these questions is because I am writing an SQLite database "manager" of sorts to let you build and view databases without needing to know SQL (something I am learning in the process). I would rather stay away from wrappers that may become obsolete and use something more long term. Any word on when a plugin could be available?

Kevin

[ptrex]

@blink314

Hello this is the answer to your questions about the SQLite usage

These are the possibilities to use SQLite

ActiveX?

There are several ActiveX/Com DLL available that provide a COM interface that can be used in AutoIT or

VB(A) and many other languages

You mean LiteX?

LiteX is only 1 of the ActiveX wrappers that I used as an example, because there where nice VB scripts available that could be easily transformed in AutoIT scripts.

Advantage:

Just ship the ActiveX dll with your AutoIT appl. And thats it.

Disadvantage:

Like you said. When choosing for a certain ActiveX DLL you are stuck to it, relating to DLL upgrades and SQL syntax specifics. So this is not the best final solution for AutoIT.

Is the Command line tool much different?

SQLite goes along together with a command line tool, which the native interface to the database.

Which has no GUI interface, available. But that's not a problem for AutoIT, as is shown in this example.

Here the native DLL and command line tool is used to create the DB and the SQL statements

http://www.autoitscript.com/forum/index.ph...614entry61614

Advantage:

This is a better temporarily solution, because there in no DLL to register and you can follow easily the latest SQLite developments and upgrades.

Disadvantage:

If you build an AutoIT applications and you want to distribute it, you will have to ship also the SQLite DLL and the Command line tool.

Native

Not available for now. But this is the best solution.

All SQL syntax is understood by AutoIT and the Syntax would be 1:1 to the native SQLite syntax.

When distributing the AutoIT appl. no extra DLL' s should be shipped or registered.

Disadvantage: someone shoud incorporate the SQL "C" Library code into a PlugIn or in the AutoIT.exe.

I am curious to see your SQLite DB management tool. There are already some available written in different languages. If you are interested I can give you some more info on this.

[blink314]

I see, that makes more sense. I think I may try rewriting my utility to use the command line tool.

Regarding the manager, yeah I've seen a few around... I've used one called SQLite Browser (I think) but it's kind of clunky. My main purpose for this is to learn SQL and create a functional script for keeping track of parts here at work and misc stuff at home. Eventually I hope to have two sides: a raw database view/creation side, and a side that allows you to do complex queries on the other side. We'll see how things go. I'm coding at work (since I will use it here) but it's kind of on the back burner. Once I get a little further I may post something here. But, I wont promise anything!

Kevin

[ptrex]

@blink314

Thanks for the feedback.

If I can help you out let me know.

So we can learn form each other, and the other members interested in SQL can learn as well.

Don't be afraid to post something, you're welcome.

[blink314]

Ok, here is an EXTREMELY rough cut of what I am trying to do. Please remember that this the first time I have ever used SQL before. Currently you can create a new database, add tables with fields, and add new records. You cannot edit records... or pretty much anything else!

I am not looking for comments changing the basic functionality of the program... I'm coding this for myself and distributing it as a prototype. Take it and do what you want with it.

However, I do have some questions and any feedback on my use of SQL is more than welcome.

- Any idea how to get the tabs to work correctly??

- Should every table have a PRIMARY KEY? Right now I have it included so I can replace/edit records... how else are you going to know which record to edit?

- What are some of the most common field modifiers (eg COLLATE, DUPLICATES)? Right now I have 4 dataypes available and no modifiers... I'd like to change that. WHat is normal for these types of things?

- When editing a record, do you delete it and reinsert, or can you modify the current record in-place?

- Is there any way to put functions that require objects in another au3 file? This way I can attempt to keep track of common database commands. Right now, i get an error saying my database object is not defined... even though I have #included the other file in my main file.

Like I said I'm new to SQL so any tricks on that front will be much appreciated. Right now, I am going to go back and go through my code in an effort to make it friendlier. The code I'm posting is rather hodge podge.

Enjoy!

Couple of bugs I just found: Indexes are not handled when opening the database.. it crashes!

likewise it crashes if you try and make a table that has the same name as another table.

Kevin

Database.au3

Edited November 8, 2005 by blink314

[ptrex]

@blink314

Here's some feedback. Not that I am an expert in SQL but I have been using SQL quite a while in other environments.

- Any idea how to get the tabs to work correctly??

I am not sure what you mean whith tabs. Is this a SQL problem or rather a AutoIT problem you are referring to.

- Should every table have a PRIMARY KEY? Right now I have it included so I can replace/edit records... how else are you going to know which record to edit?

Best for performance is to make 1 or more keys per table. More is needed in a more complex SQL environment. Keys are also needed if you are going to make JOINS between tables. But I am not sure if you up to that allready. So just create a key on the field that contains the primary data to start with.

- What are some of the most common field modifiers (eg COLLATE, DUPLICATES)? Right now I have 4 dataypes available and no modifiers... I'd like to change that. WHat is normal for these types of things?

To modify a field you will have to use the ALTER table statement in SQL. But be aware that when you design your database model, that some things can' t be changed anymore afterwards. This requires that you first think hard in advance before starting creating table, fields and indexes !!

Best way to get started is starting to read the syntax page on the SQLite hompepage.

http://www.sqlite.org/lang.html

- When editing a record, do you delete it and reinsert, or can you modify the current record in-place?

Editing, deleting, inserting, creating, ... these are all done by SQL statements. For each action there are specific SQL statements for deleting use DELETE statement, for EDITING use the UPDATE SET statement, etc.

Look on the SQLite hompage -> Syntax to see all the possibilities. This is to extensive to cover them all in this post. Best way to start is to put of these statements in the script, run it and see what the result is. That way you can learn fast

- Is there any way to put functions that require objects in another au3 file? This way I can attempt to keep track of common database commands. Right now, i get an error saying my database object is not defined... even though I have #included the other file in my main file.

I can' t tell this. I never put CreateObject in an other file but the main file. But it is an interesting question.

This is something for the developers to answer how the design of AutoIT works.

Best is to post this question in the developers area.

[blink314]

Yeah, the tabs problem is an autoit question... cant get them to switch correctly. There are always some left over GUI elements from the other tab visible.

"Just create a key on a field that contains the primary data..."

You can do that? I thought keys were a data type.

As for modifying the field, I know about ALTER but I was talking about in the initial CREATE statement. In your sample you have COLLATE and unaccented keywords... what other ways can you change the field attributes while creating?

Editing a record. Yes, there are SQL statments for this I just dont know whether I should update based on a primary key or some other piece of data. As I think about this, I think I know the answer... I'll have to work it out!

Thanks,

Kevin

[ptrex]

Feedback:

tabs problem

Without a script included or a print screen of the problem, it' s hard nearly impossible to tell form my side.

create a key

Keys are more important when you do JOINS - ORDER BY etc statements. So don' t worry about that to much right now. This is for later.

COLLATE and unaccented keywords

These funtions are only available with the LiteX wrapper DLL. These are not standard SQLite statements.

Best way is to move away from that DLL and start using the standard SQLite DLL. Like we discussed earlier.

Editing a record

You have got it ?

I hope this can help you proceed with what you are doing.

[blink314]

No script? Did the file not attach? If not let me know and I'll retry. As far as using the SQLite DLL (well, I guess it's actually the command line util), I've decided to continue using LiteX for now. I dont like the idea of dumping to a file and reading the file into AutoIt. We do this type of thing a lot at work so I know how to do it, I just like the idea of an object better.

Concerning other keywords, I understand. I'll add the ability to add custom keywords to handle this. Thanks.

Not sure yet about editing (got some other work to do here at work first) but I'll let you know if I get it.

Thanks,

Kevin