SQLite semi Embedded database functionality in AutoIt

[blink314]

Well, technically I already have substituted most of the dll routines into the "database" script. It would be at least another day till I get everything changed and debugged.

Regarding 2darray... I thought I checked that out just the other week and it was just in a messagebox. However, no, my script doesn't do what yours does. It displays in a listview but without all the fancy formatting. I just can't use it in my script because it doesn't close. i have had this problem before trying to use multiple guis at once. It may have something to do with me using events and you using polling. But, i have never gotten two GUIs to work correctly. In database, I have kind of gotten around the issue but not well. In my 2dArray I bring up an inputbox to hold the script at a certain point.

Looks nice though... I would use it if I could!!

Kevin

[piccaso]

@blink314

you changed the calculation of the pointer struct size, (i think) this will cut of the last row...

New Func Lib (with 2d Array support)

removed sqlite.au3 (save space )

Added _SQLite_GetTable2d()

Added _SQLite_Commit()

Added _SQLite_GlobalRecover()

now its Opt("MustDeclareVars", 1) safe

Fixed _SQLite_GetTable2d()

Edited November 22, 2005 by piccaso

[blink314]

Thanks for adding that into the udf...

I wasnt aware that I changed the pointer construction size. I just went back and checked and I havent missed any rows. I'll keep looking though.

Incidentally, if you are extracting a lot of data, using gettable takes a long time because you have to play with the array afterwards. For a lot of data its faster to take GetTable and modify it. It was taking obscenely long for the 37000 table... now it only takes a while

Kevin

As I look at how you handled the 2d array I notice that you read it into a 1d array and then make a 2d array from it... this will take Veeeeerry long with big chunks of data (try the OrderDetails table in Nwind). It was taking forever even making the 2d array intially, it will take twice as long this way. It may be better to make two functions... one gives a 1d array and one gives a 2d array.

Edited November 21, 2005 by blink314

[piccaso]

didn't check on performance jet.

but i'll do

[jpm]

@blink314

you changed the calculation of the pointer struct size, (i think) this will cut of the last row...

New Func Lib (with 2d Array support)

List of Func's

_SQLite_Startup([$sDll_Filename]) Loads SQLite3.dll
_SQLite_Shutdown() Unloads SQLite3.dll
_SQLite_Open($sDatabase_Filename) Opens Database, Sets Standard Handle, Returns Handle
_SQLite_Close($hDB) Closes Database
_SQLite_GetTable($hDB | -1, $sSQL, ByRef $aResult, ByRef $iRows, ByRef $iColumns, ByRef $sErrorMsg, [$iCharSize = 64]) Executes $sSQL Query to $aResult, Returns Error Code
_SQLite_Exec($hDB | -1, $sSQL, ByRef $sErrorMsg, $iCharSize = 64) Executes $sSQL (No Result), Returns Error Code
_SQLite_LibVersion() Returns Dll's Version No.
_SQLite_LastInsertRowID($hDB) Returns Last INSERT ROWID
_SQLite_Changes([$hDB]) Returns Number of Changes (Excluding Triggers)
_SQLite_TotalChanges([$hDB]) Returns Number of All Changes (Including Triggers)
_SQLite_ErrCode([$hDB]) Returns Last Error Code (Numeric)
_SQLite_ErrMsg([$hDB]) Returns Last Error Message
_SQLite_Make2dResult($aResult, $iRows, $iColumns) Returns 2d Array of $aResult. Needs $iRows & $iColumns From _SQLite_GetTable()
_SQLite_Display2DResult($aResult,[$iCellWidth = 0]) Prints a 2d Array using ConsoleWrite()

Just curiosity, Why Commit cannot be a synchroneous function such as _SQLite_Commit with perhaps a timeout parameter? even incorporated in the close by default.

[piccaso]

_SQLite_Commit with perhaps a timeout parameter?

good idea

[blink314]

ptrex: I see you loaded the database file into memory... looking through your code i couldnt find where you did this. What did you do to accomplish this?

Kevin

[blink314]

