This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Issue related to Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Issue related to "jdbcTemplate.queryForList(sqlForExtract)"" Watch "Issue related to "jdbcTemplate.queryForList(sqlForExtract)"" New topic
Author

Issue related to "jdbcTemplate.queryForList(sqlForExtract)"

muruga dhanapal
Greenhorn

Joined: Jun 02, 2010
Posts: 24
Dear Friends,
I have an issue in the production.what am trying to do is I have seven extracter spring bean out of that am getting an exception while processing the seventh bean.The problem is am using this "jdbcTemplate.queryForList(sqlForExtract)" method for fetching the data fom DB. Its a date field and the issue is, becasue of this date field when am getting a valid value its working fine and whenever there is "NULL" it takes it as string and throws the following exception.

My understanding is by using this method its trying to map the list in some format and there occurs problem.
This is my error log
Caused by: java.sql.SQLException: [SQL0181] Value in date, time, or timestamp string not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:485)
at com.ibm.as400.access.JDServerRowCache.fetch(JDServerRowCache.java:252)
at com.ibm.as400.access.JDServerRowCache.fetch(JDServerRowCache.java:183)
at com.ibm.as400.access.JDServerRowCache.next(JDServerRowCache.java:588)
at com.ibm.as400.access.AS400JDBCResultSet.next(AS400JDBCResultSet.java:1700)
UG Development - UGCUAT
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:423)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)

Can you please give a programmatic approach to handle this issue.

Frineds please give your valid inputs to solve this issue. Thanks in advance.
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 10171
    
    8

Please do take out time and show some effort to CarefullyChooseOneForum. This forum is for Job Discussions.
Moving topic.


[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

Can you post some code, specifically around the problem. Remember to use code tags.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

could you run the sql against the db (i presume sqlforextract is an sql statement) and see what it is returning, maybe something you are trying to use as a date, isn't valid?
muruga dhanapal
Greenhorn

Joined: Jun 02, 2010
Posts: 24
Wendy Gibbons wrote:could you run the sql against the db (i presume sqlforextract is an sql statement) and see what it is returning, maybe something you are trying to use as a date, isn't valid?


As you suggested when I ran the query am getting all the records and for the date field where there is no date value it returns null.

Hope my guess is when am using the following code
List rows = this.jdbcTemplate.queryForList(sqlForExtract);
return rows;

the exception is caused when am using the method jdbcTemplate.queryForList() to convert the reultant to query to a list. So is there any other alternate method where we can use to overcome this issue
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

What is the SQL you are passing to the method?
muruga dhanapal
Greenhorn

Joined: Jun 02, 2010
Posts: 24
James Boswell wrote:What is the SQL you are passing to the method?


This is the SQL am passing to the nmethod

Select non_mi_ins.data_00001 as data_src_code,
ins_s00001 as status,
non_mi_ins.case_code as case_code,
non_mi_borrowers.bw_seq_nbr as bwsn,
non_mi_borrowers.bw_fnme_name,
non_mi_borrowers.bw_mid_name,
non_mi_borrowers.bw_last_name,
non_mi_borrowers.bw_ssn,
non_mi_borrowers.bw_bday_date,
non_mi_loan_info.Orig_00001 as originator_id,
lenders.name as originator_name
from ugc.nmi_n00001 as non_mi_ins
left outer join ugc.nmb_n00001 non_mi_borrowers on non_mi_ins.case_code = non_mi_borrowers.case_code
left outer join ugc.nml_n00001 non_mi_loan_info on non_mi_ins.case_code = non_mi_loan_info.case_code
left outer join ugc.inllp1 lenders on non_mi_loan_info.Orig_00001 = lenders.hoke
where ins_s00001 in
('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23') with NC


This is the field where am facing the issue "non_mi_borrowers.bw_bday_date, " My understanding is because of this date field am getting the above mentioned exception
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

Are you able to successfully run this SQL against the database outside of your code?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

What does "with NC" at the end of the last line of your query mean? (I've never seen such a syntax).

Which columns in the query are date fields?

Are you sure you're using correct JDBC driver for your database?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I would try leaving the date out of the select, and see if it works, or does that give you a different error?
muruga dhanapal
Greenhorn

Joined: Jun 02, 2010
Posts: 24
Wendy Gibbons wrote:I would try leaving the date out of the select, and see if it works, or does that give you a different error?


I fixed that issue. The problem was with the date field so I have converted the dated field to string by using the function Char() and moreover its a AS400 system so I used the above function. And By using simpledateformat I acheived the required date format. Thanks for your suggestions.
 
jQuery in Action, 2nd edition
 
subject: Issue related to "jdbcTemplate.queryForList(sqlForExtract)"
 
Similar Threads
Getting a Four Digit Date from SQL call
MySQL Problem !!!
ognl.MethodFailedException “setCreatedDate” failed for object com.security.data.PasswordHistory@d5b5
java.sql.SQLException: Connection has already been closed.
Bug in Suns 1.6 server VM (I think)