How do I get LongVarchar out param from SPROC in ADO.NET 2.0 with SQLAnywhere 10?

Question

Hi All,

I have sproc 'up_selfassessform_view' which has the following parameters:

in ai_eqidentkey SYSKEY
in ai_acidentkey SYSKEY
out as_eqcomments TEXT_STRING
out as_acexplanation TEXT_STRING

 -  which are domain objects - SYSKEY is 'integer' and TEXT_STRING is 'long varchar'.

I can call the sproc fine from iSQL using the following code:

create variable @eqcomments TEXT_STRING;
create variable @acexamples TEXT_STRING;
call up_selfassessform_view (75000146, 3, @eqcomments, @acexamples);
select @eqcomments, @acexamples;

 - which returns the correct values from the DB (so I know the SPROC is good).

I have configured the out param in ADO.NET like so (which has worked up until now for 'integer', 'timestamp', 'varchar(255)', etc):

SAParameter as_acexplanation   = cmd.CreateParameter();

as_acexplanation.Direction     = ParameterDirection.Output;
as_acexplanation.ParameterName = "as_acexplanation";
as_acexplanation.SADbType      = SADbType.LongVarchar;

cmd.Parameters.Add(as_acexplanation);

When I run the following code:

SADataReader reader = cmd.ExecuteReader();

I receive the following error:

Parameter[2]: the Size property has an invalid size of 0.

Which (I suppose) makes sense...

But the thing is, I don't know the size of the field (it's just "long varchar" it doesn't have a predetermined length - unlike varchar(XXX)).

Anyhow, just for fun, I add the following:

as_acexplanation.Size = 1000;

and the above error goes away, but now when I call:

as_acexplanation.Value

i get back a string of length = 1000 which is just '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0...' (\0 repeated 1000 times).

So I'm really really stuck... Any help one this one would be much appreciated.

Cheers! ;)

Tod T.

Answer 1

Have you tried not passing the parameter data type and letting ADO.NET figure it out on its own?

Answer 2

Have you tried setting the size to -1?

Look at this answer.