Bug 16892 - Exception during execution if SqlParameter value is DbNull and SqlDbType is specified
Summary: Exception during execution if SqlParameter value is DbNull and SqlDbType is s...
Status: NEW
Alias: None
Product: Class Libraries
Classification: Mono
Component: System.Data (show other bugs)
Version: 3.2.x
Hardware: PC All
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2013-12-19 06:15 UTC by peterzt
Modified: 2017-09-01 09:29 UTC (History)
19 users (show)

See Also:
Tags:
Is this bug a regression?: ---
Last known good build:


Attachments

Description peterzt 2013-12-19 06:15:05 UTC
Mono crashes with exception below.
Here is minimal code
using (var _cnt = new SqlConnection("..."))
			{
				_cnt.Open ();
				using (var _cmd = _cnt.CreateCommand()) {
					_cmd.CommandText = "update Job Set FinishDt=@FinishDt";
					var _par = new SqlParameter ("@FinishDt", System.Data.SqlDbType.DateTime);
					//_par.ResetSqlDbType (); <-- If line is uncommented, then execution succeeds, otherwise fails
					_par.Value = DBNull.Value;
					_cmd.Parameters.Add(_par);
					_cmd.ExecuteNonQuery();
				}

			}

Exception:
Unhandled Exception:
System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@FinishDt"):
Data type 0x6F has an invalid data length or metadata length.
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00032] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlConnection.cs:318
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage (Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x0000b] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1719
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (TdsPacketSubType subType) [0x000ef] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1759
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x0012a] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1811
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x0004a] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:641
  at Mono.Data.Tds.Protocol.Tds.SkipToEnd () [0x00005] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:714
  at Mono.Data.Tds.Protocol.Tds70.ExecRPC (TdsRpcProcId rpcId, System.String sql, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults) [0x000ad] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds70.cs:505
  at Mono.Data.Tds.Protocol.Tds80.Execute (System.String commandText, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults) [0x0003e] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds80.cs:241
  at System.Data.SqlClient.SqlCommand.Execute (Boolean wantResults) [0x00202] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlCommand.cs:536
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery () [0x00015] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlCommand.cs:571
  at Test.MainClass.Main (System.String[] args) [0x0004b] in /home/peter/Test/Test/Program.cs:20
[ERROR] FATAL UNHANDLED EXCEPTION: System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@FinishDt"): Data type 0x6F has an invalid data length or metadata length.
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00032] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlConnection.cs:318
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage (Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x0000b] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1719
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (TdsPacketSubType subType) [0x000ef] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1759
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x0012a] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:1811
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x0004a] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:641
  at Mono.Data.Tds.Protocol.Tds.SkipToEnd () [0x00005] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds.cs:714
  at Mono.Data.Tds.Protocol.Tds70.ExecRPC (TdsRpcProcId rpcId, System.String sql, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults) [0x000ad] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds70.cs:505
  at Mono.Data.Tds.Protocol.Tds80.Execute (System.String commandText, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults) [0x0003e] in C:\cygwin\sources\mono\mcs\class\Mono.Data.Tds\Mono.Data.Tds.Protocol\Tds80.cs:241
  at System.Data.SqlClient.SqlCommand.Execute (Boolean wantResults) [0x00202] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlCommand.cs:536
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery () [0x00015] in C:\cygwin\sources\mono\mcs\class\System.Data\System.Data.SqlClient\SqlCommand.cs:571
  at Test.MainClass.Main (System.String[] args) [0x0004b] in /home/peter/Test/Test/Program.cs:20
Comment 1 Mauricio 2014-01-11 12:13:38 UTC
I am experiencing the same issue, but with TinyInt datatype. However, this doesn't happen .NET for desktop, so I cannot share code between the PC and the Mobile devices.

Any idea why is this happening?

Thanks for your support.
Comment 2 Mauricio 2014-01-11 12:58:59 UTC
One thing that make me think it is a bug, is that the same procedure in an earlier version worked fine.
Comment 3 Danre 2014-03-05 00:19:55 UTC
I have same issue also on latest mono 3.2.8

CODE:

SqlConnection con = new SqlConnection(textview3.Buffer.Text);
con.Open();

SqlParameter account1 = new SqlParameter ();
account1.ParameterName = "Account1";
account1.Direction = ParameterDirection.Input;
account1.Value = 47062;
account1.DbType = DbType.Int32;

