Announcement Announcement Module
Collapse
No announcement yet.
Updates with JdbcTemplate not being commited Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Updates with JdbcTemplate not being commited

    Hi, I'm trying to update a record in a MS SQL 2000 Db using the JdbcTemplate.Update method. While using the JdbcTemplate to query the database works correctly, the update method has no effect. My java code which attempts to update the DB is as follows:

    Code:
        public int updateRank(Object[] statement) {
            try {
                String sql = "UPDATE ValidRank SET ?=? WHERE [Id]=?";
                return this.jdbcTemplate.update(sql, statement);
            } catch (Exception e) {
                MessageLog.logError(e, "Error in updateRank");
                return -1;
            }
        }
    The object being passed contains the relevant values and the Id of the row to be updated. The SQL throws no errors and the same SQL with the relevant values updates the row when entered using MS query browser. The method also returns 1 when called, presumably because it thinks it has updated 1 row in the DB. However no changes are ever committed to the DB. There are no transactional features being used and my connection string is as follows:

    Code:
    <Resource name="jdbc/personnel" 
    url="jdbc:microsoft:sqlserver://somewhere.domain.org:1433;databasename=xxx;user=xxx;password=xxx"
    auth="Container" defaultAutoCommit="true" 
    driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver" 
    type="javax.sql.DataSource" />
    I've got no clue as to why this is happening, so I'd appreciate some pointers. This is all happening in Tomcat 6.0 by the way.

  • #2
    Configure transactions. Without transactions nothing is going to be inserted/updated to the database.

    Comment


    • #3
      Are you sure "... SET ?=? ..." is valid usage for SQL Server. Looks suspicious to me to pass in a column name as a parameter. Try "... SET " + columnName + "=? ..."

      Comment


      • #4
        It appears having the field name within the statement was indeed causing the problem. Changing the update method to the following fixed the issue:

        Code:
            public int updateRank(String fieldName, Object[] statement) {
                try {
                    String sql = "UPDATE ValidRank SET " + fieldName + "=? WHERE [Id]=?";
                    return this.jdbcTemplate.update(sql, statement);
                } catch (Exception e) {
                    MessageLog.logError(e, "Error in updateRank");
                    return -1;
                }
            }
        Thanks everyone for the input.

        Comment

        Working...
        X