AutoHotkey Community

[nick]

Hello,

I found an interesting script SQlite.au3 at http://www.autoitscript.com/forum/index.php?showtopic=17099 and thought it could be interesting for the AHK community too. So I tried a "translation" and now in my opinion the important functions are realized and raw tested. I'll try to use for some productive tool, but I'd want some others to test it too and give response for missing functions and/or optimizations.

To try out you need the SQLite3.exe and SQLite3.dll (3.3.7) from http://www.sqlite.org/download.html. Both are free and don't need any installation. Once there, you should have a look on http://www.sqlite.org/lang.html and
http://www.sqlite.org/sqlite.html. This Documentation is rather brief, but I found no other.

Now, what's the script for:

The script's functions give full access on SQLite3 databases. You even can create databases - if you really need - using the _SQLite_SQLiteExe function.

Queries (SELECT statements) are supported as follows:

_SQLite_GetTable returns the full result of a query including column names in a string with column values/names separated by "|" and rows seperated by "`n".

_SQLite_Query prepares a query and creates a query handle. To get column names, you have to call _SQLite_Fetchnames, to get the values of first/next row, you have to call _SQLite_FetchData. The results are passed out in a string with "|" separated values.

All non query SQL-statements (INSERT, UPDATE, DELETE...) are executed via _SQLite_Exec.

To make testing a little bit easier I added the script SQLite_sample.ahk. It creates a small database, tries some functions on it and displays the result of a SELECT * FROM ... in a ListView.

Changelog:

2006-09-23

New global $SQLITE_s_ERROR for functions error messages instead of "ByRef $sErr" parameter in function calls.

New optional parameter $iMaxResult in _SQLite_GetTable to restrict the amount of returned rows.

SQLite_sample.ahk adapted.

2006-11-15

Adjusted code in _SQLite_GetTable to run under AHK 1.0.45.
Maybe some more changes will be needed!

SQLite.ahk - 2006-11-15

Please download the script from http://www.autohotkey.net/~dwuttke/SQLite/SQLite.ahk



Here's some sample code to show how to use the functions:

[PhiLho]

Oh, I suppose it is a follow-up of http://www.autohotkey.com/forum/viewtopic.php?t=8324
Thank you for sharing, I am sure lot of people will find this interesting.
The alternative is Cheetah -- lighter, but less common, no SQL.
Or the big MySQL...
Or using ODBC for abstracting the database.
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

[nick]

[Chris]

I like your introductory remarks and the nicely structured script. Thanks for creating and sharing this resource.

[Guest]

Thanks for your work.

The sample script returns an error message

[PhiLho]

Dirk, remember to use a nickname, even if not logged in, thanks.
You should do a search in the code you try to run. Searching SHOW_LISTVIEW in the topic shows it only thrice: a Gosub, the label definition, and in your message.
Perhaps you accidentally pasted the code twice?
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

[finet]

I copied the script one again. Error message remains.

Dirk

[Dolby]

I had no problems with the script. It works fine. The issue must be on your end.

[finet]

It was indeed on my end.
Sorry!
Dirk

[valenfor]

OK, as I noted in the thread I had started thanks again for this module. In my excitement I have been up way past the time I should be sleeping however trying to figure out an error I am having.

I am sorry for what may be a double post, however I wanted to make sure I got this in the right spot since you started a new thread. I also realize the above person's note that 'it worked for them so it must be on my machine'. I wouldn't disagree, however I could use some pointers if someone has any as to what might be the cause.

On my first run, I had experience a strange error where the program was overwriting itself replacing items with 'name name' as I mention in the original thread here http://www.autohotkey.com/forum/viewtopic.php?t=8324

However I started fresh and now I am getting another error that feels similar stating: Error in #include file "C:\dev\sqliteahk\SQLite.ahk": This DllCall requires a prior VarSetCapacity. The program is now unstable and will exit. It no longer hard crashes to the point it wants to send a note to Microsoft (as it did in my first error description) however it does still fail after showing the 'Last Insert ID'. It is not overwriting the code with various pieces of 'Name Name'

Now I have made sure I am on the same SQLite version, and have moved from 3.7 to 3.8 without any changes. I had 1.0.45 of AHK installed, and that was when I had the first error described (name name replacing items in the running file). I tried downgrading to 1.0.44 as the author points out in the source with no change, however when I moved up to the latest version that is when the error did change.

Again, any thoughts on what I may have done wrong here would be appreciated. I look forward to implementing all kinds of goodies using SQLite which I didn't have the ability to complete myself - KUDO's.

[PhiLho]

This is because AutoHotkey has become, with latest version, more picky on the size of variables used as buffer, ie. where the WinAPI will write.
That's a good thing, the old versions probably has hidden side effects, like memory overwriting.

Quick fix: Search in the code all the "UInt *" declarations in DllCalls.
Look at the variable name after this declaration. It is, most of the time, initialized like $h_DB := 0. Just change 0 to 0000 and it should be OK.
In _SQLite_Exec, $iErr isn't even initialized, add a $iErr := 0000 line after $i_RC init.

I suppose nick will update his script. nick, I suggest you remove the code pasted in the message and upload the file to AutoHotkey.net, if possible. Above a given number of lines, this becomes impractical. Thanks.
Note that the sample can remain there, it is rather small and it gives an idea of what the API looks like.

I fear the day the v.2 will be out... Will lot of scripts no longer working, we will have lot of similar messages...
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

[nick]

[PhiLho]

[valenfor]

Thank you for the tip. I did the changes you suggested PhiLho, however the error still persisted. I traced back and found that when this call was made inside _SQLite_GetTable the $s_Col doesn't have its capacity set. So, just trying to find the solution I added this line as shown in the code below:

VarSetCapacity($s_Col, 1024)

[nick]