mcs/class/Npgsql/Npgsql/NpgsqlCommand.cs

   1 // created on 21/5/2002 at 20:03
   2
   3 // Npgsql.NpgsqlCommand.cs
   4 //
   5 // Author:
   6 //      Francisco Jr. (fxjrlists@yahoo.com.br)
   7 //
   8 //      Copyright (C) 2002 The Npgsql Development Team
   9 //      npgsql-general@gborg.postgresql.org
  10 //      http://gborg.postgresql.org/project/npgsql/projdisplay.php
  11 //
  12 // This library is free software; you can redistribute it and/or
  13 // modify it under the terms of the GNU Lesser General Public
  14 // License as published by the Free Software Foundation; either
  15 // version 2.1 of the License, or (at your option) any later version.
  16 //
  17 // This library is distributed in the hope that it will be useful,
  18 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  20 // Lesser General Public License for more details.
  21 //
  22 // You should have received a copy of the GNU Lesser General Public
  23 // License along with this library; if not, write to the Free Software
  24 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  25
  26 using System;
  27 using System.Data;
  28 using System.Text;
  29 using System.Resources;
  30 using System.ComponentModel;
  31 using System.Collections;
  32
  33 using NpgsqlTypes;
  34
  35 using System.Text.RegularExpressions;
  36
  37 #if WITHDESIGN
  38 using Npgsql.Design;
  39 #endif
  40
  41 namespace Npgsql
  42 {
  43     /// <summary>
  44     /// Represents a SQL statement or function (stored procedure) to execute
  45     /// against a PostgreSQL database. This class cannot be inherited.
  46     /// </summary>
  47     #if WITHDESIGN
  48     [System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
  49     #endif
  50     public sealed class NpgsqlCommand : Component, IDbCommand, ICloneable
  51     {
  52         // Logging related values
  53         private static readonly String CLASSNAME = "NpgsqlCommand";
  54         private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommand));
  55         private static readonly Regex parameterReplace = new Regex(@"([:@][\w\.]*)", RegexOptions.Singleline);
  56
  57         private NpgsqlConnection            connection;
  58         private NpgsqlConnector             connector;
  59         private NpgsqlTransaction           transaction;
  60         private String                      text;
  61         private Int32                       timeout;
  62         private CommandType                 type;
  63         private NpgsqlParameterCollection   parameters;
  64         private String                      planName;
  65
  66         private NpgsqlParse                 parse;
  67         private NpgsqlBind                  bind;
  68
  69         private Boolean                                         invalidTransactionDetected = false;
  70
  71         private CommandBehavior             commandBehavior;
  72
  73         private Int64                       lastInsertedOID = 0;
  74
  75         // Constructors
  76
  77         /// <summary>
  78         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class.
  79         /// </summary>
  80         public NpgsqlCommand() : this(String.Empty, null, null)
  81         {}
  82         /// <summary>
  83         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query.
  84         /// </summary>
  85         /// <param name="cmdText">The text of the query.</param>
  86         public NpgsqlCommand(String cmdText) : this(cmdText, null, null)
  87         {}
  88         /// <summary>
  89         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query and a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>.
  90         /// </summary>
  91         /// <param name="cmdText">The text of the query.</param>
  92         /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
  93         public NpgsqlCommand(String cmdText, NpgsqlConnection connection) : this(cmdText, connection, null)
  94         {}
  95         /// <summary>
  96         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
  97         /// </summary>
  98         /// <param name="cmdText">The text of the query.</param>
  99         /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
 100         /// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
 101         public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
 102         {
 103             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
 104
 105             planName = String.Empty;
 106             text = cmdText;
 107             this.connection = connection;
 108
 109             if (this.connection != null)
 110                 this.connector = connection.Connector;
 111
 112             parameters = new NpgsqlParameterCollection();
 113             type = CommandType.Text;
 114             this.Transaction = transaction;
 115             commandBehavior = CommandBehavior.Default;
 116
 117             SetCommandTimeout();
 118
 119
 120         }
 121
 122         /// <summary>
 123         /// Used to execute internal commands.
 124         /// </summary>
 125         internal NpgsqlCommand(String cmdText, NpgsqlConnector connector)
 126         {
 127             resman = new System.Resources.ResourceManager(this.GetType());
 128             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
 129
 130
 131             planName = String.Empty;
 132             text = cmdText;
 133             this.connector = connector;
 134             type = CommandType.Text;
 135             commandBehavior = CommandBehavior.Default;
 136
 137             parameters = new NpgsqlParameterCollection();
 138
 139             // Internal commands aren't affected by command timeout value provided by user.
 140             timeout = 20;
 141         }
 142
 143         // Public properties.
 144         /// <summary>
 145         /// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
 146         /// </summary>
 147         /// <value>The Transact-SQL statement or stored procedure to execute. The default is an empty string.</value>
 148         [Category("Data"), DefaultValue("")]
 149         public String CommandText {
 150             get
 151             {
 152                 return text;
 153             }
 154
 155             set
 156             {
 157                 // [TODO] Validate commandtext.
 158                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandText", value);
 159                 text = value;
 160                 planName = String.Empty;
 161                 parse = null;
 162                 bind = null;
 163                 commandBehavior = CommandBehavior.Default;
 164             }
 165         }
 166
 167         /// <summary>
 168         /// Gets or sets the wait time before terminating the attempt
 169         /// to execute a command and generating an error.
 170         /// </summary>
 171         /// <value>The time (in seconds) to wait for the command to execute.
 172         /// The default is 20 seconds.</value>
 173         [DefaultValue(20)]
 174         public Int32 CommandTimeout
 175         {
 176             get
 177             {
 178                 return timeout;
 179             }
 180
 181             set
 182             {
 183                 if (value < 0)
 184                     throw new ArgumentOutOfRangeException(resman.GetString("Exception_CommandTimeoutLessZero"));
 185
 186                 timeout = value;
 187                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandTimeout", value);
 188             }
 189         }
 190
 191         /// <summary>
 192         /// Gets or sets a value indicating how the
 193         /// <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> property is to be interpreted.
 194         /// </summary>
 195         /// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
 196         [Category("Data"), DefaultValue(CommandType.Text)]
 197         public CommandType CommandType
 198         {
 199             get
 200             {
 201                 return type;
 202             }
 203
 204             set
 205             {
 206                 type = value;
 207                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandType", value);
 208             }
 209         }
 210
 211         IDbConnection IDbCommand.Connection
 212         {
 213             get
 214             {
 215                 return Connection;
 216             }
 217
 218             set
 219             {
 220                 Connection = (NpgsqlConnection) value;
 221                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Connection", value);
 222             }
 223         }
 224
 225         /// <summary>
 226         /// Gets or sets the <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>
 227         /// used by this instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 228         /// </summary>
 229         /// <value>The connection to a data source. The default value is a null reference.</value>
 230         [Category("Behavior"), DefaultValue(null)]
 231         public NpgsqlConnection Connection
 232         {
 233             get
 234             {
 235                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
 236                 return connection;
 237             }
 238
 239             set
 240             {
 241                 if (this.Connection == value)
 242                     return;
 243
 244                 //if (this.transaction != null && this.transaction.Connection == null)
 245                   //  this.transaction = null;
 246
 247                 if (this.transaction != null && this.connection != null && this.Connector.Transaction != null)
 248                     throw new InvalidOperationException(resman.GetString("Exception_SetConnectionInTransaction"));
 249
 250
 251                 this.connection = value;
 252                 Transaction = null;
 253                 if (this.connection != null)
 254                     connector = this.connection.Connector;
 255
 256                 SetCommandTimeout();
 257
 258                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
 259             }
 260         }
 261
 262         internal NpgsqlConnector Connector
 263         {
 264             get
 265             {
 266                 if (this.connection != null)
 267                     connector = this.connection.Connector;
 268
 269                 return connector;
 270             }
 271         }
 272
 273         IDataParameterCollection IDbCommand.Parameters {
 274             get
 275             {
 276                 return Parameters;
 277             }
 278         }
 279
 280         /// <summary>
 281         /// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
 282         /// </summary>
 283         /// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
 284         #if WITHDESIGN
 285         [Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 286         #endif
 287
 288         public NpgsqlParameterCollection Parameters
 289         {
 290             get
 291             {
 292                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
 293                 return parameters;
 294             }
 295         }
 296
 297
 298         IDbTransaction IDbCommand.Transaction
 299         {
 300             get
 301             {
 302                 return Transaction;
 303             }
 304
 305             set
 306             {
 307                 Transaction = (NpgsqlTransaction) value;
 308                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Transaction", value);
 309             }
 310         }
 311
 312         /// <summary>
 313         /// Gets or sets the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>
 314         /// within which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.
 315         /// </summary>
 316         /// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
 317         /// The default value is a null reference.</value>
 318         #if WITHDESIGN
 319         [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
 320         #endif
 321
 322         public NpgsqlTransaction Transaction {
 323             get
 324             {
 325                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Transaction");
 326
 327                 if (this.transaction != null && this.transaction.Connection == null)
 328                 {
 329                     this.transaction = null;
 330                 }
 331                 return this.transaction;
 332             }
 333
 334             set
 335             {
 336                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Transaction" ,value);
 337
 338                 this.transaction = (NpgsqlTransaction) value;
 339             }
 340         }
 341
 342         /// <summary>
 343         /// Gets or sets how command results are applied to the <see cref="System.Data.DataRow">DataRow</see>
 344         /// when used by the <see cref="System.Data.Common.DbDataAdapter.Update">Update</see>
 345         /// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
 346         /// </summary>
 347         /// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
 348         #if WITHDESIGN
 349         [Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
 350         #endif
 351
 352         public UpdateRowSource UpdatedRowSource {
 353             get
 354             {
 355
 356                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "UpdatedRowSource");
 357
 358                 return UpdateRowSource.Both;
 359             }
 360
 361             set
 362             {
 363             }
 364         }
 365
 366         /// <summary>
 367         /// Returns oid of inserted row. This is only updated when using executenonQuery and when command inserts just a single row. If table is created without oids, this will always be 0.
 368         /// </summary>
 369
 370         public Int64 LastInsertedOID
 371         {
 372             get
 373             {
 374                 return lastInsertedOID;
 375             }
 376         }
 377
 378
 379         /// <summary>
 380         /// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 381         /// </summary>
 382         /// <remarks>This Method isn't implemented yet.</remarks>
 383         public void Cancel()
 384         {
 385             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");
 386
 387             // [TODO] Finish method implementation.
 388             throw new NotImplementedException();
 389         }
 390
 391         /// <summary>
 392         /// Create a new command based on this one.
 393         /// </summary>
 394         /// <returns>A new NpgsqlCommand object.</returns>
 395         Object ICloneable.Clone()
 396         {
 397             return Clone();
 398         }
 399
 400         /// <summary>
 401         /// Create a new connection based on this one.
 402         /// </summary>
 403         /// <returns>A new NpgsqlConnection object.</returns>
 404         public NpgsqlCommand Clone()
 405         {
 406             // TODO: Add consistency checks.
 407
 408             return new NpgsqlCommand(CommandText, Connection, Transaction);
 409         }
 410
 411         /// <summary>
 412         /// Creates a new instance of an <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.
 413         /// </summary>
 414         /// <returns>An <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.</returns>
 415         IDbDataParameter IDbCommand.CreateParameter()
 416         {
 417             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.CreateParameter");
 418
 419             return (NpgsqlParameter) CreateParameter();
 420         }
 421
 422         /// <summary>
 423         /// Creates a new instance of a <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.
 424         /// </summary>
 425         /// <returns>A <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.</returns>
 426         public NpgsqlParameter CreateParameter()
 427         {
 428             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateParameter");
 429
 430             return new NpgsqlParameter();
 431         }
 432
 433         /// <summary>
 434         /// Executes a SQL statement against the connection and returns the number of rows affected.
 435         /// </summary>
 436         /// <returns>The number of rows affected.</returns>
 437         public Int32 ExecuteNonQuery()
 438         {
 439             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
 440
 441             // Initialize lastInsertOID
 442             lastInsertedOID = 0;
 443
 444             ExecuteCommand();
 445
 446             UpdateOutputParameters();
 447
 448
 449             // If nothing is returned, just return -1.
 450             if(Connector.Mediator.CompletedResponses.Count == 0)
 451             {
 452                 return -1;
 453             }
 454
 455             // Check if the response is available.
 456             String firstCompletedResponse = (String)Connector.Mediator.CompletedResponses[0];
 457
 458             if (firstCompletedResponse == null)
 459                 return -1;
 460
 461             String[] ret_string_tokens = firstCompletedResponse.Split(null);        // whitespace separator.
 462
 463
 464             // Check if the command was insert, delete, update, fetch or move.
 465             // Only theses commands return rows affected.
 466             // [FIXME] Is there a better way to check this??
 467             if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
 468                     (String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
 469                     (String.Compare(ret_string_tokens[0], "DELETE", true) == 0) ||
 470                     (String.Compare(ret_string_tokens[0], "FETCH", true) == 0) ||
 471                     (String.Compare(ret_string_tokens[0], "MOVE", true) == 0))
 472
 473
 474             {
 475                 if (String.Compare(ret_string_tokens[0], "INSERT", true) == 0)
 476                     // Get oid of inserted row.
 477                     lastInsertedOID = Int32.Parse(ret_string_tokens[1]);
 478
 479                 // The number of rows affected is in the third token for insert queries
 480                 // and in the second token for update and delete queries.
 481                 // In other words, it is the last token in the 0-based array.
 482
 483                 return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
 484             }
 485             else
 486                 return -1;
 487         }
 488
 489
 490
 491         private void UpdateOutputParameters()
 492         {
 493             // Check if there was some resultset returned. If so, put the result in output parameters.
 494             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "UpdateOutputParameters");
 495
 496             // Get ResultSets.
 497             ArrayList resultSets = Connector.Mediator.ResultSets;
 498
 499             if (resultSets.Count != 0)
 500             {
 501                 NpgsqlResultSet nrs = (NpgsqlResultSet)resultSets[0];
 502
 503                 if ((nrs != null) && (nrs.Count > 0))
 504                 {
 505                     NpgsqlAsciiRow nar = (NpgsqlAsciiRow)nrs[0];
 506
 507                     Int32 i = 0;
 508                     Boolean hasMapping = false;
 509
 510                     // First check if there is any mapping between parameter name and resultset name.
 511                     // If so, just update output parameters which has mapping.
 512
 513                     foreach (NpgsqlParameter p in Parameters)
 514                     {
 515                         if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
 516                         {
 517                             hasMapping = true;
 518                             break;
 519                         }
 520
 521                     }
 522
 523
 524                     if (hasMapping)
 525                     {
 526                         foreach (NpgsqlParameter p in Parameters)
 527                         {
 528                             if (((p.Direction == ParameterDirection.Output) ||
 529                                 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
 530                             {
 531                                 Int32 fieldIndex = nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1));
 532
 533                                 if (fieldIndex > -1)
 534                                 {
 535                                     p.Value = nar[fieldIndex];
 536                                     i++;
 537                                 }
 538
 539                             }
 540                         }
 541
 542                     }
 543                     else
 544                         foreach (NpgsqlParameter p in Parameters)
 545                         {
 546                             if (((p.Direction == ParameterDirection.Output) ||
 547                                 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
 548                             {
 549                                 p.Value = nar[i];
 550                                 i++;
 551                             }
 552                         }
 553                 }
 554
 555             }
 556
 557
 558         }
 559
 560         /// <summary>
 561         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 562         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 563         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 564         /// </summary>
 565         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 566         IDataReader IDbCommand.ExecuteReader()
 567         {
 568             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader");
 569
 570             return (NpgsqlDataReader) ExecuteReader();
 571         }
 572
 573         /// <summary>
 574         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 575         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 576         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 577         /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 578         /// </summary>
 579         /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 580         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 581         IDataReader IDbCommand.ExecuteReader(CommandBehavior cb)
 582         {
 583             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader", cb);
 584
 585             return (NpgsqlDataReader) ExecuteReader(cb);
 586         }
 587
 588         /// <summary>
 589         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 590         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 591         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 592         /// </summary>
 593         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 594         public NpgsqlDataReader ExecuteReader()
 595         {
 596             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader");
 597
 598             return ExecuteReader(CommandBehavior.Default);
 599         }
 600
 601         /// <summary>
 602         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 603         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 604         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 605         /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 606         /// </summary>
 607         /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 608         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 609         /// <remarks>Currently the CommandBehavior parameter is ignored.</remarks>
 610         public NpgsqlDataReader ExecuteReader(CommandBehavior cb)
 611         {
 612             // [FIXME] No command behavior handling.
 613
 614             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader", cb);
 615             commandBehavior = cb;
 616
 617             ExecuteCommand();
 618
 619             UpdateOutputParameters();
 620
 621             // Get the resultsets and create a Datareader with them.
 622             return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, cb, this);
 623         }
 624
 625         ///<summary>
 626         /// This method binds the parameters from parameters collection to the bind
 627         /// message.
 628         /// </summary>
 629         private void BindParameters()
 630         {
 631
 632             if (parameters.Count != 0)
 633             {
 634                 Object[] parameterValues = new Object[parameters.Count];
 635                 Int16[] parameterFormatCodes = bind.ParameterFormatCodes;
 636
 637                 for (Int32 i = 0; i < parameters.Count; i++)
 638                 {
 639                     // Do not quote strings, or escape existing quotes - this will be handled by the backend.
 640                     // DBNull or null values are returned as null.
 641                     // TODO: Would it be better to remove this null special handling out of ConvertToBackend??
 642
 643                     // Do special handling of bytea values. They will be send in binary form.
 644                     // TODO: Add binary format support for all supported types. Not only bytea.
 645                     if (parameters[i].TypeInfo.NpgsqlDbType != NpgsqlDbType.Bytea)
 646                     {
 647
 648                         parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
 649                     }
 650                     else
 651                     {
 652                         parameterFormatCodes[i] = (Int16) FormatCode.Binary;
 653                         parameterValues[i]=(byte[])parameters[i].Value;
 654                     }
 655                 }
 656                 bind.ParameterValues = parameterValues;
 657                 bind.ParameterFormatCodes = parameterFormatCodes;
 658             }
 659
 660             Connector.Bind(bind);
 661
 662             // See Prepare() method for a discussion of this.
 663             Connector.Mediator.RequireReadyForQuery = false;
 664             Connector.Flush();
 665
 666
 667             connector.CheckErrorsAndNotifications();
 668         }
 669
 670         /// <summary>
 671         /// Executes the query, and returns the first column of the first row
 672         /// in the result set returned by the query. Extra columns or rows are ignored.
 673         /// </summary>
 674         /// <returns>The first column of the first row in the result set,
 675         /// or a null reference if the result set is empty.</returns>
 676         public Object ExecuteScalar()
 677         {
 678             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
 679
 680             ExecuteCommand();
 681
 682             // Now get the results.
 683             // Only the first column of the first row must be returned.
 684
 685             // Get ResultSets.
 686             ArrayList resultSets = Connector.Mediator.ResultSets;
 687
 688             // First data is the RowDescription object.
 689             // Check all resultsets as insert commands could have been sent along
 690             // with resultset queries. The insert commands return null and and some queries
 691             // may return empty resultsets, so, if we find one of these, skip to next resultset.
 692             // If no resultset is found, return null as per specification.
 693
 694             NpgsqlAsciiRow ascii_row = null;
 695             foreach( NpgsqlResultSet nrs in resultSets )
 696             {
 697                 if( (nrs != null) && (nrs.Count > 0) )
 698                 {
 699                     ascii_row = (NpgsqlAsciiRow) nrs[0];
 700                     return ascii_row[0];
 701                 }
 702             }
 703
 704             return null;
 705         }
 706
 707         /// <summary>
 708         /// Creates a prepared version of the command on a PostgreSQL server.
 709         /// </summary>
 710         public void Prepare()
 711         {
 712             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");
 713
 714             // Check the connection state.
 715             CheckConnectionState();
 716
 717             // reset any responses just before getting new ones
 718             Connector.Mediator.ResetResponses();
 719
 720             // Set command timeout.
 721             connector.Mediator.CommandTimeout = CommandTimeout;
 722
 723             if (! connector.SupportsPrepare)
 724             {
 725                 return; // Do nothing.
 726             }
 727
 728             if (connector.BackendProtocolVersion == ProtocolVersion.Version2)
 729             {
 730                 NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connector );
 731                 command.ExecuteNonQuery();
 732             }
 733             else
 734             {
 735                 try
 736                 {
 737
 738                     connector.StopNotificationThread();
 739
 740                     // Use the extended query parsing...
 741                     planName = connector.NextPlanName();
 742                     String portalName = connector.NextPortalName();
 743
 744                     parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
 745
 746                     connector.Parse(parse);
 747
 748                     // We need that because Flush() doesn't cause backend to send
 749                     // ReadyForQuery on error. Without ReadyForQuery, we don't return
 750                     // from query extended processing.
 751
 752                     // We could have used Connector.Flush() which sends us back a
 753                     // ReadyForQuery, but on postgresql server below 8.1 there is an error
 754                     // with extended query processing which hinders us from using it.
 755                     connector.Mediator.RequireReadyForQuery = false;
 756                     connector.Flush();
 757
 758                     // Check for errors and/or notifications and do the Right Thing.
 759                     connector.CheckErrorsAndNotifications();
 760
 761
 762                     // Description...
 763                     NpgsqlDescribe describe = new NpgsqlDescribe('S', planName);
 764
 765
 766                     connector.Describe(describe);
 767
 768                     connector.Sync();
 769
 770                     Npgsql.NpgsqlRowDescription returnRowDesc = connector.Mediator.LastRowDescription;
 771
 772                     Int16[] resultFormatCodes;
 773
 774
 775                     if (returnRowDesc != null)
 776                     {
 777                         resultFormatCodes = new Int16[returnRowDesc.NumFields];
 778
 779                         for (int i=0; i < returnRowDesc.NumFields; i++)
 780                         {
 781                             Npgsql.NpgsqlRowDescriptionFieldData returnRowDescData = returnRowDesc[i];
 782
 783
 784                             if (returnRowDescData.type_info != null && returnRowDescData.type_info.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Bytea)
 785                             {
 786                             // Binary format
 787                                 resultFormatCodes[i] = (Int16)FormatCode.Binary;
 788                             }
 789                             else
 790                             // Text Format
 791                                 resultFormatCodes[i] = (Int16)FormatCode.Text;
 792                         }
 793
 794
 795                     }
 796                     else
 797                         resultFormatCodes = new Int16[]{0};
 798
 799                     bind = new NpgsqlBind("", planName, new Int16[Parameters.Count], null, resultFormatCodes);
 800                 }
 801                 catch
 802                 {
 803                     // See ExecuteCommand method for a discussion of this.
 804                     connector.Sync();
 805
 806                     throw;
 807                 }
 808                 finally
 809                 {
 810                     connector.ResumeNotificationThread();
 811                 }
 812
 813
 814
 815             }
 816         }
 817
 818         /*
 819         /// <summary>
 820         /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 821         /// </summary>
 822         protected override void Dispose (bool disposing)
 823         {
 824
 825             if (disposing)
 826             {
 827                 // Only if explicitly calling Close or dispose we still have access to
 828                 // managed resources.
 829                 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
 830                 if (connection != null)
 831                 {
 832                     connection.Dispose();
 833                 }
 834                 base.Dispose(disposing);
 835
 836             }
 837         }*/
 838
 839         ///<summary>
 840         /// This method checks the connection state to see if the connection
 841         /// is set or it is open. If one of this conditions is not met, throws
 842         /// an InvalidOperationException
 843         ///</summary>
 844         private void CheckConnectionState()
 845         {
 846             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");
 847
 848
 849             // Check the connection state.
 850             if (Connector == null || Connector.State != ConnectionState.Open)
 851             {
 852                 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
 853             }
 854         }
 855
 856         /// <summary>
 857         /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
 858         /// to their actual values.
 859         /// The parameter name format is <b>:ParameterName</b>.
 860         /// </summary>
 861         /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
 862         internal String GetCommandText()
 863         {
 864             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");
 865
 866             if (planName == String.Empty)
 867                 return GetClearCommandText();
 868             else
 869                 return GetPreparedCommandText();
 870         }
 871
 872
 873         private String GetClearCommandText()
 874         {
 875             if (NpgsqlEventLog.Level == LogLevel.Debug)
 876                 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
 877
 878             Boolean addProcedureParenthesis = false;    // Do not add procedure parenthesis by default.
 879
 880             Boolean functionReturnsRecord = false;      // Functions don't return record by default.
 881
 882             Boolean functionReturnsRefcursor = false;   // Functions don't return refcursor by default.
 883
 884             String result = text;
 885
 886             if (type == CommandType.StoredProcedure)
 887             {
 888
 889                 if (Parameters.Count > 0)
 890                     functionReturnsRecord = CheckFunctionReturn("record");
 891
 892                 functionReturnsRefcursor = CheckFunctionReturn("refcursor");
 893
 894                 // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection. Also check if command text finishes in a ";" which would make Npgsql incorrectly append a "()" when executing this command text.
 895                 if ((!result.Trim().EndsWith(")")) && (!result.Trim().EndsWith(";")))
 896                 {
 897                     addProcedureParenthesis = true;
 898                     result += "(";
 899                 }
 900
 901                 if (Connector.SupportsPrepare)
 902                     result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
 903                 else
 904                     result = "select " + result;        //Only a single result return supported. 7.2 and earlier.
 905             }
 906             else if (type == CommandType.TableDirect)
 907                 return "select * from " + result;       // There is no parameter support on table direct.
 908
 909             if (parameters == null || parameters.Count == 0)
 910             {
 911                 if (addProcedureParenthesis)
 912                     result += ")";
 913
 914
 915                 // If function returns ref cursor just process refcursor-result function call
 916                 // and return command which will be used to return data from refcursor.
 917
 918                 if (functionReturnsRefcursor)
 919                     return ProcessRefcursorFunctionReturn(result);
 920
 921
 922                 if (functionReturnsRecord)
 923                     result = AddFunctionReturnsRecordSupport(result);
 924
 925
 926                 result = AddSingleRowBehaviorSupport(result);
 927
 928                 result = AddSchemaOnlyBehaviorSupport(result);
 929
 930                 return result;
 931             }
 932
 933
 934             // Get parameters in query string to translate them to their actual values.
 935
 936             // This regular expression gets all the parameters in format :param or @param
 937             // and everythingelse.
 938             // This is only needed if query string has parameters. Else, just append the
 939             // parameter values in order they were put in parameter collection.
 940
 941
 942             // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
 943             if (!addProcedureParenthesis)
 944             {
 945                 StringBuilder sb = new StringBuilder();
 946                 NpgsqlParameter p;
 947                 string[] queryparts = parameterReplace.Split(result);
 948
 949                 foreach (String s in queryparts)
 950                 {
 951                     if (s == string.Empty)
 952                         continue;
 953
 954                     if ((s[0] == ':' || s[0] == '@') &&
 955                         Parameters.TryGetValue(s, out p))
 956                     {
 957                         // It's a parameter. Lets handle it.
 958                         if ((p.Direction == ParameterDirection.Input) ||
 959                              (p.Direction == ParameterDirection.InputOutput))
 960                         {
 961                             // FIXME DEBUG ONLY
 962                             // adding the '::<datatype>' on the end of a parameter is a highly
 963                             // questionable practice, but it is great for debugging!
 964                             sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
 965
 966                             // Only add data type info if we are calling an stored procedure.
 967
 968                             if (type == CommandType.StoredProcedure)
 969                             {
 970                                 sb.Append("::");
 971                                 sb.Append(p.TypeInfo.Name);
 972
 973                                 if (p.TypeInfo.UseSize && (p.Size > 0))
 974                                     sb.Append("(").Append(p.Size).Append(")");
 975                             }
 976                         }
 977
 978                     }
 979                     else
 980                         sb.Append(s);
 981                 }
 982
 983                 result = sb.ToString();
 984             }
 985
 986             else
 987             {
 988
 989                 for (Int32 i = 0; i < parameters.Count; i++)
 990                 {
 991                     NpgsqlParameter Param = parameters[i];
 992
 993
 994                     if ((Param.Direction == ParameterDirection.Input) ||
 995                          (Param.Direction == ParameterDirection.InputOutput))
 996
 997
 998                         result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
 999                 }
1000
1001
1002                 // Remove a trailing comma added from parameter handling above. If any.
1003                 // Maybe there are only output parameters. If so, there will be no comma.
1004                 if (result.EndsWith(","))
1005                     result = result.Remove(result.Length - 1, 1);
1006
1007                 result += ")";
1008             }
1009
1010             if (functionReturnsRecord)
1011                 result = AddFunctionReturnsRecordSupport(result);
1012
1013             // If function returns ref cursor just process refcursor-result function call
1014             // and return command which will be used to return data from refcursor.
1015
1016             if (functionReturnsRefcursor)
1017                 return ProcessRefcursorFunctionReturn(result);
1018
1019
1020             result = AddSingleRowBehaviorSupport(result);
1021
1022             result = AddSchemaOnlyBehaviorSupport(result);
1023
1024             return result;
1025         }
1026
1027
1028
1029         private Boolean CheckFunctionReturn(String ReturnType)
1030         {
1031             String returnRecordQuery = "select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where prorettype = ( select oid from pg_type where typname = :typename ) and proargtypes=:proargtypes and proname=:proname and n.nspname=:nspname";
1032
1033             StringBuilder parameterTypes = new StringBuilder("");
1034
1035
1036             // Process parameters
1037
1038             foreach(NpgsqlParameter p in Parameters)
1039             {
1040                 if ((p.Direction == ParameterDirection.Input) ||
1041                      (p.Direction == ParameterDirection.InputOutput))
1042                 {
1043                     parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
1044                 }
1045             }
1046
1047
1048             // Process schema name.
1049
1050             String schemaName = String.Empty;
1051             String procedureName = String.Empty;
1052
1053
1054             String[] schemaProcedureName = CommandText.Split('.');
1055
1056             if (schemaProcedureName.Length == 2)
1057             {
1058                 schemaName = schemaProcedureName[0];
1059                 procedureName = schemaProcedureName[1];
1060             }
1061             else
1062             {
1063                 schemaName = "public";
1064                 procedureName = CommandText;
1065             }
1066
1067
1068
1069
1070             NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection);
1071
1072             c.Parameters.Add(new NpgsqlParameter("typename", NpgsqlDbType.Text));
1073             c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Text));
1074             c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
1075             c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
1076
1077             c.Parameters[0].Value = ReturnType;
1078             c.Parameters[1].Value = parameterTypes.ToString();
1079             c.Parameters[2].Value = procedureName;
1080             c.Parameters[3].Value = schemaName;
1081
1082
1083             Boolean ret = (Boolean) c.ExecuteScalar();
1084
1085             // reset any responses just before getting new ones
1086             connector.Mediator.ResetResponses();
1087
1088             // Set command timeout.
1089             connector.Mediator.CommandTimeout = CommandTimeout;
1090
1091             return ret;
1092
1093
1094         }
1095
1096
1097         private String AddFunctionReturnsRecordSupport(String OriginalResult)
1098         {
1099
1100             StringBuilder sb = new StringBuilder(OriginalResult);
1101
1102             sb.Append(" as (");
1103
1104             foreach(NpgsqlParameter p in Parameters)
1105             {
1106                 if ((p.Direction == ParameterDirection.Output) ||
1107                 (p.Direction == ParameterDirection.InputOutput))
1108                 {
1109                     sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
1110                 }
1111             }
1112
1113             String result = sb.ToString();
1114
1115             result = result.Remove(result.Length - 2, 1);
1116
1117             result += ")";
1118
1119
1120
1121             return result;
1122
1123
1124         }
1125
1126         ///<summary>
1127         /// This methods takes a string with a function call witch returns a refcursor or a set of
1128         /// refcursor. It will return the names of the open cursors/portals which will hold
1129         /// results. In turn, it returns the string which is needed to get the data of this cursors
1130         /// in form of one resultset for each cursor open. This way, clients don't need to do anything
1131         /// else besides calling function normally to get results in this way.
1132         ///</summary>
1133
1134         private String ProcessRefcursorFunctionReturn(String FunctionCall)
1135         {
1136             NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
1137
1138             NpgsqlDataReader dr = c.ExecuteReader();
1139
1140             StringBuilder sb = new StringBuilder();
1141
1142             while (dr.Read())
1143             {
1144                 sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
1145
1146             }
1147
1148             sb.Append(";"); // Just in case there is no response from refcursor function return.
1149
1150             // reset any responses just before getting new ones
1151             connector.Mediator.ResetResponses();
1152
1153             // Set command timeout.
1154             connector.Mediator.CommandTimeout = CommandTimeout;
1155
1156             return sb.ToString();
1157
1158
1159         }
1160
1161
1162
1163         private String GetPreparedCommandText()
1164         {
1165             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
1166
1167             if (parameters.Count == 0)
1168                 return "execute " + planName;
1169
1170
1171             StringBuilder result = new StringBuilder("execute " + planName + '(');
1172
1173
1174             for (Int32 i = 0; i < parameters.Count; i++)
1175             {
1176                 result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
1177             }
1178
1179             result = result.Remove(result.Length - 1, 1);
1180             result.Append(')');
1181
1182             return result.ToString();
1183
1184         }
1185
1186
1187
1188         private String GetParseCommandText()
1189         {
1190             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
1191
1192             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1193
1194             String parseCommand = text;
1195
1196             if (type == CommandType.StoredProcedure)
1197             {
1198                 // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection.
1199                 if (!parseCommand.Trim().EndsWith(")"))
1200                 {
1201                     addProcedureParenthesis = true;
1202                     parseCommand += "(";
1203                 }
1204
1205                 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
1206             }
1207             else if (type == CommandType.TableDirect)
1208                 return "select * from " + parseCommand; // There is no parameter support on TableDirect.
1209
1210             if (parameters.Count > 0)
1211             {
1212                 // The ReplaceParameterValue below, also checks if the parameter is present.
1213
1214                 String parameterName;
1215                 Int32 i;
1216
1217                 for (i = 0; i < parameters.Count; i++)
1218                 {
1219                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1220                     (parameters[i].Direction == ParameterDirection.InputOutput))
1221                     {
1222
1223                         if (!addProcedureParenthesis)
1224                         {
1225                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1226                             parameterName = parameters[i].ParameterName;
1227                             //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
1228                             parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
1229                         }
1230                         else
1231                             parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
1232                     }
1233
1234                 }
1235             }
1236
1237             if (addProcedureParenthesis)
1238                 return parseCommand + ")";
1239             else
1240                 return parseCommand;
1241
1242         }
1243
1244
1245         private String GetPrepareCommandText()
1246         {
1247             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
1248
1249             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1250
1251             planName = Connector.NextPlanName();
1252
1253             StringBuilder command = new StringBuilder("prepare " + planName);
1254
1255             String textCommand = text;
1256
1257             if (type == CommandType.StoredProcedure)
1258             {
1259                 // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection.
1260                 if (!textCommand.Trim().EndsWith(")"))
1261                 {
1262                     addProcedureParenthesis = true;
1263                     textCommand += "(";
1264                 }
1265
1266                 textCommand = "select * from " + textCommand;
1267             }
1268             else if (type == CommandType.TableDirect)
1269                 return "select * from " + textCommand; // There is no parameter support on TableDirect.
1270
1271
1272             if (parameters.Count > 0)
1273             {
1274                 // The ReplaceParameterValue below, also checks if the parameter is present.
1275
1276                 String parameterName;
1277                 Int32 i;
1278
1279                 for (i = 0; i < parameters.Count; i++)
1280                 {
1281                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1282                     (parameters[i].Direction == ParameterDirection.InputOutput))
1283                     {
1284
1285                         if (!addProcedureParenthesis)
1286                         {
1287                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1288                             parameterName = parameters[i].ParameterName;
1289                             // The space in front of '$' fixes a parsing problem in 7.3 server
1290                             // which gives errors of operator when finding the caracters '=$' in
1291                             // prepare text
1292                             textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
1293                         }
1294                         else
1295                             textCommand += " $" + (i+1);
1296                     }
1297
1298                 }
1299
1300                 //[TODO] Check if there are any missing parameters in the query.
1301                 // For while, an error is thrown saying about the ':' char.
1302
1303                 command.Append('(');
1304
1305                 for (i = 0; i < parameters.Count; i++)
1306                 {
1307                     //                    command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
1308                     command.Append(parameters[i].TypeInfo.Name);
1309
1310                     command.Append(',');
1311                 }
1312
1313                 command = command.Remove(command.Length - 1, 1);
1314                 command.Append(')');
1315
1316             }
1317
1318             if (addProcedureParenthesis)
1319                 textCommand += ")";
1320
1321             command.Append(" as ");
1322             command.Append(textCommand);
1323
1324
1325             return command.ToString();
1326
1327         }
1328
1329
1330         private static String ReplaceParameterValue(String result, String parameterName, String paramVal)
1331         {
1332
1333             String quote_pattern = @"['][^']*[']";
1334             String pattern = "[- |\n\r\t,)(;=+/]" + parameterName + "([- |\n\r\t,)(;=+/]|$)";
1335             Int32 start, end;
1336             String withoutquote = result;
1337             Boolean found = false;
1338             // First of all
1339             // Suppress quoted string from query (because we ave to ignore them)
1340             MatchCollection results = Regex.Matches(result,quote_pattern);
1341             foreach (Match match in results)
1342             {
1343                 start = match.Index;
1344                 end = match.Index + match.Length;
1345                 String spaces = new String(' ', match.Length-2);
1346                 withoutquote = withoutquote.Substring(0,start + 1) + spaces + withoutquote.Substring(end - 1);
1347             }
1348             do
1349             {
1350                 // Now we look for the searched parameters on the "withoutquote" string
1351                 results = Regex.Matches(withoutquote,pattern);
1352                 if (results.Count == 0)
1353                 // If no parameter is found, go out!
1354                     break;
1355                 // We take the first parameter found
1356                 found = true;
1357                 Match match = results[0];
1358                 start = match.Index;
1359                 if ((match.Length - parameterName.Length) == 2)
1360                     // If the found string is not the end of the string
1361                     end = match.Index + match.Length - 1;
1362                 else
1363                     // If the found string is the end of the string
1364                     end = match.Index + match.Length;
1365                 result = result.Substring(0, start + 1) + paramVal + result.Substring(end);
1366                 withoutquote = withoutquote.Substring(0,start + 1) + paramVal + withoutquote.Substring(end);
1367             }
1368             while (true);
1369             if (!found)
1370                 throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"),
1371                     parameterName));
1372             return result;
1373         }
1374
1375
1376         private String AddSingleRowBehaviorSupport(String ResultCommandText)
1377         {
1378
1379             ResultCommandText = ResultCommandText.Trim();
1380
1381             // Do not add SingleRowBehavior if SchemaOnly behavior is set.
1382
1383             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1384                 return ResultCommandText;
1385
1386             if ((commandBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)
1387             {
1388                 if (ResultCommandText.EndsWith(";"))
1389                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1390                 ResultCommandText += " limit 1;";
1391
1392             }
1393
1394
1395
1396             return ResultCommandText;
1397
1398         }
1399
1400         private String AddSchemaOnlyBehaviorSupport(String ResultCommandText)
1401         {
1402
1403             ResultCommandText = ResultCommandText.Trim();
1404
1405             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1406             {
1407                 if (ResultCommandText.EndsWith(";"))
1408                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1409                 ResultCommandText += " limit 0;";
1410
1411             }
1412
1413
1414             return ResultCommandText;
1415
1416         }
1417
1418
1419         private void ExecuteCommand()
1420         {
1421             // Check the connection state first.
1422             CheckConnectionState();
1423
1424             // reset any responses just before getting new ones
1425             Connector.Mediator.ResetResponses();
1426
1427             // Set command timeout.
1428             connector.Mediator.CommandTimeout = CommandTimeout;
1429
1430
1431             connector.StopNotificationThread();
1432
1433
1434             if (parse == null)
1435             {
1436                 connector.Query(this);
1437
1438
1439                 connector.ResumeNotificationThread();
1440
1441                 // Check for errors and/or notifications and do the Right Thing.
1442                 connector.CheckErrorsAndNotifications();
1443
1444
1445
1446             }
1447             else
1448             {
1449                 try
1450                 {
1451
1452                     BindParameters();
1453
1454                     connector.Execute(new NpgsqlExecute(bind.PortalName, 0));
1455
1456                     // Check for errors and/or notifications and do the Right Thing.
1457                     connector.CheckErrorsAndNotifications();
1458                 }
1459                 catch
1460                 {
1461                     // As per documentation:
1462                     // "[...] When an error is detected while processing any extended-query message,
1463                     // the backend issues ErrorResponse, then reads and discards messages until a
1464                     // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
1465                     // So, send a sync command if we get any problems.
1466
1467                     connector.Sync();
1468
1469                     throw;
1470                 }
1471                 finally
1472                 {
1473                     connector.ResumeNotificationThread();
1474                 }
1475             }
1476
1477         }
1478
1479         private void SetCommandTimeout()
1480         {
1481             if (Connector != null)
1482                 timeout = Connector.CommandTimeout;
1483             else
1484                 timeout = ConnectionStringDefaults.CommandTimeout;
1485         }
1486
1487
1488
1489
1490     }
1491 }