| 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?
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,
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
the
> > 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
**)&locator,
> > 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,
(dvoid*)&locator,
> > 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,
(dvoid
> > *) buff, loblen, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *,
ub4*,
> > 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
> >>
> >
> >
> >
>
Received on Thu Sep 04 2003 - 14:22:03 CDT
 |
 |