Search...
FAQs
Subscribe
Pie
FAQs
Recent topics
Flagged topics
Hot topics
Best topics
Search...
Search within Spring
Search Coderanch
Advance search
Google search
Register / Login
Post Reply
Bookmark Topic
Watch Topic
New Topic
programming forums
Java
Mobile
Certification
Databases
Caching
Books
Engineering
Micro Controllers
OS
Languages
Paradigms
IDEs
Build Tools
Frameworks
Application Servers
Open Source
This Site
Careers
Other
Pie Elite
all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Campbell Ritchie
Ron McLeod
Tim Cooke
Liutauras Vilda
Jeanne Boyarsky
Sheriffs:
Paul Clapham
Rob Spoor
Junilu Lacar
Saloon Keepers:
Stephan van Hulst
Tim Holloway
Piet Souris
Carey Brown
Bartenders:
Forum:
Spring
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar
padma beerelli
Greenhorn
Posts: 6
posted 9 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Hi,
I am new to spring and getting below exception
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call USP_RETAIL_PKG.get_retail_stores(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_RETAIL_STORES' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
public class GetRetailStoresProcedure extends StoredProcedure { @Autowired @Qualifier("sldbdataSource") private DataSource sldbdataSource; @PostConstruct private void initialize() { System.out.println("get sldb connections for Procedure DAO"); setDataSource(sldbdataSource); } private static final String sp = "USP_RETAIL_PKG.get_retail_stores"; // public GetRetailStoresProcedure(JdbcTemplate jdbcTemplate) { public GetRetailStoresProcedure(DataSource sldbdataSource) { super(sldbdataSource, sp); System.out.println("proc getRetailStoresProcedure"); // RowMapper rowMapper = new storeDetailsMapper(); // setFunction(true); // declareParameter(new SqlReturnResultSet("out_records",rowMapper)); // declareParameter(new SqlOutParameter("out_records",OracleTypes.CURSOR,rowMapper)); declareParameter(new SqlReturnResultSet("out_records",storeDetailsMapper)); declareParameter(new SqlParameter("in_flag",OracleTypes.VARCHAR)); declareParameter(new SqlParameter("in_value",OracleTypes.VARCHAR)); declareParameter(new SqlParameter("in_status", OracleTypes.VARCHAR)); // now compile stored proc compile(); } @SuppressWarnings({ "unchecked", "rawtypes" }) public Map getStoreDetails(String inFlag, String inValue, String inStatus) { System.out.println("proc getStoreDetails........"); // set the input params Map inParameters = new HashMap(); inParameters.put("in_flag", inFlag); inParameters.put("in_value", inValue); inParameters.put("in_status",inStatus); // inParameters.put("out_records",rowMapper); // now execute System.out.println("proc execute getStoreDetails........"); Map out = execute(inParameters); // [color=red][b] getting exception here[/b][/color] if(!out.isEmpty()){ System.out.println("proc out :" + out); } return out; } @SuppressWarnings("unused") // private static final class storeDetailsMapper implements RowMapper<GetRetailStores> { private RowMapper<GetRetailStores> storeDetailsMapper = new RowMapper<GetRetailStores>() { // @Override public GetRetailStores mapRow(ResultSet rs, int i) throws SQLException { System.out.println("inner maprow"); int retailId=rs.getInt("retail_id"); String mlc=rs.getString("mlc"); String storeName=rs.getString("store_name"); String storeNumber=rs.getString("store_number"); String area=rs.getString("area"); String region=rs.getString("region"); String city=rs.getString("city"); String state=rs.getString("state"); String phoneNumber=rs.getString("phone_number"); String pendingStatus=rs.getString("pending_status"); String direct=rs.getString("direct"); String disasterImpacted=rs.getString("disaster_impacted"); GetRetailStores retailStore = new GetRetailStores(retailId,mlc,storeName,storeNumber,area, region,city, state,phoneNumber,pendingStatus,direct, disasterImpacted); return retailStore; } }; }
and from Data Access Object
Map data = getRetailStoresProcedure.getStoreDetails(inFlag, inValue, inStatus);
padma beerelli
Greenhorn
Posts: 6
posted 9 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
and here is stored procedure
ROCEDURE get_retail_stores (in_flag IN VARCHAR2,
in_value IN VARCHAR2,
in_status IN VARCHAR2,
out_records OUT out_ref_cursor);
Mark Spritzler
ranger
Posts: 17347
11
I like...
posted 9 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Does the order of setting parameter types in the constructor important?
You have the out parameter first, but in the procedure it is the last parameter.
I haven't used the StoredProcedure class before, so this is a guess here.
Mark
Perfect World Programming, LLC
-
iOS Apps
How to Ask Questions the Smart Way FAQ
padma beerelli
Greenhorn
Posts: 6
posted 9 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Thank you for reply.
I changed as per your suggestion but didnot work.
Mark Spritzler
ranger
Posts: 17347
11
I like...
posted 9 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Wait, I didn't notice this at first, but in your error
bad SQL grammar [{call USP_RETAIL_PKG.get_retail_stores(?, ?, ?)}]
There are three question marks there. Wouldn't there need to be 4?
I would assume that would be passing in the out paramter. Is that how it works?
Not sure, just something I noticed.
Mark
Perfect World Programming, LLC
-
iOS Apps
How to Ask Questions the Smart Way FAQ
reply
reply
Bookmark Topic
Watch Topic
New Topic
Boost this thread!
Similar Threads
Spring StoredProcedure + RowMapper issue
Spring-RowMapper-DB2-Stored Procedure
org.springframework.jdbc.BadSqlGrammarException:CallableStatementCallback; bad SQL grammar
RowMapper & Stored Proc @ DB2
Spring :Approach to call Stored Procedures
More...