[Mono-bugs] [Bug 699643] New: saving a DBNull field saves as and empty string in the sqlserver database
bugzilla_noreply at novell.com
bugzilla_noreply at novell.com
Mon Jun 13 16:37:00 EDT 2011
https://bugzilla.novell.com/show_bug.cgi?id=699643
https://bugzilla.novell.com/show_bug.cgi?id=699643#c0
Summary: saving a DBNull field saves as and empty string in the
sqlserver database
Classification: Mono
Product: Mono: Runtime
Version: SVN
Platform: x86-64
OS/Version: RHEL 5
Status: NEW
Severity: Normal
Priority: P5 - None
Component: misc
AssignedTo: mono-bugs at lists.ximian.com
ReportedBy: srfcanada at hotmail.com
QAContact: mono-bugs at lists.ximian.com
Found By: ---
Blocker: ---
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; .NET40C;
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, at p1 int, at p2 bit, at p3
bit, at p4 datetime, at p5 uniqueidentifier, at p6 nvarchar(4000), at p7 varbinary(max)
, at p8 nvarchar(max) , at p9 bigint, at p10 nvarchar(max) , at p11 uniqueidentifier, at p12
int, at p13 nvarchar(2000), at p14 varbinary(max) , at p15 varbinary(max) , at p16
varchar(8000), at p17 varbinary(max) , at p18 bigint, at p19 int, at p20 varchar(8000), at p21
smallint, at p22 real, at p23 varbinary(max) , at p24 float, at p25 nvarchar(4000), at p26
uniqueidentifier, at p27 uniqueidentifier, at p28 bit, at p29 tinyint, at p30
varbinary(max) , at p31 nvarchar(2000), at p32 datetime, at p33
uniqueidentifier', at p0=1, at p1=0, at p2=0, at p3=0, at p4='1900-01-01
00:00:00', at p5='00000000-0000-0000-0000-000000000000', at p6=NULL, at p7=NULL, at p8=NULL, at p9=0, at p10=N'', at p11='00000000-0000-0000-0000-000000000000', at p12=0, at p13=N'ChildAObject
{baachcfiacbilejejlcoddifhppadpib}', at p14=NULL, at p15=NULL, at p16='0', at p17=NULL, at p18=0, at p19=0, at p20='0', at p21=0, at p22=0, at p23=NULL, at p24=0, at p25=NULL, at p26='DD846FC8-77D9-4935-A4C4-C1F029E8838A', at p27='00000000-0000-0000-0000-000000000000', at p28=1, at p29=0, at p30=NULL, at p31=N'', at p32='2011-06-13
19:34:43.600', at 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', at p0=1, at p1=0, at p2=0, at p3=0, at p4='1900-01-01
00:00:00', at p5='00000000-0000-0000-0000-000000000000', at p6=NULL, at p7=NULL, at p8=NULL, at p9=0, at p10=N'', at p11='00000000-0000-0000-0000-000000000000', at p12=0, at p13=N'ChildAObject
{gbclfcohokcdmhoejkelejfaihgagiil}', at p14=NULL, at p15=NULL, at p16='0', at p17=NULL, at p18=0, at p19=0, at p20='0', at p21=0, at p22=0, at p23=NULL, at p24=0, at p25=NULL, at p26='AAAAF4F7-2121-4467-A5A1-57AE9332F0A2', at p27='00000000-0000-0000-0000-000000000000', at p28=1, at p29=0, at p30=NULL, at p31=N'', at p32='2011-06-13
20:21:02.380', at 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.
--
Configure bugmail: https://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.
You are the assignee for the bug.
More information about the mono-bugs
mailing list