db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Insert into Table (identity,CLOB) impossible ?? how to ?
Date Fri, 10 Aug 2007 12:16:13 GMT
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?


> 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

View raw message