Opened 17 years ago

Closed 17 years ago

Last modified 17 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 17 years ago.
bug fix for BLOB support in the SQLite driver
sqlite_blob_simple_test.zip (2.6 KB ) - added by Mateusz Łoskot 17 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)

by Kosta, 17 years ago

Attachment: sqlite_blob.patch added

bug fix for BLOB support in the SQLite driver

comment:1 by warmerdam, 17 years ago

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 by Mateusz Łoskot, 17 years ago

Status: newassigned

comment:3 by Mateusz Łoskot, 17 years ago

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 by Mateusz Łoskot, 17 years ago

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

comment:5 by Kosta, 17 years ago

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 by Mateusz Łoskot, 17 years ago

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 by Mateusz Łoskot, 17 years ago

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.

in reply to:  6 comment:8 by Kosta, 17 years ago

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 by Mateusz Łoskot, 17 years ago

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

by Mateusz Łoskot, 17 years ago

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 by Mateusz Łoskot, 17 years ago

Resolution: fixed
Status: assignedclosed

The patch is applied (r11484).

Kosta, great job, big thanks!

comment:11 by Mateusz Łoskot, 17 years ago

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.