Strange Sybase Stored Procedure Parameter Issue

[ajgoswami]

Hi All,

I am stuck and Your quick help will be much apprecaited !!!


I am getting the following exceptions while executing the code below. Please note that I am mainly using Spring 3.1 jars and jconn3.jar.


Error: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.


Exception Stack Trace
org.springframework.dao.TransientDataAccessResourc eException: CallableStatementCallback; SQL [{? = call pact_get_cusip_details()}]; JZ0SC: Callable Statement: attempt to set the return status as an input parameter.; nested exception is java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.
at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:107)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:969)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:1003)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:144)
at com.citi.icg.rdc.service.ProductServiceImpl$Sybase StoredProcedureExecutor.executeProcedure(ProductSe rviceImpl.java:174)
at com.citi.icg.rdc.service.ProductServiceImpl.search ProductUsingSybaseStoredProcedure(ProductServiceIm pl.java:80)
at com.citi.icg.rdc.util.Main.searchProduct(Main.java :61)
at com.citi.icg.rdc.util.Main.main(Main.java:23)
Caused by: java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter.
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unkn own Source)
at com.sybase.jdbc3.jdbc.ParamManager.doSetParam(Unkn own Source)
at com.sybase.jdbc3.jdbc.ParamManager.setParam(Unknow n Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unkno wn Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unkno wn Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.setStri ng(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.setString(DelegatingPreparedStatement.java:135)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.setString(DelegatingPreparedStatement.java:135)
at org.springframework.jdbc.core.StatementCreatorUtil s.setValue(StatementCreatorUtils.java:274)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:216)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:127 )
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:212)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:947)
... 6 more






The Sybase Procedure
CREATE PROCEDURE dbo.get_details (@listOfInputValues varchar(10000) in) as
declare @sql varchar(10000)
begin
set @sql =
'select
a.Cusip as cusip,
a.SecTyp as security_type
FROM
abc a
WHERE
a.Cusip in (' + @listOfInputValues + ')'
exec (@sql)
end





Source Code

public class ProductServiceImpl {

private Logger productServiceLogger = Logger.getLogger(this.getClass());

private DataSource classDataSource;

public void setDataSource(DataSource dataSource)
{
classDataSource = dataSource;
}


public List<ProductBean> searchProductUsingSybaseStoredProcedure(BaseBean baseBean) throws SQLException, Exception
{
List<ProductBean> productList = new ArrayList<ProductBean>();

Map<String, String> inputParameters = new HashMap<String, String>();
inputParameters.put("listOfInputValues", "\"'3137A7F47', '38377RL60'\"");
SybaseStoredProcedureExecutor sybaseStoredProcedure = new SybaseStoredProcedureExecutor(classDataSource);

Map output = sybaseStoredProcedure.executeProcedure(inputParame ters);
productList = ((List<ProductBean>) output.get("result"));

return productList;
}

class SybaseStoredProcedureExecutor extends StoredProcedure
{
private static final String SQL = "get_details";

public SybaseStoredProcedureExecutor(DataSource dataSource)
{
setDataSource(dataSource);
setFunction(true);
setSql(SQL);

declareParameter(new SqlReturnResultSet("result", new RowMapper<ProductBean>()
{
public ProductBean mapRow(ResultSet rs, int rowNum) throws SQLException
{
ProductBean productBean = new ProductBean();
productBean.setCusip(rs.getString("CUSIP"));
productBean.setSecurityType(rs.getString("SECURITY _TYPE"));
return productBean;
}
}));
declareParameter(new SqlParameter("listOfInputValues", Types.VARCHAR));
compile();
}


public Map<String, String> executeProcedure(Map<String, String> inputParameters)
{
productServiceLogger.info("Input params values are: " + inputParameters);
Map outParams = execute(inputParameters);
productServiceLogger.info("Out param is: " + outParams.get("result"));
return outParams;
}
}

[wpoitras]

You only list one parameter (besides the results), yet you also setFunction(true), so it thinks the one parameter is the output parameter.

Either setFunction(false) or if the stored procedure try returns a result code, then add another parameter to accept the output parameter.

And in the future use [ CODE] blocks surrounding your code, it makes it more readable.

[ajgoswami]

Thanks a lot. Your input helped me resolve the issue.