Menu
▾
▴
Re: RE : [Springframework-developer] SqlUpdate and insert functionality
|
From: Isabelle M. <isa...@me...> - 2003-05-24 09:18:37
|
Hi Jean-Pierre,
As I said in the mail I sent a minute ago, the fundamental difference between Oracle and MySQL is that in the first you can get the next value beforehand, while in the latter you get if afterwards. So I don't think your way would work.
Anyway, the thing is badly broken right now.
Isabelle
On Sat, May 24, 2003 at 12:41:36AM +0200, JP Pawlak wrote:
> Ken,
>
> If the javadoc says that a column with auto_increment is to be used,
> it's clearly a mistake.
> But that doesn't solve the issue!
> I used a similar approach with the old framework, but without the Binder
> technique. I get manually the nextValue in each DAO and set its value
> like the others parameters in the callback method and it works fine.
> For now, the Binder mechanism has an open issue (ref last Isabelle's
> post).
>
> Regards,
> Jean-Pierre
>
> -----Message d'origine-----
> De : spr...@li...
> [mailto:spr...@li...] De la
> part de Ken Krebs
> Envoyé : samedi 24 mai 2003 00:09
> ÃÂ : Isabelle Muszynski
> Cc : spr...@li...
> Objet : [Springframework-developer] SqlUpdate and insert functionality
>
> Isabelle,
>
> I don't understand this. I thought auto_increment on the visits id
> column is what makes it work. The javadoc for MySQLMaxValueIncrementer
> says that is to be used with an auto_increment column.
>
> I tried removing the auto_increment as you suggested and it no longer
> works.
>
> Ken
>
> As I said earlier:
>
> "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."
>
> Isabelle Muszynski wrote:
>
> 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
|
