Bug #16299
closed
BLOBs doesn't work in Oracle (in the WHERE clause)
0%
Description
In Oracle you cannot simply fetch data via SELECT from a BLOB field. Neither in when specified in the SELECT clause directly by name nor indirectly by *.
This fails:
SELECT "blob" FROM "blobtable"
SELECT * FROM "blobtable"
To overcome this problem DBAL could use CBLOB or long VARCHARS 2(4000) [yes, they me be up to 4000 chars!) instead. As TYPO3 usually stores only strings and not real binary data both variants are an option.
Alternatively TYPO3 could switch to type TEXT. So TEXT can be mapped to CBLOB.
(issue imported from #M3762)
Updated by Karsten Dambekalns over 18 years ago
Hi, any easy to reproduce example where this breaks something?
Updated by Martin Kutschker over 18 years ago
I have turned on DBAL logging and browsed in the BE. I got one entry from the indexed search (error ORA-00932):
SELECT * FROM "index_config" WHERE "hidden" = 0 AND ("starttime" = 0 OR "starttime" <= 1151427696) AND "set_id" = 0 AND "type" = 1 AND "table2index" = 'tt_content' AND (("alternative_source_pid" = '' AND "pid" = 104) OR ("alternative_source_pid" = '104')) AND "records_indexonchange" = 1
After more browsing I see that the indexer uses this statement on any insert/change of a tt_content record (alt_doc.php).
I don't know if it hurts the indexed, but I do know that if this statement fails other will fail too.
The odd thing is that using the Oracle web console (Application Express) I cannot use SELECT * from "pages". It'll break because of the BLOB.
But this statement in the log has no error:
SELECT * FROM "pages" WHERE "uid" = 104 AND "pages"."deleted" = 0
Are all logged queries checked for errors?
An Internet investigation reveild that one has to use an API to load BLOB fields.
Updated by Martin Kutschker over 18 years ago
More info:
While the standard behaviour of Oracle is to load LOBs seprately the PHP-API has the flag OCI_RETURN_LOBS to force the loading of them within the fetch command automatically.
Probably ADOdb uses this mode usually. The question is why the statement from the indexer is not exceuted with the flag set. It's in the DBAL log, so it must have been run through DBAL/ADOdb, mustn't it?
Updated by Martin Kutschker over 18 years ago
I have found the isue with SELECT from comment no.9149 (28.6.). The problem is the column alternative_source_pid. It's defined as a BLOB and you cannot compare a BLOB like that.
Updated by Karsten Dambekalns over 17 years ago
This particular bug has been fixed on March 1st by me in indexed search.
Generally I consider bugs caused by comparing to a LOB field in an inappropriate way a bug f the code doing the comparison, not of the DBAL.
Updated by Martin Kutschker over 17 years ago
This only proves we should be very careful, when using LOBs. Maybe this should be added to the coding guidelines.