Setting parameters on a CallableStatement

[Christopher . Mathrusse]

This is the first time I've ever come across this and it's causing me a bit of a headache. This is not an iBatis issue but rather a Sybase JConnect issue. (As I see it)

 

I have a stored proc that I am attempting to execute to perform an insert. (32 parameters) All of which allow null to be specified except the first which is the OUT parameter that returned the new records ID.  I had coded up the statement to use inline parameters, specifying the IN and OUT modes. Not all parameters are supplied so rather than specifying properties on my object I simply set those parameters to NULL literal.

 

So my call would look like the following:

   {call p_ins_con(
        #id,jdbcType=INTEGER,javaType=java.lang.Integer,mode=INOUT#
      , #addressFreeze,jdbcType=CHAR,javaType=java.lang.String,mode=IN#
      , #address1,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #address2,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #address3,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #address4,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #city,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #language,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #companyName,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #country.isoCode,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #county,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #createLogin,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , #email,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , #fax1No,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , NULL
      , NULL
      , #fax2No,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , NULL
      , NULL
      , #name,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , NULL
      , #phone,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , NULL
      , NULL
      , NULL
      , #postalCode,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , #state,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#
      , NULL
      , NULL
      , #active,jdbcType=CHAR,javaType=java.lang.Boolean,mode=IN#)
   }

 

Each time I executed this I would see an odd message from the driver stating:

JZ0SG: A CallableStatement did not return
as many output parameters as the application had registered for it.

Very informative. So I finally found a bit of information in their documentation that says the following:

If you execute a stored procedure in a CallableStatement object that represents parameter values as question marks, you get better performance than if you use both question marks and literal values for parameters. Further, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

 

So now I am forced to use placeholders to populate the positional parameters. The only solution I have come up with so far is to keep specifying a given field on my object that is known to be null, but that is extremely ugly. Is there a better way to ensure that a NULL will be set for each parameter other than the way I've listed above?

 

Thanks for the help guys...

 

Chris Mathrusse

[EMAIL PROTECTED]

(925) 236-5553