bulk insert & auto-incremented column

[Alec Wysoker]

Hi Max,

I thought I read about this on the list a while back, but I can't find
the message. Is the id column of a KeyedEntity not set during bulk
insert? Is this simply not possible, or just not implemented yet.

Thanks, Alec

[Maxime Lévesque]

Here is the thread :

  http://groups.google.com/group/squeryl/browse_thread/thread/76edf4f191c489b0

There are two strategies for auto increment generation on the database side :

 1) auto increment as a behavior of the column  (MySQL, MSSQL, H2)
 2) the use if a sequence object (Oracle, DB2, Postgres)

In order to fetch the DB generated keys, it is nice to be able to do it in the same database roundtrip as
the insert call.

for (1) there is a JDBC feature getGeneratedKeys, see :
  http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29

 In theory it should work also with bulk insert, I'm hoping that it does, but havent tried it yet.

for (2) most likely the best that can be done is with two database trips, *but only if* there is a way to get
multiple "nextvals" to the sequence (in a single DB call) :

   http://forums.oracle.com/forums/thread.jspa?threadID=1113582

   let ROW_COUNT be the number of rows in the bulk insert

   i)   val  pks = "get ROW_COUNT PKs from the sequenec"
   ii)  assign(pks) to the iterable of object to insert
   iii) do a normal bulk insert...

As you can see, it will be harder to implement for database that use sequences, I wish there was something like :

 val pks = "select mySequence.nextVals(N) from dual"

which would return a list of PKs

So to answer the question : it isn't implemented yet, and there is a bit of reaseach to be done,
for databases like oracle, postgres, and BD2..

Cheers

[Alec Wysoker]

thanks!

2010/12/10 Maxime Lévesque <maxime....@gmail.com>:

[tksfz]

Hi,

With respect to Oracle, there are a few methods documented here for
retrieving auto-generated id's:

http://stackoverflow.com/questions/1976625/value-from-last-inserted-row-in-db

The typical method people use is an INSERT ... RETURNING INTO clause.
It looks like there's also a JDBC 3 method executeUpdate(sql, string
array) that will let you retrieve the values for the columns specified
in the string array. The RETURNING clause requires an anonymous pl/
sql block and can work with bulk inserts.. the pl/sql gets a little
complicated for bulk inserts though.

However, it wouldn't fit nicely with the existing code which seems to
use the addBatch/executeBatch approach. I like your proposal to fetch
all the sequence values and set those - although I'm not sure how
you'd want to handle errors. You can fetch multiple sequence values
as described here:

http://forums.oracle.com/forums/thread.jspa?threadID=1113582
select level,seq1.nextval from dual
connect by level<=10;

On Dec 10, 4:55 am, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Here is the thread :
>

> http://groups.google.com/group/squeryl/browse_thread/thread/76edf4f19...

>
> There are two strategies for auto increment generation on the database side
> :
>
>  1) auto increment as a behavior of the column  (MySQL, MSSQL, H2)
>  2) the use if a sequence object (Oracle, DB2, Postgres)
>
> In order to fetch the DB generated keys, it is nice to be able to do it in
> the same database roundtrip as
> the insert call.
>
> for (1) there is a JDBC feature getGeneratedKeys, see :
>

> http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#...

[Alec Wysoker]

Hi,

What kind of errors are you concerned about with fetching multiple
sequences values before doing the insert?

Thanks, Alec

[tksfz]

I was thinking for example if the transaction fails and you rollback
everything, you may want to un-set the id's that you'd set.

On Dec 26, 7:23 pm, Alec Wysoker <al...@pobox.com> wrote:
> Hi,
>
> What kind of errors are you concerned about with fetching multiple
> sequences values before doing the insert?
>
> Thanks, Alec
>
>
>
>
>
>
>
> On Sat, Dec 25, 2010 at 11:20 PM, tksfz <analytic...@yahoo.com> wrote:
> > Hi,
>
> > With respect to Oracle, there are a few methods documented here for
> > retrieving auto-generated id's:
>

> >http://stackoverflow.com/questions/1976625/value-from-last-inserted-r...

[tksfz]

Hmm I suppose you may just be able to set the id's afterwards, once
the transaction's succeeded...

[Rudolf Bargholz]

Hi Max,

The following can be used in DB2 to fetch a list multiple sequences, in the case below the next 5 sequences:

WITH temp1 (n1) AS

(VALUES 1 

 UNION ALL

 SELECT n1 + 1

 FROM   temp1

 WHERE  n1 < 5

)

SELECT NEXTVAL FOR MySequence

 AS seqnr                                        

FROM   temp1  

where MySequence is a sequence object in DB2.

Regards

Rudolf

[Alec Wysoker]

It occurred to me that it probably isn't necessary to un-set the ids
if the transaction fails. After all, this doesn't happen for single
insert. Setting the ids after the transaction has completed won't
work for me, because the whole point is to be able to insert a bunch
of entites, and then insert other entities that use the ids of the
first entities as foreign keys, all in the same transaction.

-Alec