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 |
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 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):
As a side-note, in Microsoft SQL Server via 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 Thanks for your response Jeff, yes I will post some sample code next time.
(19 Nov '13, 03:42)
Simon Smith
|
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?
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...