Re: [Springframework-developer] SqlUpdate and insert functionality

[Isabelle M. <isa...@me...>]

Hi Ken,

Looking at the mail you sent me this morning (included below), in table visits, column id is auto-increment, and it shouldn't be. Neither should the sequence column be, which is correct in the code below.

The definition of column id should be "int not null primary key"

Does this help?

Isabelle

On Fri, May 23, 2003 at 09:04:01AM -0500, Ken Krebs wrote:
> Isabelle,
> 
> My key column isn't auto-incremented, only the actual id column is.
> 
> My latest code snapshot is attached.
> 
> Ken
> 
> 
> Isabelle Muszynski wrote:
> 
> >Hi Ken,
> >
> >Your key column should NOT be auto-increment.
> >
> >If that doesn't solve the problem, let me know, and pls attach the code so 
> >I don't have top type it over. Will look at it tonight or tomorrow. I'll 
> >also look into the strange log output, probably a stupid booboo.
> >
> >Isabelle
> >
> >On Thu, May 22, 2003 at 11:25:08PM -0500, Ken Krebs wrote:
> > 
> >
> >>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
> >>
> >>
> >>
> >>   
> >>
> >
> > 
> >
> 



-- 
Isabelle Muszynski
Software Engineer
Zandweellaan 4
2660 Antwerpen
Belgium
Tel. 32-(0)3-830 18 54
Mobile: 32-(0)485 49 50 89
Email: isa...@me...
Website: www.meta-logix.com