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 (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                 result = AddSchemaOnlyBehaviorSupport(result);
 818
 819                 return result;
 820             }
 821
 822
 823             // Get parameters in query string to translate them to their actual values.
 824
 825             // This regular expression gets all the parameters in format :param or @param
 826             // and everythingelse.
 827             // This is only needed if query string has parameters. Else, just append the
 828             // parameter values in order they were put in parameter collection.
 829
 830
 831             // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
 832             if (!addProcedureParenthesis)
 833             {
 834
 835                 Regex a = new Regex(@"(:[\w]*)|(@[\w]*)|(.)", RegexOptions.Singleline);
 836
 837                 //CheckParameters();
 838
 839                 StringBuilder sb = new StringBuilder();
 840
 841                 for ( Match m = a.Match(result); m.Success; m = m.NextMatch() )
 842                 {
 843                     String s = m.Groups[0].ToString();
 844
 845                     if ((s.StartsWith(":") ||
 846                          s.StartsWith("@")) &&
 847                          Parameters.Contains(s))
 848                     {
 849                         // It's a parameter. Lets handle it.
 850
 851                         NpgsqlParameter p = Parameters[s];
 852                         if ((p.Direction == ParameterDirection.Input) ||
 853                              (p.Direction == ParameterDirection.InputOutput))
 854                         {
 855
 856                             // FIXME DEBUG ONLY
 857                             // adding the '::<datatype>' on the end of a parameter is a highly
 858                             // questionable practice, but it is great for debugging!
 859                             sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
 860
 861                             // Only add data type info if we are calling an stored procedure.
 862
 863                             if (type == CommandType.StoredProcedure)
 864                             {
 865                                 sb.Append("::");
 866                                 sb.Append(p.TypeInfo.Name);
 867
 868                                 if (p.TypeInfo.UseSize && (p.Size > 0))
 869                                     sb.Append("(").Append(p.Size).Append(")");
 870                             }
 871                         }
 872
 873                     }
 874                     else
 875                         sb.Append(s);
 876
 877                 }
 878
 879                 result = sb.ToString();
 880             }
 881
 882
 883             else
 884             {
 885
 886                 for (Int32 i = 0; i < parameters.Count; i++)
 887                 {
 888                     NpgsqlParameter Param = parameters[i];
 889
 890
 891                     if ((Param.Direction == ParameterDirection.Input) ||
 892                          (Param.Direction == ParameterDirection.InputOutput))
 893
 894
 895                         result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
 896                 }
 897
 898
 899                 // Remove a trailing comma added from parameter handling above. If any.
 900                 // Maybe there are only output parameters. If so, there will be no comma.
 901                 if (result.EndsWith(","))
 902                     result = result.Remove(result.Length - 1, 1);
 903
 904                 result += ")";
 905             }
 906
 907             if (functionReturnsRecord)
 908                 result = AddFunctionReturnsRecordSupport(result);
 909
 910             // If function returns ref cursor just process refcursor-result function call
 911             // and return command which will be used to return data from refcursor.
 912
 913             if (functionReturnsRefcursor)
 914                 return ProcessRefcursorFunctionReturn(result);
 915
 916
 917             result = AddSingleRowBehaviorSupport(result);
 918
 919             result = AddSchemaOnlyBehaviorSupport(result);
 920
 921             return result;
 922         }
 923
 924
 925
 926         private Boolean CheckFunctionReturn(String ReturnType)
 927         {
 928
 929             String returnRecordQuery = "select count(*) > 0 from pg_proc where prorettype = ( select oid from pg_type where typname = :typename ) and proargtypes=:proargtypes and proname=:proname;";
 930
 931             StringBuilder parameterTypes = new StringBuilder("");
 932
 933             foreach(NpgsqlParameter p in Parameters)
 934             {
 935                 if ((p.Direction == ParameterDirection.Input) ||
 936                      (p.Direction == ParameterDirection.InputOutput))
 937                 {
 938                     parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
 939                 }
 940             }
 941
 942
 943             NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection);
 944
 945             c.Parameters.Add(new NpgsqlParameter("typename", NpgsqlDbType.Text));
 946             c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Text));
 947             c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
 948
 949             c.Parameters[0].Value = ReturnType;
 950             c.Parameters[1].Value = parameterTypes.ToString();
 951             c.Parameters[2].Value = CommandText;
 952
 953
 954             Boolean ret = (Boolean) c.ExecuteScalar();
 955
 956             // reset any responses just before getting new ones
 957             connector.Mediator.ResetResponses();
 958             return ret;
 959
 960
 961         }
 962
 963
 964         private String AddFunctionReturnsRecordSupport(String OriginalResult)
 965         {
 966
 967             StringBuilder sb = new StringBuilder(OriginalResult);
 968
 969             sb.Append(" as (");
 970
 971             foreach(NpgsqlParameter p in Parameters)
 972             {
 973                 if ((p.Direction == ParameterDirection.Output) ||
 974                 (p.Direction == ParameterDirection.InputOutput))
 975                 {
 976                     sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
 977                 }
 978             }
 979
 980             String result = sb.ToString();
 981
 982             result = result.Remove(result.Length - 2, 1);
 983
 984             result += ")";
 985
 986
 987
 988             return result;
 989
 990
 991         }
 992
 993         ///<summary>
 994         /// This methods takes a string with a function call witch returns a refcursor or a set of
 995         /// refcursor. It will return the names of the open cursors/portals which will hold
 996         /// results. In turn, it returns the string which is needed to get the data of this cursors
 997         /// in form of one resultset for each cursor open. This way, clients don't need to do anything
 998         /// else besides calling function normally to get results in this way.
 999         ///</summary>
