sql - Getting a bad grammar error in a call to a Spring Stored Procedure with Java -
i'm having debug code written else , i've run problem in 1 of dao implementations. we're using java 1.6 , spring (i think there's hibernate in portions of application don't think come play here.) code when runs erroring on "outmap = super.execute(inmap);" line of code. error throws is
systemerr r callhistorysp() org.springframework.jdbc.badsqlgrammarexception: callablestatementcallback; bad sql grammar [{call db2admin/quoteaccessorials(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception java.sql.sqlexception: number of parameter values set or registered not match number of parameters.
here's object (at least relevant parts).
private class ratequotehistorystoredproc extends storedprocedure { private final string sql = getstoredprocname(); private final string quote_num = "ratequotenumber", accessorial = "accessorial", accessorial_amt = "accessorialamt", fakratehistory = "fakratehistory", handling_units = "numhandlingunits", pieces = "numpieces", cubic_feet = "cubicfeet", bunker_fuel_surcharge = "puertoricobunkerfuel", canadian_border_fee = "canadacrossborderfee" , tx_arbitrary_charge = "texasarbitrarychg" , washington_dc_charge = "washingtondcarbitrarychg", nyc_charge = "nycarbitrarychg" , northern_virginia_charge = "northvirginiaarbitrarychg" , florida_arbitrary_charge = "flkeysarbitrarychg" , ferry_charge ="ferrycharge"; private map outmap = new hashmap(); public ratequotehistorystoredproc(datasource ds){ try { setdatasource(ds); setsql(sql); declareparameter(new sqlparameter(quote_num, types.decimal)); (int = 0; < getmax_accessorials(); i++) { declareparameter(new sqloutparameter(accessorial + i, types.char)); } (int = 0; < getmax_accessorials(); i++) { declareparameter(new sqloutparameter(accessorial_amt + i, types.decimal)); } declareparameter(new sqloutparameter(fakratehistory, types.char)); declareparameter(new sqloutparameter(handling_units, types.decimal)); declareparameter(new sqloutparameter(pieces, types.decimal)); declareparameter(new sqloutparameter(cubic_feet, types.decimal)); declareparameter(new sqloutparameter(bunker_fuel_surcharge, types.decimal)); declareparameter(new sqloutparameter(canadian_border_fee, types.decimal)); declareparameter(new sqloutparameter(tx_arbitrary_charge, types.decimal)); declareparameter(new sqloutparameter(washington_dc_charge, types.decimal)); declareparameter(new sqloutparameter(nyc_charge, types.decimal)); declareparameter(new sqloutparameter(northern_virginia_charge, types.decimal)); declareparameter(new sqloutparameter(florida_arbitrary_charge, types.decimal)); declareparameter(new sqloutparameter(ferry_charge, types.decimal)); compile(); } catch (exception e) { log.error("ratequotestoredproc()",e); } } public void callhistorysp(ltlcustomratesbean bean) throws exception { hashmap inmap = new hashmap(); inmap.put(quote_num, bean.getratequotenumber()); for(int = 0; < getmax_accessorials(); i++){ inmap.put(accessorial+i, " "); } for(int = 0; < getmax_accessorials(); i++){ inmap.put(accessorial_amt+i, "0"); } try { outmap = super.execute(inmap); } catch (nullpointerexception npe){ if(bean.getcustomer() == null){ log.error("callhistorysp() customer null"); }else{ log.error("callhistorysp()",npe); } } catch (exception e) { log.error("callsp()",e); } if (isdebugmode()) { log.debug("callsp(): outmap: "+outmap); } saveresults(outmap, bean); } } here's stored procedure calling.
create procedure db2admin.quoteaccessorials ( in quotenum decimal(7, 0) , inout acccd1 char(6) , inout acccd2 char(6) , inout acccd3 char(6) , inout acccd4 char(6) , inout acccd5 char(6) , inout acccd6 char(6) , inout acccd7 char(6) , inout acccd8 char(6) , inout acccd9 char(6) , inout acccd10 char(6) , inout acccd11 char(6) , inout acccd12 char(6) , inout acccd13 char(6) , inout acccd14 char(6) , inout acccd15 char(6) , inout acccd16 char(6) , inout acccd17 char(6) , inout acccd18 char(6) , inout acccd19 char(6) , inout acccd20 char(6) , inout accam1 decimal(9, 2) , inout accam2 decimal(9, 2) , inout accam3 decimal(9, 2) , inout accam4 decimal(9, 2) , inout accam5 decimal(9, 2) , inout accam6 decimal(9, 2) , inout accam7 decimal(9, 2) , inout accam8 decimal(9, 2) , inout accam9 decimal(9, 2) , inout accam10 decimal(9, 2) , inout accam11 decimal(9, 2) , inout accam12 decimal(9, 2) , inout accam13 decimal(9, 2) , inout accam14 decimal(9, 2) , inout accam15 decimal(9, 2) , inout accam16 decimal(9, 2) , inout accam17 decimal(9, 2) , inout accam18 decimal(9, 2) , inout accam19 decimal(9, 2) , inout accam20 decimal(9, 2) , out fakratehistory char(20) , out handling_unit decimal(5, 0) , out pieces decimal(5, 0) , out cubic_feet decimal(5, 0) , out bunker_fuel_surcharge decimal(9, 2) , out canadian_border_fee decimal(9, 2) , out tx_arbitrary_charge decimal(9, 2) , out washington_dc_charge decimal(9, 2) , out nyc_charge decimal(9, 2) , out northern_virginia_charge decimal(9, 2) , out florida_arbitrary_charge decimal(9, 2) , out ferry_charge decimal(9, 2) ) language rpg specific db2admin.quoteaccessorials not deterministic modifies sql data called on null input external name 'ltl400avx2/ww7r010' parameter style general ; you'll note sp has 53 parameters. has 1 input parameter, 40 in/out parameters, , 12 output parameters. call i'm making seems have correct number of parameters , can't figure out causing error. appreciated. i'm sure problem simple don't know spring enough see wrong code. haven't been able google answer normal either. thanks!
andrew
you declaring 40 inout parameters using sqloutparameter. if declare them using sqlinoutparameter instead?
Comments
Post a Comment