db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: Insert into Table (identity,CLOB) impossible ?? how to ?
Date Fri, 10 Aug 2007 20:00:17 GMT


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




Mime
View raw message