Hi,
I need to read and write BLOBs (ca 50 - 100MB each) as fast as possible.
I don't want to allocate the whole BLOB into memory at any time, but rather use some streaming funtionality.
The Oracle LOB manual states: "The most efficient way to write large amounts of LOB data is to use OCILobWrite2() with the streaming mechanism enabled, and using polling or a callback "
What is the best way to implement this using ODAC?
Method1:
MyOraLob = MyOraTable->GetLob("MYBLOBFIELD");
MyOraLob->Write(...); //loop this statement until whole blob is written
Method2:
MyOraTableMYBLOBFIELD->LoadFromStream(...);
Method3:
MyOraStoredProc->ParamByName('blob')->AsBLOBLocator->LoadFromStream(...);
MyOraStoredProc->Execute();
Or maybe some other way that I've not yet discovered???
Are there different pro's con's of the various methods?
/Leif
Fastest way to write/read BLOBs
The performance of all the methods that you have listed is approximately equal.
When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. In this case BLOB values will not be stored in the dataset.
When writing BLOBs to database whole BLOB value is always stored in the memory. We recommend to use the way when a BLOB value is written via parameters like your Method3. In this case only one value is held in the memory. The TOraTable component holds in the memory values for all records which BLOB fields was changed.
When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. In this case BLOB values will not be stored in the dataset.
When writing BLOBs to database whole BLOB value is always stored in the memory. We recommend to use the way when a BLOB value is written via parameters like your Method3. In this case only one value is held in the memory. The TOraTable component holds in the memory values for all records which BLOB fields was changed.
Okay, but isn't there any way of operating directly on a BLOB?
The Oracle manual points to either the LOBLocator API's ( OCILobWrite2() ) or the "Data Interface for persistent LOBs".
I was hoping that I could get a LOBLocator (wrapped in the TOraLob class?) and directly write to the LOB in database.
If I can't do that with ODAC, is there any way I can do that by manually calling OCI functions, or have I got it all wrong?
The Oracle manual points to either the LOBLocator API's ( OCILobWrite2() ) or the "Data Interface for persistent LOBs".
I was hoping that I could get a LOBLocator (wrapped in the TOraLob class?) and directly write to the LOB in database.
If I can't do that with ODAC, is there any way I can do that by manually calling OCI functions, or have I got it all wrong?
Use the OCILobLocator property of TOraLob to get an OCI locator. Note that the LOB locator returned by a SQL query cannot be used after the transaction is committed.
When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. Also set DeferredLobRead in TOraDataSet.Options to True. When DeferredLobRead is True the dataset do not read LOB values on opening. LOB values are fetched only when they are explicitly requested.
When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. Also set DeferredLobRead in TOraDataSet.Options to True. When DeferredLobRead is True the dataset do not read LOB values on opening. LOB values are fetched only when they are explicitly requested.
OK, I'll try to implement this manually using the OCILobLocator.
But, would you consider extending the functionality of ODAC to include direct streamed writing and reading of BLOBs?
I think it would be really useful for the kind of applications that must handle really large BLOBs.
Another way might be to implement the "BLOB Append" function.
/Leif
But, would you consider extending the functionality of ODAC to include direct streamed writing and reading of BLOBs?
I think it would be really useful for the kind of applications that must handle really large BLOBs.
Another way might be to implement the "BLOB Append" function.
/Leif