Menu
▾
▴
Re: RE : RE : [Springframework-developer] SqlUpdate and insert functionality
|
From: Isabelle M. <isa...@me...> - 2003-05-24 16:42:27
|
Hi Jean-Pierre,
On second thought, it has to be done your way. Otherwise I'd still have the deferred binding problem. Am implementing it as I write this mail.
Isabelle
On Sat, May 24, 2003 at 12:41:20PM +0200, JP Pawlak wrote:
> Hi Isabelle,
>
>
>
> Why should code such as below not work?
>
> Both for Oracle and MySql, the Incrementer is able to provide a key before the main statement. Letting the developer request for a key and letting him handling this value as it came from the user request will always be possible. There is no always need for sophistication.
>
> If the keys are picked by bunches, we don’t have two database requests.
>
>
>
> Modified test:
>
> /**
>
> * Test an insert that is using sequencing
>
> * @throws Exception if anything goes wrong
>
> */
>
> public void testInsertWithSequence() throws Exception {
>
>
>
> int[] types = new int[] { Types.INTEGER, Types.INTEGER };
>
> Object[] params = new Object[] { null, new Integer(1) };
>
>
>
> JdbcTemplate tpl = new JdbcTemplate(ds);
>
> MySQLMaxValueIncrementer incr = new MySQLMaxValueIncrementer(ds, "insert_test_seq", "seq2");
>
> params[0] = new Integer(incr.nextIntValue());
>
> PreparedStatementCreator psc =
>
> PreparedStatementCreatorFactory.newPreparedStatementCreator("insert into insert_test values(?, ?)", types, params);
>
> numRows = tpl.update(psc);
>
> assertTrue("Row was not inserted", 1 == result.getRowsAffected());
>
> // assertTrue("Key should have been 101", 101 == ((Integer)result.getKey()).intValue());
>
> // Don't need getKey() as we have used incr.nextIntValue()
>
> }
>
>
>
> Best Regards,
>
> Jean-Pierre
>
>
>
> > -----Message d'origine-----
>
> > De : Isabelle Muszynski [mailto:isa...@me...]
>
> > Envoyé : samedi 24 mai 2003 11:18
>
> > À : JP Pawlak
>
> > Cc : spr...@li...
>
> > Objet : Re: RE : [Springframework-developer] SqlUpdate and insert
>
> > functionality
>
> >
>
> > 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
>
--
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
|
