Thursday 11 March 2004

Sybase output parameter issue with ColdFusion

Using Sybase 12.5 we have a stored procedure that
returns a string in an output parameter.

The Stored procedure is declared like

create proc sp_foobar
@pc_char1 char(10),
@pi_int1 int,
@pvc_vc1 varchar(512),
@pc_char2 char(6),
@pvc_vc2 varchar(40),
@pvc_vc3 varchar(255),
@pb_bit1 bit,
@pvc_vc4 varchar(20),
@pvc_return_code varchar(30) output
as


...

In ColdFusion, we were calling the stored procedure as follows.













But this throws an error,

[Macromedia][Sybase JDBC Driver][Sybase]Operand type clash: VARCHAR is incompatible with VOID TYPE

and it highlights the last cfprocparam (the output variable).as the issue

I worked out that if you pass it a value="" parameter, it stops it from failing.. ie



The sproc, in this example as part of it's internal logic/contract returns "SPROC_SUCCESS" as it's varchar, in @pvc_return_code. What Coldfusion puts in this
v_return_code is now equal to only the first letter. (ie 'S')

Hrmmm, .. so .. I worked a way around this one also... A Fix for this BUG .. make the 'value=""' a string as long as the string you expect to be returned. eg,



and then you would get #v_return_code# equal to "SPROC_SUCCESS"

Odd, any takers? Someone SURELY has seen this before.

Current 5 booksmarks @ del.icio.us/pefdus