Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Exclamation Unanswered: Mule, Postgres and Auto Generated Keys

    I am using Mule and Postgres and I have a trivial task to insert two rows in two related tables. The primary key of the first table is a foreign key in the second one.
    So to insert the second row I need to get autogenerated key and put it into the second one.

    The problem is that Mule returns error:

    Root Exception stack trace:
    org.postgresql.util.PSQLException: Returning autogenerated keys is not supported.
    Is it a problem related only to the Postgres database engine? If so, what is the best way to get Auto Generated Key from the database?

    Regards,

    PS. Environment: Mule CE 3.6 and Mule Anypoint Studio. Postgres 9.2

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I have no idea what this Mule thing is, but in Postgres there are two ways to get the generated ID:
    Code:
    select lastval();
    or
    insert into foo (...) values (...) returning id;
    Your error message looks like a Java/JDBC Exception. In that case, you will need to show us the code that generates that exception. Using JDBC the following works for me:
    Code:
    // table foo is defined as: create table foo (id serial not null, bar text);
    PreparedStatement pstmt = con.prepareStatement("insert into foo (bar) values (?)", PreparedStatement.RETURN_GENERATED_KEYS);
    pstmt.setString(1, "foobar");
    pstmt.executeUpdate();
    rs = pstmt.getGeneratedKeys();
    rs.next();
    System.out.println("id:" + rs.getInt(1));
    Maybe you are using an outdated JDBC driver
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2015
    Posts
    3

    Post Mule or JDBC: that's question

    Hello,
    Thank you for the prompt reply.

    The driver is the latest postgresql-9.3-1102.jdbc41.jar (also I have tried different ones)
    Mule code won't tell anything at this stage. It is just a configuration.

    But people from MuleSoft point to Postgres... And there seems to be no way to say either side.
    I will try different options and in the mean time thank you very much for your support.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by xata777 View Post
    But people from MuleSoft point to Postgres... And there seems to be no way to say either side.
    You can show them my sample code. This the way it is defined in the JDBC API.
    If they can't implement that, there is something seriously wrong with that library
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •