Babelfish for Aurora PostgreSQL not returning generated row id

Question

We recently converted our SQL Server database to AWS Aurora PostgreSQL(v.13.6).

We're using Babelfish for Aurora PostgreSQL to connect to the database with Java 8 & JDBC & SQL Server driver. While testing, we discovered that Babelfish does NOT return generated row id on SQL insert statements (code below). How can this be fixed?

SQL Server driver:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>10.2.1.jre8</version>
 </dependency>

However, the same test code works when using the PostgreSQL URL & postgres driver. (returns the generated row id)

Postgres driver:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.0</version>
</dependency>

Test code:

    private void testInsert(String dbUrl, String sql) {
        logger.debug("dbUrl: {}", dbUrl);
        try (Connection conn = DriverManager.getConnection(
                dbUrl, DBUtil.DB_USERNAME, DBUtil.DB_PASSWORD);
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            setValues(ps);
            conn.setAutoCommit(false); // begin Transaction
            logger.debug("executeUpdate: {}", sql);

            int rows = ps.executeUpdate();

            logger.debug("rows: {}", rows);
            
            try (ResultSet rs = ps.getGeneratedKeys()) {
                logger.debug("rs: {}", rs);
                if (rs.next()) {
                    Long id = rs.getLong(1);
                    logger.debug("id: {}", id);
                }
                else {
                    logger.debug("ResultSet is empty");
                }
            }
          
            conn.rollback();
 //           conn.commit();
        } catch (SQLException e) {
            logger.error(null, e);
        }
    }

Output:

# results with Babelfish URL & SQL Server driver: (returns 0 for row ID)
rows: 1
rs: SQLServerResultSet:1
id: 0

# results with PostgreSQL URL & postgres driver: (returns the row ID)
rows: 1
rs: org.postgresql.jdbc.PgResultSet@2aceadd4
id: 1548

Babelfish for Aurora PostgreSQL not returning generated row id

0 Answers0