Announcement Announcement Module
Collapse
No announcement yet.
getGeneratedKeys() no result, SqlUpdate / MSSQL Server Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • getGeneratedKeys() no result, SqlUpdate / MSSQL Server

    I'm in the process of learning Spring, and I've hit a snag when working with MS SQL Server. After creating the myusers application following the directions in "Spring Live" and using Hibernate as the database I want to port it to MS SQL Server.

    I'm using the JNetDirect JSQLConnect JDBC driver which support JDBC 3.0, and have verified that getGeneratedKeys() is returning the correct values when using regular JDBC code:

    int updateCount = st.executeUpdate("INSERT INTO app_user(first_name, last_name) VALUES ('Birger','Rud')", Statement.RETURN_GENERATED_KEYS);
    if (updateCount > 0 ) { //The insert was successful
    ResultSet keyValues = st.getGeneratedKeys();
    keyValues.next();
    System.out.println("Inserted row identity is:"+keyValues.getInt(1));
    }


    The above works fine, inserts the user and returns the generated key value correctly.

    I then tried the following:

    String sql = "INSERT INTO app_user (first_name, last_name) values (?, ?)";

    SqlUpdate su = new SqlUpdate(getDataSource(), sql);
    su.declareParameter(new SqlParameter("first_name", Types.VARCHAR));
    su.declareParameter(new SqlParameter("last_name", Types.VARCHAR));

    Object[] params = new Object[] {user.getFirstName(), user.getLastName()};
    KeyHolder keys = new GeneratedKeyHolder();
    su.update(params, keys);

    if (logger.isDebugEnabled())
    {
    logger.info("Key: " + keys.getKeyList());
    }


    The code above inserts the user correctly, but returns with no keys. I verifed that the SqlUpdate.update( ...) call to getGeneratedKeys() did not return any values:

    "[junit] SQL update affected 1 rows and returned 0 keys"


    Any help welcome.

  • #2
    That's odd. In order to debug this, could you run your code against MySQL to see if you see the same bahavior?

    Comment


    • #3
      Originally posted by trisberg
      That's odd. In order to debug this, could you run your code against MySQL to see if you see the same bahavior?
      MySQL works fine with no changes to code, only changed the datasource-definition.

      Comparing the JDBCTemplate to my JDBC test-application I found the major difference to be that it uses PreparedStatement instead of Statement. After rewriting the test to use PreparedStatement I was able to duplicate the error, so this is not a Spring issue.

      New code:

      PreparedStatement pst = con.prepareStatement("INSERT INTO app_user(first_name, last_name) VALUES ('Birger','Rud')");

      int updateCount = pst.executeUpdate();
      if (updateCount > 0 ) { //The insert was successful
      ResultSet keyValues = pst.getGeneratedKeys();
      keyValues.next();
      System.out.println("Inserted row identity is:"+keyValues.getInt(1));
      }


      Fails runtime on keyValues.next() due to keyValues = null.

      The previous version passes Statement.RETURN_GENERATED_KEYS as a parameter to Statement.executeUpdate whereas the new one using PreparedStatement does not explicitly ask to have the generated keys returned - maybe this is the real issue and where the MySQL / SQLServer drivers differ in behaviour?

      Comment


      • #4
        BTW, I posted this as an issue over at the JSQLConnect forum - we'll see if they respond.

        http://www.jnetdirect.com/forum/viewtopic.php?p=446#446

        Comment


        • #5
          Good that it's not a Spring issue. Have you tried using the jTDS diver?

          Comment


          • #6
            Originally posted by trisberg
            Good that it's not a Spring issue. Have you tried using the jTDS diver?
            Downloaded & tested with jTDS, exact same result - no generated keys returned. Also tried DataDirect driver - same result.

            Turns out this is a Spring issue after all. When adding the Statement.RETURN_GENERATED_KEYS as a parameter to the prepareStatement call in my test application it works. Either I'm using Spring wrong (not requesting generated keys to be returned) or Spring does not correctly pass this parameter.

            I will look around in the Spring-source and see if I can figure it out.

            Comment


            • #7
              As expected, I was using Spring wrong. :oops:

              Adding the following line and all is well.
              su.setReturnGeneratedKeys(true);

              Anyway, all that ends well...

              Comment


              • #8
                Exactly what i was looking for. thanks.

                Comment

                Working...
                X