2 // Mono.Data.Tds.Protocol.Tds50.cs
5 // Tim Coleman (tim@timcoleman.com)
7 // Copyright (C) 2002 Tim Coleman
11 // Permission is hereby granted, free of charge, to any person obtaining
12 // a copy of this software and associated documentation files (the
13 // "Software"), to deal in the Software without restriction, including
14 // without limitation the rights to use, copy, modify, merge, publish,
15 // distribute, sublicense, and/or sell copies of the Software, and to
16 // permit persons to whom the Software is furnished to do so, subject to
17 // the following conditions:
19 // The above copyright notice and this permission notice shall be
20 // included in all copies or substantial portions of the Software.
22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
35 namespace Mono
.Data
.Tds
.Protocol
37 [MonoTODO ("FIXME: Can packetsize be anything other than 512?")]
38 public sealed class Tds50
: Tds
42 public static readonly TdsVersion Version
= TdsVersion
.tds50
;
50 public Tds50 (string server
, int port
)
51 : this (server
, port
, 512, 15)
55 public Tds50 (string server
, int port
, int packetSize
, int timeout
)
56 : base (server
, port
, packetSize
, timeout
, Version
)
58 this.packetSize
= packetSize
;
61 #endregion // Constructors
65 public string BuildExec (string sql
)
67 if (Parameters
== null || Parameters
.Count
== 0)
70 StringBuilder
select = new StringBuilder ();
71 StringBuilder
set = new StringBuilder ();
72 StringBuilder declare
= new StringBuilder ();
74 foreach (TdsMetaParameter p
in Parameters
) {
75 declare
.Append (String
.Format ("declare {0}\n", p
.Prepare ()));
76 set.Append (String
.Format ("select {0}=", p
.ParameterName
));
77 if (p
.Direction
== TdsParameterDirection
.Input
)
78 set.Append (FormatParameter (p
));
81 select.Append (p
.ParameterName
);
83 select.Append ("select ");
90 return String
.Format ("{0}{1}{2}\n{3}", declare
.ToString (), set.ToString (), sql
, select.ToString ());
93 public override bool Connect (TdsConnectionParameters connectionParameters
)
96 throw new InvalidOperationException ("The connection is already open.");
98 byte[] capabilityRequest
= {0x03, 0xef, 0x65, 0x41, 0xff, 0xff, 0xff, 0xd6}
;
99 byte[] capabilityResponse
= {0x00, 0x00, 0x00, 0x06, 0x48, 0x00, 0x00, 0x08}
;
101 SetCharset (connectionParameters
.Charset
);
102 SetLanguage (connectionParameters
.Language
);
105 byte[] empty
= new byte[0];
107 Comm
.StartPacket (TdsPacketType
.Logon
);
109 // hostname (offset 0)
111 byte[] tmp
= Comm
.Append (connectionParameters
.Hostname
, 30, pad
);
112 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
114 // username (offset 31 0x1f)
116 tmp
= Comm
.Append (connectionParameters
.User
, 30, pad
);
117 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
119 // password (offset 62 0x3e)
121 tmp
= Comm
.Append (connectionParameters
.Password
, 30, pad
);
122 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
124 // hostproc (offset 93 0x5d)
126 tmp
= Comm
.Append ("37876", 30, pad
);
127 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
129 // Byte order of 2 byte ints
130 // 2 = <MSB, LSB>, 3 = <LSB, MSB>
132 Comm
.Append ((byte) 3);
134 // Byte order of 4 byte ints
135 // 0 = <MSB, LSB>, 1 = <LSB, MSB>
137 Comm
.Append ((byte) 1);
139 // Character representation
140 // (6 = ASCII, 7 = EBCDIC)
142 Comm
.Append ((byte) 6);
144 // Eight byte floating point representation
145 // 4 = IEEE <MSB, ..., LSB>
147 // 10 = IEEE <LSB, ..., MSB>
150 Comm
.Append ((byte) 10);
152 // Eight byte date format
153 // 8 = <MSB, ..., LSB>
155 Comm
.Append ((byte) 9);
159 Comm
.Append ((byte) 1);
161 // disallow dump/load and bulk insert
163 Comm
.Append ((byte) 1);
165 // sql interface type
167 Comm
.Append ((byte) 0);
169 // type of network connection
171 Comm
.Append ((byte) 0);
175 Comm
.Append (empty
, 7, pad
);
179 tmp
= Comm
.Append (connectionParameters
.ApplicationName
, 30, pad
);
180 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
184 tmp
= Comm
.Append (DataSource
, 30, pad
);
185 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
189 Comm
.Append (empty
, 2, pad
);
190 tmp
= Comm
.Append (connectionParameters
.Password
, 253, pad
);
191 Comm
.Append ((byte) (tmp
.Length
< 253 ? tmp
.Length
+ 2 : 253 + 2));
195 Comm
.Append ((byte) 5);
196 Comm
.Append ((byte) 0);
197 Comm
.Append ((byte) 0);
198 Comm
.Append ((byte) 0);
202 tmp
= Comm
.Append (connectionParameters
.ProgName
, 10, pad
);
203 Comm
.Append ((byte) (tmp
.Length
< 10 ? tmp
.Length
: 10));
207 Comm
.Append ((byte) 6);
208 Comm
.Append ((byte) 0);
209 Comm
.Append ((byte) 0);
210 Comm
.Append ((byte) 0);
212 // auto convert short
214 Comm
.Append ((byte) 0);
218 Comm
.Append ((byte) 0x0d);
222 Comm
.Append ((byte) 0x11);
226 tmp
= Comm
.Append (Language
, 30, pad
);
227 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
229 // notify on lang change
231 Comm
.Append ((byte) 1);
233 // security label hierarchy
235 Comm
.Append ((short) 0);
237 // security components
239 Comm
.Append (empty
, 8, pad
);
243 Comm
.Append ((short) 0);
245 // security login role
247 Comm
.Append ((byte) 0);
251 tmp
= Comm
.Append (Charset
, 30, pad
);
252 Comm
.Append ((byte) (tmp
.Length
< 30 ? tmp
.Length
: 30));
254 // notify on charset change
256 Comm
.Append ((byte) 1);
258 // length of tds packets
260 tmp
= Comm
.Append (this.packetSize
.ToString (), 6, pad
);
261 Comm
.Append ((byte) (tmp
.Length
< 6 ? tmp
.Length
: 6));
263 Comm
.Append (empty
, 8, pad
);
266 //Comm.Append (empty, 4, pad);
269 Comm
.Append ((byte) TdsPacketSubType
.Capability
);
270 Comm
.Append ((short) 20);
271 Comm
.Append ((byte) 0x01); // TDS_CAP_REQUEST
272 Comm
.Append (capabilityRequest
);
273 Comm
.Append ((byte) 0x02);
274 Comm
.Append (capabilityResponse
);
284 public override void ExecPrepared (string id
, TdsMetaParameterCollection parameters
, int timeout
, bool wantResults
)
286 Parameters
= parameters
;
287 bool hasParameters
= (Parameters
!= null && Parameters
.Count
> 0);
289 Comm
.StartPacket (TdsPacketType
.Normal
);
291 Comm
.Append ((byte) TdsPacketSubType
.Dynamic
);
292 Comm
.Append ((short) (id
.Length
+ 5));
293 Comm
.Append ((byte) 0x02); // TDS_DYN_EXEC
294 Comm
.Append ((byte) (hasParameters
? 0x01 : 0x00));
295 Comm
.Append ((byte) id
.Length
);
297 Comm
.Append ((short) 0);
306 CheckForData (timeout
);
311 public override void Execute (string sql
, TdsMetaParameterCollection parameters
, int timeout
, bool wantResults
)
313 Parameters
= parameters
;
314 string ex
= BuildExec (sql
);
315 ExecuteQuery (ex
, timeout
, wantResults
);
318 public override void ExecProc (string commandText
, TdsMetaParameterCollection parameters
, int timeout
, bool wantResults
)
320 Parameters
= parameters
;
321 ExecuteQuery (BuildProcedureCall (commandText
), timeout
, wantResults
);
324 private string BuildProcedureCall (string procedure
)
326 string exec
= String
.Empty
;
328 StringBuilder declare
= new StringBuilder ();
329 StringBuilder
select = new StringBuilder ();
330 StringBuilder
set = new StringBuilder ();
333 if (Parameters
!= null) {
334 foreach (TdsMetaParameter p
in Parameters
) {
335 if (p
.Direction
!= TdsParameterDirection
.Input
) {
338 select.Append ("select ");
340 select.Append (", ");
341 select.Append (p
.ParameterName
);
343 declare
.Append (String
.Format ("declare {0}\n", p
.Prepare ()));
345 if (p
.Direction
!= TdsParameterDirection
.ReturnValue
) {
346 if( p
.Direction
== TdsParameterDirection
.InputOutput
)
347 set.Append (String
.Format ("set {0}\n", FormatParameter(p
)));
349 set.Append (String
.Format ("set {0}=NULL\n", p
.ParameterName
));
355 if (p
.Direction
== TdsParameterDirection
.ReturnValue
)
356 exec
= p
.ParameterName
+ "=";
359 exec
= "exec " + exec
;
361 string sql
= String
.Format ("{0}{1}{2}{3} {4}\n{5}", declare
.ToString (),
364 BuildParameters (), select.ToString ());
368 private string BuildParameters ()
370 if (Parameters
== null || Parameters
.Count
== 0)
373 StringBuilder result
= new StringBuilder ();
374 foreach (TdsMetaParameter p
in Parameters
) {
375 if (p
.Direction
!= TdsParameterDirection
.ReturnValue
) {
376 if (result
.Length
> 0)
377 result
.Append (", ");
378 if (p
.Direction
== TdsParameterDirection
.InputOutput
)
379 result
.Append (String
.Format("{0}={0} output", p
.ParameterName
));
381 result
.Append (FormatParameter (p
));
384 return result
.ToString ();
388 private string FormatParameter (TdsMetaParameter parameter
)
390 if (parameter
.Direction
== TdsParameterDirection
.Output
)
391 return String
.Format ("{0} output", parameter
.ParameterName
);
393 if (parameter
.Value
== null || parameter
.Value
== DBNull
.Value
)
396 switch (parameter
.TypeName
) {
397 case "smalldatetime":
399 DateTime d
= (DateTime
)parameter
.Value
;
400 return String
.Format(System
.Globalization
.CultureInfo
.InvariantCulture
,
401 "'{0:MMM dd yyyy hh:mm:ss tt}'", d
);
411 return parameter
.Value
.ToString ();
414 return String
.Format ("N'{0}'", parameter
.Value
.ToString ().Replace ("'", "''"));
415 case "uniqueidentifier":
416 return String
.Format ("0x{0}", ((Guid
) parameter
.Value
).ToString ("N"));
418 if (parameter
.Value
.GetType () == typeof (bool))
419 return (((bool) parameter
.Value
) ? "0x1" : "0x0");
420 return parameter
.Value
.ToString ();
424 return String
.Format ("0x{0}", BitConverter
.ToString ((byte[]) parameter
.Value
).Replace ("-", string.Empty
).ToLower ());
426 return String
.Format ("'{0}'", parameter
.Value
.ToString ().Replace ("'", "''"));
430 public override string Prepare (string sql
, TdsMetaParameterCollection parameters
)
432 Parameters
= parameters
;
434 Random rand
= new Random ();
435 StringBuilder idBuilder
= new StringBuilder ();
436 for (int i
= 0; i
< 25; i
+= 1)
437 idBuilder
.Append ((char) (rand
.Next (26) + 65));
438 string id
= idBuilder
.ToString ();
440 //StringBuilder declare = new StringBuilder ();
443 sql
= String
.Format ("create proc {0} as\n{1}", id
, sql
);
444 short len
= (short) ((id
.Length
) + sql
.Length
+ 5);
446 Comm
.StartPacket (TdsPacketType
.Normal
);
447 Comm
.Append ((byte) TdsPacketSubType
.Dynamic
);
449 Comm
.Append ((byte) 0x1); // PREPARE
450 Comm
.Append ((byte) 0x0); // UNUSED
451 Comm
.Append ((byte) id
.Length
);
453 Comm
.Append ((short) sql
.Length
);
463 protected override void ProcessColumnInfo ()
465 isSelectQuery
= true;
466 /*int totalLength = */Comm
.GetTdsShort ();
467 int count
= Comm
.GetTdsShort ();
468 for (int i
= 0; i
< count
; i
+= 1) {
469 string columnName
= Comm
.GetString (Comm
.GetByte ());
470 int status
= Comm
.GetByte ();
471 bool hidden
= (status
& 0x01) > 0;
472 bool isKey
= (status
& 0x02) > 0;
473 bool isRowVersion
= (status
& 0x04) > 0;
474 bool isUpdatable
= (status
& 0x10) > 0;
475 bool allowDBNull
= (status
& 0x20) > 0;
476 bool isIdentity
= (status
& 0x40) > 0;
478 Comm
.Skip (4); // User type
480 byte type
= Comm
.GetByte ();
481 bool isBlob
= (type
== 0x24);
483 TdsColumnType columnType
= (TdsColumnType
) type
;
489 if (columnType
== TdsColumnType
.Text
|| columnType
== TdsColumnType
.Image
) {
490 bufLength
= Comm
.GetTdsInt ();
491 Comm
.Skip (Comm
.GetTdsShort ());
493 else if (IsFixedSizeColumn (columnType
))
494 bufLength
= LookupBufferSize (columnType
);
496 //bufLength = Comm.GetTdsShort ();
497 bufLength
= Comm
.GetByte ();
499 if (columnType
== TdsColumnType
.Decimal
|| columnType
== TdsColumnType
.Numeric
) {
500 precision
= Comm
.GetByte ();
501 scale
= Comm
.GetByte ();
504 Comm
.Skip (Comm
.GetByte ()); // Locale
506 Comm
.Skip (Comm
.GetTdsShort ()); // Class ID
508 TdsDataColumn col
= new TdsDataColumn ();
511 col
.ColumnType
= columnType
;
512 col
.ColumnName
= columnName
;
513 col
.IsIdentity
= isIdentity
;
514 col
.IsRowVersion
= isRowVersion
;
515 col
.ColumnType
= columnType
;
516 col
.ColumnSize
= bufLength
;
517 col
.NumericPrecision
= precision
;
518 col
.NumericScale
= scale
;
519 col
.IsReadOnly
= !isUpdatable
;
521 col
.AllowDBNull
= allowDBNull
;
522 col
.IsHidden
= hidden
;
524 col
["ColumnType"] = columnType
;
525 col
["ColumnName"] = columnName
;
526 col
["IsIdentity"] = isIdentity
;
527 col
["IsRowVersion"] = isRowVersion
;
528 col
["ColumnType"] = columnType
;
529 col
["ColumnSize"] = bufLength
;
530 col
["NumericPrecision"] = precision
;
531 col
["NumericScale"] = scale
;
532 col
["IsReadOnly"] = !isUpdatable
;
533 col
["IsKey"] = isKey
;
534 col
["AllowDBNull"] = allowDBNull
;
535 col
["IsHidden"] = hidden
;
540 private void SendParamFormat ()
542 Comm
.Append ((byte) TdsPacketSubType
.ParamFormat
);
544 int len
= 2 + (8 * Parameters
.Count
);
545 TdsColumnType metaType
;
546 foreach (TdsMetaParameter p
in Parameters
) {
547 metaType
= p
.GetMetaType ();
548 if (!IsFixedSizeColumn (metaType
))
550 if (metaType
== TdsColumnType
.Numeric
|| metaType
== TdsColumnType
.Decimal
)
554 Comm
.Append ((short) len
);
555 Comm
.Append ((short) Parameters
.Count
);
557 foreach (TdsMetaParameter p
in Parameters
) {
558 string locale
= String
.Empty
;
559 string parameterName
= String
.Empty
;
565 if (p
.Direction
== TdsParameterDirection
.Output
)
568 metaType
= p
.GetMetaType ();
570 Comm
.Append ((byte) parameterName
.Length
);
571 Comm
.Append (parameterName
);
572 Comm
.Append (status
);
573 Comm
.Append (userType
);
574 Comm
.Append ((byte) metaType
);
576 if (!IsFixedSizeColumn (metaType
))
577 Comm
.Append ((byte) p
.Size
); // MAXIMUM SIZE
578 if (metaType
== TdsColumnType
.Numeric
|| metaType
== TdsColumnType
.Decimal
) {
579 Comm
.Append (p
.Precision
);
580 Comm
.Append (p
.Scale
);
582 Comm
.Append ((byte) locale
.Length
);
583 Comm
.Append (locale
);
587 private void SendParams ()
589 Comm
.Append ((byte) TdsPacketSubType
.Parameters
);
591 TdsColumnType metaType
;
592 foreach (TdsMetaParameter p
in Parameters
) {
593 metaType
= p
.GetMetaType ();
594 bool isNull
= (p
.Value
== DBNull
.Value
|| p
.Value
== null);
595 if (!IsFixedSizeColumn (metaType
))
596 Comm
.Append ((byte) p
.GetActualSize ());
598 Comm
.Append (p
.Value
);
602 public override void Unprepare (string statementId
)
604 Comm
.StartPacket (TdsPacketType
.Normal
);
605 Comm
.Append ((byte) TdsPacketSubType
.Dynamic
);
606 Comm
.Append ((short) (3 + statementId
.Length
));
607 Comm
.Append ((byte) 0x04);
608 Comm
.Append ((byte) 0x00);
609 Comm
.Append ((byte) statementId
.Length
);
610 Comm
.Append (statementId
);
611 //Comm.Append ((short) 0);
618 protected override bool IsValidRowCount (byte status
, byte op
)
621 return (isSelectQuery
= false);
623 // TODO : Need to figure out how to calculate rowcount inside stored
624 // procedures. For now, Ignoring RowCount if they are returned by
625 // statements executing inside a StoredProcedure
627 if (((status
& (byte)0x40) != 0) || ((status
& (byte)0x10) == 0))
633 #endregion // Methods