Memo/blob field showing unusable data sqlite3

[marcov]

There are no known bugs with Sqlite when you use Sqldb. 

https://forum.lazarus.freepascal.org/index.php/topic,72363.msg566336.html#msg566336

[H₂SO₄]

Something's wrong with that database file... when I try to open it using DB Browser for SQLite, it throws a nonsensical error. It also doesn't really stick to UTF-16 (LE); use a hex editor to look at the XML data and you'll see what I mean.

Fortunately the SQLite3 CLI can still access the database and is able to rebuild it through back-to-back serialization & deserialization:

sqlite3 74TTL.db -- .dump | sqlite3 74TTL-Rebuilt.db

All symbol drawings are perfectly readable in the resulting database (which is now entirely UTF-8 as a bonus).

[cdbc]

Hi
@Sulfuric Acid: Cool, thanks, how does one go about 'seeing' them nice pictures?!? I mean XML?!?
Regards Benny

[Lutz Mändle]

In the attached archive is a small test program that opens the database and displays the table Symbol.
The archive contains also the original database and 2 rebuilded files as UTF-8 and as UTF-16be, the original file is UTF-16le.

The database filename is set in the FormCreate method of the mainform and can given on the commandline.

Only in the UTF8-version the column Data is readable without tweaks, the other versions need a special sql statement and a special handling of the OnGetText event of the column Data.

The sqlite function unhex used in the tweaked sql statement is available since sqlite version 3.41 (february 2023).

[Lutz Mändle]

The column Data of the table Symbol can read without tweaks if the column type is BLOB instead of TEXT regardless to the encoding.

[jamie]

Thats perfect!

I down loaded the newer versions of the DLL's and this is the statement I used.

 Sql:= 'SELECT SymbolID, unhex(hex(Data)) as Data from Symbol Where [SymbolID] = '+symbol.FilePos.ToString+';'; 

 I don't understand how the original program was able to process this with the version of the DLL it was using but it did, which leads me to believe there is more to it, I did see a zlib.dll in the source files but I don't know of any area of the code where that is being used, maybe I missed somewhere, the unpacking etc.

Now I need to know how to handle selecting the proper DLL for 32 bit verses 64 bit targets, I need to somehow set the path for the Sqlite.DLL before it loads.

Thank you very much, now I can debug the rest of the code.

Jamie
   

[jamie]

Although I have a working code base atm, I have noticed this, the original program reads and writes this field in as "BLOB", but checking the type here reports it as a "MEMO".

 is there a way to change the field definition locally within the FPC/LAZ code to trick the Sql code to read it as BLOB without editing the database files?

Maybe I can construct a SQL statement that indicates it as being a blob?

Jamie

[Lutz Mändle]

Maybe I can construct a SQL statement that indicates it as being a blob?

Use:

Code: Text  [Select][+][-]

1. select SymbolID, cast(Data as Blob) as Data from Symbol;
2.  

This has the benefit, that older sqlite versions (before 3.41) are also working.

[jamie]

Thank you for that, I will try that


 That works !  Thank you!
Jamie