SqlParameter account2 = new SqlParameter ();
account2.ParameterName = "Account2";
account2.Direction = ParameterDirection.Input;
account2.Value = 47162;
account2.DbType = DbType.Int32;

SqlParameter corr1 = new SqlParameter ();
corr1.ParameterName = "Corr1";
corr1.DbType = DbType.Int32;
corr1.Direction = ParameterDirection.Input;
corr1.Value = DBNull.Value;

SqlParameter corr2 = new SqlParameter ();
corr2.ParameterName = "Corr2";
corr2.DbType = DbType.Int32;
corr2.Direction = ParameterDirection.Input;
corr2.Value = DBNull.Value;

SqlCommand c = new SqlCommand ();
c.Connection = con;
c.CommandText = "dbo.GetBacount";
c.CommandTimeout = 0;
c.CommandType = CommandType.StoredProcedure;

c.Parameters.Add (account1);
c.Parameters.Add (account2);
c.Parameters.Add (corr1);
c.Parameters.Add (corr2);

SqlDataAdapter adapter = new SqlDataAdapter (c);

var dataSet = new DataSet ();

adapter.Fill (dataSet);

EXCEPTION:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@Corr1"): Data type 0x26 has an invalid data length or metadata length.

STACKSTRACE:
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender, Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00032] in /home/danre/monobuild/mono/mcs/class/System.Data/System.Data.SqlClient/SqlConnection.cs:318 
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage (Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x0000b] in /home/danre/monobuild/mono/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds.cs:1719 
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (TdsPacketSubType subType) [0x000ef] in /home/danre/monobuild/mono/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds.cs:1759 
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x0012a] in /home/danre/monobuild/mono/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds.cs:1811 
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x0004a] in /home/danre/monobuild/mono/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds.cs:641 
  at System.Data.SqlClient.SqlDataReader.NextResult () [0x0003b] in /home/danre/monobuild/mono/mcs/class/System.Data/System.Data.SqlClient/SqlDataReader.cs:1515 
  at System.Data.SqlClient.SqlDataReader..ctor (System.Data.SqlClient.SqlCommand command) [0x00019] in /home/danre/monobuild/mono/mcs/class/System.Data/System.Data.SqlClient/SqlDataReader.cs:113 
  at (wrapper remoting-invoke-with-check) System.Data.SqlClient.SqlDataReader:.ctor (System.Data.SqlClient.SqlCommand)
  at System.Data.SqlClient.SqlCommand.ExecuteReader (CommandBehavior behavior) [0x0003c] in /home/danre/monobuild/mono/mcs/class/System.Data/System.Data.SqlClient/SqlCommand.cs:597
Comment 4 Danre 2014-03-05 00:32:20 UTC
> is that the same procedure in an earlier version worked fine.
Which version mono worked fine?
Comment 5 Danre 2014-03-05 05:23:53 UTC
On mono 2.10.8.1 it works correct without exceptions
On latest mono 3.2.3, 3.2.8 incorrect

Looks like this bug crept between versions [from 2.10.9 to 3.2.3]
Comment 6 Andres G. Aragoneses 2014-03-05 05:28:55 UTC
Why did you add me to CC, any special reason?

Danre: do you have time to bisect it?
Comment 7 Danre 2014-03-05 05:46:47 UTC
>Why did you add me to CC, any special reason?
Mono.Data.Tds has bug, right?
I add to CC all people whos commit to Mono.Data.Tds
by history https://github.com/mono/mono/commits/master/mcs/class/Mono.Data.Tds
This bug on these people. Whats wrong?

>Danre: do you have time to bisect it?
I have no experience in the correction of such errors. But I think, the person which commit this bug knows how to fix it.
Comment 8 Andres G. Aragoneses 2014-03-05 05:59:07 UTC
> But I think, the person which commit this bug knows how to fix it.

Probably, but we still don't know who committed the bug, so someone needs to bisect it first. Any volunteers?
Comment 9 Danre 2014-03-05 06:24:40 UTC
Looks like problem in
https://github.com/mono/mono/commit/4109fd65d2e3f780fd5b905edd531dcf84e7b3cb

/*
+* If the value is null, not setting the size to 0 will cause varchar
+* fields to get inserted as an empty string rather than an null.
+*/
+if (param.Value == null || param.Value == DBNull.Value)
+  size = 0;

If comment out these lines, then everything is working correctly.
Who can commit a fix these?
Comment 10 Andres G. Aragoneses 2014-03-05 06:29:19 UTC
> Looks like problem in

