SQLite semi Embedded database functionality in AutoIt

[ptrex]

@picasso

ptrex's gui uses version 3.2.5 which is not compatible with version 3.2.7 (latest)

Small correction. The GUI in Post #1 doesn't use version 3.2.5 but uses the ActiveX version LiteX, wich is a wrapper of SQLite version 3.1.5.

@randallc

The reason why you are puzzled is that you are mixing 2 incompatible techniques.

First I started introducing LiteX SQLite a COM object, into AutoIT. This LiteX was based on SQLite 3.1.5

Later on picasso introduced the DLL call to the native SQLite DLL version 3.2.7

Both technique have a life on there own, and are not compatible.

So make sure that you either use 1 technique like DLL call, and if needed you will have to translate the SQLite_GUI using the DLL call technique. I wall planning to do the translation. But I don' t have to time right now to do this.

I hope this clears out the confusion between the 2 techniques involved.

[randallc]

hi @ptrex,

1. No, I am not confused; just trying to learn the syntax for @piccaso's ; and do not worry, I am quite clear when I am using either sqlite3.dll!

2. I have, in fact, managed to translate the original script; disappointing speed, and I will be checking with @piccaso if I am missing anything.

3. If you try your way-back original scipt, you will see there is a bug in beta 91 and 92 [? 90 but I cannot find it to check] which does not allow the GUI to work! [see in Bug reports]

Yet this is not related to your SQlite nor Piccaso's.

4. music7 or whatever IS able to be opened and edited by your sql_gui, and I wondered why he was not pointed in that direction.

5. I would kindly request again, when you have time, that you test my excelCom, as I have changed some things for clipboard checking (not yet posted), and I would like to know if I have fixed your problem; or exactly which scripts in my examples work, and which do not. I can attach the update for you to test anytime, but will not otherwise bother to post the changed script unless someone is willing to test it.

Best, and thanks again for your scripts (and @blink and @piccaso!)

Best, Randall

Edited December 5, 2005 by randallc

[ptrex]

@randallc

1. Go to hear that it is clear to you.

2. If you post the translated script I can check to see if things are missing.

3. Thanks a lot for informing us. I am currently running on Beta 89. could you tell us what the bug exactly is ? And or there plans to solve this as well ?

4. Music.DB not interested in that.

5. You can upload and than I will do a test somewhere this week.

Till later.

[randallc]

@ptrex;

speed looks OK on random 1000, but try it on 10000; the difference becomes significant [for this example GUI now fixed so it works on on beta92]

Note that I have @piccaso sqlite in "include" direcory for consistency with other includes.....

best, Randall

WS visible and close tab definition

1st attachment; NOT using wrapped sqlite [as per 2 piccaso] (sorry still has wrong comments inside it; I have changed)

IMPORTANT :

Make sure you have the SQLite3.DLL in your include directory; sqlite3.dll 3.7 or similar

NOT using Sqlite example using LiteX ActiveX wrapper

**************************************************

2nd attachment; needs wrapped 3.1.5 as per @ptrex, with appropriate installation in system32

Edited December 5, 2005 by randallc

[ptrex]

@randallc

I ran the 2 example. All data is generated properly.

But when testing the export to XLS, I still have the same problem. An empty workbook is opened, with as sheet "ListView" and no data in it.

This problem might have something to do with different localisations ?! Nevertheless I use an English MS EXCEL version.

[randallc]

@Ptrex, Thanks for looking.

Was that with the latest ExcelCOM.au3 2.65? - I had hoped the paste problem was the answer.

If not, I might get a clue if you run some of the example scripts on that Excel 1st post and tell me which work and which do not? (there are 9 or 10 example scripts) - later!...

Best, Randall

[jpm]

@jpm

your right, i'll add a 0 on next release

@randallc

oh, i didnt see the BEGIN.

yes it worked.

ptrex's gui uses version 3.2.5 which is not compatible with version 3.2.7 (latest)

but if you want to view data with this gut you can point _SQLite_Startup("sqlite.dll") to the dll ptrex's gui is using... (with the disatvantage that you use a old version of sqlite)

or use example_gui.au3 to view data (but you need some sql(ite) skills)

@greenseed

i played around a bit with sql/perfomance to learn stuff and modified 'album scrubber'

its much faster now.

perhaps you want to offer this on some xbox forums since you sayd that xbox users would like to have someting...

(i dont care much about credits, but you could mention that it was done using AutoIT...)

and pm me a link, i would like to see what the users say...

Trying to understand your UDF I am confusing about the end of _SQLite_FetchData

The code can be better

EndIf

    _SQLite_QueryFinalize($hQuery)
    Return $iRval_Step[0]