Ok, here is a version of the script using the new udf. You have to use the version of SQLite.au3 supplied with this post because as of now, Picasso's doesnt support the 2d array output in one function. Make sure all three au3 files are in the same folder along with an SQLite3.dll file.

All this version does is open databases and load the table contents into the excel control. I think you can add records... but that's it. Use it as a help to see how to use the UDF.

Kevin

CommonActions.au3

Database_DLLtrial.au3

SQLite.au3

[piccaso]

... because as of now, Picasso's doesnt support the 2d array output in one function.

Now it does

http://www.autoitscript.com/forum/index.ph...c=17099&st=210#

[blink314]

Cool! By the way, I see you added COMMIT... I thought you had to BEGIN TRANSACTION before you enter the data, otherwise autocommit is in effect. Could be wrong, but COMMITing without BEGINing is pointless. Adding a BEGIN function would make it work though!

http://www.sqlite.org/capi3ref.html Look for sqlite3_get_autocommit.

Kevin

[ptrex]

@blink314

I' ll get back to you. I haven't got much time the next comming days. In the middle of an Exchange migration !!

Till later.

[piccaso]

when the dll is unloaded while a query is still running it doesent finish it.

its just to make sure...

[blink314]

Ahhh ok... It still may be helpful to have a BEGIN TRANSACTION function in case people want to use rollback.

Also, two things about gettable2d:

- You have to check for 0 columns and 0 rows before you dim the array. If you dont you will have errors if your query returns nothing. If there are 0 columns or 0 rows you skip filling the array and jump to the error stuff.

- This is merely personal taste, but I noticed you switched dimensions on the 2d array (1st is rows, 2nd is columns) was this intentional? if it was, that's fine, I'll just change it on my end.

Kevin

[blink314]

Is there any reason why a DLLstructGetData that retrieves a null ("") returns 0? Is there any way to get this to return a ""??

Kevin

[piccaso]

Ahhh ok... It still may be helpful to have a BEGIN TRANSACTION function in case people want to use rollback.

Also, two things about gettable2d:

- You have to check for 0 columns and 0 rows before you dim the array. If you dont you will have errors if your query returns nothing. If there are 0 columns or 0 rows you skip filling the array and jump to the error stuff.

- This is merely personal taste, but I noticed you switched dimensions on the 2d array (1st is rows, 2nd is columns) was this intentional? if it was, that's fine, I'll just change it on my end.

Kevin

_SQLite_Begin() against _SQLite_Exec(-1,"BEGIN",$sErrMsg)

its not that bad...

your right, i forgot about checking. Fixed

Sorry about switching, i didnt notice, dont know which format is better ist easyer for me as it is...

[blink314]

Like I said this is preference based... the reason I do it this way is because in VBA you can only change the last dimension. I tend to add rows onto arrays more than columns so rows ends up last.

Regarding BEGIN, yeah I know it's not bad, just may look funny to a newbie. Not saying it has to be added!

Kevin

[ptrex]

@blink314

In my post #1 I uploaded a new/modified version of the SQL_GUI.

This is what I have added :

- Timer to see the time needed to execute theQry, in milliseconds.

Click treeview and see info in the statusbar

- DB Integrity check

- PageFile Size

- TempStore loaded in MEMORY

- DB cacheSize increased 10 time to load the DB in MEMORY

- Show AutoVacuum parameter

- Show Sync parameter

all changes have been done in the statusbar at the bottem and on the top or the treelist.

(look for "PTREX" in the script to easily find my changes)

I did not have time to add some controls to it to modify the parameters relating to performance.

I still need to do this when I see the time for it.

There' s lot' s of other Tuning and Information available that can be showed. Will be continued.

PS: I updated the thumbnail picture for you all to see the latest changes.

Forgot to mention, that I added some handy shortkeys.

F9 to execute a QRY

CRTL+TAB to swich the TABS

Edited November 22, 2005 by ptrex

[piccaso]

i'm thinking about having the _SQLite_Commit() func removed, the _SQLite_Close could be modified to wait until sqlite isnt busy any more. which wold be better than just wait until a commit is done right because this doesent solve the problem, its the time that passes meanwihle... (sorry my fault)

