Oracle field generated in trigger before_insert

[fanfaron]

Hi I've been looking at the forum for my needs but can't find it.

I'm using spring JDBC and oracle server 8i,

I need to be able to retrieve a field value of the record that I have
inserted. I also need to retrieve the primary key that I have generated
using à sequencer in the before_update trigger of that table.

I saw on the petClinic example that these two databases use
the methods getIdentityQuery()

in hsqlDB, it uses "call identity"

in mysql, it uses "select last_insert_id()"

is there a equivalent in Oracle

or I have to make query to get the record I have inserted?

thanks a lot in advance.

[croco]

Why not use java.sql.Statement.getGeneratedKeys() method ?

[fanfaron]

Hi,

I try to use the class SqlUpdate with the class KeyHolder

and I can't make it work

My oracle server is 8.1.7.4
My jdbc is 10g, and I use the ojdb14.jar, I dowloaded the latest version.

Spring 1.2.5
Java jdk 1.5 + netbean 4.1

Here's my code

protected class DemandeInsert extends SqlUpdate {

protected DemandeInsert(DataSource ds) {

super(ds, "insert into cp_demandes (pre_demande, id_arrondissement, id_direction, " +
"id_service, id_statut) values (?,?,?,?,2)");

String[] nomChamp = {"ID_DEMANDE", "NO_DEMANDE"};

declareParameter(new SqlParameter(Types.VARCHAR));
declareParameter(new SqlParameter(Types.INTEGER));
declareParameter(new SqlParameter(Types.INTEGER));
declareParameter(new SqlParameter(Types.INTEGER));
setReturnGeneratedKeys(true);
setGeneratedKeysColumnNames(nomChamp);
compile();
}

protected void insert(DemandePersonnel demandePersonnel) {
Object[] objs = new Object[] {
demandePersonnel.getPreDemande(), new Integer(demandePersonnel.getIdArrondissement()),
new Integer(demandePersonnel.getIdDirection()), new Integer(demandePersonnel.getIdService())};
KeyHolder keyHolder = new GeneratedKeyHolder();

// super.update(objs);
super.update(objs, keyHolder);
retrieveKeyGenerated(demandePersonnel, keyHolder);
}

the constructor DemandeInsert works fine

things get weird when executing this line:
super.update(objs, keyHolder);

it throws exception like this :
PreparedStatementCallback; uncategorized SQLException for SQL [insert into cp_demandes (pre_demande, id_arrondissement, id_direction, id_service, id_statut) values (?,?,?,?,2)]; SQL state [null]; error code [17412]; La longueur du type est supérieure à la valeur maximale; nested exception is java.sql.SQLException: La longueur du type est supérieure à la valeur maximale
java.sql.SQLException: La longueur du type est supérieure à la valeur maximale

sorry there's a bit a french in that.

what I want to know is : is this the right way to use sqlupdate with a keyholder.

thank you

otherwise, I will try to use straight JDBC.

thank you very much.

[trisberg]

You need to specify the name(s) of the generated columns - otherwise you get the rowid back.

setGeneratedKeysColumnNames(String[] names)

This is a method on SqlUpdate not the KeyHolder.

Returning the generated column values is a new Orackle feature and I'm not sure the latest 10gR2 driver is compatible with 8i - see http://www.oracle.com/technology/tec...dbc/index.html

[fanfaron]

Hi,

that's what I did

protected DemandeInsert(DataSource ds) {

super(ds, "insert into cp_demandes (pre_demande, id_arrondissement, id_direction, " +
"id_service, id_statut) values (?,?,?,?,2)");

String[] nomChamp = {"ID_DEMANDE", "NO_DEMANDE"};

declareParameter(new SqlParameter(Types.VARCHAR));
declareParameter(new SqlParameter(Types.INTEGER));
declareParameter(new SqlParameter(Types.INTEGER));
declareParameter(new SqlParameter(Types.INTEGER));
setReturnGeneratedKeys(true);
setGeneratedKeysColumnNames(nomChamp);
compile();
}

There's the line setGeneratedKeysColumnNames(nomChamp);

I checked on oracle site, it says that the newest JDBC driver is backward
compatible till 8.1.7

So maybe I should try to use a oracle jdbc 9i?

I have setReturnGeneratedKeys(true);
after I have setGeneratedKeysColumnNames(nomChamp);

when I execute my query, I use the update(objs, keyHolder);

is this the correct way to use get generated fields?

thank you again

[trisberg]

You're right I missed that part at first read. That looks like the correct way - maybe it is a problem with running against 8i. If you could try against at least a 9iR2 db then that would confirm it.

[zliu]

I was trying to use GeneratedKeyHolder to deal with the same problem: insert/trigger/new_id. Initially, I used the Oracle9.2 jdbc driver, ojdbc14.jar. The error was ORA-17023: Unsupported feature. It seems that Oracle9.2 jdbc driver might not be fully JDBC3.0 compatible. Then I downloaded Oracle10.2 jdbc driver and it worked. My environment is Spring 1.2.6, Oracle database 9.2 and Oracle jdbc driver 10.2. HTH.

[trisberg]

Right, this is only available in the most recent JDBC driver 10gR2. Any earlier version don't support this feature.

[fanfaron]

I try on a 10g database with 10g jdbc
and it just working great!!!

Oracle 8i doesn't support this functionnality, I guess

In oracle documentation, it says that the 10g jdbc is
compatible with 8.1.7, it seems that not every function
is implemented.

Thank you very much