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                         parameterFormatCodes[i] = (Int16) FormatCode.Binary;
 669                         parameterValues[i]=(byte[])parameters[i].Value;
 670                     }
 671                 }
 672                 bind.ParameterValues = parameterValues;
 673                 bind.ParameterFormatCodes = parameterFormatCodes;
 674             }
 675
 676             Connector.Bind(bind);
 677
 678             // See Prepare() method for a discussion of this.
 679             Connector.Mediator.RequireReadyForQuery = false;
 680             Connector.Flush();
 681
 682
 683             connector.CheckErrorsAndNotifications();
 684         }
 685
 686         /// <summary>
 687         /// Executes the query, and returns the first column of the first row
 688         /// in the result set returned by the query. Extra columns or rows are ignored.
 689         /// </summary>
 690         /// <returns>The first column of the first row in the result set,
 691         /// or a null reference if the result set is empty.</returns>
 692         public Object ExecuteScalar()
 693         {
 694             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
 695
 696             ExecuteCommand();
 697
 698
 699             // Now get the results.
 700             // Only the first column of the first row must be returned.
 701
 702             // Get ResultSets.
 703             ArrayList resultSets = Connector.Mediator.ResultSets;
 704
 705             // First data is the RowDescription object.
 706             // Check all resultsets as insert commands could have been sent along
 707             // with resultset queries. The insert commands return null and and some queries
 708             // may return empty resultsets, so, if we find one of these, skip to next resultset.
 709             // If no resultset is found, return null as per specification.
 710
 711             NpgsqlAsciiRow ascii_row = null;
 712             foreach( NpgsqlResultSet nrs in resultSets )
 713             {
 714                 if( (nrs != null) && (nrs.Count > 0) )
 715                 {
 716                     ascii_row = (NpgsqlAsciiRow) nrs[0];
 717                     return ascii_row[0];
 718                 }
 719             }
 720
 721             return null;
 722         }
 723
 724         /// <summary>
 725         /// Creates a prepared version of the command on a PostgreSQL server.
 726         /// </summary>
 727         public void Prepare()
 728         {
 729             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");
 730
 731             // Check the connection state.
 732             CheckConnectionState();
 733
 734             // reset any responses just before getting new ones
 735             Connector.Mediator.ResetResponses();
 736
 737             // Set command timeout.
 738             connector.Mediator.CommandTimeout = CommandTimeout;
 739
 740             if (! connector.SupportsPrepare)
 741             {
 742                 return; // Do nothing.
 743             }
 744
 745             if (connector.BackendProtocolVersion == ProtocolVersion.Version2)
 746             {
 747                 NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connector );
 748                 command.ExecuteNonQuery();
 749             }
 750             else
 751             {
 752                 try
 753                 {
 754
 755                     connector.StopNotificationThread();
 756
 757                     // Use the extended query parsing...
 758                     planName = connector.NextPlanName();
 759                     String portalName = connector.NextPortalName();
 760
 761                     parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
 762
 763                     connector.Parse(parse);
 764
 765                     // We need that because Flush() doesn't cause backend to send
 766                     // ReadyForQuery on error. Without ReadyForQuery, we don't return
 767                     // from query extended processing.
 768
 769                     // We could have used Connector.Flush() which sends us back a
 770                     // ReadyForQuery, but on postgresql server below 8.1 there is an error
 771                     // with extended query processing which hinders us from using it.
 772                     connector.Mediator.RequireReadyForQuery = false;
 773                     connector.Flush();
 774
 775                     // Check for errors and/or notifications and do the Right Thing.
 776                     connector.CheckErrorsAndNotifications();
 777
 778
 779                     // Description...
 780                     NpgsqlDescribe describe = new NpgsqlDescribe('S', planName);
 781
 782
 783                     connector.Describe(describe);
 784
 785                     connector.Sync();
 786
 787                     Npgsql.NpgsqlRowDescription returnRowDesc = connector.Mediator.LastRowDescription;
 788
 789                     Int16[] resultFormatCodes;
 790
 791
 792                     if (returnRowDesc != null)
 793                     {
 794                         resultFormatCodes = new Int16[returnRowDesc.NumFields];
 795
 796                         for (int i=0; i < returnRowDesc.NumFields; i++)
 797                         {
 798                             Npgsql.NpgsqlRowDescriptionFieldData returnRowDescData = returnRowDesc[i];
 799
 800
 801                             if (returnRowDescData.type_info != null && returnRowDescData.type_info.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Bytea)
 802                             {
 803                             // Binary format
 804                                 resultFormatCodes[i] = (Int16)FormatCode.Binary;
 805                             }
 806                             else
 807                             // Text Format
 808                                 resultFormatCodes[i] = (Int16)FormatCode.Text;
 809                         }
 810
 811
 812                     }
 813                     else
 814                         resultFormatCodes = new Int16[]{0};
 815
 816                     bind = new NpgsqlBind("", planName, new Int16[Parameters.Count], null, resultFormatCodes);
 817                 }
 818                 catch (IOException e)
 819                 {
 820                     ClearPoolAndThrowException(e);
 821                 }
 822                 catch
 823                 {
 824                     // See ExecuteCommand method for a discussion of this.
 825                     connector.Sync();
 826
 827                     throw;
 828                 }
 829                 finally
 830                 {
 831                     connector.ResumeNotificationThread();
 832                 }
 833
 834
 835
 836             }
 837         }
 838
 839         /*
 840         /// <summary>
 841         /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 842         /// </summary>
 843         protected override void Dispose (bool disposing)
 844         {
 845
 846             if (disposing)
 847             {
 848                 // Only if explicitly calling Close or dispose we still have access to
 849                 // managed resources.
 850                 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
 851                 if (connection != null)
 852                 {
 853                     connection.Dispose();
 854                 }
 855                 base.Dispose(disposing);
 856
 857             }
 858         }*/
 859
 860         ///<summary>
 861         /// This method checks the connection state to see if the connection
 862         /// is set or it is open. If one of this conditions is not met, throws
 863         /// an InvalidOperationException
 864         ///</summary>
 865         private void CheckConnectionState()
 866         {
 867             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");
 868
 869
 870             // Check the connection state.
 871             if (Connector == null || Connector.State != ConnectionState.Open)
 872             {
 873                 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
 874             }
 875         }
 876
 877         /// <summary>
 878         /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
 879         /// to their actual values.
 880         /// The parameter name format is <b>:ParameterName</b>.
 881         /// </summary>
 882         /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
 883         internal String GetCommandText()
 884         {
 885             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");
 886
 887             if (planName == String.Empty)
 888                 return GetClearCommandText();
 889             else
 890                 return GetPreparedCommandText();
 891         }
 892
 893
 894         private String GetClearCommandText()
 895         {
 896             if (NpgsqlEventLog.Level == LogLevel.Debug)
 897                 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
 898
 899             Boolean addProcedureParenthesis = false;    // Do not add procedure parenthesis by default.
 900
 901             Boolean functionReturnsRecord = false;      // Functions don't return record by default.
 902
 903             Boolean functionReturnsRefcursor = false;   // Functions don't return refcursor by default.
 904
 905             String result = text;
 906
 907             if (type == CommandType.StoredProcedure)
 908             {
 909
 910                 if (Parameters.Count > 0)
 911                     functionReturnsRecord = CheckFunctionReturn("record");
 912
 913                 functionReturnsRefcursor = CheckFunctionReturn("refcursor");
 914
 915                 // 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.
 916                 if ((!result.Trim().EndsWith(")")) && (!result.Trim().EndsWith(";")))
 917                 {
 918                     addProcedureParenthesis = true;
 919                     result += "(";
 920                 }
 921
 922                 if (Connector.SupportsPrepare)
 923                     result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
 924                 else
 925                     result = "select " + result;        //Only a single result return supported. 7.2 and earlier.
 926             }
 927             else if (type == CommandType.TableDirect)
 928                 return "select * from " + result;       // There is no parameter support on table direct.
 929
 930             if (parameters == null || parameters.Count == 0)
 931             {
 932                 if (addProcedureParenthesis)
 933                     result += ")";
 934
 935
 936                 // If function returns ref cursor just process refcursor-result function call
 937                 // and return command which will be used to return data from refcursor.
 938
 939                 if (functionReturnsRefcursor)
 940                     return ProcessRefcursorFunctionReturn(result);
 941
 942
 943                 if (functionReturnsRecord)
 944                     result = AddFunctionReturnsRecordSupport(result);
 945
 946
 947                 result = AddSingleRowBehaviorSupport(result);
 948
 949                 result = AddSchemaOnlyBehaviorSupport(result);
 950
 951                 return result;
 952             }
 953
 954
 955             // Get parameters in query string to translate them to their actual values.
 956
 957             // This regular expression gets all the parameters in format :param or @param
 958             // and everythingelse.
 959             // This is only needed if query string has parameters. Else, just append the
 960             // parameter values in order they were put in parameter collection.
 961
 962
 963             // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
 964             if (!addProcedureParenthesis)
 965             {
 966                 StringBuilder sb = new StringBuilder();
 967                 NpgsqlParameter p;
 968                 string[] queryparts = parameterReplace.Split(result);
 969
 970                 foreach (String s in queryparts)
 971                 {
 972                     if (s == string.Empty)
 973                         continue;
 974
 975                     if ((s[0] == ':' || s[0] == '@') &&
 976                         Parameters.TryGetValue(s, out p))
 977                     {
 978                         // It's a parameter. Lets handle it.
 979                         if ((p.Direction == ParameterDirection.Input) ||
 980                              (p.Direction == ParameterDirection.InputOutput))
 981                         {
 982                             // FIXME DEBUG ONLY
 983                             // adding the '::<datatype>' on the end of a parameter is a highly
 984                             // questionable practice, but it is great for debugging!
 985                             sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
 986
 987                             // Only add data type info if we are calling an stored procedure.
 988
 989                             if (type == CommandType.StoredProcedure)
 990                             {
 991                                 sb.Append("::");
 992                                 sb.Append(p.TypeInfo.Name);
 993
 994                                 if (p.TypeInfo.UseSize && (p.Size > 0))
 995                                     sb.Append("(").Append(p.Size).Append(")");
 996                             }
 997                         }
 998
 999                     }
1000                     else
1001                         sb.Append(s);
1002                 }
1003
1004                 result = sb.ToString();
1005             }
1006
1007             else
1008             {
1009
1010                 for (Int32 i = 0; i < parameters.Count; i++)
1011                 {
1012                     NpgsqlParameter Param = parameters[i];
1013
1014
1015                     if ((Param.Direction == ParameterDirection.Input) ||
1016                          (Param.Direction == ParameterDirection.InputOutput))
1017
1018
1019                         result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
1020                 }
1021
1022
1023                 // Remove a trailing comma added from parameter handling above. If any.
1024                 // Maybe there are only output parameters. If so, there will be no comma.
1025                 if (result.EndsWith(","))
1026                     result = result.Remove(result.Length - 1, 1);
1027
1028                 result += ")";
1029             }
1030
1031             if (functionReturnsRecord)
1032                 result = AddFunctionReturnsRecordSupport(result);
1033
1034             // If function returns ref cursor just process refcursor-result function call
1035             // and return command which will be used to return data from refcursor.
1036
1037             if (functionReturnsRefcursor)
1038                 return ProcessRefcursorFunctionReturn(result);
1039
1040
1041             result = AddSingleRowBehaviorSupport(result);
1042
1043             result = AddSchemaOnlyBehaviorSupport(result);
1044
1045             return result;
1046         }
1047
1048
1049
1050         private Boolean CheckFunctionReturn(String ReturnType)
1051         {
1052             // Updated after 0.99.3 to support the optional existence of a name qualifying schema and allow for case insensitivity
1053             // when the schema or procedure name do not contain a quote.
1054             // The hard-coded schema name 'public' was replaced with code that uses schema as a qualifier, only if it is provided.
1055
1056             String returnRecordQuery;
1057
1058             StringBuilder parameterTypes = new StringBuilder("");
1059
1060
1061             // Process parameters
1062
1063             foreach(NpgsqlParameter p in Parameters)
1064             {
1065                 if ((p.Direction == ParameterDirection.Input) ||
1066                      (p.Direction == ParameterDirection.InputOutput))
1067                 {
1068                     parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
1069                 }
1070             }
1071
1072
1073             // Process schema name.
1074
1075             String schemaName = String.Empty;
1076             String procedureName = String.Empty;
1077
1078
1079             String[] fullName = CommandText.Split('.');
1080
1081             if (fullName.Length == 2)
1082             {
1083                 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";
1084
1085                 schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
1086                 procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
1087             }
1088             else
1089             {
1090                 // Instead of defaulting don't use the nspname, as an alternative, query pg_proc and pg_namespace to try and determine the nspname.
1091                 //schemaName = "public"; // This was removed after build 0.99.3 because the assumption that a function is in public is often incorrect.
1092                 returnRecordQuery = "select count(*) > 0 from pg_proc p where prorettype = ( select oid from pg_type where typname = :typename ) and proargtypes=:proargtypes and proname=:proname";
1093
1094                 procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower();
1095             }
1096
1097
1098
1099
1100             NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection);
1101
1102             c.Parameters.Add(new NpgsqlParameter("typename", NpgsqlDbType.Text));
1103             c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Text));
1104             c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
1105
1106             c.Parameters[0].Value = ReturnType;
1107             c.Parameters[1].Value = parameterTypes.ToString();
1108             c.Parameters[2].Value = procedureName;
1109
1110             if (schemaName != null && schemaName.Length > 0)
1111             {
1112                 c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
1113                 c.Parameters[3].Value = schemaName;
1114             }
1115
1116
1117             Boolean ret = (Boolean) c.ExecuteScalar();
1118
1119             // reset any responses just before getting new ones
1120             connector.Mediator.ResetResponses();
1121
1122             // Set command timeout.
1123             connector.Mediator.CommandTimeout = CommandTimeout;
1124
1125             return ret;
1126
1127
1128         }
1129
1130
1131         private String AddFunctionReturnsRecordSupport(String OriginalResult)
1132         {
1133
1134             StringBuilder sb = new StringBuilder(OriginalResult);
1135
1136             sb.Append(" as (");
1137
1138             foreach(NpgsqlParameter p in Parameters)
1139             {
1140                 if ((p.Direction == ParameterDirection.Output) ||
1141                 (p.Direction == ParameterDirection.InputOutput))
1142                 {
1143                     sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
1144                 }
1145             }
1146
1147             String result = sb.ToString();
1148
1149             result = result.Remove(result.Length - 2, 1);
1150
1151             result += ")";
1152
1153
1154
1155             return result;
1156
1157
1158         }
1159
1160         ///<summary>
1161         /// This methods takes a string with a function call witch returns a refcursor or a set of
1162         /// refcursor. It will return the names of the open cursors/portals which will hold
1163         /// results. In turn, it returns the string which is needed to get the data of this cursors
1164         /// in form of one resultset for each cursor open. This way, clients don't need to do anything
1165         /// else besides calling function normally to get results in this way.
1166         ///</summary>
1167
1168         private String ProcessRefcursorFunctionReturn(String FunctionCall)
1169         {
1170             NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
1171
1172             NpgsqlDataReader dr = c.ExecuteReader();
1173
1174             StringBuilder sb = new StringBuilder();
1175
1176             while (dr.Read())
1177             {
1178                 sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
1179
1180             }
1181
1182             sb.Append(";"); // Just in case there is no response from refcursor function return.
1183
1184             // reset any responses just before getting new ones
1185             connector.Mediator.ResetResponses();
1186
1187             // Set command timeout.
1188             connector.Mediator.CommandTimeout = CommandTimeout;
1189
1190             return sb.ToString();
1191
1192
1193         }
1194
1195
1196
1197         private String GetPreparedCommandText()
1198         {
1199             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
1200
1201             if (parameters.Count == 0)
1202                 return "execute " + planName;
1203
1204
1205             StringBuilder result = new StringBuilder("execute " + planName + '(');
1206
1207
1208             for (Int32 i = 0; i < parameters.Count; i++)
1209             {
1210                 result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
1211             }
1212
1213             result = result.Remove(result.Length - 1, 1);
1214             result.Append(')');
1215
1216             return result.ToString();
1217
1218         }
1219
1220
1221
1222         private String GetParseCommandText()
1223         {
1224             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
1225
1226             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1227
1228             String parseCommand = text;
1229
1230             if (type == CommandType.StoredProcedure)
1231             {
1232                 // 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.
1233                 if (!parseCommand.Trim().EndsWith(")"))
1234                 {
1235                     addProcedureParenthesis = true;
1236                     parseCommand += "(";
1237                 }
1238
1239                 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
1240             }
1241             else if (type == CommandType.TableDirect)
1242                 return "select * from " + parseCommand; // There is no parameter support on TableDirect.
1243
1244             if (parameters.Count > 0)
1245             {
1246                 // The ReplaceParameterValue below, also checks if the parameter is present.
1247
1248                 String parameterName;
1249                 Int32 i;
1250
1251                 for (i = 0; i < parameters.Count; i++)
1252                 {
1253                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1254                     (parameters[i].Direction == ParameterDirection.InputOutput))
1255                     {
1256
1257                         if (!addProcedureParenthesis)
1258                         {
1259                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1260                             parameterName = parameters[i].ParameterName;
1261                             //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
1262                             parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
1263                         }
1264                         else
1265                             parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
1266                     }
1267
1268                 }
1269             }
1270
1271             if (addProcedureParenthesis)
1272                 return parseCommand + ")";
1273             else
1274                 return parseCommand;
1275
1276         }
1277
1278
1279         private String GetPrepareCommandText()
1280         {
1281             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
1282
1283             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1284
1285             planName = Connector.NextPlanName();
1286
1287             StringBuilder command = new StringBuilder("prepare " + planName);
1288
1289             String textCommand = text;
1290
1291             if (type == CommandType.StoredProcedure)
1292             {
1293                 // 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.
1294                 if (!textCommand.Trim().EndsWith(")"))
1295                 {
1296                     addProcedureParenthesis = true;
1297                     textCommand += "(";
1298                 }
1299
1300                 textCommand = "select * from " + textCommand;
1301             }
1302             else if (type == CommandType.TableDirect)
1303                 return "select * from " + textCommand; // There is no parameter support on TableDirect.
1304
1305
1306             if (parameters.Count > 0)
1307             {
1308                 // The ReplaceParameterValue below, also checks if the parameter is present.
1309
1310                 String parameterName;
1311                 Int32 i;
1312
1313                 for (i = 0; i < parameters.Count; i++)
1314                 {
1315                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1316                     (parameters[i].Direction == ParameterDirection.InputOutput))
1317                     {
1318
1319                         if (!addProcedureParenthesis)
1320                         {
1321                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1322                             parameterName = parameters[i].ParameterName;
1323                             // The space in front of '$' fixes a parsing problem in 7.3 server
1324                             // which gives errors of operator when finding the caracters '=$' in
1325                             // prepare text
1326                             textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
1327                         }
1328                         else
1329                             textCommand += " $" + (i+1);
1330                     }
1331
1332                 }
1333
1334                 //[TODO] Check if there are any missing parameters in the query.
1335                 // For while, an error is thrown saying about the ':' char.
1336
1337                 command.Append('(');
1338
1339                 for (i = 0; i < parameters.Count; i++)
1340                 {
1341                     //                    command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
1342                     command.Append(parameters[i].TypeInfo.Name);
1343
1344                     command.Append(',');
1345                 }
1346
1347                 command = command.Remove(command.Length - 1, 1);
1348                 command.Append(')');
1349
1350             }
1351
1352             if (addProcedureParenthesis)
1353                 textCommand += ")";
1354
1355             command.Append(" as ");
1356             command.Append(textCommand);
1357
1358
1359             return command.ToString();
1360
1361         }
1362
1363
1364         private static String ReplaceParameterValue(String result, String parameterName, String paramVal)
1365         {
1366
1367             String quote_pattern = @"['][^']*[']";
1368             String pattern = "[- |\n\r\t,)(;=+/]" + parameterName + "([- |\n\r\t,)(;=+/]|$)";
1369             Int32 start, end;
1370             String withoutquote = result;
1371             Boolean found = false;
1372             // First of all
1373             // Suppress quoted string from query (because we ave to ignore them)
1374             MatchCollection results = Regex.Matches(result,quote_pattern);
1375             foreach (Match match in results)
1376             {
1377                 start = match.Index;
1378                 end = match.Index + match.Length;
1379                 String spaces = new String(' ', match.Length-2);
1380                 withoutquote = withoutquote.Substring(0,start + 1) + spaces + withoutquote.Substring(end - 1);
1381             }
1382             do
1383             {
1384                 // Now we look for the searched parameters on the "withoutquote" string
1385                 results = Regex.Matches(withoutquote,pattern);
1386                 if (results.Count == 0)
1387                 // If no parameter is found, go out!
1388                     break;
1389                 // We take the first parameter found
1390                 found = true;
1391                 Match match = results[0];
1392                 start = match.Index;
1393                 if ((match.Length - parameterName.Length) == 2)
1394                     // If the found string is not the end of the string
1395                     end = match.Index + match.Length - 1;
1396                 else
1397                     // If the found string is the end of the string
1398                     end = match.Index + match.Length;
1399                 result = result.Substring(0, start + 1) + paramVal + result.Substring(end);
1400                 withoutquote = withoutquote.Substring(0,start + 1) + paramVal + withoutquote.Substring(end);
1401             }
1402             while (true);
1403             if (!found)
1404                 throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"),
1405                     parameterName));
1406             return result;
1407         }
1408
1409
1410         private String AddSingleRowBehaviorSupport(String ResultCommandText)
1411         {
1412
1413             ResultCommandText = ResultCommandText.Trim();
1414
1415             // Do not add SingleRowBehavior if SchemaOnly behavior is set.
1416
1417             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1418                 return ResultCommandText;
1419
1420             if ((commandBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)
1421             {
1422                 if (ResultCommandText.EndsWith(";"))
1423                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1424                 ResultCommandText += " limit 1;";
1425
1426             }
1427
1428
1429
1430             return ResultCommandText;
1431
1432         }
1433
1434         private String AddSchemaOnlyBehaviorSupport(String ResultCommandText)
1435         {
1436
1437             ResultCommandText = ResultCommandText.Trim();
1438
1439             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1440             {
1441                 if (ResultCommandText.EndsWith(";"))
1442                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1443                 ResultCommandText += " limit 0;";
1444
1445             }
1446
1447
1448             return ResultCommandText;
1449
1450         }
1451
1452
1453         private void ExecuteCommand()
1454         {
1455             try
1456             {
1457
1458             // Check the connection state first.
1459             CheckConnectionState();
1460
1461             // reset any responses just before getting new ones
1462             Connector.Mediator.ResetResponses();
1463
1464             // Set command timeout.
1465             connector.Mediator.CommandTimeout = CommandTimeout;
1466
1467
1468             connector.StopNotificationThread();
1469
1470
1471             if (parse == null)
1472             {
1473                 connector.Query(this);
1474
1475
1476                 connector.ResumeNotificationThread();
1477
1478                 // Check for errors and/or notifications and do the Right Thing.
1479                 connector.CheckErrorsAndNotifications();
1480
1481
1482
1483             }
1484             else
1485             {
1486                 try
1487                 {
1488
1489                     BindParameters();
1490
1491                     connector.Execute(new NpgsqlExecute(bind.PortalName, 0));
1492
1493                     // Check for errors and/or notifications and do the Right Thing.
1494                     connector.CheckErrorsAndNotifications();
1495                 }
1496                 catch
1497                 {
1498                     // As per documentation:
1499                     // "[...] When an error is detected while processing any extended-query message,
1500                     // the backend issues ErrorResponse, then reads and discards messages until a
1501                     // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
1502                     // So, send a sync command if we get any problems.
1503
1504                     connector.Sync();
1505
1506                     throw;
1507                 }
1508                 finally
1509                 {
1510                     connector.ResumeNotificationThread();
1511                 }
1512             }
1513
1514             }
1515
1516             catch(IOException e)
1517             {
1518                 ClearPoolAndThrowException(e);
1519             }
1520
1521         }
1522
1523         private void SetCommandTimeout()
1524         {
1525             if (Connector != null)
1526                 timeout = Connector.CommandTimeout;
1527             else
1528                 timeout = ConnectionStringDefaults.CommandTimeout;
1529         }
1530
1531         private void ClearPoolAndThrowException(Exception e)
1532         {
1533             Connection.ClearPool();
1534             throw new NpgsqlException(resman.GetString("Exception_ConnectionBroken"), e);
1535
1536         }
1537
1538
1539
1540
1541     }
1542 }