Question about generated keys

[jujibo]

Is the first time I try Persist and it looks too easy to work with it.

Doing tests with autogenerated fields, I got it working properly. (I
can insert objects with no problems). I also want to point in the
manual it says to use:

@Column(autoIncrement=true)

and it gives me an error so I had to use:

@Column(autoGenerated=true)

I guess this is because the manual is not updated to the latest
version.

But my question is.. why when I do : persist.insert(item) for example,
the record is inserted properly but then if I check item.getId() it
gets 0 and not the generated key? as designed?

Is there a way to get the generated key using Persist? (Other than
using the jdbc getGeneratedKeys...)

Thanks

Julio Jiménez

[jujibo]

Nevermind , it was in the API :)

public void setUpdateAutoGeneratedKeys(boolean
updateAutoGeneratedKeys)

I'm testing it with H2 database, and looks working fine. How about
Derby and other databases?

And aggregate? may I create a class to receive the calculated values?
for example for select sum(field) from sometable

Thanks

Julio Jiménez.

[julio]

Hi,

Not all databases support updates on auto generated keys from a
prepared statement. Derby seems to support it in embedded mode, but
its JDBC driver metadata doesn't say so (I'll test it later and maybe
add some specific code to override support for Derby). As a rule of
thumb, as long as the JDBC driver connection metadata states it
supports fetching auto generated keys, Persist will respect it while
using setUpdateAutoGeneratedKeys(true).

Persist doesn't care how data gets produced in your SQL statements.
You can safely do something like
int total = persist.read(int.class, "select sum(field) from
sometable");

I hope this helps

[jujibo]

On 23 oct, 02:31, julio <jcam...@gmail.com> wrote:
> Hi,
>
> Not all databases support updates on auto generated keys from a
> prepared statement. Derby seems to support it in embedded mode, but
> its JDBC driver metadata doesn't say so (I'll test it later and maybe
> add some specific code to override support for Derby). As a rule of
> thumb, as long as the JDBC driver connection metadata states it
> supports fetching auto generated keys, Persist will respect it while
> using setUpdateAutoGeneratedKeys(true).
>

Yes, now I know it. When I did my questions was the first time I used
Persist (a 5-10 minutes of use) now I have been surfing in the API and
understand better how it works.

I'll test Derby too, I guess it must work (at last in embedded mode).
For other databases don't supporting (like FirebirdSQL) it isn't a big
problem because you always can retrieve the sequence mode and then
assign it to the field in your class.

For example in firebird you can do: int id = persist.read(int.class,
"SELECT NEXT VALUE FOR <SequenceName> FROM RDB$DATABASE;"); and then
yourobject.setId(id); and persist.insert(yourobject);

> Persist doesn't care how data gets produced in your SQL statements.
> You can safely do something like
> int total = persist.read(int.class, "select sum(field) from
> sometable");
>
> I hope this helps
>

Yes, I understand it (it's ok to retrieve a simple value), but I mean
when you retrieve more than one record and more than one field. Guess
I can create a class to store the values and then retrieve a List of
objects. Dunno if it works.

BTW, congrats for your project, Persist is really simple, easy to use
and powerful, and thanks for your help too.

Just curiosity, are you spanish, I see you stole my real name ;-)

Cheers

Julio

[jujibo]

On 23 oct, 02:31, julio <jcam...@gmail.com> wrote:

> Hi,
>
> Not all databases support updates on auto generated keys from a
> prepared statement. Derby seems to support it in embedded mode,

Derby supports it, but Persist is unable to manage it.

I create this table and try to insert a record:

persist.executeUpdate("create table nombres (id integer not null
GENERATED BY DEFAULT AS IDENTITY primary key, nombre varchar(50))");
persist.setUpdateAutoGeneratedKeys(true);
nombres r = new nombres();
r.setNombre("Jujibo");
persist.insert(r);

Then I get the follow exception:

Exception in thread "main" net.sf.persist.RuntimeSQLException: While
inserting instance of [testpersist.nombres] autoUpdateGeneratedKeys is
set to [true] but the database doesn't support this feature
at net.sf.persist.Persist.insert(Persist.java:1203)
Java result: 1

But really Derby supports it. I usually enable it in my DAO class in
the way:

stInsert = con.prepareStatement(sqlInsert,
Statement.RETURN_GENERATED_KEYS);

with Statement.RETURN_GENERATED_KEYS you can enable it.

Thanks.

Julio