4 Replies Latest reply on May 14, 2014 7:24 AM by gadeyne.bram

    Generated Key from MySQL

    gadeyne.bram

      Hi,

       

      I'm using Teiid 8.6. My virtual database connects to a MySQL table that contains an autoincrement value.

       

      I'd like to know this inserted key so I'm using Statement.RETURN_GENERATED_KEYS. When the method executeUpdate is executed I get an error :

       

      Remote java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().

       

      Does Teiid support this functionality? In the documentation I found that it only works on columns that have a SERIAL option.

       

      With kind regards

      Bram

        • 1. Re: Generated Key from MySQL
          shawkins

          You are probably hitting [TEIID-2852] Retrieval of AUTO GENERATED Keys does not work with JDBC Translator - JBoss Issue Tracker

           

          Can you provide more of the stacktrace to confirm?

          • 2. Re: Generated Key from MySQL
            gadeyne.bram

            Oh, no I don't think so. I close the statement in a finally block.

             

            String query = "insert into patient_admission(patientid,admissiontime, dischargetime, bed, ward) values(?,?,?,?,?)";

            PreparedStatement pstmt=null;

            try{

                 pstmt=TeiidConnectionManager.getInstance().getStatementWithReturn(query);

                 pstmt.setX //setters for pstmt

             

                 pstmt.executeUpdate(); // At this line the exception is thrown

            } catch (SQLException e) {

                        log.log(Level.SEVERE,"Could not insert patient_admission",e);

                        return null;

                    }finally{

                        if(pstmt!=null){

                            try {pstmt.close();} catch (SQLException e) {}

                        }

                    }

             

            The getStatement method from TeiidConnectionManager looks like this:

             

            public PreparedStatement getStatementWithReturn(String query) throws SQLException{

                    return getConnection().prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

                }

             

            The getConnection method just sets up the the connection.

            • 3. Re: Generated Key from MySQL
              shawkins

              The issue affects the translator, not your client code.  Can you retry your scenario with 8.7?

              • 4. Re: Generated Key from MySQL
                gadeyne.bram

                Great! In 8.7 it works!

                 

                With kind regards

                Bram