SQLite 3 blob encoding

Freature requests from users for ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist, mdaems

Post Reply
salvois
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 22.05.2008, 11:06

SQLite 3 blob encoding

Post by salvois »

As of Zeos 6.6.2rc, blobs are saved in SQLite 3 as string with a custom escaping. This is not per SQLite 3 specification, that recommends the X'0123456789ABCDEF' SQL standard notation for blobs. Saving data with this format switches the field data type to blob instead of string, and is more efficient for both storage and performance. Moreover, this allows to share the database with other applications written in different languages, or using different toolkits, but conforming to the SQLite specification (or SQL-92 in general).

Changing the write part is trivial: just change ZDbcSqLiteUtils.EncodeString to a 4-line function using BinToHex, although prepared statements would do better.

Changing the read part is more complex, because native blobs may include null bytes, and the current implementation assumes null terminated values. A separate "field length" value would be needed for this, and I've not found it in the code.

In order to avoid breaking existing Zeos-based applications, a compile-time define or run-time parameter/property could be used to retain the "legacy" (escaped string) behavior or the standard SQL one.
Post Reply