SAParameter long binary size always 32k

[Former Member]

Hi, I am using the iAnywhere ADO.NET provider. After calling SACommandBuilder.DeriveParameters() on a stored procedure, any long binary or long nvarchar parameters have their size set to 32767. From what I read this is the default size of 32k. So far I get it.

However, after setting the parameter values with data longer than 32k, the size property still reads 32767 and therefore only a portion of my data actually gets passed to the stored procedure. I guess my question is, should the Size property of my parameter automatically adjust based on the content of the Value property? I'm thinking it should - but am reluctant to report it as a bug if I am missing something.

Almost forgot to mention - I've tried this with both version 12 and 16 (latest builds).

Thanks Simon

[Former Member]

Having researched further, I think the Size of a long binary parameter should be -1, not 32767. When manually setting the Size to 0 in code, all seems to work correctly. I'm edging ever closer to thinking this is indeed a bug. Anyone else have experience with this?

[Former Member]

Correction to my above comment, the Size of a long binary parameter should be 0. I have now overcome this problem by manually coding for this, also checking for long nvarchar parameters also. I think this is a bug in the ADO.NET provider though...

[jeff_albion · Accepted solution]

UPDATE: 2013/11/19 - CR #751207 has now been fixed in 12.0.1.4015 and 16.0.0.1734 and higher

---

Hi Simon,

Yes, I can also reproduce this issue - thanks for the report. When posting to the forum in the future to report an error though, please also post a short code sample to illustrate what you're trying specifically to see the error - this will help us reproduce your issue more quickly.

Here is what I tried:

SQL:

create procedure p1 ( @in_val long varchar)
begin
  select 1;
end;

C#:

SAConnection conn = new SAConnection("uid=dba;pwd=sql;eng=demo");
conn.Open();

SACommand comm = new SACommand();
comm.Connection = conn;
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.CommandText = "p1";

SACommandBuilder.DeriveParameters(comm);
foreach (SAParameter saparm in comm.Parameters){
  Console.Out.WriteLine("Parameter: " + saparm.ParameterName + ", size: " + saparm.Size + "\\n");
}

You can note that the procedure parameters are actually described by the system table SYS.SYSPROCPARMS in the SQL Anywhere database (which is where the ADO.NET provider looks for these values).

A 'long varchar' parameter (and other LOBs) is described as 32767 bytes there also, however this usage is really intended for allocating the largest buffer size that can be used in an ODBC/ESQL environment (e.g. is the return value in SQLDescribeParam and corresponds to a LOB's transfer octet length), so the system value is not really 'incorrect' from that usage standpoint.

However, I would agree that this seems to be a bug for ADO.NET in the provider environment, and that the value should really be '2147483647' (the actual maximum length):

From: SqlParameter.Size Property

For variable-length data types, Size describes the maximum amount of data to transmit to the server.

As a side-note, in Microsoft SQL Server via SqlCommandBuilder.DeriveParameters with similar code as above, this field is described back as length -1 as you had originally indicated:

CREATE PROCEDURE p1
@p1 varbinary(max)
AS
BEGIN
SET NOCOUNT ON
    SELECT 1
END
GO

Results:

Parameter: @RETURN_VALUE, size: 0
Parameter: @p1, size: -1

I have now opened CR #751207 to address this issue. Your simple workaround for just setting the .Size explicitly should work until we have a fix available. Thank you again for the bug report.

[Former Member · Accepted solution]

Thanks for your response Jeff, yes I will post some sample code next time.