1000
1001         private String ProcessRefcursorFunctionReturn(String FunctionCall)
1002         {
1003             NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
1004
1005             NpgsqlDataReader dr = c.ExecuteReader();
1006
1007             StringBuilder sb = new StringBuilder();
1008
1009             while (dr.Read())
1010             {
1011                 sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
1012
1013             }
1014
1015             sb.Append(";"); // Just in case there is no response from refcursor function return.
1016
1017             // reset any responses just before getting new ones
1018             connector.Mediator.ResetResponses();
1019
1020             return sb.ToString();
1021
1022
1023         }
1024
1025
1026
1027         private String GetPreparedCommandText()
1028         {
1029             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
1030
1031             if (parameters.Count == 0)
1032                 return "execute " + planName;
1033
1034
1035             StringBuilder result = new StringBuilder("execute " + planName + '(');
1036
1037
1038             for (Int32 i = 0; i < parameters.Count; i++)
1039             {
1040                 result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
1041             }
1042
1043             result = result.Remove(result.Length - 1, 1);
1044             result.Append(')');
1045
1046             return result.ToString();
1047
1048         }
1049
1050
1051
1052         private String GetParseCommandText()
1053         {
1054             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
1055
1056             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1057
1058             String parseCommand = text;
1059
1060             if (type == CommandType.StoredProcedure)
1061             {
1062                 // 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.
1063                 if (!parseCommand.Trim().EndsWith(")"))
1064                 {
1065                     addProcedureParenthesis = true;
1066                     parseCommand += "(";
1067                 }
1068
1069                 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
1070             }
1071             else if (type == CommandType.TableDirect)
1072                 return "select * from " + parseCommand; // There is no parameter support on TableDirect.
1073
1074             if (parameters.Count > 0)
1075             {
1076                 // The ReplaceParameterValue below, also checks if the parameter is present.
1077
1078                 String parameterName;
1079                 Int32 i;
1080
1081                 for (i = 0; i < parameters.Count; i++)
1082                 {
1083                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1084                     (parameters[i].Direction == ParameterDirection.InputOutput))
1085                     {
1086
1087                         if (!addProcedureParenthesis)
1088                         {
1089                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1090                             parameterName = parameters[i].ParameterName;
1091                             //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
1092                             parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
1093                         }
1094                         else
1095                             parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
1096                     }
1097
1098                 }
1099             }
1100
1101             if (addProcedureParenthesis)
1102                 return parseCommand + ")";
1103             else
1104                 return parseCommand;
1105
1106         }
1107
1108
1109         private String GetPrepareCommandText()
1110         {
1111             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
1112
1113             Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
1114
1115             planName = Connector.NextPlanName();
1116
1117             StringBuilder command = new StringBuilder("prepare " + planName);
1118
1119             String textCommand = text;
1120
1121             if (type == CommandType.StoredProcedure)
1122             {
1123                 // 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.
1124                 if (!textCommand.Trim().EndsWith(")"))
1125                 {
1126                     addProcedureParenthesis = true;
1127                     textCommand += "(";
1128                 }
1129
1130                 textCommand = "select * from " + textCommand;
1131             }
1132             else if (type == CommandType.TableDirect)
1133                 return "select * from " + textCommand; // There is no parameter support on TableDirect.
1134
1135
1136             if (parameters.Count > 0)
1137             {
1138                 // The ReplaceParameterValue below, also checks if the parameter is present.
1139
1140                 String parameterName;
1141                 Int32 i;
1142
1143                 for (i = 0; i < parameters.Count; i++)
1144                 {
1145                     if ((parameters[i].Direction == ParameterDirection.Input) ||
1146                     (parameters[i].Direction == ParameterDirection.InputOutput))
1147                     {
1148
1149                         if (!addProcedureParenthesis)
1150                         {
1151                             //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1152                             parameterName = parameters[i].ParameterName;
1153                             // The space in front of '$' fixes a parsing problem in 7.3 server
1154                             // which gives errors of operator when finding the caracters '=$' in
1155                             // prepare text
1156                             textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
1157                         }
1158                         else
1159                             textCommand += " $" + (i+1);
1160                     }
1161
1162                 }
1163
1164                 //[TODO] Check if there is any missing parameters in the query.
1165                 // For while, an error is thrown saying about the ':' char.
1166
1167                 command.Append('(');
1168
1169                 for (i = 0; i < parameters.Count; i++)
1170                 {
1171                     //                    command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
1172                     command.Append(parameters[i].TypeInfo.Name);
1173
1174                     command.Append(',');
1175                 }
1176
1177                 command = command.Remove(command.Length - 1, 1);
1178                 command.Append(')');
1179
1180             }
1181
1182             if (addProcedureParenthesis)
1183                 textCommand += ")";
1184
1185             command.Append(" as ");
1186             command.Append(textCommand);
1187
1188
1189             return command.ToString();
1190
1191         }
1192
1193
1194         private String ReplaceParameterValue(String result, String parameterName, String paramVal)
1195         {
1196             Int32 resLen = result.Length;
1197             Int32 paramStart = result.IndexOf(parameterName);
1198             Int32 paramLen = parameterName.Length;
1199             Int32 paramEnd = paramStart + paramLen;
1200             Boolean found = false;
1201
1202
1203             while(paramStart > -1)
1204             {
1205                 if((resLen > paramEnd) && !Char.IsLetterOrDigit(result, paramEnd))
1206                 {
1207                     result = result.Substring(0, paramStart) + paramVal + result.Substring(paramEnd);
1208                     found = true;
1209                 }
1210                 else if(resLen == paramEnd)
1211                 {
1212                     result = result.Substring(0, paramStart)+ paramVal;
1213                     found = true;
1214                 }
1215                 else
1216                     break;
1217                 resLen = result.Length;
1218                 paramStart = result.IndexOf(parameterName, paramStart);
1219                 paramEnd = paramStart + paramLen;
1220
1221             }//while
1222             if(!found)
1223                 throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"), parameterName));
1224
1225
1226             return result;
1227         }//ReplaceParameterValue
1228
1229
1230         private String AddSingleRowBehaviorSupport(String ResultCommandText)
1231         {
1232
1233             ResultCommandText = ResultCommandText.Trim();
1234
1235             // Do not add SingleRowBehavior if SchemaOnly behavior is set.
1236
1237             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1238                 return ResultCommandText;
1239
1240             if ((commandBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)
1241             {
1242                 if (ResultCommandText.EndsWith(";"))
1243                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1244                 ResultCommandText += " limit 1;";
1245
1246             }
1247
1248
1249
1250             return ResultCommandText;
1251
1252         }
1253
1254         private String AddSchemaOnlyBehaviorSupport(String ResultCommandText)
1255         {
1256
1257             ResultCommandText = ResultCommandText.Trim();
1258
1259             if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1260             {
1261                 if (ResultCommandText.EndsWith(";"))
1262                     ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1263                 ResultCommandText += " limit 0;";
1264
1265             }
1266
1267
1268             return ResultCommandText;
1269
1270         }
1271
1272
1273         private void ExecuteCommand()
1274         {
1275             // Check the connection state first.
1276             CheckConnectionState();
1277
1278             // reset any responses just before getting new ones
1279             Connector.Mediator.ResetResponses();
1280
1281
1282             if (parse == null)
1283             {
1284                 Connector.Query(this);
1285
1286                 // Check for errors and/or notifications and do the Right Thing.
1287                 connector.CheckErrorsAndNotifications();
1288             }
1289             else
1290             {
1291                 try
1292                 {
1293
1294                     BindParameters();
1295
1296                     connector.Execute(new NpgsqlExecute(bind.PortalName, 0));
1297
1298                     // Check for errors and/or notifications and do the Right Thing.
1299                     connector.CheckErrorsAndNotifications();
1300                 }
1301                 finally
1302                 {
1303                     // As per documentation:
1304                     // "[...] When an error is detected while processing any extended-query message,
1305                     // the backend issues ErrorResponse, then reads and discards messages until a
1306                     // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
1307                     // So, send a sync command if we get any problems.
1308
1309                     connector.Sync();
1310                 }
1311             }
1312         }
1313
1314
1315
1316
1317     }
1318 }