#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)
Change History (13)
by , 17 years ago
Attachment: | sqlite_blob.patch added |
---|
comment:1 by , 17 years ago
Cc: | added |
---|---|
Milestone: | → 1.4.2 |
Owner: | changed from | to
Severity: | major → normal |
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 , 17 years ago
Status: | new → assigned |
---|
comment:3 by , 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 , 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 , 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...
follow-up: 8 comment:6 by , 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 , 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.
comment:8 by , 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 , 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 , 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 , 17 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
The patch is applied (r11484).
Kosta, great job, big thanks!
comment:11 by , 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.
bug fix for BLOB support in the SQLite driver