[Springframework-developer] SqlUpdate and insert functionality

[Ken K. <kk...@kk...>]

Hi Isabelle,

I have a problem using the following petclinic class that inserts a new 
visit into the DB :

    class NewVisit extends SqlUpdate {
       
        public NewVisit(DataSource ds) {
            super(ds, "INSERT INTO visits VALUES(?,?,?,?)");
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.DATE));
            declareParameter(new SqlParameter(Types.VARCHAR));
            compile();
        }
       
        public int insert(Visit visit) {
            KeyBinder keybinder = new KeyBinder() {
                public void bind(PreparedStatement ps, Object obj) 
throws SQLException {
                    ps.setObject(1, obj);
                }
            };
           
            MySQLMaxValueIncrementer incr = new 
MySQLMaxValueIncrementer(getDataSource(), "visits_seq", "seq", 1);
           
            logger.info("Visit petId = " + visit.getPetId());
           
            Object[] objs = new Object[] {
                null,
                new Integer(visit.getPetId()),
                visit.getVisitDate(),
                visit.getDescription()
            };
           
            JdbcTemplate.InsertRetval retVal = update(objs, keybinder, 
incr, Integer.class);
            visit.setId(((Integer) retVal.getKey()).intValue());
           
            logger.info("Visit id = " + visit.getId() + "   petId = " + 
visit.getPetId());
           
            return retVal.getRowsAffected();
        }
       
    }

My table definitions for visits and its sequencer are :

CREATE TABLE visits (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  pet_id INT(4) UNSIGNED NOT NULL REFERENCES pets(id),
  visit_date DATE,
  description VARCHAR(255),
  PRIMARY KEY(id),
  INDEX(pet_id)
);
INSERT INTO visits VALUES (1, 7, '1996-03-04', 'rabies shot');
INSERT INTO visits VALUES (NULL, 8, '1996-03-04', 'rabies shot');
INSERT INTO visits VALUES (NULL, 8, '1996-06-04', 'neutered');
INSERT INTO visits VALUES (NULL, 7, '1996-09-04', 'spayed');

CREATE TABLE visits_seq (
  seq INT(4) UNSIGNED NOT NULL
);
INSERT INTO visits_seq VALUES (5);


The data is written correctly to the DB using the auto-incremented 
visit_id. The problem is that the getKey() function of the 
returned InsertRetval returns 0, not the id that was used for the 
insert. I wanted to use the value to update my cache directly without 
having to requery the DB for all this pet's visits. I am working around 
it by doing just that.


I include below a relevant snippet from the INFO log:

2003-05-22 22:53:37,966 INFO [petclinic.support.ClinicImpl$NewVisit] - 
<Compiled OK>
2003-05-22 22:53:37,996 INFO [petclinic.support.ClinicImpl$NewVisit] - 
<Visit petId = 7>
2003-05-22 22:53:38,006 INFO [com.interface21.jdbc.object.SqlUpdate] - 
<Compiled OK>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.core.JdbcTemplate] - 
<JDBCTemplate: update affected 1 rows>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.object.SqlUpdate] - 
<1 rows affected by SQL update [update visits_seq set seq = 
last_insert_id(seq + 1)]>
2003-05-22 22:53:38,026 INFO [com.interface21.jdbc.object.SqlFunction] - 
<Compiled OK>
2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] - 
<Executing SQL query using PreparedStatement: 
[PreparedStatementCreatorFactory.PreparedStatementCreatorImpl: 
sql={select last_insert_id()}: params={}]>
2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] - 
<JDBCTemplate: update affected 
com.interface21.jdbc.core.JdbcTemplate$InsertRetval@1ebe3f0 rows>
2003-05-22 22:53:38,036 INFO [petclinic.support.ClinicImpl$NewVisit] - 
<Visit id = 0   petId = 7>

Note that the last line shows the visit_id == 0.
Also note the odd output of the 2nd last line where instead of 
outputting the no. of rows affected it's printing a toString() of the 
InsertRetval !!!???


Am I doing something wrong here ?


Regards,

Ken