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