Insert into Table (identity,CLOB) impossible ?? how to ?

["Van Vlierberghe, Steven" <st...@esko.com>]

Use case:
  MyTable (id identity, data CLOB)
  insert some bytes into the table and return the id of the record

Problem:
1. I need the generated key (identity), so I cannot use a
PreparedStatement
beause with Derby, one can only get the generated key via
Statement.execute (sql, RETURN_GENERATED_KEYS);

2. Hence, as a PreparedStatement cannot be used, a Statement must be
used
Now, of course, I do not want (or even can) to form a String for an SQL
insert statement in which the CLOB is included .

3. Euh, that brings me , in order to insert an CLOB, to create a record
first, get the generated key and then update that record with the clob
data
So, in 2 phases
  first:  id = DoInsert ();
  second:  DoUpdate (id,data);
Tried this as well,
if one does:      stm.execute ("insert into MyTable")
or stm.execute ("insert into MyTable () values ()")
dont' go  :   syntax errors

So, how to insert some bytes into a table with an identity column and
have the identity value as return value ??

Any solution welcome
Thanks

steven
svv@esko.com

Re: Insert into Table (identity,CLOB) impossible ?? how to ?

[Mark Thornton <mt...@optrak.co.uk>]

Jim Newsham wrote:
> Hi,
>
> I have a related question.
>
> Is getGeneratedKeys() any more efficient than executing a second query of
> "values identity_val_local()"?  I'm using the latter everywhere, and wonder
>   
Not when I tested it recently. In fact the second query was slightly 
faster! A bit disappointing really.

Mark Thornton

RE: Insert into Table (identity,CLOB) impossible ?? how to ?

[Jim Newsham <jn...@referentia.com>]

Hi,

I have a related question.

Is getGeneratedKeys() any more efficient than executing a second query of
"values identity_val_local()"?  I'm using the latter everywhere, and wonder
if there's anything to be gained from changing to the former.  Using
embedded mode.

Thanks,
Jim


> -----Original Message-----
> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
> Sent: Friday, August 10, 2007 2:16 AM
> To: Derby Discussion
> Subject: Re: Insert into Table (identity,CLOB) impossible ?? how to ?
> 
> Van Vlierberghe, Steven wrote:
> > Use case:
> >   MyTable (id identity, data CLOB)
> >   insert some bytes into the table and return the id of the record
> >
> > Problem:
> > 1. I need the generated key (identity), so I cannot use a
> PreparedStatement
> > beause with Derby, one can only get the generated key via
> > Statement.execute (sql, RETURN_GENERATED_KEYS);
> 
> Hi Steven,
> 
> That is not correct.
> You can use a PreparedStatement by using Connection.prepareCall(String
> sql, int generatedKeys). After you have executed it, you can obtain a
> resultset with the autogenerated key by using
> PreparedStatement.getGeneratedKeys() (the method is actually inherited
> from Statement).
> 
> 
> 
> Note that Derby is a bit limited regarding auto-generated keys. I don't
> think you can use the prepare-methods taking an array as input for the
> keys. Also, not sure about this, but isn't it currently only possible to
> have one auto-generated key column per table?
> 
> 
> 
> regards,
> --
> Kristian
> 
> >
> > 2. Hence, as a PreparedStatement cannot be used, a Statement must be
> used
> > Now, of course, I do not want (or even can) to form a String for an SQL
> > insert statement in which the CLOB is included .
> >
> > 3. Euh, that brings me , in order to insert an CLOB, to create a record
> > first, get the generated key and then update that record with the clob
> data
> >
> > So, in 2 phases
> >   first:  id = DoInsert ();
> >   second:  DoUpdate (id,data);
> > Tried this as well,
> > if one does:      stm.execute ("insert into MyTable")
> > or stm.execute ("insert into MyTable () values ()")
> > dont' go  :   syntax errors
> >
> > So, how to insert some bytes into a table with an identity column and
> > have the identity value as return value ??
> >
> > Any solution welcome
> > Thanks
> >
> > steven
> > svv@esko.com
> >
> 

Re: Insert into Table (identity,CLOB) impossible ?? how to ?

[Kristian Waagan <Kr...@Sun.COM>]

Van Vlierberghe, Steven wrote:
> Use case:
>   MyTable (id identity, data CLOB)
>   insert some bytes into the table and return the id of the record
> 
> Problem:
> 1. I need the generated key (identity), so I cannot use a PreparedStatement
> beause with Derby, one can only get the generated key via 
> Statement.execute (sql, RETURN_GENERATED_KEYS);

Hi Steven,

That is not correct.
You can use a PreparedStatement by using Connection.prepareCall(String 
sql, int generatedKeys). After you have executed it, you can obtain a 
resultset with the autogenerated key by using 
PreparedStatement.getGeneratedKeys() (the method is actually inherited 
from Statement).



Note that Derby is a bit limited regarding auto-generated keys. I don't 
think you can use the prepare-methods taking an array as input for the 
keys. Also, not sure about this, but isn't it currently only possible to 
have one auto-generated key column per table?



regards,
-- 
Kristian

> 
> 2. Hence, as a PreparedStatement cannot be used, a Statement must be used
> Now, of course, I do not want (or even can) to form a String for an SQL 
> insert statement in which the CLOB is included .
> 
> 3. Euh, that brings me , in order to insert an CLOB, to create a record 
> first, get the generated key and then update that record with the clob data
> 
> So, in 2 phases
>   first:  id = DoInsert ();
>   second:  DoUpdate (id,data);
> Tried this as well,
> if one does:      stm.execute ("insert into MyTable")
> or stm.execute ("insert into MyTable () values ()")
> dont' go  :   syntax errors
> 
> So, how to insert some bytes into a table with an identity column and 
> have the identity value as return value ??
> 
> Any solution welcome
> Thanks
> 
> steven
> svv@esko.com
>