Good catch!

Then, as you found a bug in a commit which didn't include unit tests, what you can do is:
- Write a unit test that fails with Mono master and works with commit 4109fd reverted.
- Create a pull request to mono that adds the unit test above, and reverts commit 4109fd.

This way, if the committer of 4109fd tries to fix his bug again, he will first need to make sure that he doesn't break the unit tests (existing functionality).
Comment 11 Danre 2014-03-06 06:12:15 UTC
https://github.com/mono/mono/pull/935
Comment 12 Neale Ferguson 2014-03-06 09:52:33 UTC
Before reverting please take a look at bug from old Bugzilla 699643. 

https://bugzilla.novell.com/show_bug.cgi?id=699643 

The original problem it was attempting to resolve was as follows:

When saving data into sqlserver, if the parameter value is equal to DBNull, it ends up storing and empty string rather than null. It seems to be related to the SqlParameter.Size field, if this is set to 0, it ends up storing null but if its anything else it will store and empty string. I guess you could ask way the Size field would be not 0 if the Value is null but the Size field doesn't have a direct relationship to the the actual size of the Value and you really shouldn't be using the size of the Value to generate the sqlserver statement. Running on windows it works but on mono it exhibits this behavior.

This site has been using this fix in production for over two years so reversion will break them, so rather than a straight reversion can we determine something that will make everyone happy?
Comment 13 Andres G. Aragoneses 2014-03-06 10:19:30 UTC
Neale, I guess what you need is simply a unit test that passes with your fix committed but fails with your commit reverted.

If that is committed before the revert, then the revert would break the unit test and it would be the one which is not acceptable ;)
Comment 14 Danre 2014-03-07 00:14:27 UTC
Neale, Well, you've fixed the bug https://bugzilla.novell.com/show_bug.cgi?id=699643
So you understand the problem.

Explain then how to fix the current bug?

Or do you think that exception like this:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol
stream is incorrect. Parameter [...] Data type [...] has an invalid data
length or metadata length."

is normal?


I tested on Ubuntu and Open Suse with mono 3.2.3 and 3.2.8. SqlServer 2008.

How to execute a query to avoid the above described error occurred?

There was an error and it should be solved somehow. I do not well understand the TDS protocol and have no idea how should be size of parameters, but the fact remains that, after you commit, We can not perform a simple query with null or DBNull.Value parameter.
Maybe size not need to assign a zero for all types? Only for varchar?
Comment 15 Mihi 2014-04-25 09:05:07 UTC
Any progress on this?

I think it's very serious, making parameters unusable in all sql statements and stored procedures where type in not string and you want to pass a null value. 

My simple test case on Ubuntu and Mono 3.4.1:

using (SqlCommand testParamCmd = connection.CreateCommand())
{
   testParamCmd.CommandText = "SELECT * FROM TestTbl WHERE testInt = @testParam;";
   SqlParameter param = testParamCmd.CreateParameter();
   param.SqlDbType = SqlDbType.Int;
   param.ParameterName = "@testParam";
   param.Value = DBNull.Value;

   testParamCmd.Parameters.Add(param);

   Console.WriteLine(testParamCmd.ExecuteNonQuery());
}
Comment 16 Andrew Knowles 2014-06-12 17:54:36 UTC
Can we at least make it only set the length to 0 for INPUT direction?

As is (with everything set to 0) it is unusable.
Comment 18 Jonathan Channon 2014-09-01 11:13:48 UTC
Is there any update to this?

I've just started using a codebase where they set the parameter value to DBNull.Value and it blows up. I can't seem to set the size, it always stays as 4. Setting IsNullable = true makes no difference.  

I can't believe this hasn't been addressed, seems such a common type of thing to do in code.

Is there a way to get it to work without waiting on a fix from Xamarin?

Thanks
Comment 19 Alexander Köplinger 2014-09-09 06:57:31 UTC
A pull request that claims to fix this bug was opened: https://github.com/mono/mono/pull/1265
Comment 20 Miguel de Icaza [MSFT] 2014-09-09 10:35:56 UTC
No tests for this stuff.
Comment 21 Miguel de Icaza [MSFT] 2014-09-09 10:43:22 UTC
Adding the original bug report that introduced this problem:

User-Agent:       Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.2; WOW64;
Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; MS-RTC LM 8; .NET4.0C;
.NET4.0E; .NET CLR 3.5.30729)

