Sign in to follow this  
Followers 0
kylomas

SQLite Examples

6 posts in this topic

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites



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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

+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 by SmOke_N

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

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.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

@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!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0