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