When saving data into sqlserver, if the parameter value is equal to DBNull, it
ends up storing and empty string rather than null. It seems to be related to
the SqlParameter.Size field, if this is set to 0, it ends up storing null but
if its anything else it will store and empty string. I guess you could ask way
the Size field would be not 0 if the Value is null but the Size field doesnt
have a direct relationship to the the actual size of the Value and you really
shoulding be using the size of the Value to generate the sqlserver statement.
Running on windows it works but on mono it exibits this behavior.This following
patch seems to fix it:

Tds70.cs insert this code into like 525:
         //if the value is null, not setting the size to 0 will cause varchar
fields to get inserted as an empty 
         //string rather than an null.
         if (param.Value == null || param.Value == DBNull.Value)
            size=0;

this will set the size to 0 if the value is null.  The more unusual thing is
how it generates the sql statements. We are using Nhibernate and its trying to
generate very consistent sql statement to help the performance of hitting the
query cache in the database. So , it will always using VarChar(max) for really
large fields but on mono, it will alway set the size in the query to the size
of the data which would cause database performance issues . For example,
following are 2 .net create statement generate different sql statement calls ,
one on windows and one on Mono:

//windows
exec sp_executesql N'INSERT INTO [CHILDAOBJECT] ([COMMITCOUNT], [BASECHILDINT],
[BASECHILDBOOL1], [BASECHILDBOOL2], [BASECHILDDATETIME], [BASECHILDGUID],
[BASECHILDTYPE], [BASECHILDSERIALIZABLEOBJECT], [BASECHILDSTRING],
[BASECHILDINT64], [PERSISTABLEBACKREFERENCES], [PERSISTABLECASECONTEXTID],
[PERSISTABLECASECONTEXTREFERENC], [NAME], [ROOTPARENT], [BASEOBJECT],
[BASECHILDUINT64], [ROOTOBJECT], [UINT], [USHORT], [ULONG], [SBYTE],
[BASECHILDFLOAT], [CHILDAOBJECT], [BASEDOUBLE], [DOMAINOBJECTTYPE],
[LINEAGEID], [PERSISTEDLABELID], [ISBACKREFERENCETRACKINGENABLED],
[CASECONTEXTBEHAVIOR], [GUIDEDTASKCONTEXTS], [DESCRIPTION], [CREATED], [ID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,
@p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25,
@p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33)',N'@p0 int,@p1 int,@p2 bit,@p3
bit,@p4 datetime,@p5 uniqueidentifier,@p6 nvarchar(4000),@p7 varbinary(max)
,@p8 nvarchar(max) ,@p9 bigint,@p10 nvarchar(max) ,@p11 uniqueidentifier,@p12
int,@p13 nvarchar(2000),@p14 varbinary(max) ,@p15 varbinary(max) ,@p16
varchar(8000),@p17 varbinary(max) ,@p18 bigint,@p19 int,@p20 varchar(8000),@p21
smallint,@p22 real,@p23 varbinary(max) ,@p24 float,@p25 nvarchar(4000),@p26
uniqueidentifier,@p27 uniqueidentifier,@p28 bit,@p29 tinyint,@p30
varbinary(max) ,@p31 nvarchar(2000),@p32 datetime,@p33
uniqueidentifier',@p0=1,@p1=0,@p2=0,@p3=0,@p4='1900-01-01
00:00:00',@p5='00000000-0000-0000-0000-000000000000',@p6=NULL,@p7=NULL,@p8=NULL,@p9=0,@p10=N'',@p11='00000000-0000-0000-0000-000000000000',@p12=0,@p13=N'ChildAObject
{baachcfiacbilejejlcoddifhppadpib}',@p14=NULL,@p15=NULL,@p16='0',@p17=NULL,@p18=0,@p19=0,@p20='0',@p21=0,@p22=0,@p23=NULL,@p24=0,@p25=NULL,@p26='DD846FC8-77D9-4935-A4C4-C1F029E8838A',@p27='00000000-0000-0000-0000-000000000000',@p28=1,@p29=0,@p30=NULL,@p31=N'',@p32='2011-06-13
19:34:43.600',@p33='650BAB45-721E-4328-87D7-9F0104337E8E'

