Menu
▾
▴
springframework-developer
|
From: Isabelle M. <isa...@me...> - 2003-05-21 12:29:54
|
Hi everyone, I've checked in a new version of SqlUpdate with insert functionality (see the update methods with a KeyBinder argument). I cannot run any tests though, there seems to be a problem with AOP code. setDataSource in jdbcTemplate gives a NoClassDefFoundError on org.aopalliance.interceptor. I've made a clean checkout of the sources from CVS into a new directory and the problem remains. So there seems to be something wrong with the AOP jars. By the way I've also moved the livetest source files into the correct directory structure for their package. Isabelle -- 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 |
|
From: Ken K. <kk...@kk...> - 2003-05-23 04:30:17
|
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
|
|
From: Isabelle M. <isa...@me...> - 2003-05-23 07:24:58
|
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
|
|
From: Isabelle M. <isa...@me...> - 2003-05-23 14:31:54
|
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
|
|
From: Isabelle M. <isa...@me...> - 2003-05-23 15:05:34
|
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
|
|
From: Ken K. <kk...@kk...> - 2003-05-23 22:14:26
|
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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>
>
>
>
>
|
|
From: JP P. <jp....@ti...> - 2003-05-23 22:46:49
|
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=A0: spr...@li...
[mailto:spr...@li...] De la
part de Ken Krebs
Envoy=E9=A0: samedi 24 mai 2003 00:09
=C0=A0: Isabelle Muszynski
Cc=A0: spr...@li...
Objet=A0: [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=20
visit_id. The problem is that the getKey() function of the=20
returned InsertRetval returns 0, not the id that was used for the=20
insert. I wanted to use the value to update my cache directly without=20
having to requery the DB for all this pet's visits. I am working around=20
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:
=20
Isabelle,
My key column isn't auto-incremented, only the actual id column is.
My latest code snapshot is attached.
Ken
Isabelle Muszynski wrote:
=20
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=20
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:
=20
Hi Isabelle,
I have a problem using the following petclinic class that inserts a new=20
visit into the DB :
class NewVisit extends SqlUpdate {
=20
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();
}
=20
public int insert(Visit visit) {
KeyBinder keybinder =3D new KeyBinder() {
public void bind(PreparedStatement ps, Object obj)=20
throws SQLException {
ps.setObject(1, obj);
}
};
=20
MySQLMaxValueIncrementer incr =3D new=20
MySQLMaxValueIncrementer(getDataSource(), "visits_seq", "seq", 1);
=20
logger.info("Visit petId =3D " + visit.getPetId());
=20
Object[] objs =3D new Object[] {
null,
new Integer(visit.getPetId()),
visit.getVisitDate(),
visit.getDescription()
};
=20
JdbcTemplate.InsertRetval retVal =3D update(objs, keybinder,=20
incr, Integer.class);
visit.setId(((Integer) retVal.getKey()).intValue());
=20
logger.info("Visit id =3D " + visit.getId() + " petId =3D " + =
visit.getPetId());
=20
return retVal.getRowsAffected();
}
=20
}
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=20
visit_id. The problem is that the getKey() function of the=20
returned InsertRetval returns 0, not the id that was used for the=20
insert. I wanted to use the value to update my cache directly without=20
having to requery the DB for all this pet's visits. I am working around=20
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] -=20
<Compiled OK>
2003-05-22 22:53:37,996 INFO [petclinic.support.ClinicImpl$NewVisit] -=20
<Visit petId =3D 7>
2003-05-22 22:53:38,006 INFO [com.interface21.jdbc.object.SqlUpdate] -=20
<Compiled OK>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.core.JdbcTemplate] -=20
<JDBCTemplate: update affected 1 rows>
2003-05-22 22:53:38,016 INFO [com.interface21.jdbc.object.SqlUpdate] -=20
<1 rows affected by SQL update [update visits_seq set seq =3D=20
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] -=20
<Executing SQL query using PreparedStatement:=20
[PreparedStatementCreatorFactory.PreparedStatementCreatorImpl:=20
sql=3D{select last_insert_id()}: params=3D{}]>
2003-05-22 22:53:38,036 INFO [com.interface21.jdbc.core.JdbcTemplate] -=20
<JDBCTemplate: update affected=20
com.interface21.jdbc.core.JdbcTemplate$InsertRetval@1ebe3f0 rows>
2003-05-22 22:53:38,036 INFO [petclinic.support.ClinicImpl$NewVisit] -=20
<Visit id =3D 0 petId =3D 7>
Note that the last line shows the visit_id =3D=3D 0.
Also note the odd output of the 2nd last line where instead of=20
outputting the no. of rows affected it's printing a toString() of the=20
InsertRetval !!!???
Am I doing something wrong here ?
Regards,
Ken
=20
=20
=20
=20
|
|
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
|
|
From: JP P. <jp....@ti...> - 2003-05-24 11:12:35
|
Hi Isabelle,
Why should code such as below not work?=20
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=E2=80=99t have two database =
requests.=20
Just we don't use the Oracle's common approach to give a null and =
setting a trigger providing the value.=20
Modified test:
/**
* Test an insert that is using sequencing
* @throws Exception if anything goes wrong
*/
public void testInsertWithSequence() throws Exception {
int[] types =3D new int[] { Types.INTEGER, Types.INTEGER };
Object[] params =3D new Object[] { null, new Integer(1) };
JdbcTemplate tpl =3D new JdbcTemplate(ds);
MySQLMaxValueIncrementer incr =3D new MySQLMaxValueIncrementer(ds, =
"insert_test_seq", "seq2");
params[0] =3D new Integer(incr.nextIntValue());
PreparedStatementCreator psc =3D=20
PreparedStatementCreatorFactory.newPreparedStatementCreator("insert =
into insert_test values(?, ?)", types, params);
numRows =3D tpl.update(psc);
assertTrue("Row was not inserted", 1 =3D=3D result.getRowsAffected());
// assertTrue("Key should have been 101", 101 =3D=3D =
((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=C3=A9 : samedi 24 mai 2003 11:18
> =C3=80 : JP Pawlak
> Cc : spr...@li...
> Objet : Re: RE : [Springframework-developer] SqlUpdate and insert
> functionality
>=20
> Hi Jean-Pierre,
>=20
> 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.
>=20
> Anyway, the thing is badly broken right now.
>=20
> Isabelle
>=20
> 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=C3=82 : spr...@li...
> > [mailto:spr...@li...] De la
> > part de Ken Krebs
> > Envoy=C3=83=C2=A9=C3=82 : samedi 24 mai 2003 00:09
> > =C3=83=E2=82=AC=C3=82 : Isabelle Muszynski
> > Cc=C3=82 : spr...@li...
> > Objet=C3=82 : [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.
> >
|
|
From: Isabelle M. <isa...@me...> - 2003-05-24 16:30:21
|
Hi Jean-Pierre,
I was away all afternoon and did some more thinking about the problem, and came to the same conclusion as you apparently did : the sequence table should stay, but the KeyBinder interface needs to go away. Instead of the KeyBinder argument, the caller needs to pass the index of the key column so that JdbcTemplate can do the binding.
The difference with your sample is that the caller doesn't do the binding, JdbcTemplate does.
Are we on the same wavelength?
Best regards,
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
|
|
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
|
|
From: Isabelle M. <isa...@me...> - 2003-05-24 09:16:04
|
Hi everyone,
The javadoc comment is a major booboo on my part. The column should not be auto-increment, because the sequence table does that.
HOWEVER: I have been rethinking the whole approach, and I think I need to get rid of the KeyBinder and use the auto-increment feature instead. The user would then pass NULL, the column IS auto-increment, and there are no sequence tables).
I'm giving this top priority, we cannot release with a bug. I'm hoping to get it done by sunday night.
Reading in the MySQL Cookbook, there is a way to retrieve the last inserted id in one round-trip using MySQL-specific API methods. The alternative is 2 use 2 statements : first the insert with a with a NULL, then call last-insert-id(). The last inserted id is kept on a per-connection basis, so I think everything should be OK when used in a container that pools connections.
It seems to me that, when inserting with a sequence, we have to have something like
preprocess()
update()
postprocess()
return id
Depending on the database, either or both of preprocess and postprocess may be empty.
Comments are welcome.
Isabelle
On Fri, May 23, 2003 at 05:09:28PM -0500, Ken Krebs wrote:
> 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
|
|
From: Isabelle M. <isa...@me...> - 2003-05-23 16:17:26
|
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
|
|
From: Rod J. <rod...@in...> - 2003-05-21 21:20:10
|
Isabelle, I'm puzzled by this. I've done a clean checkout and everything works for me. The AOPAlliance sources are in synch with Spring. The aopalliance.jar file has moved to its own directory: is your old one still in the parent directory? Regards, Rod ----- Original Message ----- From: "Isabelle Muszynski" <isa...@me...> To: <spr...@li...> Sent: Wednesday, May 21, 2003 1:29 PM Subject: [Springframework-developer] SqlUpdate and insert functionality > Hi everyone, > > I've checked in a new version of SqlUpdate with insert functionality (see the update methods with a KeyBinder argument). > > I cannot run any tests though, there seems to be a problem with AOP code. setDataSource in jdbcTemplate gives a NoClassDefFoundError on org.aopalliance.interceptor. > > I've made a clean checkout of the sources from CVS into a new directory and the problem remains. So there seems to be something wrong with the AOP jars. > > By the way I've also moved the livetest source files into the correct directory structure for their package. > > Isabelle > > -- > 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 |
|
From: JP P. <jp....@ti...> - 2003-05-21 21:55:16
|
If it is about the testSuites, all pass on my machine. Regards, Jean-Pierre > -----Message d'origine----- > De=A0: spr...@li... > [mailto:spr...@li...] De la part > de Rod Johnson > Envoy=E9=A0: mercredi 21 mai 2003 23:17 > =C0=A0: Isabelle Muszynski; spr...@li... > Objet=A0: Re: [Springframework-developer] SqlUpdate and insert functionality >=20 > Isabelle, >=20 > I'm puzzled by this. I've done a clean checkout and everything works for > me. > The AOPAlliance sources are in synch with Spring. The aopalliance.jar file > has moved to its own directory: is your old one still in the parent > directory? >=20 > Regards, > Rod >=20 > ----- Original Message ----- > From: "Isabelle Muszynski" <isa...@me...> > To: <spr...@li...> > Sent: Wednesday, May 21, 2003 1:29 PM > Subject: [Springframework-developer] SqlUpdate and insert functionality >=20 >=20 > > Hi everyone, > > > > I've checked in a new version of SqlUpdate with insert functionality > (see > the update methods with a KeyBinder argument). > > > > I cannot run any tests though, there seems to be a problem with AOP > code. > setDataSource in jdbcTemplate gives a NoClassDefFoundError on > org.aopalliance.interceptor. > > > > I've made a clean checkout of the sources from CVS into a new directory > and the problem remains. So there seems to be something wrong with the AOP > jars. > > > > By the way I've also moved the livetest source files into the correct > directory structure for their package. > > > > Isabelle > > > > -- > > 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 >=20 >=20 >=20 >=20 > ------------------------------------------------------- > 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 |
|
From: Isabelle M. <isa...@me...> - 2003-05-22 07:25:29
|
Hi Rod, Jean-Pierre It can't be that, I did a clean checkout myself. Can you run the livetests? Isabelle On Wed, May 21, 2003 at 10:16:57PM +0100, Rod Johnson wrote: > Isabelle, > > I'm puzzled by this. I've done a clean checkout and everything works for me. > The AOPAlliance sources are in synch with Spring. The aopalliance.jar file > has moved to its own directory: is your old one still in the parent > directory? > > Regards, > Rod > > ----- Original Message ----- > From: "Isabelle Muszynski" <isa...@me...> > To: <spr...@li...> > Sent: Wednesday, May 21, 2003 1:29 PM > Subject: [Springframework-developer] SqlUpdate and insert functionality > > > > Hi everyone, > > > > I've checked in a new version of SqlUpdate with insert functionality (see > the update methods with a KeyBinder argument). > > > > I cannot run any tests though, there seems to be a problem with AOP code. > setDataSource in jdbcTemplate gives a NoClassDefFoundError on > org.aopalliance.interceptor. > > > > I've made a clean checkout of the sources from CVS into a new directory > and the problem remains. So there seems to be something wrong with the AOP > jars. > > > > By the way I've also moved the livetest source files into the correct > directory structure for their package. > > > > Isabelle > > > > -- > > 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 |
