Re: [Springframework-developer] SqlUpdate and insert functionality

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

Ken,

I'll have a look at it as soon as I can.

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