//mono , note it doesnt use varchar(max) but uses the size of the Value
instead.
exec sp_executesql N'INSERT INTO [CHILDAOBJECT] ([COMMITCOUNT], [BASECHILDINT],
[BASECHILDBOOL1], [BASECHILDBOOL2], [BASECHILDDATETIME], [BASECHILDGUID],
[BASECHILDTYPE], [BASECHILDSERIALIZABLEOBJECT], [BASECHILDSTRING],
[BASECHILDINT64], [PERSISTABLEBACKREFERENCES], [PERSISTABLECASECONTEXTID],
[PERSISTABLECASECONTEXTREFERENC], [NAME], [ROOTPARENT], [BASEOBJECT],
[BASECHILDUINT64], [ROOTOBJECT], [UINT], [USHORT], [ULONG], [SBYTE],
[BASECHILDFLOAT], [CHILDAOBJECT], [BASEDOUBLE], [DOMAINOBJECTTYPE],
[LINEAGEID], [PERSISTEDLABELID], [ISBACKREFERENCETRACKINGENABLED],
[CASECONTEXTBEHAVIOR], [GUIDEDTASKCONTEXTS], [DESCRIPTION], [CREATED], [ID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,
@p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25,
@p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33)',N'@p0 int, @p1 int, @p2 bit,
@p3 bit, @p4 datetime, @p5 uniqueidentifier, @p6 nvarchar(4000), @p7 image, @p8
nvarchar(4000), @p9 bigint, @p10 nvarchar(1), @p11 uniqueidentifier, @p12 int,
@p13 nvarchar(47), @p14 image, @p15 image, @p16 varchar(8000), @p17 image, @p18
bigint, @p19 int, @p20 varchar(8000), @p21 smallint, @p22 real, @p23 image,
@p24 float, @p25 nvarchar(4000), @p26 uniqueidentifier, @p27 uniqueidentifier,
@p28 bit, @p29 tinyint, @p30 image, @p31 nvarchar(1), @p32 datetime, @p33
uniqueidentifier',@p0=1,@p1=0,@p2=0,@p3=0,@p4='1900-01-01
00:00:00',@p5='00000000-0000-0000-0000-000000000000',@p6=NULL,@p7=NULL,@p8=NULL,@p9=0,@p10=N'',@p11='00000000-0000-0000-0000-000000000000',@p12=0,@p13=N'ChildAObject
{gbclfcohokcdmhoejkelejfaihgagiil}',@p14=NULL,@p15=NULL,@p16='0',@p17=NULL,@p18=0,@p19=0,@p20='0',@p21=0,@p22=0,@p23=NULL,@p24=0,@p25=NULL,@p26='AAAAF4F7-2121-4467-A5A1-57AE9332F0A2',@p27='00000000-0000-0000-0000-000000000000',@p28=1,@p29=0,@p30=NULL,@p31=N'',@p32='2011-06-13
20:21:02.380',@p33='F850E930-A6E9-4363-8B41-9F01045DE52D'

notice on windows, its always using varbinary(max) when the SqlParameter(size)
field is set to 1073741823 and sqlserver client on windows will always pick up
on this being a varchar(max) value . Mono seems to ignore it and will use the
size of the field . This isnt really going to cause a bug other than this null
issue but it could be a performance issue given the sql statement could always
be different and have a harder time hitting the query cache on the database. 

If we want to look at the performance issue that would be great but its not a
show stopper but this null issue is since it would cause querying issues in the
future since its not storing the correct data so if at least we could apply the
patch I recomended that would be ok.


To actually test the null issue ,  this following code will cause this null
issue:
private static void SqlQueryTest()
      {
         string queryString =
             "INSERT INTO [CHILDAOBJECT] ([COMMITCOUNT], [BASECHILDSTRING],
[ID]) VALUES (@p0, @p1, @p2)";

         string connectionString = "Data
Source=1.1.1.1;Database=TESTSCOTT3;Integrated Security=True;Connection
timeout=200;";

         using (SqlConnection connection =
         new SqlConnection(connectionString))
         {
            // Create the Command and Parameter objects.
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@p0", 1);
            SqlParameter p1 = new SqlParameter("@p1", DBNull.Value);
            p1.Size = 1073741823;
            //p1.Size = 0; //setting it to 0 will cause it to store null
            command.Parameters.Add(p1);
            command.Parameters.AddWithValue("@p2", Guid.NewGuid());
            try
            {
               connection.Open();
               command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
               Console.WriteLine(ex.Message);

            }
            Console.ReadLine();
         }

