File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Spring JDBC: registering the SQL type with MapSqlParameterSource

 
Hiren Shah
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am using simpleJdbcTemplate.queryForInt for retrieving the count. Table has one CHAR(4) datatype and the value for the parameter for queryForInt is "88" supplied as String.

When i execute this method by passing MapSqlParameterSource or the Object having "88" it returns "0". However, when i harcode "88" directly in the sql it returns the expected count.

Do you know what am i missing here? I even tried specifying Types.CHAR as the sqlType.

Thank you,
SpringForever
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Spring Forever"

Please click on the My Profile link above and change your display name to meet the JavaRanch Naming Policy of using your real first and real last names.

Thanks

Mark
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After changing your display name, then please post your code so we can see what you mean.

Thanks

Mark
 
Hiren Shah
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, Mark!

The code and the sql is:

public int getRecordsCount(VehicleVO vehicleVO)
{
MapSqlParameterSource mapParameters = new MapSqlParameterSource();
mapParameters.addValue("id",vehicleVO.getId(),Types.VARCHAR);
mapParameters.addValue("code", vehicleVO.getCd(),Types.CHAR);
mapParameters.addValue("date", vehicleVO.getDt(), Types.NUMERIC);

return jdbcOperations.queryForInt(getRecordCountQuery, mapParameters);
}

SELECT COUNT (*) TOTAL_RECORDS
FROM VEHICLE_DETAIL
WHERE ID = :id
AND PRICE_CD = :code
AND REC_DT = ate


If i put the hard-coded values in the sql then it works fine. But with the method above it returns "0". When i changed the column type for "code" to varchar in the database then it returns the count greater than "0".
[ October 06, 2008: Message edited by: Hiren Shah ]
 
Hiren Shah
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got this reply on Spring forum.....

"You are using a CHAR(4) column so the data in that field is 4 characters long which would be '88 ' (notice the two spaces at the end). Supplying a string of length 2 will never match.

The reason the hardcoded SQL works is because the database engine is converting the literals provided to match the column type and is padding the values provided. When you are using bind variables there is no such conversion - you have to provide the correct data values."

It works.

Thank you,
Hiren
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic