SQLite semi Embedded database functionality in AutoIt

[ptrex]

@picasso

Good to know !!

Thanks for clarifying.

BTW a nice example added regarding the different approach of doing a qry

[piccaso]

but why remove a function that works...

if i get bored i'll add func's for BEGIN/END/ROLLBACK

but i'll sure do something like this in the next days...

_SQLite_QueryNoResult ($hDB, $sSQL)

> Returns Like most (of my) sqlite functions (0 = $SQITE_OK, ...)

> sets @error on DllCall error (like its brothers)

> Retrys until sqlite istnt $SQLITE_BUSY against a global timeout variable

> No memory allocated for results (like in _SQLite_Exec() )

_SQlite_Query ($hDB, $sSQL, ByRef $hQuery)

> Prepares and executes query

> Returns Like most sqlite functions (0 = $SQITE_OK, ...)

> sets @error on DllCall error (like its brothers)

> Retrys until sqlite istnt $SQLITE_BUSY against a global timeout variable

_SQLite_FetchArray ($hQuery, ByRef $aRow)

> Gets row size (no of Colums) and puts each as 'TEXT' in an Array ($aRow)

> Returns 1 on Success

> Returns 0 if there is no Row left

> Lower mem usage than _SQLite_GetTable* (only one row each time)

For easy use with While...WEnd Loops

... thats the way i would like to use it

or how would you like to see it?

[blink314]

The whole prepare/query idea is a good one. It saves having to read all the data out of the database at one time. But, I thought it used callback... evidently I was wrong.

Kevin

[ptrex]

@picasso

Looks fine for me. But then again I am not using DLL call at the moment.

So maybe @blink314 can give you more relevant feedback.

@ blink314

Can you upload your reworked Database.au3, so I could start reworking my version as well.

[blink314]

Right now, I am debating what to do. I am continuing development, but my interests and the interests I see here aren't lining up (Thats not saying what is going here is bad!!). Trying to use the UDF is causing headaches for me because of different coding styles (once again, NOT saying Picasso's work is bad!!). However, at this point, you cant just use the udf to run my code and I am not going to try to make that work. Also, while the wrappers are extremely nice and handy, I find myself doing the actual DLL calls myself because I can have more control over them.

