63 replies to this topic

[just me]

Update on 2013-01-25:

• Added new download link

Update on 2012-09-19:

• Added "Basic usage" section

Update 2 on 2012-08-11:

• Added 64-bit support (somewhat tested)
  You need to download the 64-bit version of SQLite3.dll :arrow: http://www.sqlite.or...x64-3071300.zip for AHK U64.
• Updated scripts.

Update on 2012-08-11:

• Added more advanced BLOB support:
  The new method StoreBLOB() accepts INSERT/UPDATE/REPLACE statements with ? parameters and an array containing objects holding the address and size of the BLOB to bind to these parameters. ? parameters are numbered automatically from left to right starting with 1, so you have to store the BLOB describing object on the corresponding position in the array (http://www.sqlite.or.../bind_blob.html).
• Updated BLOB_sample.ahk

Update on 2012-08-10:

• Added basic support for BLOBs (as a proof of concept)
  To INSERT a BLOB into a table you have convert it into a hexadecimal string and use the following syntax:
  

  Literal Values
  BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. For example: X'53514C697465'

  To retrieve a BLOB you have to use the DataBase.Query() and RecordSet.Next() methods. They are passed in the appropriate row array field as objects containing two keys/value pairs: Size (size in bytes) and Blob (binary BLOB data).
• I've added a short BLOB_Sample.ahk script to show the usage.

Update on 2012-07-24:

• Attached download files (they aren't accessible via AutoHotkey.net for unknown reasons)

Update on 2011-08-10:

• New syntax for instance variables in AHK 1.1.02

 

 

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.[/list]

So if you want to give it a try:
 

Download the scripts!

[Learning one]

Great job! Thanks for sharing!

[Learning one]

Is it true that [charlist] wildcards can't be used in SQLite!?

Works":yqk5jf6v">

SELECT * FROM Test WHERE Name LIKE 'Näme%'

Does not work - [charlist">

wildcards":yqk5jf6v]SELECT * FROM Test WHERE Name LIKE 'N[aä]me%'

Another solutions that work":yqk5jf6v">

SELECT * FROM Test WHERE Name LIKE 'Name%' OR Name LIKE 'Näme%'
SELECT * FROM Test WHERE Name LIKE 'N_me%'

[just me]

http://www.sqlite.org/lang_expr.html

[Learning one]

Thanks. So no [charlist] wildcards in SQLite.


Another question;

Case-insensitive matching of Unicode characters does not work.

Case-insensitive matching of Unicode characters is a MUST HAVE for me. So should I use SQLite or not than? How to deal with it?

For example, let's execute the following statements in your sample database;

INSERT INTO Test Values('Marko','Đorđević','','')
INSERT INTO Test Values('Ivan','Karađorđević','','')
INSERT INTO Test Values('Pero','KarađorĐević','','')

Now, if I want to find all rows containing đorđe string in FName column, and use case-insensitive matching, my statment should look like this

SELECT * FROM Test WHERE FName LIKE '%ĐorĐe%' OR FName LIKE '%đorĐe%' OR FName LIKE '%Đorđe%' OR FName LIKE '%đorđe%'

Right? Is there any better way to deal with case-insensitive matching of unicode characters?

or maybe execute

SELECT * FROM Test WHERE FName LIKE '%_or_e%'

which matches đorđe, sorđe, morže, žorže, (etc. thousands of possible matches), and than filter results with AHK - remove all results than don't match đorđe (case-insensitive, of course)

Or some thing else? How to deal with it?
Or is the answer simply; If you need case-insensitive matching of Unicode characters, forget SQLite. :?

P.S. I know for "ICU" extension, but using that with SQLite is beyond my skills.

[just me]

...with one of my favorite projects: SQLite.

may be mistakable, I'm not an expert in SQLite, my project is the wrapper class. But trying to answer your question:

Taking your example as real, i.e. mixing of cases within words is possible, I'd think there is no alternative for the "OR" operator.

[pajenn]

thanks. i need to work with SQLite databases (albeit in visual basic), but i look forward to testing your script on them.

[IsNull]

Under which licence is this code released?

[fincs]

I've skimmed through the code and I must congratulate you for producing such a well structured library! I am only going to nitpick about your assumption that pointers are always 4 bytes and fit in an UInt. This is not true in 64-bit AutoHotkey_L, but as there doesn't appear to be an official 64-bit Windows version of SQLite, then I think it's justified :lol:

[sinkfaze]

Under which licence is this code released?

It doesn't matter because the license wouldn't stand up to scrutiny in any court. Next.

[IsNull]

Under which licence is this code released?

It doesn't matter because the license wouldn't stand up to scrutiny in any court. Next.

Actually it does matter for me, as "just me" aka "ich_L" aka "nick" etc.. has some strange ideas about usage rights on his code:

Moin IsNull,

ich möchte nicht, dass Du den Code von "[Class] SQliteDB" hier verwendest. Würdest Du also bitte so nett sein, Deine eigene Interpretation selbst zu schreiben?

en:

Hello IsNull

I don't want you to use my SQliteDB wrapper code in your code. Would you be so kind to write your own wrapper library?
Source

:roll:

I'm aware that this statement isn't anything valuable to law but I like to clarify that anyway :wink:

[just me]

Hello IsNull,

you forgot the link: DBA 0.3 (OOP-SQL DataBase in AHK {SQLite})

[sinkfaze]

@just me aka ich_l aka nick

If you don't wish to truly share your work, find a way to protect your source, or better yet, don't post it at an AHK forum. Don't come to these forums giving people the ins and outs of your code and then think you have the right to tell them when and how to use it. Not only is it not legally valid in virtually any country, it makes a mockery of the spirit of the forums. We are an "open source" community for a reason.

[just me]

Edit: Nonsense!

[just me]

I am only going to nitpick about your assumption that pointers are always 4 bytes and fit in an UInt.

I cannot test in a 64-bit environment. So I do'nt know what happens, if a 64-bit pointers is passed to a 32-bit DLL. When you tell me that it's save, I will change "UInt" to "Ptr".