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