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