Announcement Announcement Module
Collapse
No announcement yet.
Oracle field generated in trigger before_insert Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle field generated in trigger before_insert

    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.

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

    Comment


    • #3
      can't make it work

      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.

      Comment


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

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

        Comment


        • #5
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


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

                Comment


                • #9
                  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

                  Comment

                  Working...
                  X