Re: How to insert BLOB?

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
>>