[Class] SQLiteDB - Update on 2016-03-28

Post your working scripts, libraries and tools
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

[Class] SQLiteDB - Update on 2016-03-28

19 Dec 2013, 04:19

Primarily released at http://www.autohotkey.com/board/topic/67427-class-sqlitedb-now-with-blob-and-u64-support/


Change history:


Hello,

I'm coming up with one of my favorite projects: SQLite. Since I noticed the "class syntax" of AHK 1.1, I had to try to design a class for the handling of SQLite databases.

  • It may be buggy, because I had to test it on my own and could not test in a 64 bit environment
  • It should support both ANSI and Unicode versions of AHK 1.1
  • It is assuming the databases to be encoded with UTF-8

Basic usage:
  • SQLite Documentation

  • SQLite Download Page

  • Download the sqlite3.dll and store it in the script's folder. If you want to store it in another place, you have to provide the path via a "SQLiteDB.ini" file in the script's folder as follows:
    [Main]
    DllPath=Path to SQLite3.dll

  • Create a new instance of the class SQLiteDB calling MyDB := New SQLiteDB

  • Open your database calling MyDB.OpenDB(MyDatabaseFilePath). If the file doesn't exist, a new database will be created unless you specify "False" as the third parameter.

  • MyDB object provides four methods to pass SQL statements to the database:
    MyDB.Exec(SQL)
    should be called for all SQL statements which don't return values from the database (e.g. CREATE, INSERT, UPDATE, etc.).
    MyDB.GetTable(SQL, Table, ...)
    should be called for SELECT statements whenever you want to get the complete result of the query as a "Table" object for direct access via the row index. All field values will be returned "in their zero-terminated string representation" (and accordingly an empty string for NULL values).
    MyDB.Query(SQL, RecordSet, ...)
    should be called for SELECT statements whenever you want to get the result of the query as a "RecordSet" object. You'll have to call the built-in method RecordSet.Next() to access the records sequentially. Only DB-Query() does handle BLOBs properly. All other field types will be returned as strings (see DB.GetTable). If you don't need the RecordSet anymore, call RecordSet.Free() to release the resources.
    MyDB.StoreBLOB(SQL, BlobArray)
    should be called whenever BLOBs shall be stored in the database. For each BLOB in the row you have to specify a ? parameter within the statement. The parameters are numbered automatically from left to right starting with 1. For each parameter you have to pass an object within BlobArray containing the address and the size of the BLOB.

  • After all work is done, call MyDB.CloseDB() to close the database. For all still existing queries RecordSet.Free() will be called internally.

  • For more details look at the inline documentation in the class script and the sample scripts, please.


Code: [Select all] [Expand] [Download] (Class_SQLiteDB.ahk)GeSHi © Codebox Plus



View sources on GitHub
Download from GitHub
Last edited by just me on 29 Mar 2016, 01:43, edited 6 times in total.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
arcticir
Posts: 376
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

19 Dec 2013, 05:15

Can you add support for ahk v2 it?
I tried to modify, but always errors, especially reading and writing binary part.
Thanks.
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

19 Dec 2013, 09:51

I'll try, but it would be nice to have a separate v2 (sub)forum for v2 versions. ;)
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

20 Dec 2013, 02:12

*Added v2 version (marginally tested)*
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
arcticir
Posts: 376
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

20 Dec 2013, 04:00

Thanks. I will transfer to V2.
I used to add two functions make it more easy to use:

Code: [Select all] [Download] (Script.ahk)GeSHi © Codebox Plus

Class_SQLiteDB(){
Return New SQLiteDB
}

SQLite_(v){
Return InStr(v, "`'") ? StrReplace(v,"`'","`'`'") : v
}
User avatar
hoppfrosch
Posts: 244
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: [Class] SQLiteDB

03 Jan 2014, 03:11

Hi just me,

which one of your SQLite-Libs/Classes would you consider to be more mature and which one will you support in future:

