Re: [Springframework-developer] SqlUpdate and insert functionality

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

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