database table:
CREATE TABLE [dbo].[CHILDAOBJECT](
    [ID] [uniqueidentifier] NOT NULL,
    [COMMITCOUNT] [int] NOT NULL,
    [BASECHILDINT] [int] NULL,
    [BASECHILDBOOL1] [bit] NULL,
    [BASECHILDBOOL2] [bit] NULL,
    [BASECHILDDATETIME] [datetime] NULL,
    [BASECHILDGUID] [uniqueidentifier] NULL,
    [BASECHILDTYPE] [nvarchar](2000) NULL,
    [BASECHILDSERIALIZABLEOBJECT] [varbinary](max) NULL,
    [BASECHILDSTRING] [nvarchar](max) NULL,
    [BASECHILDINT64] [bigint] NULL,
    [PERSISTABLEBACKREFERENCES] [nvarchar](max) NULL,
    [PERSISTABLECASECONTEXTID] [uniqueidentifier] NULL,
    [PERSISTABLECASECONTEXTREFERENC] [int] NULL,
    [NAME] [nvarchar](2000) NULL,
    [ROOTPARENT] [varbinary](max) NULL,
    [BASEOBJECT] [varbinary](max) NULL,
    [BASECHILDUINT64] [numeric](20, 0) NULL,
    [ROOTOBJECT] [varbinary](max) NULL,
    [UINT] [numeric](10, 0) NULL,
    [USHORT] [numeric](5, 0) NULL,
    [ULONG] [numeric](20, 0) NULL,
    [SBYTE] [numeric](3, 0) NULL,
    [BASECHILDFLOAT] [real] NULL,
    [CHILDAOBJECT] [varbinary](max) NULL,
    [BASEDOUBLE] [float] NULL,
    [DOMAINOBJECTTYPE] [nvarchar](2000) NULL,
    [LINEAGEID] [uniqueidentifier] NULL,
    [PERSISTEDLABELID] [uniqueidentifier] NULL,
    [ISBACKREFERENCETRACKINGENABLED] [bit] NULL,
    [CASECONTEXTBEHAVIOR] [tinyint] NULL,
    [GUIDEDTASKCONTEXTS] [varbinary](max) NULL,
    [CREATED] [datetime] NULL,
    [DESCRIPTION] [nvarchar](2000) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY]

GO

thanks

scott

Reproducible: Always

Steps to Reproduce:
1.
2.
3.
[reply] [-] Comment 1 Zoltan Varga 2011-06-14 19:19:02 UTC
-> sys.data.
[reply] [-] Comment 2 scott fluto 2011-06-15 21:42:12 UTC
looking at the performance issue around how it generates the parameter
information, if you do the following:

modify TdsMetaParameter.cs:
line 249:
from:
int paramSize = GetActualSize () / 2;
to:
int paramSize = Size < 1 ? GetActualSize () / 2 : Size;

and line 221:
from:
typeName = "image";
to:
typeName = "varbinary(max)";

doing this will cause the parameter information to generate the same TDS
parameter data the same as windows does and will generate the same TDS
Parameter data for the same sql command which should improve the query
performance since sqlserver will be able to look up the query in its cache
rather than have to parse it every time. 

Note that the change to line 221 may be an issue for older versions of
sqlserver that dont use varbinary(max) but keeping it as Image should still be
ok since it will still generate the same sql command.

Ill rerun our performance tests to see if I see and differences in performance.
Comment 22 Alex 2014-10-12 20:30:59 UTC
This is driving me crazy, all our SQL code are in sprocs, can't work like this, please fix! I appreciate your help.

We are on:

Mono JIT compiler version 3.10.0 ((detached/47db868 Tue Sep 30 03:36:53 EDT 2014)
Comment 23 Mladen Mihajlovic 2014-10-15 12:57:15 UTC
I also thing the importance of this bug needs to be increased. This is huge. Nothing works currently.
Comment 24 Alex 2014-10-15 13:37:11 UTC
Apparently it has been fixed, I assume it will be pushed on the next release:

https://github.com/mono/mono/pull/1265
Comment 25 Seref Arikan 2014-11-18 19:09:41 UTC
This problem is not specific to explicitly using parameters. I have a code base in which data table (ADO.NET) is used to update a db table by adding a row etc.
During the updates some columns are not assigned values and they are defined nullable in the db. 

When a data adapter is used to update the table, the underlying implementation most likely uses dbnull and and the update leads to the exception in this bug. 

This means I can't run this code under Mono. 

If I install the bleeding edge would the current fix at the github repo Alex has pointed at help? Should I expect that fix to have made its way into daily builds etc?

Note You need to log in before you can comment on or make changes to this bug.