diff options
author | Damien Diederen <dd@crosstwine.com> | 2015-01-16 20:30:31 +0300 |
---|---|---|
committer | Damien Diederen <dd@crosstwine.com> | 2015-05-05 22:05:27 +0300 |
commit | f055c46a24ab01049ba6457d225259da948cdd30 (patch) | |
tree | 1ec752c23bf8b3cff58aded58b30f57f460e19ca /mcs/class/Mono.Data.Tds | |
parent | 81a0830d9e5bcb7b84638c4b465e5391d0e1cb25 (diff) |
[Mono.Data.Tds] Serialize SqlDbType.DateTime{2,Offset} as char(n)
DateTime2 and DateTimeOffset are not directly supported by the TDS 7.1
wire protocol, which is the version implemented by Mono's current TDS
driver. (Tds80.cs does in fact advertise version 0x00000071.)
Bumping the protocol version to 0x03000a73 or 0x03000b73, for TDS
7.3.A/7.3.B, is not sufficient; the stream generated by the driver
causes SQL Server to kill the connection right after the login packet.
More investigation is needed on that front.
SQL Server is fine, however, with us sending timestamps in
literal (ISO 8601) format down the wire, packed within ASCII char(n)
strings. Which is what this patch does, which allows client code
perform inserts using, e.g.:
var param = new SqlParameter("@Value", SqlDbType.DateTime2);
param.Value = DateTime.Now;
command.Parameters.Add(param);
Diffstat (limited to 'mcs/class/Mono.Data.Tds')
3 files changed, 97 insertions, 6 deletions
diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs index 54210049067..725248a8fc0 100644 --- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs +++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs @@ -586,6 +586,13 @@ namespace Mono.Data.Tds.Protocol } else if (colType == TdsColumnType.BigVarBinary) { if (size > 8000) colType = TdsColumnType.Image; + } else if (colType == TdsColumnType.DateTime2 || + colType == TdsColumnType.DateTimeOffset) { + // HACK: Wire-level DateTime{2,Offset} + // require TDS 7.3, which this driver + // does not implement correctly--so we + // serialize to ASCII instead. + colType = TdsColumnType.Char; } // Calculation of TypeInfo field /* @@ -715,6 +722,8 @@ namespace Mono.Data.Tds.Protocol case "nchar" : case "text" : case "ntext" : + case "datetime2": + case "datetimeoffset": byte [] tmp = param.GetBytes (); Comm.Append (tmp); break; diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs index af3f2271647..bc1f7fb0ba1 100644 --- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs +++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs @@ -35,6 +35,8 @@ namespace Mono.Data.Tds.Protocol { Char = 0x2f, // SYBCHAR DateTime = 0x3d, // SYBDATETIME DateTime4 = 0x3a, // SYBDATETIME4 + DateTime2 = 0x2a, // SYBMSDATETIME2 + DateTimeOffset = 0x2b, // SYBMSDATETIMEOFFSET DateTimeN = 0x6f, // SYBDATETIMN Decimal = 0x6a, // SYBDECIMAL Real = 0x3b, // SYBREAL diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs index d8a4b34fb13..f567ed118da 100644 --- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs +++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs @@ -287,18 +287,31 @@ namespace Mono.Data.Tds { internal string Prepare () { string typeName = TypeName; - - if (typeName == "varbinary") { - int size = Size; + // Cf. GetDateTimeString + TdsColumnType actualType = TdsColumnType.Char; + int size; + + switch (typeName) { + case "varbinary": + size = Size; if (size <= 0) { size = GetActualSize (); } - + if (size > 8000) { typeName = "varbinary(max)"; } + break; + case "datetime2": + actualType = TdsColumnType.DateTime2; + typeName = "char"; + break; + case "datetimeoffset": + actualType = TdsColumnType.DateTimeOffset; + typeName = "char"; + break; } - + string includeAt = "@"; if (ParameterName [0] == '@') includeAt = ""; @@ -313,7 +326,7 @@ namespace Mono.Data.Tds { case "varchar": case "varbinary": //A size of 0 is not allowed in declarations. - int size = Size; + size = Size; if (size <= 0) { size = GetActualSize (); if (size <= 0) @@ -326,6 +339,14 @@ namespace Mono.Data.Tds { result.Append (paramSize > 0 ? (paramSize > 4000 ? "(max)" : String.Format ("({0})", paramSize)) : "(4000)"); break; case "char": + size = -1; + if (actualType != TdsColumnType.Char) + size = GetDateTimeStringLength (actualType); + else if (isSizeSet) + size = Size; + if (size > 0) + result.Append (String.Format ("({0})", size)); + break; case "nchar": case "binary": if (isSizeSet && Size > 0) @@ -366,6 +387,10 @@ namespace Mono.Data.Tds { case "float": case "money": return 8; + case "datetime2": + return GetDateTimeStringLength (TdsColumnType.DateTime2); + case "datetimeoffset": + return GetDateTimeStringLength (TdsColumnType.DateTimeOffset); case "int": case "real": case "smalldatetime": @@ -386,6 +411,53 @@ namespace Mono.Data.Tds { return size; } + private int GetDateTimePrecision () + { + int precision = Precision; + + // http://msdn.microsoft.com/en-us/library/bb677335.aspx + // says that default precision is 7. How do + // we distinguish that from zero? + if (precision == 0 || precision > 7) + precision = 7; + + return precision; + } + + private int GetDateTimeStringLength (TdsColumnType type) + { + int precision = GetDateTimePrecision (); + int len = precision == 0 ? 19 : 20 + precision; + + if (type == TdsColumnType.DateTimeOffset) + len += 6; + + return len; + } + + // HACK: Wire-level DateTime{2,Offset} require TDS + // 7.3, which this driver does not implement + // correctly--so we serialize to ASCII instead. + private string GetDateTimeString (TdsColumnType type) + { + int precision = GetDateTimePrecision (); + string fmt = "yyyy-MM-dd'T'HH':'mm':'ss"; + + if (precision > 0) + fmt += ".fffffff".Substring(0, precision + 1); + + switch (type) { + case TdsColumnType.DateTime2: + DateTime dt = (DateTime)Value; + return dt.ToString(fmt); + case TdsColumnType.DateTimeOffset: + DateTimeOffset dto = (DateTimeOffset)Value; + return dto.ToString(fmt + "zzz"); + } + + throw new ApplicationException("Should be unreachable"); + } + internal byte[] GetBytes () { byte[] result = {}; @@ -403,6 +475,10 @@ namespace Mono.Data.Tds { case "char" : case "text" : return Encoding.Default.GetBytes ((string)Value); + case "datetime2": + return Encoding.Default.GetBytes (GetDateTimeString (TdsColumnType.DateTime2)); + case "datetimeoffset": + return Encoding.Default.GetBytes (GetDateTimeString (TdsColumnType.DateTimeOffset)); default : return ((byte[]) Value); } @@ -441,6 +517,10 @@ namespace Mono.Data.Tds { if (IsNullable) return TdsColumnType.DateTimeN; return TdsColumnType.DateTime4; + case "datetime2": + return TdsColumnType.DateTime2; + case "datetimeoffset": + return TdsColumnType.DateTimeOffset; case "float": if (IsNullable) return TdsColumnType.FloatN ; |