mcs/class/Npgsql/Npgsql/NpgsqlCommand.cs

   1 // created on 21/5/2002 at 20:03
   2
   3 // Npgsql.NpgsqlCommand.cs
   4 //
   5 // Author:
   6 //      Francisco Jr. (fxjrlists@yahoo.com.br)
   7 //
   8 //      Copyright (C) 2002 The Npgsql Development Team
   9 //      npgsql-general@gborg.postgresql.org
  10 //      http://gborg.postgresql.org/project/npgsql/projdisplay.php
  11 //
  12 // This library is free software; you can redistribute it and/or
  13 // modify it under the terms of the GNU Lesser General Public
  14 // License as published by the Free Software Foundation; either
  15 // version 2.1 of the License, or (at your option) any later version.
  16 //
  17 // This library is distributed in the hope that it will be useful,
  18 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  20 // Lesser General Public License for more details.
  21 //
  22 // You should have received a copy of the GNU Lesser General Public
  23 // License along with this library; if not, write to the Free Software
  24 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  25
  26 using System;
  27 using System.Data;
  28 using System.Text;
  29 using System.Resources;
  30 using System.ComponentModel;
  31 using System.Collections;
  32
  33 using NpgsqlTypes;
  34
  35 #if WITHDESIGN
  36 using Npgsql.Design;
  37 #endif
  38
  39 namespace Npgsql
  40 {
  41     /// <summary>
  42     /// Represents a SQL statement or function (stored procedure) to execute
  43     /// against a PostgreSQL database. This class cannot be inherited.
  44     /// </summary>
  45     #if WITHDESIGN
  46     [System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
  47     #endif
  48     public sealed class NpgsqlCommand : Component, IDbCommand, ICloneable
  49     {
  50         // Logging related values
  51         private static readonly String CLASSNAME = "NpgsqlCommand";
  52         private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommand));
  53
  54         private NpgsqlConnection            connection;
  55         private NpgsqlConnector             connector;
  56         private NpgsqlTransaction           transaction;
  57         private String                      text;
  58         private Int32                       timeout;
  59         private CommandType                 type;
  60         private NpgsqlParameterCollection   parameters;
  61         private String                      planName;
  62
  63         private NpgsqlParse                 parse;
  64         private NpgsqlBind                  bind;
  65
  66         private Boolean                                         invalidTransactionDetected = false;
  67
  68         private CommandBehavior             commandBehavior;
  69
  70         // Constructors
  71
  72         /// <summary>
  73         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class.
  74         /// </summary>
  75         public NpgsqlCommand() : this(String.Empty, null, null)
  76         {}
  77         /// <summary>
  78         /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query.
  79         /// </summary>
  80         /// <param name="cmdText">The text of the query.</param>
  81         public NpgsqlCommand(String cmdText) : this(cmdText, 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 and a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>.
  85         /// </summary>
  86         /// <param name="cmdText">The text of the query.</param>
  87         /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
  88         public NpgsqlCommand(String cmdText, NpgsqlConnection connection) : this(cmdText, connection, null)
  89         {}
  90         /// <summary>
  91         /// 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>.
  92         /// </summary>
  93         /// <param name="cmdText">The text of the query.</param>
  94         /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
  95         /// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
  96         public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
  97         {
  98             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
  99
 100             planName = String.Empty;
 101             text = cmdText;
 102             this.connection = connection;
 103             if (this.connection != null)
 104                 this.connector = connection.Connector;
 105
 106             parameters = new NpgsqlParameterCollection();
 107             timeout = 20;
 108             type = CommandType.Text;
 109             this.Transaction = transaction;
 110             commandBehavior = CommandBehavior.Default;
 111         }
 112
 113         /// <summary>
 114         /// Used to execute internal commands.
 115         /// </summary>
 116         internal NpgsqlCommand(String cmdText, NpgsqlConnector connector)
 117         {
 118             resman = new System.Resources.ResourceManager(this.GetType());
 119             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
 120
 121             planName = String.Empty;
 122             text = cmdText;
 123             this.connector = connector;
 124             type = CommandType.Text;
 125             commandBehavior = CommandBehavior.Default;
 126         }
 127
 128         // Public properties.
 129         /// <summary>
 130         /// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
 131         /// </summary>
 132         /// <value>The Transact-SQL statement or stored procedure to execute. The default is an empty string.</value>
 133         [Category("Data"), DefaultValue("")]
 134         public String CommandText {
 135             get
 136             {
 137                 return text;
 138             }
 139
 140             set
 141             {
 142                 // [TODO] Validate commandtext.
 143                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandText", value);
 144                 text = value;
 145                 planName = String.Empty;
 146                 parse = null;
 147                 bind = null;
 148                 commandBehavior = CommandBehavior.Default;
 149             }
 150         }
 151
 152         /// <summary>
 153         /// Gets or sets the wait time before terminating the attempt
 154         /// to execute a command and generating an error.
 155         /// </summary>
 156         /// <value>The time (in seconds) to wait for the command to execute.
 157         /// The default is 20 seconds.</value>
 158         [DefaultValue(20)]
 159         public Int32 CommandTimeout {
 160             get
 161             {
 162                 return timeout;
 163             }
 164
 165             set
 166             {
 167                 if (value < 0)
 168                     throw new ArgumentOutOfRangeException(resman.GetString("Exception_CommandTimeoutLessZero"));
 169
 170                 timeout = value;
 171                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandTimeout", value);
 172             }
 173         }
 174
 175         /// <summary>
 176         /// Gets or sets a value indicating how the
 177         /// <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> property is to be interpreted.
 178         /// </summary>
 179         /// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
 180         [Category("Data"), DefaultValue(CommandType.Text)]
 181         public CommandType CommandType {
 182             get
 183             {
 184                 return type;
 185             }
 186
 187             set
 188             {
 189                 type = value;
 190                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandType", value);
 191             }
 192         }
 193
 194         IDbConnection IDbCommand.Connection
 195         {
 196             get
 197             {
 198                 return Connection;
 199             }
 200
 201             set
 202             {
 203                 Connection = (NpgsqlConnection) value;
 204                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Connection", value);
 205             }
 206         }
 207
 208         /// <summary>
 209         /// Gets or sets the <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>
 210         /// used by this instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 211         /// </summary>
 212         /// <value>The connection to a data source. The default value is a null reference.</value>
 213         [Category("Behavior"), DefaultValue(null)]
 214         public NpgsqlConnection Connection {
 215             get
 216             {
 217                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
 218                 return connection;
 219             }
 220
 221             set
 222             {
 223                 if (this.Connection == value)
 224                     return;
 225
 226                 if (this.transaction != null && this.transaction.Connection == null)
 227                     this.transaction = null;
 228                 if (this.connection != null && this.Connector.Transaction != null)
 229                     throw new InvalidOperationException(resman.GetString("Exception_SetConnectionInTransaction"));
 230
 231
 232                 this.connection = value;
 233                 if (this.connection != null)
 234                     connector = this.connection.Connector;
 235
 236                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
 237             }
 238         }
 239
 240         internal NpgsqlConnector Connector {
 241             get
 242             {
 243                 if (connector == null && this.connection != null)
 244                     connector = this.connection.Connector;
 245
 246                 return connector;
 247             }
 248         }
 249
 250         IDataParameterCollection IDbCommand.Parameters {
 251             get
 252             {
 253                 return Parameters;
 254             }
 255         }
 256
 257         /// <summary>
 258         /// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
 259         /// </summary>
 260         /// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
 261         #if WITHDESIGN
 262         [Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 263         #endif
 264
 265         public NpgsqlParameterCollection Parameters {
 266             get
 267             {
 268                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
 269                 return parameters;
 270             }
 271         }
 272
 273
 274         IDbTransaction IDbCommand.Transaction
 275         {
 276             get
 277             {
 278                 return Transaction;
 279             }
 280
 281             set
 282             {
 283                 Transaction = (NpgsqlTransaction) value;
 284                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Transaction", value);
 285             }
 286         }
 287
 288         /// <summary>
 289         /// Gets or sets the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>
 290         /// within which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.
 291         /// </summary>
 292         /// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
 293         /// The default value is a null reference.</value>
 294         #if WITHDESIGN
 295         [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
 296         #endif
 297
 298         public NpgsqlTransaction Transaction {
 299             get
 300             {
 301                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Transaction");
 302
 303                 if (this.transaction != null && this.transaction.Connection == null)
 304                 {
 305                     this.transaction = null;
 306                 }
 307                 return this.transaction;
 308             }
 309
 310             set
 311             {
 312                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Transaction" ,value);
 313
 314                 this.transaction = (NpgsqlTransaction) value;
 315             }
 316         }
 317
 318         /// <summary>
 319         /// Gets or sets how command results are applied to the <see cref="System.Data.DataRow">DataRow</see>
 320         /// when used by the <see cref="System.Data.Common.DbDataAdapter.Update">Update</see>
 321         /// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
 322         /// </summary>
 323         /// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
 324         #if WITHDESIGN
 325         [Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
 326         #endif
 327
 328         public UpdateRowSource UpdatedRowSource {
 329             get
 330             {
 331
 332                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "UpdatedRowSource");
 333
 334                 return UpdateRowSource.Both;
 335             }
 336
 337             set
 338             {
 339                 throw new NotImplementedException();
 340             }
 341         }
 342
 343         /// <summary>
 344         /// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 345         /// </summary>
 346         /// <remarks>This Method isn't implemented yet.</remarks>
 347         public void Cancel()
 348         {
 349             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");
 350
 351             // [TODO] Finish method implementation.
 352             throw new NotImplementedException();
 353         }
 354
 355         /// <summary>
 356         /// Create a new command based on this one.
 357         /// </summary>
 358         /// <returns>A new NpgsqlCommand object.</returns>
 359         Object ICloneable.Clone()
 360         {
 361             return Clone();
 362         }
 363
 364         /// <summary>
 365         /// Create a new connection based on this one.
 366         /// </summary>
 367         /// <returns>A new NpgsqlConnection object.</returns>
 368         public NpgsqlCommand Clone()
 369         {
 370             // TODO: Add consistency checks.
 371
 372             return new NpgsqlCommand(CommandText, Connection, Transaction);
 373         }
 374
 375         /// <summary>
 376         /// Creates a new instance of an <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.
 377         /// </summary>
 378         /// <returns>An <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.</returns>
 379         IDbDataParameter IDbCommand.CreateParameter()
 380         {
 381             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.CreateParameter");
 382
 383             return (NpgsqlParameter) CreateParameter();
 384         }
 385
 386         /// <summary>
 387         /// Creates a new instance of a <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.
 388         /// </summary>
 389         /// <returns>A <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.</returns>
 390         public NpgsqlParameter CreateParameter()
 391         {
 392             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateParameter");
 393
 394             return new NpgsqlParameter();
 395         }
 396
 397         /// <summary>
 398         /// Executes a SQL statement against the connection and returns the number of rows affected.
 399         /// </summary>
 400         /// <returns>The number of rows affected.</returns>
 401         public Int32 ExecuteNonQuery()
 402         {
 403             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
 404
 405             ExecuteCommand();
 406
 407             UpdateOutputParameters();
 408
 409
 410             // If nothing is returned, just return -1.
 411             if(Connector.Mediator.CompletedResponses.Count == 0)
 412             {
 413                 return -1;
 414             }
 415
 416             // Check if the response is available.
 417             String firstCompletedResponse = (String)Connector.Mediator.CompletedResponses[0];
 418
 419             if (firstCompletedResponse == null)
 420                 return -1;
 421
 422             String[] ret_string_tokens = firstCompletedResponse.Split(null);        // whitespace separator.
 423
 424
 425             // Check if the command was insert, delete or update.
 426             // Only theses commands return rows affected.
 427             // [FIXME] Is there a better way to check this??
 428             if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
 429                     (String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
 430                     (String.Compare(ret_string_tokens[0], "DELETE", true) == 0))
 431
 432                 // The number of rows affected is in the third token for insert queries
 433                 // and in the second token for update and delete queries.
 434                 // In other words, it is the last token in the 0-based array.
 435
 436                 return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
 437             else
 438                 return -1;
 439         }
 440
 441
 442
 443         private void UpdateOutputParameters()
 444         {
 445             // Check if there was some resultset returned. If so, put the result in output parameters.
 446
 447             // Get ResultSets.
 448             ArrayList resultSets = Connector.Mediator.ResultSets;
 449
 450             if (resultSets.Count != 0)
 451             {
 452                 NpgsqlResultSet nrs = (NpgsqlResultSet)resultSets[0];
 453
 454                 if ((nrs != null) && (nrs.Count > 0))
 455                 {
 456                     NpgsqlAsciiRow nar = (NpgsqlAsciiRow)nrs[0];
 457
 458                     Int32 i = 0;
 459                     Boolean hasMapping = false;
 460
 461                     // First check if there is any mapping between parameter name and resultset name.
 462                     // If so, just update output parameters which has mapping.
 463
 464                     foreach (NpgsqlParameter p in Parameters)
 465                     {
 466                         try
 467                         {
 468                             if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
 469                             {
 470                                 hasMapping = true;
 471                                 break;
 472                             }
 473                         }
 474                         catch(ArgumentOutOfRangeException)
 475                         {}
 476                     }
 477
 478
 479                     if (hasMapping)
 480                     {
 481                         foreach (NpgsqlParameter p in Parameters)
 482                         {
 483                             if (((p.Direction == ParameterDirection.Output) ||
 484                                 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
 485                             {
 486                                 try
 487                                 {
 488                                     p.Value = nar[nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1))];
 489                                     i++;
 490                                 }
 491                                 catch(ArgumentOutOfRangeException)
 492                                 {}
 493                             }
 494                         }
 495
 496                     }
 497                     else
 498                         foreach (NpgsqlParameter p in Parameters)
 499                         {
 500                             if (((p.Direction == ParameterDirection.Output) ||
 501                                 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
 502                             {
 503                                 p.Value = nar[i];
 504                                 i++;
 505                             }
 506                         }
 507                 }
 508
 509             }
 510
 511
 512         }
 513
 514         /// <summary>
 515         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 516         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 517         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 518         /// </summary>
 519         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 520         IDataReader IDbCommand.ExecuteReader()
 521         {
 522             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader");
 523
 524             return (NpgsqlDataReader) ExecuteReader();
 525         }
 526
 527         /// <summary>
 528         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 529         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 530         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 531         /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 532         /// </summary>
 533         /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 534         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 535         IDataReader IDbCommand.ExecuteReader(CommandBehavior cb)
 536         {
 537             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader", cb);
 538
 539             return (NpgsqlDataReader) ExecuteReader(cb);
 540         }
 541
 542         /// <summary>
 543         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 544         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 545         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 546         /// </summary>
 547         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 548         public NpgsqlDataReader ExecuteReader()
 549         {
 550             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader");
 551
 552             return ExecuteReader(CommandBehavior.Default);
 553         }
 554
 555         /// <summary>
 556         /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 557         /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 558         /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 559         /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 560         /// </summary>
 561         /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 562         /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 563         /// <remarks>Currently the CommandBehavior parameter is ignored.</remarks>
 564         public NpgsqlDataReader ExecuteReader(CommandBehavior cb)
 565         {
 566             // [FIXME] No command behavior handling.
 567
 568             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader", cb);
 569             commandBehavior = cb;
 570
 571             ExecuteCommand();
 572
 573             // Get the resultsets and create a Datareader with them.
 574             return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, connection, cb);
 575         }
 576
 577         ///<summary>
 578         /// This method binds the parameters from parameters collection to the bind
 579         /// message.
 580         /// </summary>
 581         private void BindParameters()
 582         {
 583
 584             if (parameters.Count != 0)
 585             {
 586                 Object[] parameterValues = new Object[parameters.Count];
 587                 for (Int32 i = 0; i < parameters.Count; i++)
 588                 {
 589                     // Do not quote strings, or escape existing quotes - this will be handled by the backend.
 590                     // DBNull or null values are returned as null.
 591                     // TODO: Would it be better to remove this null special handling out of ConvertToBackend??
 592                     parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
 593                 }
 594                 bind.ParameterValues = parameterValues;
 595             }
 596
 597             Connector.Bind(bind);
 598             Connector.Mediator.RequireReadyForQuery = false;
 599             Connector.Flush();
 600
 601             connector.CheckErrorsAndNotifications();
 602         }
 603
 604         /// <summary>
 605         /// Executes the query, and returns the first column of the first row
 606         /// in the result set returned by the query. Extra columns or rows are ignored.
 607         /// </summary>
 608         /// <returns>The first column of the first row in the result set,
 609         /// or a null reference if the result set is empty.</returns>
 610         public Object ExecuteScalar()
 611         {
 612             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
 613
 614             /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
 615               if (parse == null)
 616                                 connection.Query(this);
 617                else
 618                {
 619                  BindParameters();
 620                  connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
 621                }
 622             else
 623                 throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
 624             */
 625
 626             ExecuteCommand();
 627
 628             // Now get the results.
 629             // Only the first column of the first row must be returned.
 630
 631             // Get ResultSets.
 632             ArrayList resultSets = Connector.Mediator.ResultSets;
 633
 634             // First data is the RowDescription object.
 635             // Check all resultsets as insert commands could have been sent along
 636             // with resultset queries. The insert commands return null and and some queries
 637             // may return empty resultsets, so, if we find one of these, skip to next resultset.
 638             // If no resultset is found, return null as per specification.
 639
 640             NpgsqlAsciiRow ascii_row = null;
 641             foreach( NpgsqlResultSet nrs in resultSets )
 642             {
 643                 if( (nrs != null) && (nrs.Count > 0) )
 644                 {
 645                     ascii_row = (NpgsqlAsciiRow) nrs[0];
 646                     return ascii_row[0];
 647                 }
 648             }
 649
 650             return null;
 651         }
 652
 653         /// <summary>
 654         /// Creates a prepared version of the command on a PostgreSQL server.
 655         /// </summary>
 656         public void Prepare()
 657         {
 658             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");
 659
 660             // Check the connection state.
 661             CheckConnectionState();
 662
 663             if (! Connector.SupportsPrepare)
 664             {
 665                 return; // Do nothing.
 666             }
 667
 668             if (connector.BackendProtocolVersion == ProtocolVersion.Version2)
 669             {
 670                 NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connector );
 671                 command.ExecuteNonQuery();
 672             }
 673             else
 674             {
 675                 // Use the extended query parsing...
 676                 //planName = "NpgsqlPlan" + Connector.NextPlanIndex();
 677                 planName = Connector.NextPlanName();
 678                 String portalName = Connector.NextPortalName();
 679
 680                 parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
 681
 682                 Connector.Parse(parse);
 683                 Connector.Mediator.RequireReadyForQuery = false;
 684                 Connector.Flush();
 685
 686                 // Check for errors and/or notifications and do the Right Thing.
 687                 connector.CheckErrorsAndNotifications();
 688
 689                 bind = new NpgsqlBind(portalName, planName, new Int16[] {0}, null, new Int16[] {0});
 690             }
 691         }
 692
 693         /*
 694         /// <summary>
 695         /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 696         /// </summary>
 697         protected override void Dispose (bool disposing)
 698         {
 699
 700             if (disposing)
 701             {
 702                 // Only if explicitly calling Close or dispose we still have access to
 703                 // managed resources.
 704                 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
 705                 if (connection != null)
 706                 {
 707                     connection.Dispose();
 708                 }
 709                 base.Dispose(disposing);
 710
 711             }
 712         }*/
 713
 714         ///<summary>
 715         /// This method checks the connection state to see if the connection
 716         /// is set or it is open. If one of this conditions is not met, throws
 717         /// an InvalidOperationException
 718         ///</summary>
 719         private void CheckConnectionState()
 720         {
 721             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");
 722
 723
 724             // Check the connection state.
 725             if (Connector == null || Connector.State != ConnectionState.Open)
 726             {
 727                 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
 728             }
 729         }
 730
 731         /// <summary>
 732         /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
 733         /// to their actual values.
 734         /// The parameter name format is <b>:ParameterName</b>.
 735         /// </summary>
 736         /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
 737         internal String GetCommandText()
 738         {
 739             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");
 740
 741             if (planName == String.Empty)
 742                 return GetClearCommandText();
 743             else
 744                 return GetPreparedCommandText();
 745         }
 746
 747
 748         private String GetClearCommandText()
 749         {
 750             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
 751
 752             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
 753
 754             Boolean functionReturnsRecord = false;    // Functions don't return record by default.
 755
 756             String result = text;
 757
 758             if (type == CommandType.StoredProcedure)
 759             {
 760
 761                 functionReturnsRecord = CheckFunctionReturnRecord();
 762
 763                 // 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.
 764                 if (!result.Trim().EndsWith(")"))
 765                 {
 766                     addProcedureParenthesis = true;
 767                     result += "(";
 768                 }
 769
 770                 if (Connector.SupportsPrepare)
 771                     result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
 772                 else
 773                     result = "select " + result;        //Only a single result return supported. 7.2 and earlier.
 774             }
 775             else if (type == CommandType.TableDirect)
 776                 return "select * from " + result;       // There is no parameter support on table direct.
 777
 778             if (parameters == null || parameters.Count == 0)
 779             {
 780                 if (addProcedureParenthesis)
 781                         result += ")";
 782
 783                 if (functionReturnsRecord)
 784                     result = AddFunctionReturnsRecordSupport(result);
 785
 786
 787                 result = AddSingleRowBehaviorSupport(result);
 788
 789                 return result;
 790              }
 791
 792
 793             //CheckParameters();
 794
 795             for (Int32 i = 0; i < parameters.Count; i++)
 796             {
 797                 NpgsqlParameter Param = parameters[i];
 798
 799
 800                 if ((Param.Direction == ParameterDirection.Input) ||
 801                     (Param.Direction == ParameterDirection.InputOutput))
 802
 803
 804                     // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
 805                     if (!addProcedureParenthesis)
 806                         // FIXME DEBUG ONLY
 807                         // adding the '::<datatype>' on the end of a parameter is a highly
 808                         // questionable practice, but it is great for debugging!
 809                         // Removed as this was going in infinite loop when the parameter name had the same name of parameter
 810                         // type name. i.e.: parameter name called :text of type text. It would conflict with the parameter type name ::text.
 811                         result = ReplaceParameterValue(
 812                                     result,
 813                                     Param.ParameterName,
 814                                     Param.TypeInfo.ConvertToBackend(Param.Value, false)
 815                                 );
 816                     else
 817                         result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + ",";
 818             }
 819
 820
 821             if (addProcedureParenthesis)
 822             {
 823                 // Remove a trailing comma added from parameter handling above. If any.
 824                 // Maybe there are only output parameters.
 825                 if (result.EndsWith(","))
 826                     result = result.Remove(result.Length - 1, 1);
 827
 828                 result += ")";
 829             }
 830
 831             if (functionReturnsRecord)
 832                 result = AddFunctionReturnsRecordSupport(result);
 833
 834             return AddSingleRowBehaviorSupport(result);
 835         }
 836
 837
 838
 839         private Boolean CheckFunctionReturnRecord()
 840         {
 841
 842             if (Parameters.Count == 0)
 843                 return false;
 844
 845             String returnRecordQuery = "select count(*) > 0 from pg_proc where prorettype = ( select oid from pg_type where typname = 'record' ) and proargtypes='{0}' and proname='{1}';";
 846
 847             StringBuilder parameterTypes = new StringBuilder("");
 848
 849             foreach(NpgsqlParameter p in Parameters)
 850             {
 851                 if ((p.Direction == ParameterDirection.Input) ||
 852                 (p.Direction == ParameterDirection.InputOutput))
 853                 {
 854                     parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
 855                 }
 856             }
 857
 858
 859             NpgsqlCommand c = new NpgsqlCommand(String.Format(returnRecordQuery, parameterTypes.ToString(), CommandText), Connection);
 860
 861             Boolean ret = (Boolean) c.ExecuteScalar();
 862
 863             // reset any responses just before getting new ones
 864             connector.Mediator.ResetResponses();
 865             return ret;
 866
 867
 868         }
 869
 870
 871         private String AddFunctionReturnsRecordSupport(String OriginalResult)
 872         {
 873
 874             StringBuilder sb = new StringBuilder(OriginalResult);
 875
 876             sb.Append(" as (");
 877
 878             foreach(NpgsqlParameter p in Parameters)
 879             {
 880                 if ((p.Direction == ParameterDirection.Output) ||
 881                 (p.Direction == ParameterDirection.InputOutput))
 882                 {
 883                     sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
 884                 }
 885             }
 886
 887             String result = sb.ToString();
 888
 889             result = result.Remove(result.Length - 2, 1);
 890
 891             result += ")";
 892
 893
 894
 895             return result;
 896
 897
 898         }
 899
 900
 901
 902         private String GetPreparedCommandText()
 903         {
 904             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
 905
 906             if (parameters.Count == 0)
 907                 return "execute " + planName;
 908
 909
 910             StringBuilder result = new StringBuilder("execute " + planName + '(');
 911
 912
 913             for (Int32 i = 0; i < parameters.Count; i++)
 914             {
 915                 result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
 916             }
 917
 918             result = result.Remove(result.Length - 1, 1);
 919             result.Append(')');
 920
 921             return result.ToString();
 922
 923         }
 924
 925
 926
 927         private String GetParseCommandText()
 928         {
 929             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
 930
 931             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
 932
 933             String parseCommand = text;
 934
 935             if (type == CommandType.StoredProcedure)
 936             {
 937                 // 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.
 938                 if (!parseCommand.Trim().EndsWith(")"))
 939                 {
 940                     addProcedureParenthesis = true;
 941                     parseCommand += "(";
 942                 }
 943
 944                 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
 945             }
 946             else if (type == CommandType.TableDirect)
 947                 return "select * from " + parseCommand; // There is no parameter support on TableDirect.
 948
 949             if (parameters.Count > 0)
 950             {
 951                 // The ReplaceParameterValue below, also checks if the parameter is present.
 952
 953                 String parameterName;
 954                 Int32 i;
 955
 956                 for (i = 0; i < parameters.Count; i++)
 957                 {
 958                     if ((parameters[i].Direction == ParameterDirection.Input) ||
 959                     (parameters[i].Direction == ParameterDirection.InputOutput))
 960                     {
 961
 962                         if (!addProcedureParenthesis)
 963                         {
 964                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
 965                             parameterName = parameters[i].ParameterName;
 966                             //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
 967                             parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
 968                         }
 969                         else
 970                             parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
 971                     }
 972
 973                 }
 974             }
 975
 976             if (addProcedureParenthesis)
 977                 return parseCommand + ")";
 978             else
 979                 return parseCommand;
 980
 981         }
 982
 983
 984         private String GetPrepareCommandText()
 985         {
 986             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
 987
 988             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
 989
 990             planName = Connector.NextPlanName();
 991
 992             StringBuilder command = new StringBuilder("prepare " + planName);
 993
 994             String textCommand = text;
 995
 996             if (type == CommandType.StoredProcedure)
 997             {
 998                 // 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.
 999                 if (!textCommand.Trim().EndsWith(")"))
1000                 {
1001                     addProcedureParenthesis = true;
1002                     textCommand += "(";
1003                 }
1004
1005                 textCommand = "select * from " + textCommand;
1006             }
1007             else if (type == CommandType.TableDirect)
1008                 return "select * from " + textCommand; // There is no parameter support on TableDirect.
1009
1010
1011             if (parameters.Count > 0)
1012             {
1013                 // The ReplaceParameterValue below, also checks if the parameter is present.
1014
1015                 String parameterName;
1016                 Int32 i;
1017
1018                 for (i = 0; i < parameters.Count; i++)
1019                 {
1020                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1021                     (parameters[i].Direction == ParameterDirection.InputOutput))
1022                     {
1023
1024                         if (!addProcedureParenthesis)
1025                         {
1026                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1027                             parameterName = parameters[i].ParameterName;
1028                             // The space in front of '$' fixes a parsing problem in 7.3 server
1029                             // which gives errors of operator when finding the caracters '=$' in
1030                             // prepare text
1031                             textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
1032                         }
1033                         else
1034                             textCommand += " $" + (i+1);
1035                     }
1036
1037                 }
1038
1039                 //[TODO] Check if there is any missing parameters in the query.
1040                 // For while, an error is thrown saying about the ':' char.
1041
1042                 command.Append('(');
1043
1044                 for (i = 0; i < parameters.Count; i++)
1045                 {
1046                     //                    command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
1047                     command.Append(parameters[i].TypeInfo.Name);
1048
1049                     command.Append(',');
1050                 }
1051
1052                 command = command.Remove(command.Length - 1, 1);
1053                 command.Append(')');
1054
1055             }
1056
1057             if (addProcedureParenthesis)
1058                 textCommand += ")";
1059
1060             command.Append(" as ");
1061             command.Append(textCommand);
1062
1063
1064             return command.ToString();
1065
1066         }
1067
1068
1069         private String ReplaceParameterValue(String result, String parameterName, String paramVal)
1070         {
1071             Int32 resLen = result.Length;
1072             Int32 paramStart = result.IndexOf(parameterName);
1073             Int32 paramLen = parameterName.Length;
1074             Int32 paramEnd = paramStart + paramLen;
1075             Boolean found = false;
1076
1077
1078             while(paramStart > -1)
1079             {
1080                 if((resLen > paramEnd) &&
1081                         (result[paramEnd] == ' ' ||
1082                          result[paramEnd] == ',' ||
1083                          result[paramEnd] == ')' ||
1084                          result[paramEnd] == ';' ||
1085                          result[paramEnd] == '\n' ||
1086                          result[paramEnd] == '\r' ||
1087                          result[paramEnd] == '\t'))
1088                 {
1089                     result = result.Substring(0, paramStart) + paramVal + result.Substring(paramEnd);
1090                     found = true;
1091                 }
1092                 else if(resLen == paramEnd)
1093                 {
1094                     result = result.Substring(0, paramStart)+ paramVal;
1095                     found = true;
1096                 }
1097                 else
1098                     break;
1099                 resLen = result.Length;
1100                 paramStart = result.IndexOf(parameterName, paramStart);
1101                 paramEnd = paramStart + paramLen;
1102
1103             }//while
1104             if(!found)
1105                 throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"), parameterName));
1106
1107
1108             return result;
1109         }//ReplaceParameterValue
1110
1111
1112         private String AddSingleRowBehaviorSupport(String ResultCommandText)
1113         {
1114
1115             ResultCommandText = ResultCommandText.Trim();
1116
1117             if ((commandBehavior & CommandBehavior.SingleRow) > 0)
1118             {
1119                 if (ResultCommandText.EndsWith(";"))
1120                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1121                 ResultCommandText += " limit 1;";
1122
1123             }
1124
1125
1126
1127             return ResultCommandText;
1128
1129         }
1130
1131
1132         private void ExecuteCommand()
1133         {
1134             // Check the connection state first.
1135             CheckConnectionState();
1136
1137             // reset any responses just before getting new ones
1138             connector.Mediator.ResetResponses();
1139
1140
1141             if (parse == null)
1142             {
1143                 Connector.Query(this);
1144
1145                 // Check for errors and/or notifications and do the Right Thing.
1146                 connector.CheckErrorsAndNotifications();
1147             }
1148             else
1149             {
1150                 try
1151                 {
1152
1153                     BindParameters();
1154
1155                     connector.Execute(new NpgsqlExecute(bind.PortalName, 0));
1156
1157                     // Check for errors and/or notifications and do the Right Thing.
1158                     connector.CheckErrorsAndNotifications();
1159                 }
1160                 finally
1161                 {
1162                     // As per documentation:
1163                     // "[...] When an error is detected while processing any extended-query message,
1164                     // the backend issues ErrorResponse, then reads and discards messages until a
1165                     // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
1166                     // So, send a sync command if we get any problems.
1167
1168                     connector.Sync();
1169                 }
1170             }
1171         }
1172     }
1173 }