Re: insert clob into table

From: steve <steve_at_aol.com>
Date: Thu, 9 Mar 2006 07:06:43 +0800
Message-ID: <duno2311t8q@news4.newsguy.com>

On Fri, 3 Mar 2006 23:13:30 +0800, mwojtow wrote (in article <du9meq$sfo$1_at_kujawiak.man.lodz.pl>):

> steve <steve_at_aol.com> wrote:
>> On Thu, 2 Mar 2006 22:10:23 +0800, amogh wrote
>> (in article <4406F9FF.7030305_at_gmail.com>):
>> 
>>> mwojtow wrote:

>>>> I have got table with many fields - including clob. And I would like to
>>>> put data using INSERT command from C++ using OCCI.
>>>> example below (not working :()
>>>>
>>>> Clob clob(conn); //existing connection object
>>>> string s("looooong string"); // assume that this string is 8k long
>>>> int s_size=s.length();
>>>> clob.setEmpty();
>>>> string sql="INSERT INTO tbl (string_col, clob_col) VALUES (:1,:2)";
>>>> stmt->setSQL(sql);
>>>> stmt->setString(1,"aaaa");
>>>> clob.write(s_size,(unsigned char *)s.c_str(),s_size);
>>>> stmt->setClob(2,clob);
>>>> stmt->executeUpdate();
>>>>
>>>> And this is not working.I get error
>>>> ORA-22275: invalid LOB locator specified

>>>>> (

>>>> If I changed lines
>>>>
>>>> clob.write(s_size,(unsigned char *)s.c_str(),s_size);
>>>> stmt->setClob(2,clob);
>>>>
>>>> to
>>>>
>>>> stmt->setString(2,s);
>>>>
>>>> clob field has only part of string s value :(
>>>>
>>>> primary key is timestamp field and is set to default TIMESTAMP_NOW so I
>>>> cannot get it's value :(
>>>>
>>>> database is on another server then client I run so I prefer not to set
>>>> primary key to client local timestamp which can be different from server
>>>> one.
>>>> I'm using Oracle 9i database and client.
>>>> Please help!

>>> 
>>> You will have to first insert an empty clob. SELECT the clob
>>> descriptor column and then UPDATE the clob. Take a look at the 
>>> occiclob.cpp sample here:
>>> 
>>> 
>> http://www.oracle.com/technology/sample_code/tech/occi/files/
>> occi10gsamples.ta
>> 
>>> r.gz
>>> 
>>> Rgds.
>>> Amogh
>> 
>> the way i do it , is when i define the table, set the  "default value" to   
>> empty_clob()
>> 
>> that sets it up automatically.
>> then you can just stuff your text in.
> the problem exists if you cannot identify record you have just inserted.
> my primary key is set automaticaly on server. after inserting I'm not
> sure if somene else has not set another record into table so I cannot
> just take the last record. and combination of fields is not unique!
> but I solved it:)
> I used setString on clob column and it seems to work:) thanks everyone
> for advices.

cannot identify records you just inserted?

use the "returning" clause for the sql statement & return a key that identifies the record.

but that said , you better be setting your clob to Empty_clob() , before putting anything into it or yuo will run into problems later. Received on Wed Mar 08 2006 - 17:06:43 CST