CODEWITHSUNDEEP
javaspringstored-proceduresjdbctemplate
Neron
Neron

Reputation: 1578

SimpleJDBCCall class parameter passing

I have a stored procedure in y db which is on MYSQL server. And when I try to call it, it gives the error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect number of arguments for PROCEDURE thu.productGetter; expected 2, got 0 at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:95) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1137) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1173) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:378) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:363)

Java code is: 

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
                                    .withSchemaName("thu")
                                    .withProcedureName("productGetter");

    jdbcCall.addDeclaredParameter(new SqlParameter("maxPrice", Types.DOUBLE));
    jdbcCall.addDeclaredParameter(new SqlParameter("minPrice", Types.DOUBLE));

    Map<String, Object> params=new HashMap<String, Object>();
    params.put("maxPrice", maxPrice);
    params.put("minPrice", minPrice);


    jdbcCall.execute(params);

And the stored procedure db code is:

CREATE DEFINER=`root`@`localhost` PROCEDURE `productGetter`(IN maxPrice double,minPrice double)
BEGIN
        ...    
END$$

I have checked plenty of examples and could not find any solution for my problem.

Any ideas?

Upvotes: 2

Views: 9572

Answers (3)

coder1608
coder1608

Reputation: 171

You could try MapSqlParamterSorce instead.

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
                                    .withSchemaName("thu")
                                    .withProcedureName("productGetter")
                                    .withoutProcedureColumnMetaDataAccess();

jdbcCall.addDeclaredParameter(new SqlParameter("maxPrice", Types.DOUBLE));
jdbcCall.addDeclaredParameter(new SqlParameter("minPrice", Types.DOUBLE));

MapSqlParameterSource source = new MapSqlParameterSource();
source.addValue("maxPrice", maxPrice);
source.addValue("minPrice", minPrice);

jdbcCall.execute(source);

Upvotes: 0

aleroot
aleroot

Reputation: 72636

I've experienced the same issue after upgrading from mysql 5.1 connector to 6.0.6 and the way to solve is just to add : .withoutProcedureColumnMetaDataAccess() in this way : 

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
                                    .withSchemaName("thu")
                                    .withProcedureName("productGetter")
                                    .withoutProcedureColumnMetaDataAccess();

    jdbcCall.addDeclaredParameter(new SqlParameter("maxPrice", Types.DOUBLE));
    jdbcCall.addDeclaredParameter(new SqlParameter("minPrice", Types.DOUBLE));

    Map<String, Object> params=new HashMap<String, Object>();
    params.put("maxPrice", maxPrice);
    params.put("minPrice", minPrice);


    jdbcCall.execute(params);

Upvotes: 5

dawid
dawid

Reputation: 374

I had the same message about missing parameters but it turned out that it was the schema name that was wrong

Upvotes: 0

Related Questions