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?
PS. Environment: Mule CE 3.6 and Mule Anypoint Studio. Postgres 9.2
I have no idea what this Mule thing is, but in Postgres there are two ways to get the generated ID:
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:
// 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);
rs = pstmt.getGeneratedKeys();
System.out.println("id:" + rs.getInt(1));