SQLite semi Embedded database functionality in AutoIt

[piccaso]

'holidays' whats this? something to eat ? right?

could you post an example script/db whatever, i'd like to see...

i cant produce it...

#compiler_run_au3check = 0
#include "sqlite.au3"

_SQLite_Startup ()
_SQLite_Open ("test.db")

; Insert 'null' value
_SQLite_QueryNoResult (-1, "create temp table tmp (a)")
_SQLite_QueryNoResult (-1, "insert into tmp (a) values(null)")

; Get The 'null' using get_table
Local $aResult, $iRows, $iColumns, $sErrorMsg
_SQLite_GetTable2d (-1, "Select * from tmp", $aResult, $iRows, $iColumns, $sErrorMsg)
_SQLite_Display2DResult ($aResult)

; Get The 'null' using prepare, step, ...
Local $hQuery, $aRow
_SQlite_Query (-1, "Select * form tmp", $hQuery)
_SQLite_FetchArray ($hQuery, $aRow)
ConsoleWrite(@CR & "null = '" & $aRow[0] & "'" & @CR)


_SQLite_Close (-1)
_SQLite_Shutdown ()

out:

a 
 0 

null = ''

me use latest unstable (3.1.1.87) Edited November 23, 2005 by piccaso

[Zach]

Nwind.zip ( 72.09k ) Number of downloads: 57

SQLite_Gui.au3 ( 64.7k ) Number of downloads: 27

I never new that the Nwind database was that big of a must have before

(checking out what you guys have done here - just found this by accident, and since I was looking around for simple way to use sqlite the last couple of days, this may hit a homerun with me )

[ptrex]

@zach

Welcome to join the club !!

Your alsways welcome to contribute to the SQLite thread. If you have tips or remarks, let us know.

simple way to use sqlite

What kind of plans did you have with SQLite ?

[Walkabout]

Guys,

Love what you are doing in here. Can't tell you the number of times I've fumed at having to set ODBC connections up when all I wanted to do was distribute a single exe.

I'm definately going to be using some of these UDF's soon.

Walkabout

[jpm]

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

When you come back pm me all files needed to repro the problem.

Thanks

Edited November 23, 2005 by jpm

[piccaso]

found a buch of bugs and fixed them...

Edited November 23, 2005 by piccaso

[piccaso]

doing stuff like this causes a similar crash

#compiler_run_au3check = 0
#include "sqlite.au3"
_SQLite_Startup ()
_SQLite_Open ("test.db")
ConsoleWrite("you can see me" & @CR)
_SQLite_QueryNoResult (12345, "select 'x'")
ConsoleWrite("you cant see me" & @CR)
_SQLite_Close (-1)
_SQLite_Shutdown ()

(Wrong db handle)

[jpm]

doing stuff like this causes a similar crash

#compiler_run_au3check = 0
#include "sqlite.au3"
_SQLite_Startup ()
_SQLite_Open ("test.db")
ConsoleWrite("you can see me" & @CR)
_SQLite_QueryNoResult (12345, "select 'x'")
ConsoleWrite("you cant see me" & @CR)
_SQLite_Close (-1)
_SQLite_Shutdown ()

(Wrong db handle)

THe crash occur inside sqlite3.dll not in AutoIT.

It is always possible to crash inside a dllcall with wrong parameter passed.

My knowledge of error catching in Windows is too small to avoid the crash

[jpm]

in case of some housekeeping of you sqlite.au3

[ptrex]

@Walkabout

Thanks for the kind words.

(most of the development is done by blink314 and picasso)

distribute a single exe

That' s what this whole project was about. Distributing an EXE & SQL in 1 piece. I am glad you know to apreciate it.

Nevertheless I had (and still have) a hard time convincing most of the AutoIT users, that AutoIt and SQLite is the perfect mariage.

Whatever you want to contribute is very much appreciated !!

[jpm]

@Walkabout

Thanks for the kind words.

(most of the development is done by blink314 and picasso)

That' s what this whole project was about. Distributing an EXE & SQL in 1 piece. I am glad you know to apreciate it.

Nevertheless I had (and still have) a hard time convincing most of the AutoIT users, that AutoIt and SQLite is the perfect mariage.

Whatever you want to contribute is very much appreciated !!

I am not in the impression that we disagree that's it is a perfect mariage.

I think the mariage is easy with fileinstall ("sqlite3.dll",@tempdir & '\sqlite3.dll')

perhaps it would be even better when "plugin" have taken their final form

[Walkabout]

I've just tried to run the latest version of SQLite.au3 with:

#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)

but I get :