EndFunc  ;==>_SQLite_FetchData

if it is really what you want to return

instead of

ElseIf $iRval_Step[0] = $SQLITE_DONE Then
        _SQLite_QueryFinalize($hQuery)
        Return $iRval_Step[0]
    Else
        _SQLite_QueryFinalize($hQuery)
        Return $iRval_Step[0]
    EndIf
EndFunc  ;==>_SQLite_FetchData

[piccaso]

@randallc

_SQLite_QueryNoResult() waits until db is ready (if its busy) or timeout...

_SQLite_Exec() just returns $SQLITE_BUSY and query is not executed

@ptrex

sorry, mixed up the numbers.

did you try to recompile litex.dll?

it was made with vc6 wich i should get from a friend till weekend,...

i still have no c skills so i hope it compiles flawless...

@jpm

you'r right, i missed logic here...

changed that.

thanks!

update:

Changed _SQLite_FetchData(), NULL Values will be Skipped

empty columns of tables created like 'CREATE TABLE tbl (a text default NULL);' where causing a crash when trying to read out the value with _SQLite_FetchData().

Now returns an Empty string.

i Created a little SQLite Browser - no direct edit (jet) just view and exec. query's

included as 'example_browser.au3'

SQLite.zip

Edited December 5, 2005 by piccaso

[randallc]

@piccaso,

In my posted script (3 posts back in this thread), you will see the "GetData()" function for fast sorting of listview; is there a quicker way to do this, as it is slow with large db compared to the wrapped sqlite?

Thanks, Randall

Func Getdata()

; Read data from Database into Listview

Local $hQuery, $aRow, $sMsg,$tmp, $aResult, $iRows, $iColumns, $sErrorMsg

_LockAndWait2()

if $i_OrderNumber<1 then $i_OrderNumber=1

if $s_SortOrder<>"DESC" then

$s_SortOrder= "DESC"

Else

$s_SortOrder= "ASC"

EndIf

$s_OrderColumn=StringLower(chr(64+$i_OrderNumber))

$sDb=$Path&"\"&$Dbfile

If not FileExists($sDb) then

MsgBox(0,"Feedback","Database has not been created yet !!",3)

Else

$s_Prepare="SELECT a , b, c , d FROM test ORDER BY "&$s_OrderColumn&" "&$s_SortOrder &", b "&$s_SortOrder

$iRval = _SQlite_Query (-1, $s_Prepare&";", $hQuery)

if not $iRval = $SQLITE_OK Then

MsgBox(0,"SQLite Error: " & $iRval,_SQLite_ErrMsg())

EndIf

$numb=0

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK

$numb=$numb+1

if $numb>GUICtrlRead($Tab1combo1) then exitloop

$sMsg = "|"&_ArrayToString($aRow,"|")

$dataCol=GUICtrlCreateListViewItem($sMsg,$listview)

WEnd

EndIf

_ResetLockWait2()

EndFunc ;==>Getdata

[ptrex]

@picasso

Nice update !!

I ran the example_browser, and it runs fine. It is like you said very basic, but it is a nice example of how to use the SQLite DLL Call functions.

did you try to recompile litex.dll?

No I didn't, because I don' t have vc6. Nor do I have the "C" skills to work with vc6.

If you have luck to recompile it using your friends vc6, it would be a blast !! This way we can compare the 2 techniques simultaniously.

@randallc

I will some more tests on your ExcelCOM.au3 2.65 and the examples posted, later this week.

I see that you guys keep the SQLite thread in the Scripts & Scraps top 10 Hit Parade for a long time now.

Maybe we can have a reword for this, having this thread in the top 10 so long now

Keep on going !!

[piccaso]

@randallc

try...

...using :memory: database (_SQLite_Open() ; whitout arguments)

or temp tables CREATE TABLE and make sure

that temp tables are stored in memory ( PRAGMA temp_store = MEMORY; )

...read docs on PRAGMA synchronous = OFF;

makes sqlite up to 50 times faster, but db might be corrupted on power loss/ os crash... (which shouldnt be so bad if its a temp table/ memory db which would be lost anyway...)

...append input statements together like 'INSERT INTO tbl(a) VALUES ('x');INSERT INTO tbl(a) VALUES ('x');...'

but not too much or it uses to much memory...

i did it with groups of 6 but there was no speed increase below 1000 statements...

(so you might skip the last one)

@ptrex

whish me luck... :">

[randallc]

Hi, thanks,

Unfortunately, the problem with getting data seems to be AutoIt, not SQL, so speeding SQL won't help (or for later....?)

