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.
UG Development - UGCUAT
Can you please give a programmatic approach to handle this issue.
Frineds please give your valid inputs to solve this issue. Thanks in advance.
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);
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 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_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
Which columns in the query are date fields?
Are you sure you're using correct JDBC driver for your database?
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.