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