Re: [Springframework-developer] SqlUpdate and insert functionality

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

Hi Ken,

I've been able to reproduce your problem. So I'm going to look further into it.

Isabelle

On Fri, May 23, 2003 at 09:24:45AM +0200, 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
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
> 
> 

-- 
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