I can upload the latest version, but it will not jive well with anything done previously. I have rewritten most of it at least two times (once for my UDF, once for Picasso's) and now am in the midst of deciding how complient I will be with whatever comes out next.

Once again, please do not take this as me saying things arent going right... in this case, right is in the eye of the coder! But, my code will not easily go along with what is here and I'm not going to constantly rewrite sections so they can handle changes in the UDF. (The whole prepare/step thing greatly intigues me because I had looked at this, but thought it used callback.)

Kevin

Edited November 22, 2005 by blink314

[ptrex]

@blink314

I understand your position. That' s why I didn' t add things to your work untill it was handed over.

Just to avoid getting in a position not knowing what has changed or how to align my changes with yours.

I think now is the time you should agree with Picasso, which version is the best to build on future versions of DLL call apps.

He was asking feedback on how we liked it, wasn' t it. So if you 2 can agree on something (a kind of protocol). Than there won't be any of these diverted situations anymore, I hope.

I will not jump into the DLL call untill you 2 have agreed how to proceed. I think this is for everyones best interest.

Anyhow, you both did a great job so for with the DLL call !!

PS : Any comments on my new version (in post #1)

[blink314]

I see no problem with Picasso's implementation. I dont see where we have to agree on things. As far as I'm concerned development is proceeding with you and Picasso. The reason I agreed to "relinquish control" last week is that it was becoming too tedious for me to keep up and ideas were diverging. Granted, most of this stress is self induced... but I do not intend to take it back up! I will help out where I can with minor coding details, but I am more concerned with getting "something" working so I can continue working on my frontend, the part that will actually be useful to me at work and at home.

So you two can decide on things! It would be better anyway if the code that is posted here that I wrote is rewritten. I will continue to watch what is happening to see if any new breakthroughs come (Prepare and loading the db into memory in particular) but I am not going to debate standards and usability issues.

Please dont think I'm mad, angry, or otherwise discomfitted... I learned a lot and continue to learn a lot by watching this thread, but I dont desire to be on the cutting edge so to speak. Go ahead and make your changes!

Kevin

[ptrex]

@blink314

Prepare and loading the db into memory in particular.

This is already implemented. Download the SQLite_GUI.au3 and review the code.

Let me know what you think of it.

[blink314]

I looked at it and didnt see anything pertaining to loading the database. I searched for PTREX... this should have turned it up, correct?

Your modifications look fine... they are an example of the reasons that you should be doing the modifications. I dont see a need to add them, but I dont see anything wrong with them! Keep changing away. Actually you may want to rewrite it, since it's kind of kludged together.

Kevin

Incidentally, all VIEWs and I'm assuming Triggers are kept in the sqlit_master. "SELECT * FROM sqlite_master" to see them.

Edited November 22, 2005 by blink314

[piccaso]

in the next days...

didnt take so long

@blink314

You (and everybody else) are free to use/learn from/modify (from) my work here. but you dont have to.

Of course i do understand that you dont want to rewrite your app. only because i released something new

I just want to work out a easy to use lib. to access sqlite from autoit.

here is an example of the prepare/step/finalize method

#include "SQLite.au3"
Local $hQuery, $aRow, $sMsg
_SQLite_Startup ()
_SQLite_Open ("test.db")
_SQLite_QueryNoResult (-1, "CREATE TABLE aTest (a,b,c);")
_SQLite_QueryNoResult (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")
_SQLite_QueryNoResult (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")
_SQLite_QueryNoResult (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');")
_SQlite_Query (-1, "SELECT c FROM aTest ORDER BY a;", $hQuery)
While _SQLite_FetchArray ($hQuery, $aRow)
    $sMsg &= $aRow[0]
WEnd
_SQLite_QueryNoResult (-1, "DROP TABLE aTest;")
ConsoleWrite($sMsg & @CR)

Output:

Hello World

Edited November 23, 2005 by piccaso

[blink314]

Here is another quick and dirty Prepare script. You will need to look at the 2 dimensional array (n columns and 2 rows) to see it but it should help give the flow of things.

Kevin

expandcollapse popup

#include "sqlite.au3"

_SQLite_Startup()

$hdll = $SQLiteWrapperGlobalVar_hDll
$hdb = _SQLite_Open("Trial.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 * FROM sqlite_master"

;Prepare
$r = DllCall($hdll,"int","sqlite3_prepare","ptr",$hdb,"str",$sql,"int",StringLen($sql),"long_ptr",0,"long_ptr",0)
$ppStmt = $r[4]

;Find Number of Columns
$ColumnNumDLL = dllcall($hdll,"int","sqlite3_column_count","ptr",$ppstmt)
$ColNum = $ColumnNumDLL[0]

$r = DllCall($hdll,"int","sqlite3_step","ptr",$ppStmt)
dim $DataArray[$ColNum][2]

;Get Column Names
for $ColCount = 0 to $ColNum-1
    $Temp = DllCall($hdll,"str","sqlite3_column_name","ptr",$ppStmt,"int",$ColCount)    
    $DataArray[$ColCOunt][0] = $Temp[0]
Next

;Get Column Data
while $r[0] <> 0
    For $ColCount = 0 to $ColNum-1
        $Temp = DllCall($hdll,"str","sqlite3_column_text","ptr",$ppStmt,"int",$ColCOunt)
        $DataArray[$ColCOunt][1] = $Temp[0]
    Next

;_displayarray($Dataarray)
    $r = DllCall($hdll,"int","sqlite3_step","ptr",$ppStmt)
    
WEnd

;Finalize
$r = DllCall($hdll,"int","sqlite3_finalize","ptr",$ppStmt)
_SQLite_Close($hdb)
_SQLite_Shutdown()

[piccaso]

nice

better use sqlite3_data_count instead of sqlite3_column_count i mixt it up in the prev. post...

sqlite3_data_count returns 0 if there is no data.

sqlite3_column_count allways returns no. of columns.

and if you call sqlite3_column* with a wrong iCol something real bad happens...

Edited November 22, 2005 by piccaso

[blink314]

I was getting some weird crashes (autoit crashes... not my program). Are you? I'll try the data count rather than column count and see if it fixes it.

Kevin

Still getting them... if I try to extract data from the Nwind Categories table I get a crash. It seems to happen most often on cells that have a null string in them, though not all nulls cause the crash. I did check and I'm getting the right column count.

Edited November 22, 2005 by blink314

[piccaso]

this is not the only showcase autoit is crashing...

most of them i had came from using wrong 'type' in dll call

or trying to sqlite3_free_table a db handle...

[blink314]

What return type should you use for a "const unsigned char?" This is according the SQLite api for get_column_text. I'm using str and I see you are too... why does this sometimes crash on a null value?

Kevin

If I go into SQLiteBrowser and "clear" the null cell (no change visually) there is no problem and it reads fine... then crashes at the next null.

One problem it seems is that I have been inserting nullstrings as null. If I insert or edit as '' they display without crashing. Anybody know the SQL to search and replace all null cells with null strings??

Edited November 22, 2005 by blink314

[ptrex]

@blink314

I looked at it and didnt see anything pertaining to loading the database

This is what is happening :

When temp_store is MEMORY (2) temporary tables and indices are kept in memory.

Source : http://www.sqlite.org

Maybe you didn' t understand the syntax, used in my modificactions. have a look at the site.

Most of the data I bring up in the modified SQLite_Gui.au3, seem innocent. But they change the behaviour of the VDBE (virtual database Engine) of SQLite. These settings influence the performance of the database.

Theze' s lots of tuning parameters available.

The important thing I noticed, is that the SQLite Qry runs lightning fast. But it takes a long time to populate the GUI

So most of the performance increases have to be obtained by tuning the AutoIT code. And not the DB.

Edited November 22, 2005 by ptrex

[blink314]

Ahhh i see... so it's not actually loading the entire database into memory, just any temp tables you create. Useful to know for the future... I haven't used temp tables yet.

Kevin

[ptrex]

@blink314

You don' t need to use temp tables particularly.

EVERY SQLite DB you create, creates and uses them automatically. These things are hidden items.

You can read about the VDBE behaviour on the SQLite site.

You need to do that first, before you can understand how the DB Engine actually works.

Next you can start to modify the different parameters, to optimize the performance.

[jpm]

I was getting some weird crashes (autoit crashes... not my program). Are you? I'll try the data count rather than column count and see if it fixes it.

Kevin

Still getting them... if I try to extract data from the Nwind Categories table I get a crash. It seems to happen most often on cells that have a null string in them, though not all nulls cause the crash. I did check and I'm getting the right column count.

I am curious to see which kind of AutoIt Crash you get.

Are they real crash or just fatal error popup?

[blink314]

AutoIt goes down hard. I get popup saying AutoIt has crashed and then all things AutoIt are closed. I can read the error report (well, actually I cant since it's in hex mostly), but it's not a normal crash like when my program crashes. No error report is generated in SciTe and if I have msgboxes around the DLL call I get one before but not one immediately after.

It is only when retrieving certain null values from databases. It seems to be that if I insert a null using an SQL statement like this Field1=null I get a crash. If I insert it Field1='' I dont get a crash (at least as of now!). I am using beta 87.

I'm leaving for home for the holidays so I will only be checking this periodicaly.

Kevin

Edited November 23, 2005 by blink314