SQLite semi Embedded database functionality in AutoIt

[ptrex]

OK, I see there is mixed feelings going on here.

With some against it and more in favour, so far.

BTW it is an honor to see that some of the early adopters of AutoIT (like steve8tch member no 9 !! whow), are reponding this thread

So now it is time to make my case clear !!

@flyingboz

I think you are missing the point here. It is not about have a possibility to have access to a DB functionality, like it is available using any COM or ODBC.

Rather it is about the concept and the amazing results of the combination of AutoIT and SQLite.

Let me explain :

When using the traditional approach (Like COM) you would need to have an other COM application installed on that machine to run the script (Compile or not). Because it uses COM. Conceptually speaking nothing wrong with that. But to get database access functionality, you would need MS ACCESS, MySQL, MS SQL, ...

Which are large applications and heavy burden on the hardware compared with an dompiled AutoIT script and a standalone .DB file.

Integrating SQLite (in whatever form, using a plugin, natively or by including a wrapper) would get rid of the dependency of any other traditional COM application like mentioned above.

When distributing you compiled (database application) you would not have to install any ODBC drivers, no applications to install. No need to figure out if the client has MS ACCESS or MySQL or STAROFFICE, ...

Just create your script compile .EXE it and distribute it with a standalone (very small and lightning fast) .DB file. And your are finished. This is more in line with AutoIT' s filosophy, to run it on any windows version, no dependencies, small footprint, etc ....

To make it more clear : Creating a database and filling it with 5000 items takes about 1 second !!

The size of the database incl. the 5000 items is only 200 Kb large !!

To distribute a full database compliant SQL application using AutoIt and SQLite (incl. 5000 items)

Takes only 200 Kb for the compile Gui in my case, and 200 Kb for the 5000 Items database file

Which kind of traditional solution can do better, let me know.

BTW if you say we don' t need it, who is WHE ? When analysing all the post way back to the beginning of this Forum. There are people asking for SQL support in AutoIT strating from early 2004 !!

@steve8tch

I thanks you for taking the effort to test my example. Glad to hear that you understand what it is about.

@Sykes

My case statements above, show that it is not about having to have the need, to have access to a database using AutoIT. Because that is already available, like you said as well.

It is also a case of not being dependend on any traditional databases avaible. If you need database functionality. The standalone distribution capability of AutoIT and SQLite, together with all the previous mentioned benefits makes this very special.

At least the AutoIT ers would not always have to write data into text files to store them. You would have a .DB file for it and SQL to query it.

On top of this, if SQLite is integrated in some way, we would not have to rely on some individual who is willing to write a wrapper for SQLite. Like now I have used a Polish DLL. There is also a Russian guy who made one. But I can t read Russian, so that s no good. Than there are Commercial wrappers, that' s no good as well.

So leave us finding our own solution for it.

That' s what so many language already did like PHP, ASP, C++, Delphi, ... Thare are more than 30 languages which adopted SQLite. This can tell can' t it. more info here :

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Anyhow I appreciate that you agree for the plugin approach as well.

@tonedeaf

I see that you agree with my statements before.

You summarized my case well in a few lines !!

@plugin developer and AutoIT developers

Please take a closer look at this. It is realy worthwhile proceeding with this.

In the Development area of the forum I will post my approach on how I could see this getting started.

I can not rest my case untill the mariage between AutoIT and SQLite is completed. It is to good to let go !!

[ziiion]

well im a newbie to all the programing things but my opinion is that this would be a big improvement to autoit.

but not in the main core of autoit, since has someone stated it would double the size of a compiled script, that is not pratical for many of the autoit users.

so that said i tink the plugin would be a awsome addition to autoit :">

Edited October 21, 2005 by ziiion

[ptrex]

@ziiion

Thanks for standing up and sharing your ideas.

I could settle with a plugin approach.

This is what the majority a aiming for, as I read the comments comming in.

The only thing now is to find a plugin developer who could make it happen.

I am not into C-coding, so I can' t help in that direction unfortunately

But I will post the question in the developers zone today.

[randallc]

So, OK,

I need to warn you that I am only a hobby programmer! - I think i get it, though I am not sure why you mention the "ListView";

1. apart from your situation, where you are exporting to text file from "DB", where is the use of the data from a "ListView" going to excel?

2. For programming, is there a "FSO" for "createXLfile" like there is for "CreateTextFile"

3. do we just "CreateTextFile" with commas instead of tabs, and label it "csv" to open the data in Excel?

4. Will your database be limited by 4000 entries as the "ListView" is, or will you only "ListView" 4000 at a time?

5. Do you think any more complex data exchange is needed directly, or once the data is out of the "DB" into csv or xls, we can use the functions already present? Changing some of them over to your more rapid technique may have its advantages if it is possible?

Best, randall

[ptrex]

@randallc

Here' s my answer:

1. apart from your situation, where you are exporting to text file from "DB", where is the use of the data from a "ListView" going to excel?

->I had something in mind by using COM, It creates a new Blank XLS file. And than fills up the cells starting in A1 the records retrieved in the ListView.

2. For programming, is there a "FSO" for "createXLfile" like there is for "CreateTextFile"

-> I don' t think there is a CreateXLfile. But I am not sure.

3. do we just "CreateTextFile" with commas instead of tabs, and label it "csv" to open the data in Excel?

-> This is answered in topic 1. The data should go direct in a new XLS file. Which you can save anywhere you want after the export.

4. Will your database be limited by 4000 entries as the "ListView" is, or will you only "ListView" 4000 at a time?

-> This is something I have to work on, to get only 4000 records at the time via SQL statements

-> Optional I could start exporting everthing in the XLS file.

