How do I get LongVarchar out param from SPROC in ADO.NET 2.0 with SQLAnywhere 10?
- by todthomson
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.