* Class_SQLiteDB as mentioned in this thread
* or DBA

The latter one, seems to have more recent modifications, but you have not mentioned it within this forum ...

Which would you recommend to use?
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

03 Jan 2014, 05:36

Hi hoppfrosch,

the SQLite Lib/Class included in DBA is a (non-authorized ;) ) version maintained by IsNull. Feel free to use it, but I will not support this version.

BTW: I replaced the sample script on GitHub, thanks for reporting.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
User avatar
hoppfrosch
Posts: 244
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: [Class] SQLiteDB

03 Jan 2014, 05:42

Uh - I didn't realize that DBA is by IsNull ... :oops:

I'll have a more detailled look in both of your libs ...
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

03 Jan 2014, 05:52

The most important difference is the handling of BLOBs. It's better integrated in IsNull's version. Maybe I'll try to rewrite my version, too. But personally I don't need (large) BLOBs, and there are other ways like Base64 or Hex encoding to store and read binary content.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
whippy
Posts: 2
Joined: 10 Jan 2014, 13:08

Re: [Class] SQLiteDB

10 Jan 2014, 13:12

Hello. I have tried to get this working but continually get an error message on Line 28 of the Class ahk saying that BaseClass is an unknown class. I have the SQLite3.dll ver 3.8 from the website in the directory, also tried moving it into a subfolder and using the ini file to set path, but nothing seems to work. Help, not sure what i need to be doing?
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

11 Jan 2014, 00:20

Changelog wrote:1.1.11.00 - June 21, 2013
Fixed class X extends Y to allow Y to be defined after X.
Seems that you'll have to update your AHK.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
whippy
Posts: 2
Joined: 10 Jan 2014, 13:08

Re: [Class] SQLiteDB

12 Jan 2014, 09:35

Many thanks.
djvj11
Posts: 14
Joined: 21 Jun 2014, 01:30

Re: [Class] SQLiteDB

07 Jul 2014, 17:06

Having trouble just launching the sample script, getting this error:

Error in line 28 in #Include \Class_SQLiteDB.ahk
Line Text: SQLiteDB.Base.Class
Error: Unknown Class

I'm using AHK_H v1.1.9.4 ANSI 32-bit. H is based off L, so it should work. I don't get the Class error using the DBA sqlite library, but I have another error later on with objects. Was hoping to get one of these libraries to work.
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

08 Jul 2014, 02:46

Sorry, but I didn't use AHK_H as yet. If there are incompatibilities between the two versions, they should be fixed in AHK_H.
Last edited by just me on 08 Jul 2014, 11:36, edited 1 time in total.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

08 Jul 2014, 11:34

I'm using AHK_H v1.1.9.4 ANSI 32-bit.

I overlooked that. You need an AHK_H version based on AHK 1.1.11.00+ -> Fixed class X extends Y to allow Y to be defined after X.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.
HotKeyIt
Posts: 1232
Joined: 29 Sep 2013, 18:35
Contact:

Re: [Class] SQLiteDB

08 Jul 2014, 13:40

Get latest AHK_H v1 or AHK_H v2.
User avatar
joedf
Posts: 5250
Joined: 29 Sep 2013, 17:08
Facebook: J0EDF
Google: +joedf
GitHub: joedf
Location: Canada, Quebec
Contact:

Re: [Class] SQLiteDB

08 Jul 2014, 14:29

Repo star'd!
Gicu
Posts: 102
Joined: 19 Aug 2014, 08:19
Location: Italy

Re: [Class] SQLiteDB

08 May 2015, 10:40

Thanks a lot!
arcticir
Posts: 376
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

25 Aug 2015, 01:57

V2 now is wrong, can fix it? Thanks.
just me
Posts: 3994
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

25 Aug 2015, 02:23

What's wrong? Which error is reported? I do not use v2 at the moment.
Except as noted otherwise all of my code is tested on Win 10 Pro (x64) using the current AHK version.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: kczx3 and 16 guests