BLOB support in SQLite driver not working

[Kosta]

The BLOB support in the SQLite driver is completely messed up. There are similar issues as in the MySQL driver (ticket #1622)...

A patch fixing the issues is attached...

[Kosta]

bug fix for BLOB support in the SQLite driver

[warmerdam]

Matuesz,

Please verify the problem, incorporate the patch with checking and then add a test script entry for blob support. If this slips past 1.4.2 that is ok. If it seems quite dangerous you can limit it to trunk.

[mloskot]

There is a need for a mechanism of escaping special bytes like \0 in binary stream. The ExecuteSQL() function is passed with complete SQL statement, so I don't think we can provide this functionality there. I think it should be clearly stated that user is responsible to encode binary stream before putting it inside the SQL statement.

Most DBMS interfaces for Python offer their own functionality, special class or function to wrap BLOB data before sending to the SQL command. GDAL (CPL) also provides some functions for that, but I'm not if they are exposed in SWIG bindings.

I'd appreciate some comments about what strategy we should follow.

[mloskot]

Small addition to my last comment. There are exposed functions like EscapeString? and UnescapeString? by the traditional Python bindings.

[Kosta]

Why do you need to escape these special characters?

The binary data is sent to the SQL processor in hex-string-format, such as: x'ab12f3'

I used the CPLBinaryToHex() function in my patch, to create this hex-string representations...

[mloskot]

Kosta,

I understand it but this solution introduces, implicitly, requirement that all binary data in SQL statements should be encoede in HEX string. Am I right?

At least, it should be documented and clearly said it's not possible to use any other encoding.

[mloskot]

Kosta,

Could you attach a piece of code presenting your patch in action: - insert binary data into SQLite - fetch binary data into SQLite

In C, C++, Python, whatever.

Simply, I'm not sure of all assumptions you've made, so the patch in action would help.

[Kosta]

Replying to mloskot:

Kosta, I understand it but this solution introduces, implicitly, requirement that all binary data in SQL statements should be encoede in HEX string. Am I right?

This is possibly not true for all SQL-drivers; but every driver uses its own mechanism to create the ExecuteSQL() command string. So each driver can individually use the right method for constructing the SQL INSERT string. The MySQL driver and the SQLite driver both use the method of providing binary data as hex strings described above...

[mloskot]

Kosta, you're perfectly right.

I've clarified it with Frank and the ExecuteSQL() function is not assumed to work in unified way across DBMS drivers.

So, if a user wants to operate binary data with raw SQL statements, he has to encode/decode it according requirements of given DBMS. The only unified way to set binary data to a feature attribute is through OGRFeature::SetField?().

Sorry for making confusions :-)

[mloskot]

This is simple test of read and write BLOB data to SQLite database. This test uses WKB stream read from multipoint.wkb file, as a sample binary data.

[mloskot]

The patch is applied (r11484).

Kosta, great job, big thanks!

[mloskot]

I ported this patch to the branches/1.4 stable branch] targeting 1.4.2 milestone (r11486).

The Buildbot still shows green.