Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#1623 closed defect (fixed)

BLOB support in SQLite driver not working

Reported by: Kosta Owned by: Mateusz Łoskot
Priority: normal Milestone: 1.4.2
Component: OGR_SF Version: unspecified
Severity: normal Keywords: SQLite BLOB
Cc: warmerdam

Description

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...

Attachments (2)

sqlite_blob.patch (4.3 KB) - added by Kosta 13 years ago.
bug fix for BLOB support in the SQLite driver
sqlite_blob_simple_test.zip (2.6 KB) - added by Mateusz Łoskot 13 years ago.
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.

Download all attachments as: .zip

Change History (13)

Changed 13 years ago by Kosta

Attachment: sqlite_blob.patch added

bug fix for BLOB support in the SQLite driver

comment:1 Changed 13 years ago by warmerdam

Cc: warmerdam added
Milestone: 1.4.2
Owner: changed from warmerdam to Mateusz Łoskot
Severity: majornormal

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.

comment:2 Changed 13 years ago by Mateusz Łoskot

Status: newassigned

comment:3 Changed 13 years ago by Mateusz Łoskot

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.

comment:4 Changed 13 years ago by Mateusz Łoskot

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

comment:5 Changed 13 years ago by 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...

comment:6 Changed 13 years ago by Mateusz Łoskot

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.

comment:7 Changed 13 years ago by Mateusz Łoskot

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.

comment:8 in reply to:  6 Changed 13 years ago by 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...

comment:9 Changed 13 years ago by Mateusz Łoskot

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 :-)

Changed 13 years ago by Mateusz Łoskot

Attachment: sqlite_blob_simple_test.zip added

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.

comment:10 Changed 13 years ago by Mateusz Łoskot

Resolution: fixed
Status: assignedclosed

The patch is applied (r11484).

Kosta, great job, big thanks!

comment:11 Changed 13 years ago by Mateusz Łoskot

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

The Buildbot still shows green.

Note: See TracTickets for help on using tickets.