SQLite Examples

[kylomas]

I ran into the following trying to run the examples for the following SQLite stmts:

[text]

_SQLite_ErrCode - Example runs fine but is intended to show a syntactical error (based on comment). I removed

a quote from SQL and got the example to drive the error routine.

_SQLite_SQLiteExe - Example call has 5 parms but doc only shows 4 parms. The example errors with:

"ERROR: @error=1 when calling _SQLite_SQLiteExe" (can't create new database)

Raw output from call:

! SQLite.au3 Error

--> Function: _SQLite_Query

--> Query: SELECT ROWID,* FROM TblImport;

--> Error: no such table: TblImport

Verified that the temp TSV file had valid data and that the DB name variable was populated.

The input SQL is:

CREATE TABLE TblImport (Column_1,Column_2,Column_3);

.separator \t

.import 'D:\AI\~anisury.tsv' TblImport[/text]

I'm trying to get familiar with SQLite so would not be suprised if something that I'm doing is causing some of this.

@jhcd - got latest and greatest from SQLite.org as you suggested. I put the executables in the SQLite dir. Do they need to be moved to an include dir? The DLL that came with the AutoIT distribution is not the same as the version that I downloaded. The distributed version appears to be post processed in some fashion.

kylomas

[jchd]

If you start using SQLite on a regular basis, I recommend putting your SQLite directory in the path, so that you can invoke the .exe and not mix them with the AutoIt version.

Place the dll in your scriptdir and avoid using SQLite.dll.au3 altogether. Specify your lastest sqlite.dll path in the SQLite_Startup call. You can fileinstall it if needed (only once).

Now regarding the CLI (command-line utility) it sure has some merit but is really a pain to use. Go download SQLite Expert there is a free version that does a lot of things. I paid the Pro version and it saved me so much time in the first few days that the cost is negligible. I have one instance running 24/7 and in fact I'm using it as if it were a part of my applications.

_SQLite_ErrMsg will return the last error code for a connection from SQLite. These error codes are not limited to syntax errors. See the list in SQLite docs.

There is work in progress in SQLite.au3 but it hasn't been published due to lack of recent AutoIt release. _SQlite3_SQliteExe had issues which I believe are fixed in the yet to come release. I don't use SQLite.exe for the reasons explained above. Anyway if you choose to use it, make sure it uses the same library version as your program/DLL. Backward compatibility is paramount but there has been a recent change which can cause a dark corner bug and make the CLI report a catastrophic failure (DB image is malformed) while the DB is actually fine.

[SmOke_N]

+1 for SQLite Expert Pro, truly a time saver. I use it several times a day just to validate transactions I later use in my code. Edited February 12, 2011 by SmOke_N

[GEOSoft]

And another +1 for SQLite Expert. It's the best way I know of test SQLite calls.

The dll as distributed by SQLite was installed on my system by some other app initially so I don't need the one distributed with AutoIt. Now I make sure I keep the dll updated with the current sqlite distro.

As for the help file, we have a small group currently working on the docs. The bad news is we are currently a long way from checking the User Defined Function section. I'm making a note of it for now and hopefully when we get that far I'll remember where the note is.

@jchd

I must be brain dead again. Using a database (SQLite?) with checkout features is probably what we want in that message thread, the one where you were digging a hole for yourself, and I noticed you weren't digging it big enough for two.

[jchd]

@GEOsoft,

Several months ago I've been working with JPM to fix a few things in the UDF and I know he fixed bugs in SQLite.dll.au3 and in _SQLite_Startup and _SQLite_SqliteExe himself. His fixes and mine (fixes and significant improvements of the "core" functions) should have been commited and be traceable in the source tree. It's likely he fixed various errors/examples in the help files as well.

Only if people repeatedly hit _actual_ bugs will I release the latest UDF (which I've been using myself for months now for production applications). Rationale: if every contributor is going to release more or less officially the next to come version of standard UDFs, it will quickly become a huge mess for users to keep up. We don't need that currently IMHO, except when recurrent crying bugs surface and demand publishing an interim release.

And YES I've yet to write a complete SQLite primer for AutoIt users, mixing pure SQLite considerations and everyday AutoIt practice. I really want to do so, but please point me to where in the AutoIt help file is shown how to temporarily stop this fuc*ing wall clock, as I often feel it behaves more like a fan than clock hands turning slowly!

[GEOSoft]

We have the committed help files and that's what we will have to take a look at when we get to that point.

I agree that we don't need to have potentialy bad code released even on an interim basis and I know from experience what happens if too many changes are requested for a UDF or even just a function.

You will get that primer done when you get the time and thats all there is to it. It's great to help the community but not so great killing yourself to do it. You see your clock as a fan, I see mine as a bicycle wheel, going downhill, in high gear and with a tail wind. I've been at it since 0500, it's now 2200 and I still have 2 or 3 hours left to do tonight.