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

asked 13 Nov '13, 04:59

Simon%20Smith's gravatar image

Simon Smith
121338
accept rate: 0%

edited 13 Nov '13, 05:12

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?

(13 Nov '13, 09:26) Simon Smith

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...

(14 Nov '13, 05:00) Simon Smith

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.

permanent link

answered 15 Nov '13, 12:37

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 19 Nov '13, 16:32

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

(19 Nov '13, 03:42) Simon Smith
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×204
×35
×19

question asked: 13 Nov '13, 04:59

question was seen: 1,242 times

last updated: 19 Nov '13, 16:32