[BEA][Informix JDBC Driver]Invalid parameter binding(s).

[Jay Schmidgall]

I'm getting the subject error trying to invoke a stored procedure
function, and I'm wondering if I've diagnosed the problem correctly.

Here is the stored proc signature

CREATE PROCEDURE lmscfg.sp_fred_last_nnsf_dt_borr
(dts_start datetime year to minute, dts_end datetime year to minute,
i_boprofil_token int)
returning date;

Here's my sql and parameter setting:

private Timestamp m_placementDtm;
private Timestamp m_todayDtm;

String sql = "{ ? = call sp_fred_last_nnsf_dt_borr( ?, ?, ? ) }";
CallableStatement cstmt = conn.prepareCall( sql );
cstmt.registerOutParameter( 1, Types.DATE );
cstmt.setTimestamp( 2, m_placementDtm );
cstmt.setTimestamp( 3, m_todayDtm );
cstmt.setInt( 4, m_boprofilToken.intValue() );

I think the problem is that the 'datetime year to minute' datatype is
incorrect for Timestamp and instead needs to be 'datetime year to second'.

Before I go to my DBA and request the change, I was hoping to verify
that this is indeed the problem.

Thanks,

: jay

[Jay Schmidgall]

Bit of followup on this... it is the setInt() call which is actually
throwing the exception. Does this driver not support calling functions
like this? I'm thinking this is the problem.

[Joe Weinstein]

Jay Schmidgall wrote:

Now show us the actual exception.
Joe

[Jay Schmidgall]

It appears the informix drivers either do not support this convention
for calling functions, or a procedure with this signature is not
considered a function (for example, functions in Oracle are actually
declared with the FUNCTION keyword).

Anyways, changing my code to deal with the returned resultset containing
the single return value worked.

Joe Weinstein wrote:
> Now show us the actual exception.

The actual exception was

java.sql.SQLException: [BEA][Informix JDBC Driver]Invalid parameter
binding(s).
at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
at weblogic.jdbc.base.BaseParameters.set(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.setObjectInternal(Unknown
Source)
at weblogic.jdbc.base.BasePreparedStatement.setInt(Unknown Source)
at weblogic.jdbcx.base.BasePreparedStatementWrapper.setInt(Unknown
Source)
at
weblogic.jdbc.wrapper.PreparedStatement.setInt(PreparedStatement.java:349)
at
org.ecmc.lms.model.jdbc.QueryLastNonNsfPaymentDateHelper$LookupLastNonNsfPaymentDateStatementCreator.createCallableStatement(QueryLastNonNsfPaymentDateHelper.java:62)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:625)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:654)
at
org.ecmc.lms.model.jdbc.QueryLastNonNsfPaymentDateHelper$LookupLastNonNsfPaymentDateStatementCreator.call(QueryLastNonNsfPaymentDateHelper.java:83)
at
org.ecmc.lms.model.jdbc.QueryLastNonNsfPaymentDateHelper.queryLastNonNsfPaymentDate(QueryLastNonNsfPaymentDateHelper.java:40)
at
org.ecmc.lms.model.jdbc.LmsSelectFacadeBean.localQueryLastNonNsfPaymentDate(LmsSelectFacadeBean.java:150)
at
org.ecmc.lms.model.jdbc.LmsSelectFacade_jy9knk_ELOImpl.localQueryLastNonNsfPaymentDate(LmsSelectFacade_jy9knk_ELOImpl.java:45)
at
org.ecmc.nib.model.batch.ruleset.DiligenceStep.getInitialRescheduleDate(DiligenceStep.java:127)
at
org.ecmc.nib.model.batch.ruleset.DiligenceStep.initiallyScheduleRulesetStep(DiligenceStep.java:94)
at
org.ecmc.nib.model.batch.ruleset.DefaultRulesetStep.initiallyScheduleRulesetStep(DefaultRulesetStep.java:47)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.scheduleStep(DefaultRuleset.java:347)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.scheduleInitialSteps(DefaultRuleset.java:220)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.defaultInitializeRuleset(DefaultRuleset.java:158)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.initializeRuleset(DefaultRuleset.java:128)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.defaultMigrate(DefaultRuleset.java:113)
at
org.ecmc.nib.model.batch.ruleset.DiligenceRuleset.migrate(DiligenceRuleset.java:32)
at
org.ecmc.nib.model.batch.ruleset.DefaultRuleset.migrate(DefaultRuleset.java:88)
at
org.ecmc.nib.model.batch.ruleset.RulesetFacadeBean.migrate(RulesetFacadeBean.java:180)
at
org.ecmc.nib.model.batch.ruleset.RulesetFacadeBean.doMigrate(RulesetFacadeBean.java:167)
at
org.ecmc.nib.model.batch.ruleset.RulesetFacadeBean.migrate(RulesetFacadeBean.java:48)
at
org.ecmc.nib.model.batch.ruleset.RulesetFacade_i5lhdc_ELOImpl.migrate(RulesetFacade_i5lhdc_ELOImpl.java:274)
at
org.ecmc.nib.batch.session.ProcessRulesetFacadeBean.migrate(ProcessRulesetFacadeBean.java:60)
at
org.ecmc.nib.batch.session.ProcessRulesetFacade_hyvnny_EOImpl.migrate(ProcessRulesetFacade_hyvnny_EOImpl.java:301)
at
org.ecmc.nib.batch.session.ProcessRulesetFacade_hyvnny_EOImpl_WLSkel.invoke(Unknown
Source)
at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:477)
at
weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:108)
at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:420)
at
weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:353)
at
weblogic.security.service.SecurityManager.runAs(SecurityManager.java:144)
at
weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:415)
at
weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:30)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)