mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds50.cs

   1 //
   2 // Mono.Data.Tds.Protocol.Tds50.cs
   3 //
   4 // Author:
   5 //   Tim Coleman (tim@timcoleman.com)
   6 //
   7 // Copyright (C) 2002 Tim Coleman
   8 //
   9
  10 //
  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:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  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.
  29 //
  30
  31 using Mono.Data.Tds;
  32 using System;
  33 using System.Text;
  34
  35 namespace Mono.Data.Tds.Protocol {
  36         [MonoTODO ("FIXME: Can packetsize be anything other than 512?")]
  37         public class Tds50 : Tds
  38         {
  39                 #region Fields
  40
  41                 public static readonly TdsVersion Version = TdsVersion.tds50;
  42                 int packetSize;
  43                 bool isSelectQuery = false;
  44
  45                 #endregion // Fields
  46
  47                 #region Constructors
  48
  49                 public Tds50 (string server, int port)
  50                         : this (server, port, 512, 15)
  51                 {
  52                 }
  53
  54                 public Tds50 (string server, int port, int packetSize, int timeout)
  55                         : base (server, port, packetSize, timeout, Version)
  56                 {
  57                         this.packetSize = packetSize;
  58                 }
  59
  60                 #endregion // Constructors
  61
  62                 #region Methods
  63
  64                 public string BuildExec (string sql)
  65                 {
  66                         if (Parameters == null || Parameters.Count == 0)
  67                                 return sql;
  68
  69                         StringBuilder select = new StringBuilder ();
  70                         StringBuilder set = new StringBuilder ();
  71                         StringBuilder declare = new StringBuilder ();
  72                         int count = 0;
  73                         foreach (TdsMetaParameter p in Parameters) {
  74                                 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
  75                                 set.Append (String.Format ("select {0}=", p.ParameterName));
  76                                 if (p.Direction == TdsParameterDirection.Input)
  77                                         set.Append (FormatParameter (p));
  78                                 else {
  79                                         set.Append ("NULL");
  80                                         select.Append (p.ParameterName);
  81                                         if (count == 0)
  82                                                 select.Append ("select ");
  83                                         else
  84                                                 select.Append (", ");
  85                                         count += 1;
  86                                 }
  87                                 set.Append ("\n");
  88                         }
  89                         return String.Format ("{0}{1}{2}\n{3}", declare.ToString (), set.ToString (), sql, select.ToString ());
  90                 }
  91
  92                 public override bool Connect (TdsConnectionParameters connectionParameters)
  93                 {
  94                         if (IsConnected)
  95                                 throw new InvalidOperationException ("The connection is already open.");
  96
  97                         byte[] capabilityRequest = {0x03, 0xef, 0x65, 0x41, 0xff, 0xff, 0xff, 0xd6};
  98                         byte[] capabilityResponse = {0x00, 0x00, 0x00, 0x06, 0x48, 0x00, 0x00, 0x08};
  99
 100                         SetCharset (connectionParameters.Charset);
 101                         SetLanguage (connectionParameters.Language);
 102
 103                         byte pad = (byte) 0;
 104                         byte[] empty = new byte[0];
 105
 106                         Comm.StartPacket (TdsPacketType.Logon);
 107
 108                         // hostname (offset 0)
 109                         // 0-30
 110                         byte[] tmp = Comm.Append (connectionParameters.Hostname, 30, pad);
 111                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 112
 113                         // username (offset 31 0x1f)
 114                         // 31-61
 115                         tmp = Comm.Append (connectionParameters.User, 30, pad);
 116                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 117
 118                         // password (offset 62 0x3e)
 119                         // 62-92
 120                         tmp = Comm.Append (connectionParameters.Password, 30, pad);
 121                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 122
 123                         // hostproc (offset 93 0x5d)
 124                         // 93-123
 125                         tmp = Comm.Append ("37876", 30, pad);
 126                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 127
 128                         // Byte order of 2 byte ints
 129                         // 2 = <MSB, LSB>, 3 = <LSB, MSB>
 130                         // 124
 131                         Comm.Append ((byte) 3);
 132
 133                         // Byte order of 4 byte ints
 134                         // 0 = <MSB, LSB>, 1 = <LSB, MSB>
 135                         // 125
 136                         Comm.Append ((byte) 1);
 137
 138                         // Character representation
 139                         // (6 = ASCII, 7 = EBCDIC)
 140                         // 126
 141                         Comm.Append ((byte) 6);
 142
 143                         // Eight byte floating point representation
 144                         // 4 = IEEE <MSB, ..., LSB>
 145                         // 5 = VAX 'D'
 146                         // 10 = IEEE <LSB, ..., MSB>
 147                         // 11 = ND5000
 148                         // 127
 149                         Comm.Append ((byte) 10);
 150
 151                         // Eight byte date format
 152                         // 8 = <MSB, ..., LSB>
 153                         // 128
 154                         Comm.Append ((byte) 9);
 155
 156                         // notify of use db
 157                         // 129
 158                         Comm.Append ((byte) 1);
 159
 160                         // disallow dump/load and bulk insert
 161                         // 130
 162                         Comm.Append ((byte) 1);
 163
 164                         // sql interface type
 165                         // 131
 166                         Comm.Append ((byte) 0);
 167
 168                         // type of network connection
 169                         // 132
 170                         Comm.Append ((byte) 0);
 171
 172                         // spare [7]
 173                         // 133-139
 174                         Comm.Append (empty, 7, pad);
 175
 176                         // appname
 177                         // 140-170
 178                         tmp = Comm.Append (connectionParameters.ApplicationName, 30, pad);
 179                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 180
 181                         // server name
 182                         // 171-201
 183                         tmp = Comm.Append (DataSource, 30, pad);
 184                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 185
 186                         // remote passwords
 187                         // 202-457
 188                         Comm.Append (empty, 2, pad);
 189                         tmp = Comm.Append (connectionParameters.Password, 253, pad);
 190                         Comm.Append ((byte) (tmp.Length < 253 ? tmp.Length + 2 : 253 + 2));
 191
 192                         // tds version
 193                         // 458-461
 194                         Comm.Append ((byte) 5);
 195                         Comm.Append ((byte) 0);
 196                         Comm.Append ((byte) 0);
 197                         Comm.Append ((byte) 0);
 198
 199                         // prog name
 200                         // 462-472
 201                         tmp = Comm.Append (connectionParameters.ProgName, 10, pad);
 202                         Comm.Append ((byte) (tmp.Length < 10 ? tmp.Length : 10));
 203
 204                         // prog version
 205                         // 473-476
 206                         Comm.Append ((byte) 6);
 207                         Comm.Append ((byte) 0);
 208                         Comm.Append ((byte) 0);
 209                         Comm.Append ((byte) 0);
 210
 211                         // auto convert short
 212                         // 477
 213                         Comm.Append ((byte) 0);
 214
 215                         // type of flt4
 216                         // 478
 217                         Comm.Append ((byte) 0x0d);
 218
 219                         // type of date4
 220                         // 479
 221                         Comm.Append ((byte) 0x11);
 222
 223                         // language
 224                         // 480-510
 225                         tmp = Comm.Append (Language, 30, pad);
 226                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 227
 228                         // notify on lang change
 229                         // 511
 230                         Comm.Append ((byte) 1);
 231
 232                         // security label hierarchy
 233                         // 512-513
 234                         Comm.Append ((short) 0);
 235
 236                         // security components
 237                         // 514-521
 238                         Comm.Append (empty, 8, pad);
 239
 240                         // security spare
 241                         // 522-523
 242                         Comm.Append ((short) 0);
 243
 244                         // security login role
 245                         // 524
 246                         Comm.Append ((byte) 0);
 247
 248                         // charset
 249                         // 525-555
 250                         tmp = Comm.Append (Charset, 30, pad);
 251                         Comm.Append ((byte) (tmp.Length < 30 ? tmp.Length : 30));
 252
 253                         // notify on charset change
 254                         // 556
 255                         Comm.Append ((byte) 1);
 256
 257                         // length of tds packets
 258                         // 557-563
 259                         tmp = Comm.Append (this.packetSize.ToString (), 6, pad);
 260                         Comm.Append ((byte) (tmp.Length < 6 ? tmp.Length : 6));
 261
 262                         Comm.Append (empty, 8, pad);
 263                         // Padding...
 264                         // 564-567
 265                         //Comm.Append (empty, 4, pad);
 266
 267                         // Capabilities
 268                         Comm.Append ((byte) TdsPacketSubType.Capability);
 269                         Comm.Append ((short) 20);
 270                         Comm.Append ((byte) 0x01); // TDS_CAP_REQUEST
 271                         Comm.Append (capabilityRequest);
 272                         Comm.Append ((byte) 0x02);
 273                         Comm.Append (capabilityResponse);
 274
 275                         Comm.SendPacket ();
 276
 277                         MoreResults = true;
 278                         SkipToEnd ();
 279
 280                         return IsConnected;
 281                 }
 282
 283                 public override void ExecPrepared (string id, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 284                 {
 285                         Parameters = parameters;
 286                         bool hasParameters = (Parameters != null && Parameters.Count > 0);
 287
 288                         Comm.StartPacket (TdsPacketType.Normal);
 289
 290                         Comm.Append ((byte) TdsPacketSubType.Dynamic);
 291                         Comm.Append ((short) (id.Length + 5));
 292                         Comm.Append ((byte) 0x02);                  // TDS_DYN_EXEC
 293                         Comm.Append ((byte) (hasParameters ? 0x01 : 0x00));
 294                         Comm.Append ((byte) id.Length);
 295                         Comm.Append (id);
 296                         Comm.Append ((short) 0);
 297
 298                         if (hasParameters) {
 299                                 SendParamFormat ();
 300                                 SendParams ();
 301                         }
 302
 303                         MoreResults = true;
 304                         Comm.SendPacket ();
 305                         CheckForData (timeout);
 306                         if (!wantResults)
 307                                 SkipToEnd ();
 308                 }
 309
 310                 public override void Execute (string sql, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 311                 {
 312                         Parameters = parameters;
 313                         string ex = BuildExec (sql);
 314                         ExecuteQuery (ex, timeout, wantResults);
 315                 }
 316
 317                 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 318                 {
 319                         Parameters = parameters;
 320                         ExecuteQuery (BuildProcedureCall (commandText), timeout, wantResults);
 321                 }
 322
 323                 private string BuildProcedureCall (string procedure)
 324                 {
 325                         string exec = String.Empty;
 326
 327                         StringBuilder declare = new StringBuilder ();
 328                         StringBuilder select = new StringBuilder ();
 329                         StringBuilder set = new StringBuilder ();
 330
 331                         int count = 0;
 332                         if (Parameters != null) {
 333                                 foreach (TdsMetaParameter p in Parameters) {
 334                                         if (p.Direction != TdsParameterDirection.Input) {
 335
 336                                                 if (count == 0)
 337                                                         select.Append ("select ");
 338                                                 else
 339                                                         select.Append (", ");
 340                                                 select.Append (p.ParameterName);
 341
 342                                                 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
 343
 344                                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
 345                                                         if( p.Direction == TdsParameterDirection.InputOutput )
 346                                                                 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
 347                                                         else
 348                                                 set.Append (String.Format ("set {0}=NULL\n", p.ParameterName));
 349                                                 }
 350
 351                                                 count += 1;
 352                                         }
 353
 354                                         if (p.Direction == TdsParameterDirection.ReturnValue) {
 355                                                 exec = p.ParameterName + "=";
 356                                         }
 357                                 }
 358                         }
 359                         exec = "exec " + exec;
 360
 361                         string sql = String.Format ("{0}{1}{2}{3} {4}\n{5}", declare.ToString (),
 362                                                     set.ToString (),
 363                                                     exec, procedure,
 364                                                     BuildParameters (), select.ToString ());
 365                         return sql;
 366                 }
 367
 368
 369                 private string BuildParameters ()
 370                 {
 371                         if (Parameters == null || Parameters.Count == 0)
 372                                 return String.Empty;
 373
 374                         StringBuilder result = new StringBuilder ();
 375                         foreach (TdsMetaParameter p in Parameters) {
 376                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
 377                                 if (result.Length > 0)
 378                                         result.Append (", ");
 379                                         if (p.Direction == TdsParameterDirection.InputOutput)
 380                                                 result.Append (String.Format("{0}={0} output", p.ParameterName));
 381                                         else
 382                                 result.Append (FormatParameter (p));
 383                         }
 384                         }
 385                         return result.ToString ();
 386                 }
 387
 388
 389                 private string FormatParameter (TdsMetaParameter parameter)
 390                 {
 391                         if (parameter.Direction == TdsParameterDirection.Output)
 392                                 return String.Format ("{0} output", parameter.ParameterName);
 393
 394                         if (parameter.Value == null || parameter.Value == DBNull.Value)
 395                                 return "NULL";
 396
 397                         switch (parameter.TypeName) {
 398                         case "smalldatetime":
 399                         case "datetime":
 400                                 DateTime d = (DateTime)parameter.Value;
 401                                 return String.Format(System.Globalization.CultureInfo.InvariantCulture,
 402                                                      "'{0:MMM dd yyyy hh:mm:ss tt}'", d );
 403                         case "bigint":
 404                         case "decimal":
 405                         case "float":
 406                         case "int":
 407                         case "money":
 408                         case "real":
 409                         case "smallint":
 410                         case "smallmoney":
 411                         case "tinyint":
 412                                 return parameter.Value.ToString ();
 413                         case "nvarchar":
 414                         case "nchar":
 415                                 return String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 416                         case "uniqueidentifier":
 417                                 return String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
 418                         case "bit":
 419                                 if (parameter.Value.GetType () == typeof (bool))
 420                                         return (((bool) parameter.Value) ? "0x1" : "0x0");
 421                                 return parameter.Value.ToString ();
 422                         case "image":
 423                         case "binary":
 424                         case "varbinary":
 425                                 return String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
 426                         default:
 427                                 return String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 428                         }
 429                 }
 430
 431                 public override string Prepare (string sql, TdsMetaParameterCollection parameters)
 432                 {
 433                         Parameters = parameters;
 434
 435                         Random rand = new Random ();
 436                         StringBuilder idBuilder = new StringBuilder ();
 437                         for (int i = 0; i < 25; i += 1)
 438                                 idBuilder.Append ((char) (rand.Next (26) + 65));
 439                         string id = idBuilder.ToString ();
 440
 441                         //StringBuilder declare = new StringBuilder ();
 442
 443
 444                         sql = String.Format ("create proc {0} as\n{1}", id, sql);
 445                         short len = (short) ((id.Length) + sql.Length + 5);
 446
 447                         Comm.StartPacket (TdsPacketType.Normal);
 448                         Comm.Append ((byte) TdsPacketSubType.Dynamic);
 449                         Comm.Append (len);
 450                         Comm.Append ((byte) 0x1); // PREPARE
 451                         Comm.Append ((byte) 0x0); // UNUSED
 452                         Comm.Append ((byte) id.Length);
 453                         Comm.Append (id);
 454                         Comm.Append ((short) sql.Length);
 455                         Comm.Append (sql);
 456
 457                         Comm.SendPacket ();
 458                         MoreResults = true;
 459                         SkipToEnd ();
 460
 461                         return id;
 462                 }
 463
 464                 protected override TdsDataColumnCollection ProcessColumnInfo ()
 465                 {
 466                         isSelectQuery = true;
 467                         TdsDataColumnCollection result = new TdsDataColumnCollection ();
 468                         /*int totalLength = */Comm.GetTdsShort ();
 469                         int count = Comm.GetTdsShort ();
 470                         for (int i = 0; i < count; i += 1) {
 471                                 string columnName = Comm.GetString (Comm.GetByte ());
 472                                 int status = Comm.GetByte ();
 473                                 bool hidden = (status & 0x01) > 0;
 474                                 bool isKey = (status & 0x02) > 0;
 475                                 bool isRowVersion = (status & 0x04) > 0;
 476                                 bool isUpdatable = (status & 0x10) > 0;
 477                                 bool allowDBNull = (status & 0x20) > 0;
 478                                 bool isIdentity = (status & 0x40) > 0;
 479
 480                                 Comm.Skip (4); // User type
 481
 482                                 byte type = Comm.GetByte ();
 483                                 bool isBlob = (type == 0x24);
 484
 485                                 TdsColumnType columnType = (TdsColumnType) type;
 486                                 int bufLength = 0;
 487
 488                                 byte precision = 0;
 489                                 byte scale = 0;
 490
 491                                 if (columnType == TdsColumnType.Text || columnType == TdsColumnType.Image) {
 492                                         bufLength = Comm.GetTdsInt ();
 493                                         Comm.Skip (Comm.GetTdsShort ());
 494                                 }
 495                                 else if (IsFixedSizeColumn (columnType))
 496                                         bufLength = LookupBufferSize (columnType);
 497                                 else
 498                                         //bufLength = Comm.GetTdsShort ();
 499                                         bufLength = Comm.GetByte ();
 500
 501                                 if (columnType == TdsColumnType.Decimal || columnType == TdsColumnType.Numeric) {
 502                                         precision = Comm.GetByte ();
 503                                         scale = Comm.GetByte ();
 504                                 }
 505
 506                                 Comm.Skip (Comm.GetByte ()); // Locale
 507                                 if (isBlob)
 508                                         Comm.Skip (Comm.GetTdsShort ()); // Class ID
 509
 510                                 int index = result.Add (new TdsDataColumn ());
 511                                 result[index]["NumericPrecision"] = precision;
 512                                 result[index]["NumericScale"] = scale;
 513                                 result[index]["ColumnSize"] = bufLength;
 514                                 result[index]["ColumnName"] = columnName;
 515                                 result[index]["AllowDBNull"] = allowDBNull;
 516                                 result[index]["IsReadOnly"] = !isUpdatable;
 517                                 result[index]["IsIdentity"] = isIdentity;
 518                                 result[index]["IsRowVersion"] = isRowVersion;
 519                                 result[index]["IsKey"] = isKey;
 520                                 result[index]["Hidden"] = hidden;
 521                                 result[index]["ColumnType"] = columnType;
 522                         }
 523                         return result;
 524                 }
 525
 526                 private void SendParamFormat ()
 527                 {
 528                         Comm.Append ((byte) TdsPacketSubType.ParamFormat);
 529
 530                         int len = 2 + (8 * Parameters.Count);
 531                         TdsColumnType metaType;
 532                         foreach (TdsMetaParameter p in Parameters) {
 533                                 metaType = p.GetMetaType ();
 534                                 if (!IsFixedSizeColumn (metaType))
 535                                         len += 1;
 536                                 if (metaType == TdsColumnType.Numeric || metaType == TdsColumnType.Decimal)
 537                                         len += 2;
 538                         }
 539
 540                         Comm.Append ((short) len);
 541                         Comm.Append ((short) Parameters.Count);
 542
 543                         foreach (TdsMetaParameter p in Parameters) {
 544                                 string locale = String.Empty;
 545                                 string parameterName = String.Empty;
 546                                 int userType = 0;
 547
 548                                 byte status = 0x00;
 549                                 if (p.IsNullable)
 550                                         status |= 0x20;
 551                                 if (p.Direction == TdsParameterDirection.Output)
 552                                         status |= 0x01;
 553
 554                                 metaType = p.GetMetaType ();
 555
 556                                 Comm.Append ((byte) parameterName.Length);
 557                                 Comm.Append (parameterName);
 558                                 Comm.Append (status);
 559                                 Comm.Append (userType);
 560                                 Comm.Append ((byte) metaType);
 561
 562                                 if (!IsFixedSizeColumn (metaType))
 563                                         Comm.Append ((byte) p.Size);         // MAXIMUM SIZE
 564                                 if (metaType == TdsColumnType.Numeric || metaType == TdsColumnType.Decimal) {
 565                                         Comm.Append (p.Precision);
 566                                         Comm.Append (p.Scale);
 567                                 }
 568                                 Comm.Append ((byte) locale.Length);
 569                                 Comm.Append (locale);
 570                         }
 571                 }
 572
 573                 private void SendParams ()
 574                 {
 575                         Comm.Append ((byte) TdsPacketSubType.Parameters);
 576
 577                         TdsColumnType metaType;
 578                         foreach (TdsMetaParameter p in Parameters) {
 579                                 metaType = p.GetMetaType ();
 580                                 bool isNull = (p.Value == DBNull.Value || p.Value == null);
 581                                 if (!IsFixedSizeColumn (metaType))
 582                                         Comm.Append ((byte) p.GetActualSize ());
 583                                 if (!isNull)
 584                                         Comm.Append (p.Value);
 585                         }
 586                 }
 587
 588                 public override void Unprepare (string statementId)
 589                 {
 590                         Comm.StartPacket (TdsPacketType.Normal);
 591                         Comm.Append ((byte) TdsPacketSubType.Dynamic);
 592                         Comm.Append ((short) (3 + statementId.Length));
 593                         Comm.Append ((byte) 0x04);
 594                         Comm.Append ((byte) 0x00);
 595                         Comm.Append ((byte) statementId.Length);
 596                         Comm.Append (statementId);
 597                         //Comm.Append ((short) 0);
 598
 599                         MoreResults = true;
 600                         Comm.SendPacket ();
 601                         SkipToEnd ();
 602                 }
 603
 604                 protected override bool IsValidRowCount (byte status, byte op)
 605                 {
 606                         if (isSelectQuery)
 607                                 return (isSelectQuery = false);
 608
 609                         // TODO : Need to figure out how to calculate rowcount inside stored
 610                         // procedures. For now, Ignoring RowCount if they are returned by
 611                         // statements executing inside a StoredProcedure
 612
 613                         if (((status & (byte)0x40) != 0) || ((status & (byte)0x10) == 0))
 614                                 return false ;
 615
 616                         return true;
 617                 }
 618
 619                 #endregion // Methods
 620         }
 621 }