5. Do you think any more complex data exchange is needed directly, or once the data is out of the "DB" into csv or xls, we can use the functions already present? Changing some of them over to your more rapid.

technique may have its advantages if it is possible?

-> don' t think so because this is a kind of generic feature. Exporting to Excel from data in a ListView.

This can be used in any app. later on.

[randallc]

OK,

So try, simple to start!...

[ExcelCom.au3 in Include directory....]

Best, Randall

Edited October 21, 2005 by randallc

[randallc]

Or, a little more efficient;

Randall

[sykes]

Just a quick question here .... why are you using COM to access a File System Object when AutoIt has built in File handling functions?

As well as built in macros such as @Temp (path to temp directory).

[randallc]

@sykes

Hi,

I am not sure if you are referring to @ptrex or to me here?

1. Me

The only query i had was the 1 line with COM I inserted into ptrex' script [$oXLDummy=ObjCreate("Excel.Application")], when I gave up on the help file for opening Excel when the directory for Excel.exe not known (runWait would not accept "excel.exe"), with no worksheet open. ...? Any suggestions?

2. His script; @Temp (path to temp directory).; you need to ask him; I just inserted to show the "Excel" thing temporarily and had to disable Error testing to show it.

fso - Similarly; ask ptrex, but ?...it looks like he just knows those commands once he has his object, without having to look up a helpfile...?

3. ExcelCOM UDF; file handling does not do the COM commands...? how do you mean?

Thanks, Randall

Edited October 22, 2005 by randallc

[ptrex]

@sykes

See my comments in post #2

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 done for education purposes, so that people who are interested in digging in further are able to find there way back.

But thanks anyhow for your remarks.

@randallc

I will run your example on my machine and get back to you shortly.

Thanks

[ptrex]

@randallc

Feedback :

I ran your example. I worked.

It created an XLS file and all the items in it.

But I get error before executing it.

The error are related to the ExcelCom.au3 -> this is the SvenP's error handler in your ExcelCom.au3

$oMyError not declared line 471,33

ObjEvent() called with wrong number of args lnie 478,38

$FilePath not declared line 533,45

So i think it is better to leave the Error Handler in my script and not use yours.

Next :

This solution is not exactly what I was trying to say. And probably It is because I did not explain it well enough. Sorry for that, this is my fault.

Let's try agin:

Whe the data is in the listview, there is a functon in AutoIt called,

_GUICtrlListViewGetItemTextArray for all the data in the ListView and

_GUICtrlListViewGetItemText for a selection of data in the ListView

These records in the $Array could be export to Excel, using your UDF's

Once this works, you have a standard UDF for exporting data for a ListView to XLS.

[sykes]

The following line is what I was referring to randall (from ptrex's script)

$Fso = ObjCreate( "Scripting.FileSystemObject" )

I was just curious why this was being used when autoit has built in functions that handle file functions.

I now understand that it was just for educational purposes.

[randallc]

@ptrex

Once this works, you have a standard UDF for exporting data for a ListView to XLS.

I thought this was what you meant, but I have already asked also;

apart from your situation, where you are exporting to text file from "DB", where is the use of the data from a "ListView" going to excel?

and your statement did indeed confuse me;

As you can see I already did it for exporting to a .TXT file from a ListView.

...

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

i have used the exact .TXT file you made!

I can look into Listview when i have time...

Best Randall

PS I am fixing Excel Com UDF to swap to its own error handler and the back to main script error handler after running each time; I would be pleased if you could test it later on this script with your error handler running; I have not used an error handler till last week!

Edited October 23, 2005 by randallc

[ptrex]

@randallc

No problem, if you have corrected the error handling I will test it for you.

When I have time I will created a function to export from a ListView to Excel as well.

Till later.

[randallc]

Hi, did you see my post in ExcelCom?

update xLlistview

Hi, @ptrex

I have added

2_3; Oct 23rd05; _XLFromListView(ByRef $sFilePath,$h_Listview,$iListSelected=0,$si_Visible="NotVisible"){swapping Error Handler}

XLFromListView is slow, though, as is ListView generally, especially with sort!

[Also fixed error handler in ExcelCom to swap in and out with yours]Best, randall

[Needs ExcelCom2.3] Edited October 23, 2005 by randallc

[ptrex]

@randallc

Hi I did see your post, not had time yet to test.

I will get back to you today with the result of a test.

[ptrex]

@randallc

Hi

I added comments in your Excel Thread for feedback

[HansH]

@flyingboz

I can only hear you repeating yourself !!

I would like to hear some more opinions too.

Bye for now.

Well I think that it should not be embedded in autoit as default database.

I rather see standard odbc functions in autoit, which supplies you with a standard interface to a database (local or remote).

It also will make programs independent of the database, so people can choose there own database.

Since odbc is in windows it exposes no overhead to autoit.

[ptrex]

@HansH

Well Hans you can use ODBC with SQLite as well if you like that more that way. There is an ODBC driver available for download on the site.

Anyhow this discussion is more about the technology of SQLite being a standalone SQL library, which is definitely uncomparable with any other database.

Meaning that SQLite does not need any local application files to be install, in order to run or distribute the database and its content.

This is much like AutoIT.

AutoIt is also very small, self-contained and will run on 95, 98, ME, NT4, 2000, XP, 2003 out of the box with no annoying "runtimes" required! You can even make compiled executable scripts that can run without AutoIt being installed!

Source: http://www.autoitscript.com/autoit3/

That's why SQLite is the perfect match for AutoIT, because it is as well a very small, self-contained, no annoying "runtime", ... and can run without being installed

Anyway thanks for your feedback.

[elieleroy2002]

Thanks for your answer.

You made a good work.

We have to note that SQlite3 is GPL.

Bye

Edited October 31, 2005 by elieleroy2002