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
  27 using System;
  28 using System.Data;
  29 using System.Net;
  30 using System.Net.Sockets;
  31 using System.IO;
  32 using System.Text;
  33 using System.ComponentModel;
  34 using System.Collections;
  35 using NpgsqlTypes;
  36 using Npgsql.Design;
  37
  38 namespace Npgsql {
  39         /// <summary>
  40         /// Represents a SQL statement or function (stored procedure) to execute against a PostgreSQL database. This class cannot be inherited.
  41         /// </summary>
  42         [System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
  43         public sealed class NpgsqlCommand : Component, IDbCommand, IDisposable {
  44
  45                 private NpgsqlConnection connection;
  46                 private NpgsqlTransaction transaction;
  47                 private String text;
  48                 private Int32 timeout;
  49                 private CommandType type;
  50                 private NpgsqlParameterCollection parameters;
  51                 private String planName;
  52                 private static Int32 planIndex = 0;
  53           private static Int32              portalIndex = 0;
  54
  55           private NpgsqlParse               parse;
  56           private NpgsqlBind                bind;
  57
  58                 // Logging related values
  59                 private static readonly String CLASSNAME = "NpgsqlCommand";
  60                 private System.Resources.ResourceManager resman;
  61
  62                 // Constructors
  63
  64                 /// <summary>
  65                 /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class.
  66                 /// </summary>
  67                 public NpgsqlCommand() : this(String.Empty, null, null){}
  68                 /// <summary>
  69                 /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query.
  70                 /// </summary>
  71                 /// <param name="cmdText">The text of the query.</param>
  72                 public NpgsqlCommand(String cmdText) : this(cmdText, null, null){}
  73                 /// <summary>
  74                 /// 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>.
  75                 /// </summary>
  76                 /// <param name="cmdText">The text of the query.</param>
  77                 /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
  78                 public NpgsqlCommand(String cmdText, NpgsqlConnection connection) : this(cmdText, connection, null){}
  79                 /// <summary>
  80                 /// 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>.
  81                 /// </summary>
  82                 /// <param name="cmdText">The text of the query.</param>
  83                 /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
  84                 /// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
  85                 public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction) {
  86                         resman = new System.Resources.ResourceManager(this.GetType());
  87                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
  88
  89                         planName = String.Empty;
  90                         text = cmdText;
  91                         this.connection = connection;
  92                         parameters = new NpgsqlParameterCollection();
  93                         timeout = 20;
  94                         type = CommandType.Text;
  95                   this.Transaction = transaction;
  96                 }
  97
  98                 // Public properties.
  99                 /// <summary>
 100                 /// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
 101                 /// </summary>
 102                 /// <value>The Transact-SQL statement or stored procedure to execute. The default is an empty string.</value>
 103                 [Category("Data"), DefaultValue("")]
 104                 public String CommandText {
 105                         get {
 106                                 return text;
 107                         }
 108
 109                         set {
 110                                 // [TODO] Validate commandtext.
 111                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandText", value);
 112                                 text = value;
 113                                 planName = String.Empty;
 114                           parse = null;
 115                           bind = null;
 116                         }
 117                 }
 118
 119                 /// <summary>
 120                 /// Gets or sets the wait time before terminating the attempt
 121                 /// to execute a command and generating an error.
 122                 /// </summary>
 123                 /// <value>The time (in seconds) to wait for the command to execute.
 124                 /// The default is 20 seconds.</value>
 125                 [DefaultValue(20)]
 126                 public Int32 CommandTimeout {
 127                         get {
 128                                 return timeout;
 129                         }
 130
 131                         set {
 132                                 if (value < 0)
 133                                         throw new ArgumentException(resman.GetString("Exception_CommandTimeoutLessZero"));
 134
 135                                 timeout = value;
 136                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandTimeout", value);
 137                         }
 138                 }
 139
 140                 /// <summary>
 141                 /// Gets or sets a value indicating how the
 142                 /// <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> property is to be interpreted.
 143                 /// </summary>
 144                 /// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
 145                 [Category("Data"), DefaultValue(CommandType.Text)]
 146                 public CommandType CommandType {
 147                         get {
 148                                 return type;
 149                         }
 150
 151                         set {
 152                                 type = value;
 153                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandType", value);
 154                         }
 155
 156                 }
 157
 158                 IDbConnection IDbCommand.Connection {
 159                         get {
 160                                 return Connection;
 161                         }
 162
 163                         set {
 164                                 connection = (NpgsqlConnection) value;
 165                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Connection", value);
 166                         }
 167                 }
 168
 169                 /// <summary>
 170                 /// Gets or sets the <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>
 171                 /// used by this instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 172                 /// </summary>
 173                 /// <value>The connection to a data source. The default value is a null reference.</value>
 174                 [Category("Behavior"), DefaultValue(null)]
 175                 public NpgsqlConnection Connection {
 176                         get {
 177                                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
 178                                 return connection;
 179                         }
 180
 181                         set {
 182                                 if (this.transaction != null && this.transaction.Connection == null)
 183                                         this.transaction = null;
 184                                 if (this.connection != null && this.connection.InTransaction == true)
 185                                         throw new NpgsqlException(resman.GetString("Exception_SetConnectionInTransaction"));
 186                                 this.connection = value;
 187                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
 188                         }
 189                 }
 190
 191                 IDataParameterCollection IDbCommand.Parameters {
 192                         get {
 193                                 return Parameters;
 194                         }
 195                 }
 196
 197                 /// <summary>
 198                 /// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
 199                 /// </summary>
 200                 /// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
 201                 [Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 202                 public NpgsqlParameterCollection Parameters {
 203                         get {
 204                                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
 205                                 return parameters;
 206                         }
 207                 }
 208
 209                 /// <summary>
 210                 /// Gets or sets the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>
 211                 /// within which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.
 212                 /// </summary>
 213                 /// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
 214                 /// The default value is a null reference.</value>
 215                 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
 216                 public IDbTransaction Transaction {
 217                         get {
 218                                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Transaction");
 219                                 //throw new NotImplementedException();
 220                                 if (this.transaction != null && this.transaction.Connection == null){
 221                                         this.transaction = null;
 222                                 }
 223                                 return this.transaction;
 224                         }
 225
 226                         set {
 227                                 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Transaction" ,value);
 228                                 //throw new NotImplementedException();
 229                                 /*if (this.connection != null && this.connection.InTransaction == true){
 230                                         throw new NpgsqlException(resman.GetString("Exception_SetTransactionInTransaction"));
 231                                 }*/
 232                                 this.transaction = (NpgsqlTransaction) value;
 233                         }
 234                 }
 235
 236                 /// <summary>
 237                 /// Gets or sets how command results are applied to the <see cref="System.Data.DataRow">DataRow</see>
 238                 /// when used by the <see cref="System.Data.Common.DbDataAdapter.Update">Update</see>
 239                 /// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
 240                 /// </summary>
 241                 /// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
 242                 [Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
 243                 public UpdateRowSource UpdatedRowSource {
 244                         get {
 245
 246                                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "UpdatedRowSource");
 247                                 // [FIXME] Strange, the line below doesn't appears in the stack trace.
 248
 249                                 //throw new NotImplementedException();
 250                                 return UpdateRowSource.Both;
 251                         }
 252
 253                         set {
 254                                 throw new NotImplementedException();
 255                         }
 256                 }
 257
 258                 private void CheckNotification() {
 259                         if (connection.Mediator.Notifications.Count > 0)
 260                                 for (int i=0; i < connection.Mediator.Notifications.Count; i++)
 261                                         connection.Notify((NpgsqlNotificationEventArgs) connection.Mediator.Notifications[i]);
 262
 263                 }
 264
 265                 /// <summary>
 266                 /// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 267                 /// </summary>
 268                 /// <remarks>This Method isn't implemented yet.</remarks>
 269                 public void Cancel() {
 270                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");
 271
 272                         // [TODO] Finish method implementation.
 273                         throw new NotImplementedException();
 274                 }
 275
 276                 /// <summary>
 277                 /// Creates a new instance of an <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.
 278                 /// </summary>
 279                 /// <returns>An <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.</returns>
 280                 IDbDataParameter IDbCommand.CreateParameter() {
 281                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.CreateParameter");
 282
 283                         return (NpgsqlParameter) CreateParameter();
 284                 }
 285
 286                 /// <summary>
 287                 /// Creates a new instance of a <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.
 288                 /// </summary>
 289                 /// <returns>A <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.</returns>
 290                 public NpgsqlParameter CreateParameter() {
 291                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateParameter");
 292
 293                         return new NpgsqlParameter();
 294                 }
 295
 296                 /// <summary>
 297                 /// Executes a SQL statement against the connection and returns the number of rows affected.
 298                 /// </summary>
 299                 /// <returns>The number of rows affected.</returns>
 300                 public Int32 ExecuteNonQuery() {
 301                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
 302
 303                         // Check the connection state.
 304                         CheckConnectionState();
 305
 306                         /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
 307                         if (parse == null)
 308                                 connection.Query(this);
 309                     else
 310                     {
 311                       BindParameters();
 312                       connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
 313                     }
 314                         else
 315                                 throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
 316                         */
 317
 318                         ExecuteCommand();
 319
 320                         // Check if there were any errors.
 321                         if (connection.Mediator.Errors.Count > 0) {
 322                                 StringWriter sw = new StringWriter();
 323                                 sw.WriteLine(String.Format(resman.GetString("Exception_MediatorErrors"), "ExecuteNonQuery"));
 324                                 uint i = 1;
 325                                 foreach(string error in connection.Mediator.Errors){
 326                                         sw.WriteLine("{0}. {1}", i++, error);
 327                                 }
 328                                 throw new NpgsqlException(sw.ToString());
 329                         }
 330
 331                         CheckNotification();
 332
 333
 334
 335                         // The only expected result is the CompletedResponse result.
 336                         // If nothing is returned, just return -1.
 337
 338                         if(connection.Mediator.GetCompletedResponses().Count == 0)
 339                                 return -1;
 340
 341                         String[] ret_string_tokens = ((String)connection.Mediator.GetCompletedResponses()[0]).Split(null);      // whitespace separator.
 342
 343                         // Check if the command was insert, delete or update.
 344                         // Only theses commands return rows affected.
 345                         // [FIXME] Is there a better way to check this??
 346                         if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
 347                                 (String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
 348                                 (String.Compare(ret_string_tokens[0], "DELETE", true) == 0))
 349
 350                                 // The number of rows affected is in the third token for insert queries
 351                                 // and in the second token for update and delete queries.
 352                                 // In other words, it is the last token in the 0-based array.
 353
 354                                 return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
 355                         else
 356                                 return -1;
 357
 358                 }
 359
 360                 /// <summary>
 361                 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 362                 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 363                 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 364                 /// </summary>
 365                 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 366                 IDataReader IDbCommand.ExecuteReader() {
 367                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader");
 368
 369                         return (NpgsqlDataReader) ExecuteReader();
 370                 }
 371
 372                 /// <summary>
 373                 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 374                 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 375                 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 376                 /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 377                 /// </summary>
 378                 /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 379                 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 380                 IDataReader IDbCommand.ExecuteReader(CommandBehavior cb) {
 381                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader", cb);
 382
 383                         return (NpgsqlDataReader) ExecuteReader(cb);
 384
 385                 }
 386
 387                 /// <summary>
 388                 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 389                 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 390                 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
 391                 /// </summary>
 392                 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 393                 public NpgsqlDataReader ExecuteReader() {
 394                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader");
 395
 396
 397                         return ExecuteReader(CommandBehavior.Default);
 398
 399                 }
 400
 401                 /// <summary>
 402                 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
 403                 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
 404                 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
 405                 /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
 406                 /// </summary>
 407                 /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
 408                 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
 409                 /// <remarks>Currently the CommandBehavior parameter is ignored.</remarks>
 410                 public NpgsqlDataReader ExecuteReader(CommandBehavior cb) {
 411                         // [FIXME] No command behavior handling.
 412
 413                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader", cb);
 414
 415                         // Check the connection state.
 416                         CheckConnectionState();
 417
 418                         /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
 419                           if (parse == null)
 420                                   connection.Query(this);
 421                     else
 422                     {
 423                       BindParameters();
 424                       connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
 425                     }
 426                         else
 427                                 throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
 428                         */
 429
 430                         ExecuteCommand();
 431
 432                         // Check if there were any errors.
 433                         if (connection.Mediator.Errors.Count > 0) {
 434                                 StringWriter sw = new StringWriter();
 435                                 sw.WriteLine(String.Format(resman.GetString("Exception_MediatorErrors_1P"), "ExecuteReader", cb));
 436                                 uint i = 1;
 437                                 foreach(string error in connection.Mediator.Errors){
 438                                         sw.WriteLine("{0}. {1}", i++, error);
 439                                 }
 440                                 throw new NpgsqlException(sw.ToString());
 441                         }
 442
 443                         CheckNotification();
 444
 445
 446                         // Get the resultsets and create a Datareader with them.
 447                         return new NpgsqlDataReader(connection.Mediator.GetResultSets(), connection.Mediator.GetCompletedResponses(), connection);
 448                 }
 449
 450
 451                 ///<summary>
 452                 /// This method binds the parameters from parameters collection to the bind
 453                 /// message.
 454                 /// </summary>
 455                 private void BindParameters()
 456                 {
 457
 458                   if (parameters.Count != 0)
 459                   {
 460                     Object[] parameterValues = new Object[parameters.Count];
 461                     for (Int32 i = 0; i < parameters.Count; i++)
 462                         {
 463                                 parameterValues[i] = NpgsqlTypesHelper.ConvertNpgsqlParameterToBackendStringValue(parameters[i]);
 464                         }
 465                         bind.ParameterValues = parameterValues;
 466                   }
 467
 468                   connection.Bind(bind);
 469                   connection.Flush();
 470
 471                 }
 472
 473
 474                 /// <summary>
 475                 /// Executes the query, and returns the first column of the first row
 476                 /// in the result set returned by the query. Extra columns or rows are ignored.
 477                 /// </summary>
 478                 /// <returns>The first column of the first row in the result set,
 479                 /// or a null reference if the result set is empty.</returns>
 480                 public Object ExecuteScalar() {
 481                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
 482
 483
 484                         // Check the connection state.
 485                         CheckConnectionState();
 486
 487                         /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
 488                           if (parse == null)
 489                                 connection.Query(this);
 490                     else
 491                     {
 492                       BindParameters();
 493                       connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
 494                     }
 495                         else
 496                                 throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
 497                         */
 498
 499                         ExecuteCommand();
 500
 501                         // Check if there were any errors.
 502                         // [FIXME] Just check the first error.
 503                         if (connection.Mediator.Errors.Count > 0) {
 504                                 StringWriter sw = new StringWriter();
 505                                 sw.WriteLine(String.Format(resman.GetString("Exception_MediatorErrors"), "ExecuteScalar"));
 506                                 uint i = 1;
 507                                 foreach(string error in connection.Mediator.Errors){
 508                                         sw.WriteLine("{0}. {1}", i++, error);
 509                                 }
 510                                 throw new NpgsqlException(sw.ToString());
 511                         }
 512
 513                         CheckNotification();
 514
 515                         //ArrayList results = connection.Mediator.Data;
 516
 517                         //Object result = null; // Result of the ExecuteScalar().
 518
 519
 520                         // Now get the results.
 521                         // Only the first column of the first row must be returned.
 522
 523                         // Get ResultSets.
 524                         ArrayList resultSets = connection.Mediator.GetResultSets();
 525
 526
 527                         // First data is the RowDescription object.
 528                         //NpgsqlRowDescription rd = (NpgsqlRowDescription)results[0];
 529
 530                         NpgsqlResultSet firstResultSet = (NpgsqlResultSet)resultSets[0];
 531
 532                         NpgsqlRowDescription rd = firstResultSet.RowDescription;
 533
 534                         NpgsqlAsciiRow ascii_row = (NpgsqlAsciiRow)firstResultSet[0];
 535
 536                         return ascii_row[0];
 537
 538
 539                 }
 540
 541                 /// <summary>
 542                 /// Creates a prepared version of the command on a PostgreSQL server.
 543                 /// </summary>
 544                 public void Prepare() {
 545                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");
 546
 547                   // Check the connection state.
 548                         CheckConnectionState();
 549
 550                         if (!connection.SupportsPrepare)
 551                                 return; // Do nothing.
 552
 553
 554
 555                         // [TODO] Finish method implementation.
 556                         //throw new NotImplementedException();
 557
 558                         //NpgsqlCommand command = new NpgsqlCommand("prepare plan1 as " + GetCommandText(), connection );
 559
 560                   if (connection.BackendProtocolVersion == ProtocolVersion.Version2)
 561                   {
 562                           NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connection );
 563                           command.ExecuteNonQuery();
 564                   }
 565                   else
 566                   {
 567                     // Use the extended query parsing...
 568                     planName = "NpgsqlPlan" + System.Threading.Interlocked.Increment(ref planIndex);
 569                     String portalName = "NpgsqlPortal" + System.Threading.Interlocked.Increment(ref portalIndex);
 570
 571                     parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
 572
 573                     connection.Parse(parse);
 574                     connection.Flush();
 575
 576                     bind = new NpgsqlBind(portalName, planName, new Int16[] {0}, null, new Int16[] {0});
 577
 578                   }
 579                 }
 580
 581                 /// <summary>
 582                 /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 583                 /// </summary>
 584                 public new void Dispose() {
 585                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
 586                 }
 587
 588                 ///<summary>
 589                 /// This method checks the connection state to see if the connection
 590                 /// is set or it is open. If one of this conditions is not met, throws
 591                 /// an InvalidOperationException
 592                 ///</summary>
 593                 private void CheckConnectionState() {
 594                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");
 595
 596                         // Check the connection state.
 597                         if (connection == null)
 598                                 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNull"));
 599                         if (connection.State != ConnectionState.Open)
 600                                 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
 601
 602                 }
 603
 604                 /// <summary>
 605                 /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
 606                 /// to their actual values.
 607                 /// The parameter name format is <b>:ParameterName</b>.
 608                 /// </summary>
 609                 /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
 610                 internal String GetCommandText() {
 611                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");
 612
 613                         if (planName == String.Empty)
 614                                 return GetClearCommandText();
 615                         else
 616                                 return GetPreparedCommandText();
 617
 618
 619                 }
 620
 621
 622                 private String GetClearCommandText() {
 623                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
 624
 625
 626                         String result = text;
 627
 628                         if (type == CommandType.StoredProcedure)
 629                                 if (connection.SupportsPrepare)
 630                                         result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
 631                                 else
 632                                         result = "select " + result;                            // Only a single result return supported. 7.2 and earlier.
 633                         else if (type == CommandType.TableDirect)
 634                           return "select * from " + result; // There is no parameter support on table direct.
 635
 636                         if (parameters.Count == 0)
 637                                 return result;
 638
 639
 640                         //CheckParameters();
 641
 642                         String parameterName;
 643
 644                         for (Int32 i = 0; i < parameters.Count; i++) {
 645                                 parameterName = parameters[i].ParameterName;
 646
 647                                 result = ReplaceParameterValue(result, parameterName, NpgsqlTypesHelper.ConvertNpgsqlParameterToBackendStringValue(parameters[i]));
 648
 649                         }
 650
 651                         return result;
 652
 653                 }
 654
 655
 656
 657                 private String GetPreparedCommandText() {
 658                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
 659
 660                         if (parameters.Count == 0)
 661                                 return "execute " + planName;
 662
 663
 664                         StringBuilder result = new StringBuilder("execute " + planName + '(');
 665
 666
 667                         for (Int32 i = 0; i < parameters.Count; i++) {
 668                                 result.Append(NpgsqlTypesHelper.ConvertNpgsqlParameterToBackendStringValue(parameters[i]) + ',');
 669                         }
 670
 671                         result = result.Remove(result.Length - 1, 1);
 672                         result.Append(')');
 673
 674                         return result.ToString();
 675
 676                 }
 677
 678
 679
 680                 private String GetParseCommandText()
 681                 {
 682                   NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
 683
 684                   String parseCommand = text;
 685
 686                   if (type == CommandType.StoredProcedure)
 687                                 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
 688                         else if (type == CommandType.TableDirect)
 689                           return "select * from " + parseCommand; // There is no parameter support on TableDirect.
 690
 691                   if (parameters.Count > 0)
 692                         {
 693                                 // The ReplaceParameterValue below, also checks if the parameter is present.
 694
 695                           String parameterName;
 696                                 Int32 i;
 697
 698                                 for (i = 0; i < parameters.Count; i++)
 699                                 {
 700                                         //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
 701                                         parameterName = parameters[i].ParameterName;
 702                                         //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
 703                                   parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1));
 704
 705                                 }
 706                         }
 707
 708                         return parseCommand;
 709
 710                 }
 711
 712
 713                 private String GetPrepareCommandText() {
 714                         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
 715
 716
 717
 718                         planName = "NpgsqlPlan" + System.Threading.Interlocked.Increment(ref planIndex);
 719
 720                         StringBuilder command = new StringBuilder("prepare " + planName);
 721
 722                         String textCommand = text;
 723
 724                         if (type == CommandType.StoredProcedure)
 725                                 textCommand = "select * from " + textCommand;
 726                         else if (type == CommandType.TableDirect)
 727                           return "select * from " + textCommand; // There is no parameter support on TableDirect.
 728
 729
 730                         if (parameters.Count > 0) {
 731                                 // The ReplaceParameterValue below, also checks if the parameter is present.
 732
 733                                 String parameterName;
 734                                 Int32 i;
 735
 736                                 for (i = 0; i < parameters.Count; i++) {
 737                                         //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
 738                                         parameterName = parameters[i].ParameterName;
 739                                         //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
 740                                         textCommand = ReplaceParameterValue(textCommand, parameterName, "$" + (i+1));
 741
 742                                 }
 743
 744                                 //[TODO] Check if there is any missing parameters in the query.
 745                                 // For while, an error is thrown saying about the ':' char.
 746
 747                                 command.Append('(');
 748
 749                                 for (i = 0; i < parameters.Count; i++) {
 750                                         command.Append(NpgsqlTypesHelper.GetBackendTypeNameFromDbType(parameters[i].DbType));
 751
 752                                         command.Append(',');
 753                                 }
 754
 755                                 command = command.Remove(command.Length - 1, 1);
 756                                 command.Append(')');
 757
 758                         }
 759
 760                         command.Append(" as ");
 761                         command.Append(textCommand);
 762
 763
 764                         return command.ToString();
 765
 766                 }
 767
 768
 769                 private String ReplaceParameterValue(String result, String parameterName, String paramVal) {
 770                         Int32 resLen = result.Length;
 771                         Int32 paramStart = result.IndexOf(parameterName);
 772                         Int32 paramLen = parameterName.Length;
 773                         Int32 paramEnd = paramStart + paramLen;
 774                         Boolean found = false;
 775
 776                         while(paramStart > -1) {
 777                                 if((resLen > paramEnd) &&
 778                                         (result[paramEnd] == ' ' ||
 779                                         result[paramEnd] == ',' ||
 780                                         result[paramEnd] == ')' ||
 781                                         result[paramEnd] == ';')) {
 782                                         result = result.Substring(0, paramStart) + paramVal + result.Substring(paramEnd);
 783                                         found = true;
 784                                 }
 785                                 else if(resLen == paramEnd) {
 786                                         result = result.Substring(0, paramStart)+ paramVal;
 787                                         found = true;
 788                                 }
 789                                 else
 790                                         break;
 791                                 resLen = result.Length;
 792                                 paramStart = result.IndexOf(parameterName, paramStart);
 793                                 paramEnd = paramStart + paramLen;
 794
 795                         }//while
 796                         if(!found)
 797                                 throw new NpgsqlException(String.Format(resman.GetString("Exception_ParamNotInQuery"), parameterName));
 798
 799                         return result;
 800                 }//ReplaceParameterValue
 801
 802
 803         private void ExecuteCommand()
 804         {
 805           //if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
 806                           if (parse == null)
 807                                 connection.Query(this);
 808                     else
 809                     {
 810                       BindParameters();
 811                       connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
 812                     }
 813                 /*      else
 814                                 throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));*/
 815
 816         }
 817
 818
 819         }
 820
 821 }