C:\Documents and Settings\Anton\My Documents\AutoIt Scripts\Other Peoples Scripts\SQLite.au3(71,101) : ERROR: syntax error

Local $r = DllCall($SQLiteWrapperGlobalVar_hDll, "int", "sqlite3_open", "str", $sDatabase_Filename _

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

I know it's to do with the comments after the the continue-next-line "_". I made sure I'm on the latest Beta (89 now!) but I can't make the error go away.

Any suggestions?

Ta

...............Walkabout.

[Walkabout]

but I get :

C:\Documents and Settings\Anton\My Documents\AutoIt Scripts\Other Peoples Scripts\SQLite.au3(71,101) : ERROR: syntax error

Local $r = DllCall($SQLiteWrapperGlobalVar_hDll, "int", "sqlite3_open", "str", $sDatabase_Filename _

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

Hmm, even stranger. 3.1.1.89 help says:

You can combine underscore and semicolon to put comments on lines and still have a long statement span on next line.

Dim $a[8][2] = [ _

[ "Word", 4 ], _ ; Comment 1

[ "Test", 3 ], _

[ "pi", 3.14159], _ ; Associate the name with the value

[ "e", 2.718281828465], _ ; Same here

[ "test;1;2;3", 123], _

[ ';', Asc(';') ], _ ; This comment is removed, but the strings remain.

["", 0] ]

Wonder if this is a bug, or something to do with my Scite4Au3 setup.

[ptrex]

@jpm

I am not in the impression that we disagree that's it is a perfect mariage.

If you read this thread starting with the first 10 posts, you will see that not everyone has the same opinion, as we do.

But getting a plugin will propably never happen, because there is nobody interested in writing one (so far).

So the best thing we will end up with, is propably that the UDF of picasso, will ever be included as a standard UDF in AutoIT.

This way DLL Call to the SQLite DLL will be standard available, for all AutoIT scripters.

In case this becomes not a standard UDF, we than will have to download all the stuff from this thread, as we are doing right now.

Anyhow it is what it is right now, showing that nice things can be accomplished.

I am already very pleased with what we have right now.

[ptrex]

@picasso

C:\_\Apps\AutoIT3\_Development\SQLIte\DLL Call\sqlite.au3(432,34) : ERROR: syntax error

ElseIf $iRval[0]= $SQLITE_BUSY

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

I do ran the latest version and get error from the new #include line 432.

[piccaso]

sorry for the typo...

thank you jpm for cleaning up :">

[ptrex]

@picasso

Hi,

I ran your latest version and I still get errors in line 429

C:\_\Apps\AutoIT3\_Development\SQLIte\DLL Call\sqlite.au3(429,34) : ERROR: syntax error
        ElseIf $iRval[0] = $SQLITE_BUSY
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

I tested different scripts but they all gave the same error.

[piccaso]

Reworked Return values. now every func. thats based on sqlite api returns SQLite like return values (Like $SQLITE_OK -> see SQLite.au3 constants on top). or $SQLITE_MISUSE for DllCall errors...

The prepare/step/... example now looks like this:

opt("MustDeclareVars", 1)
#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) = $SQLITE_OK
    $sMsg &= $aRow[0]
WEnd
_SQLite_QueryNoResult (-1, "DROP TABLE aTest;")
ConsoleWrite($sMsg & @CR)

@ptrex

Then was missing.

sorry it seem like i never had a $SQLITE_BUSY situation

[ptrex]

@picasso

Tested !! Everything runs fine now !! Great job, Thanks.

I my first post I added a 'TO DO' list. This list came from your previous posts.

Everything seems to be finished. Is this correct ?

Could you review this so that all visitors know what the status is at the moment.

If there are open items please let me know, so I wil add them.

Thanks again.

PS: I hope that your SQLite.au3 could be added as a standard UDF in the AutoIT distribution.

[piccaso]

I my first post I added a 'TO DO' list. This list came from your previous posts.

Everything seems to be finished. Is this correct ?

Dont think so, but it can be used.

(Like beta Stage)

> Doc's & Samples are missing compleatly

> Foolproof handles

some kinde of save mode that only allows handles created by _SQLite_* func's to be used for the right functions (that can be turned of to use direct DLLCalling too)

> Something that passes out the column names for _SQLite_Query() based query's

> Manual Finalize/Reset for _SQLite_Query() based query's

PS: I hope that your SQLite.au3 could be added as a standard UDF in the AutoIT distribution.

I hope i have the time/motivation to make it UDF Standard conform...

Maybe you could ad this link ' http://www.autoitscript.com/forum/index.ph...topic&tid=17099 ' to your first post telling that the latest SQLite.au3 is found in there (for people that dont want to read all posts...)

thanks :">