... there is a noob in all of us

but instad of cleaning up yesterdays work i was playing round with the sqlite api again, here is a different approach to do a query:

#include "sqlite.au3"
_SQLite_Startup()
$hdll = $SQLiteWrapperGlobalVar_hDll
$hdb = _SQLite_Open("test.db")
;~ int sqlite3_prepare(
;~   sqlite3 *db,           /* Database handle */
;~   const char *zSql,     /* SQL statement, UTF-8 encoded */
;~   int nBytes,             /* Length of zSql in bytes. */
;~   sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
;~   const char **pzTail     /* OUT: Pointer to unused portion of zSql */
;~ );
$sql ="Select 'abc' as x;" 
$r = DllCall($hdll,"int","sqlite3_prepare","ptr",$hdb,"str",$sql,"int",StringLen($sql),"long_ptr",0,"long_ptr",0)
$ppStmt = $r[4]
ConsoleWrite(" sqlite3_prepare -> " & $r[0] & @CR)
; int sqlite3_step(sqlite3_stmt*);
$r = DllCall($hdll,"int","sqlite3_step","ptr",$ppStmt)
ConsoleWrite(" sqlite3_step -> " & $r[0] & @CR)
;const char *sqlite3_column_name(sqlite3_stmt*,int);
$r = DllCall($hdll,"str","sqlite3_column_name","ptr",$ppStmt,"int","0")
ConsoleWrite(" sqlite3_column_name 0 -> " & $r[0] & @CR)
;const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
$r = DllCall($hdll,"str","sqlite3_column_text","ptr",$ppStmt,"int","0")
ConsoleWrite(" sqlite3_column_text 0 -> " & $r[0] & @CR)
; int sqlite3_step(sqlite3_stmt*);
$r = DllCall($hdll,"int","sqlite3_step","ptr",$ppStmt)
ConsoleWrite(" sqlite3_step -> " & $r[0] & @CR)
; int sqlite3_finalize(sqlite3_stmt *pStmt);
$r = DllCall($hdll,"int","sqlite3_finalize","ptr",$ppStmt)
ConsoleWrite(" sqlite3_finalize -> " & $r[0] & @CR)
_SQLite_Close($hdb)
_SQLite_Shutdown()

output:

sqlite3_prepare -> 0
 sqlite3_step -> 100
 sqlite3_column_name 0 -> x
 sqlite3_column_text 0 -> abc
 sqlite3_step -> 101
 sqlite3_finalize -> 0

return codes shown:

Global Const $SQLITE_OK = 0 ; /* Successful result */
Global Const $SQLITE_ROW = 100 ; /* sqlite_step() has another row ready */
Global Const $SQLITE_DONE = 101 ; /* sqlite_step() has finished executing */

but its not foolproof.

modify the second parameter of 'sqlite3_column_text' or 'sqlite3_column_name' from 0 to 1 (which is out of bounds) and the process is terminated there.

does anyone have a clue why?

edit: 'sqlite3_column_count' should be used to prevent this...

Edited November 22, 2005 by piccaso

[ptrex]

@picasso

Regarding removing COMMIT.

This is not very wise !!

The correct way to do a DB transaction is 2 ways.

1. After each DB tranaction is committed immediately, as was done by Kevin in his LiteX example.

(which is not the best way to do. Because, once a transaction is committed, you cannot do a rollback

anymore !!)

2. The better way to do is to let the user descided when to COMMIT a transaction. This can be done by making a button "COMMIT" assigned to a FUNCTION commit()

And if the user did not commit the UTIMATE stage a commit needs to done is just before closing the DB.

This has to be build into the GUI event on CLOSING the MAIN GUI a COMMIT should be done.

I would never use a SQL DB where the COMMIT was removed !

[piccaso]

i cant (and i dont want to) remove COMMIT

i was talking about the _SQLite_Commit() Function i wrote

you can still use '_SQLite_Exec(-1,"COMMIT",$sErrMsg)'