Can you put the one function, "FetchData", its loops and DLL calls into a vbs Scripting object just for that one function? - That would be likely to make a big difference, I think, if possible?...

[EDIT : I guess not if vbscript does not do dll calls!!...]

[...append input statements together like 'INSERT INTO tbl(a) VALUES ('x');INSERT INTO tbl(a) VALUES ('x');...'] this is fantastic for creating a table, as needs to use an AutoIt DLL call only once; nearly as fast as wrapped.

[synchronise] - not sure how to use it, and don't think it would help....

[open in memory] I have done that ; no difference that I can see..

Best, Randall

Edited December 7, 2005 by randallc

[piccaso]

Can you put the one function, "FetchData", its loops and DLL calls into a vbs Scripting object just for that one function? - That would be likely to make a big difference, I think, if possible?...

[EDIT : I guess not if vbscript does not do dll calls!!...]

dont thinkt that this is possible

[synchronise] - not sure how to use it, and don't think it would help....

Would speed up inserts & table/index creation for sure

just do a

_SQLite_QueryNoResult(-1,"PRAGMA synchronous = OFF;")

once after opening database

[open in memory] I have done that ; no difference that I can see..

did you use temp tables and 'PRAGMA temp_store = MEMORY;'

btw:

if you would do something that does not require excel (and post it) i would be able to help you better... :">

Edited December 7, 2005 by piccaso

[piccaso]

@randallc

i almost forgot...

_SQLite_GetTable*() is much faster on larger tables

[randallc]

@piccaso

btw,

My instinct is that I need ;

1. _SQLite_QuerySingleRow to do it without using FetchData; one row at a time like GetTable but limited.

2. Option to nominate row number.

3. _SQLite_QuerySingleColumn to do it without using FetchData; one column at a time like GetTable but limited.

4. Option to nominate column number.

I think those would avoid so many dll calls?

What do you think?

Perhaps the table is the answer, but some tables (2Gig files?) might be too big for an array...

Randall

PS my previous post seems to have been deleted?....

Can't you use the script I posted?. as long as you don't try to export to Excel!; ... I can run it on machines with no excel.

Edited December 7, 2005 by randallc

[randallc]

Hi @piccaso,

The post which deleted answered you more specifically, sorry..

I want to avoid too many dllcalls from AutoIt and the GetTable does that, as I understand it... (but memory...)

did you use temp tables and 'PRAGMA temp_store = MEMORY;'

I thought you said ""or" in post above; do I need to do that "as well" as open without database name?

Would speed up inserts & table/index creation for sure

I don';t reallly need that now I can concatenate and only 1 dll call; try later

_SQLite_QueryNoResult(-1,"PRAGMA synchronous = OFF;")

SQL speed I think is not the problem, though; I don't think this will help; try later

Randall

[piccaso]

Updated example_browser.au3

SQLite.zip

@randallc

i'll think about your function suggestions

(have to find a way to limit stuff without wasteing memory...)

about excel -> i saw that you did include excelcom udf so i thaught...

i'll try it later...

_SQLite_GetTable*() is way faster...

try this:

Run example_browser.au3 (the new one)

open Nwind.db (from 1'st post)

Click on 'OrderDetails'

Click on Query

<takes about 10 sec's on my pc - time is displayed in log window>

select Use GetTable from Options menu

Cick on Query

<takes about 5 sec's>

now i'll try it on some realy big table...

[piccaso]

damn! (my) listview only displays 4082 Rows

but it was no problem for arrays.

6 columns

prepare/step -> 26 sec's

gettable -> 13 sec's

18 columns

Prepare/Step -> 52sec

GetTable -> 33sec

Edited December 8, 2005 by piccaso

[randallc]

Hi, @piccaso

I cannot put my modified GetTable into your script to compare; try these;

Make 10000 random; ptrex 2600 (slower?..) piccaso 1600

listview 100 ; 239/ 1237

listview 1000 (change in combo box) ; 1500/ 3200

textfile line write 10000; 3100/ 11900 (GetTable)

textfile line write 10000; 3100/ 9300(GetTable modified for no arrays)

At least I can see how to avoid arrays for huge files; but overall still the wrapper is usually 3times faster for retrieving data.

[EDIT; I have read more; the problem with those loops in autoit being slowewr; obj functions (as in the wrapper) are just fatser than anything involving the dll functions perhaps?...]

Best, Randall

Edited December 9, 2005 by randallc

[randallc]

@ptrex,

Do you know the Listview is not working for me ; from your base post database "SQLite GUI - v4.5" (only the excel view)... Is this fixable?

any more chance of you checking examples from the Excelcom example site?

best, randall

Edited December 9, 2005 by randallc