| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to insert BLOB?
My application is only doing logging. I.e. a lot of inserts. I'm using
two different buffers in my application. The buffers are swapped every
second. One thread fills the buffer and another thread empties the buffer. So when the buffer is full I have a lot of data 1-2-300 rows to insert. The rows has been prepared using OCIBindArrayOfStruct() on onethread and I now has to execute the statement when buffers are swapped. This has worked just fine and I could insert 1-2-300 rows in one server round trip but with BLOBs this was not that easy anymore.
The buffering is implemented because the thread filling the buffer should not be blocked by a slow/disconnected/gone-away server. So two different threds are used for the purpose. The thread filling the buffer does OCIStmtPrepare(), OCIBindByPos() and OCIBindArrayOfStruct() so everything is ready to execute with other thread when buffers are swapped.
I just got another hint on OTN about using DML returning of the insert statements. This should be supported for array interface as well.
When I stored a unique locator as 'value' on filler-thread I got an error when executing the statement but if I reused the locators for every row I could insert BLOB data but it was the same data for every row. Not very good.
Maby I should try to look into DML and run OCILobWrite() on returning clause. I would very much like to keep my array interface.
Thanks,
Ole Hansen
Amit wrote:
> I am curious why do you want to use array interface. With Blob/Clob data it
> might be a costly choice.
> Please don't quote me on this but I think this is how it works. When you
> allocate a OCILobLocator, the server creates a temporary buffer so that you
> can send the data. Then with OCILobWrite, the data is transfered from the
> client to the server and then from server temporary area to the table. It is
> however not always that this will be the execution path as the client/server
> might also use the temporary client area to hold the data (don't know the
> criteria of choice here).
> In any case, if you are sending 200 LOBs in a single command, it will be a
> good amount of memory usage on the server as well as it will kill the
> performance.
>
> I would rather use a prepared statement, then create two LobLocators and
> bind them. Then execute 100 times updating the data each time. SInce you can
> reuse the locator, it should not be costly.
> You can also do this in a transaction if you would like to do all the 100
> rows or none.
>
> If you _really_ want to use array interface, you probably will need 200
> locators as each locator is a holder for 1 data unit. Also then you might
> need to do OCILobWrite in a loop to write to each locator individually.
> I think OCILobWrite is called after the execute though I don't have a code
> in front of me to prove that. You can probably do a small experiment and
> figure that out. ;-)
>
> HTH,
> -----------------------------------------------------------------
> Amit Jindal, Development Engineer
>
> Want ease of development or performance? Why not get both!
> http://www.roguewave.com/products/sourcepro/db/
>
> [Disclaimer: All views expressed here are my personal views and they can be
> incorrect at times]
> -----------------------------------------------------------------
>
> "Ole Hansen" <rm_oha_rm_at_city.dk> wrote in message
> news:3f55d93c$0$32461$edfadb0f_at_dread16.news.tele.dk...
>
>>Hi, >> >>Thanks for your reply. >> >>I finally succeeded to insert a row with BLOB data but I still have >>trouble using the array interface with BLOBs, i.e. OCIBindArrayOfStruct(). >> >>Do you know how to use the locator when using the array interface? >>If I want to insert say 100 rows with 2 colums of type BLOB each, should >>I do 2*100 OCIDescriptorAlloc(), 2*100 OCILobCreateTemporary(), and when >>should I call OCILobWrite() then? After calling OCIStmtExecute() or >>could that be done before? Or should I only use 2 locators for this >>scenario even though I have 100 rows when using the array interface? >> >>I have tried different things but often get the ORA-22275 error: invalid >>LOB locator specified. >> >>I have data available when I start building the statement, so I can do >>whatever is needed in any order. If I only knew how.. >> >>Thanks in advance. >>Br, >>Ole Hansen >> >> >>Amit wrote: >> >>>You need to first allocate a OCILobLocator, then bind that and execute
>>>query and then use OCILobWrite. Though it will only work with Oracle 9 >>>onwards. >>>Something like this (You might need some tweaks in this code below): >>> >>> OCIBind * bind; >>> OCILobLocator *locator = 0; >>> >>> char *insert_ = "INSERT INTO MYTABLE VALUES(:locator);"; >>> >>> check(err_, OCIDescriptorAlloc((dvoid *)env_, (dvoid
>>>OCI_DTYPE_LOB, 0, (dvoid**)0)); >>> cout << "Got descriptor" << endl; >>> >>> // Now create a temporary LOB >>> check(err_, OCILobCreateTemporary(context_, err_, locator, 0, >>>SQLCS_IMPLICIT, OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)); >>> cout << "Created Temp" << endl; >>> >>> check(err_, OCIStmtPrepare(stmt_, err_, (ub1*)insert_, >>>strlen(insert_), OCI_NTV_SYNTAX, OCI_DEFAULT)); >>> cout << "Created statement" << endl; >>> >>> check(err_, OCIBindByPos(stmt_, &bind, err_, 1,
>>>sizeof(OCILobLocator *), SQLT_BLOB, 0,0,0,0,0, OCI_DEFAULT)); >>> cout << "Statement Bound" << endl; >>> >>> check(err_, OCIStmtExecute(context_, stmt_, err_, 1, 0, >>>(OCISnapshot*)NULL, (OCISnapshot *)NULL, OCI_DEFAULT)); >>> cout << "Executed" << endl; >>> >>> ub4 amtp = LargeSize; >>> ub4 loblen = LargeSize; >>> check(err_, OCILobWrite(context_, err_, locator, &amtp, 1,
>>>*) buff, loblen, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *,
>>>ub1*) >>>) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT) ); >>> cout << "Written" << endl; >>> >>> OCITransCommit(context_, err_, 0); >>> cout << "Commited. Done!" << endl; >>> >>> >>>----------------------------------------------------------------- >>>Amit Jindal, Development Engineer >>> >>>Want ease of development or performance? Why not get both! >>>http://www.roguewave.com/products/sourcepro/db/ >>> >>>[Disclaimer: All views expressed here are my personal views >>> and they can be incorrect at times] >>>----------------------------------------------------------------- >>> >>> >>>"Ole Hansen" <rm_oha_rm_at_city.dk> wrote in message >>>news:3f54aba0$0$32520$edfadb0f_at_dread16.news.tele.dk... >>> >>> >>>>Hi, >>>> >>>>Is it possible to insert BLOB data without doing a select first? I have >>>>data available and want to insert a row with BLOB data. >>>> >>>>Should I use the callback function with OCILobWrite() or is it not >>>>possible at all without doing a select? >>>> >>>>How about using a temporary LOB? I have tried everything I could come up >>>>with but nothing seems to work. >>>> >>>>Thanks in advance? >>>>Br, >>>>Ole Hansen >>>> >>> >>> >>>
 |
 |