1 // created on 21/5/2002 at 20:03
3 // Npgsql.NpgsqlCommand.cs
6 // Francisco Jr. (fxjrlists@yahoo.com.br)
8 // Copyright (C) 2002 The Npgsql Development Team
9 // npgsql-general@gborg.postgresql.org
10 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
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.
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.
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
29 using System.Resources;
30 using System.ComponentModel;
31 using System.Collections;
35 using System.Text.RegularExpressions;
44 /// Represents a SQL statement or function (stored procedure) to execute
45 /// against a PostgreSQL database. This class cannot be inherited.
48 [System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
50 public sealed class NpgsqlCommand : Component, IDbCommand, ICloneable
52 // Logging related values
53 private static readonly String CLASSNAME = "NpgsqlCommand";
54 private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommand));
55 private static readonly Regex parameterReplace = new Regex(@"([:@][\w\.]*)", RegexOptions.Singleline);
57 private NpgsqlConnection connection;
58 private NpgsqlConnector connector;
59 private NpgsqlTransaction transaction;
61 private Int32 timeout;
62 private CommandType type;
63 private NpgsqlParameterCollection parameters;
64 private String planName;
66 private NpgsqlParse parse;
67 private NpgsqlBind bind;
69 private Boolean invalidTransactionDetected = false;
71 private CommandBehavior commandBehavior;
73 private Int64 lastInsertedOID = 0;
78 /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class.
80 public NpgsqlCommand() : this(String.Empty, null, null)
83 /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query.
85 /// <param name="cmdText">The text of the query.</param>
86 public NpgsqlCommand(String cmdText) : this(cmdText, null, null)
89 /// 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>.
91 /// <param name="cmdText">The text of the query.</param>
92 /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
93 public NpgsqlCommand(String cmdText, NpgsqlConnection connection) : this(cmdText, connection, null)
96 /// 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>.
98 /// <param name="cmdText">The text of the query.</param>
99 /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
100 /// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
101 public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
103 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
105 planName = String.Empty;
107 this.connection = connection;
109 if (this.connection != null)
110 this.connector = connection.Connector;
112 parameters = new NpgsqlParameterCollection();
113 type = CommandType.Text;
114 this.Transaction = transaction;
115 commandBehavior = CommandBehavior.Default;
123 /// Used to execute internal commands.
125 internal NpgsqlCommand(String cmdText, NpgsqlConnector connector)
127 resman = new System.Resources.ResourceManager(this.GetType());
128 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
131 planName = String.Empty;
133 this.connector = connector;
134 type = CommandType.Text;
135 commandBehavior = CommandBehavior.Default;
137 parameters = new NpgsqlParameterCollection();
139 // Internal commands aren't affected by command timeout value provided by user.
143 // Public properties.
145 /// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
147 /// <value>The Transact-SQL statement or stored procedure to execute. The default is an empty string.</value>
148 [Category("Data"), DefaultValue("")]
149 public String CommandText {
157 // [TODO] Validate commandtext.
158 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandText", value);
160 planName = String.Empty;
163 commandBehavior = CommandBehavior.Default;
168 /// Gets or sets the wait time before terminating the attempt
169 /// to execute a command and generating an error.
171 /// <value>The time (in seconds) to wait for the command to execute.
172 /// The default is 20 seconds.</value>
174 public Int32 CommandTimeout
184 throw new ArgumentOutOfRangeException(resman.GetString("Exception_CommandTimeoutLessZero"));
187 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandTimeout", value);
192 /// Gets or sets a value indicating how the
193 /// <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> property is to be interpreted.
195 /// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
196 [Category("Data"), DefaultValue(CommandType.Text)]
197 public CommandType CommandType
207 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandType", value);
211 IDbConnection IDbCommand.Connection
220 Connection = (NpgsqlConnection) value;
221 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Connection", value);
226 /// Gets or sets the <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>
227 /// used by this instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
229 /// <value>The connection to a data source. The default value is a null reference.</value>
230 [Category("Behavior"), DefaultValue(null)]
231 public NpgsqlConnection Connection
235 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
241 if (this.Connection == value)
244 //if (this.transaction != null && this.transaction.Connection == null)
245 // this.transaction = null;
247 if (this.transaction != null && this.connection != null && this.Connector.Transaction != null)
248 throw new InvalidOperationException(resman.GetString("Exception_SetConnectionInTransaction"));
251 this.connection = value;
253 if (this.connection != null)
254 connector = this.connection.Connector;
258 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
262 internal NpgsqlConnector Connector
266 if (this.connection != null)
267 connector = this.connection.Connector;
273 IDataParameterCollection IDbCommand.Parameters {
281 /// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
283 /// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
285 [Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
288 public NpgsqlParameterCollection Parameters
292 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
298 IDbTransaction IDbCommand.Transaction
307 Transaction = (NpgsqlTransaction) value;
308 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Transaction", value);
313 /// Gets or sets the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>
314 /// within which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.
316 /// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
317 /// The default value is a null reference.</value>
319 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
322 public NpgsqlTransaction Transaction {
325 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Transaction");
327 if (this.transaction != null && this.transaction.Connection == null)
329 this.transaction = null;
331 return this.transaction;
336 NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Transaction" ,value);
338 this.transaction = (NpgsqlTransaction) value;
343 /// Gets or sets how command results are applied to the <see cref="System.Data.DataRow">DataRow</see>
344 /// when used by the <see cref="System.Data.Common.DbDataAdapter.Update">Update</see>
345 /// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
347 /// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
349 [Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
352 public UpdateRowSource UpdatedRowSource {
356 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "UpdatedRowSource");
358 return UpdateRowSource.Both;
367 /// Returns oid of inserted row. This is only updated when using executenonQuery and when command inserts just a single row. If table is created without oids, this will always be 0.
370 public Int64 LastInsertedOID
374 return lastInsertedOID;
380 /// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
382 /// <remarks>This Method isn't implemented yet.</remarks>
385 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");
387 // [TODO] Finish method implementation.
388 throw new NotImplementedException();
392 /// Create a new command based on this one.
394 /// <returns>A new NpgsqlCommand object.</returns>
395 Object ICloneable.Clone()
401 /// Create a new connection based on this one.
403 /// <returns>A new NpgsqlConnection object.</returns>
404 public NpgsqlCommand Clone()
406 // TODO: Add consistency checks.
408 return new NpgsqlCommand(CommandText, Connection, Transaction);
412 /// Creates a new instance of an <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.
414 /// <returns>An <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.</returns>
415 IDbDataParameter IDbCommand.CreateParameter()
417 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.CreateParameter");
419 return (NpgsqlParameter) CreateParameter();
423 /// Creates a new instance of a <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.
425 /// <returns>A <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.</returns>
426 public NpgsqlParameter CreateParameter()
428 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateParameter");
430 return new NpgsqlParameter();
434 /// Executes a SQL statement against the connection and returns the number of rows affected.
436 /// <returns>The number of rows affected.</returns>
437 public Int32 ExecuteNonQuery()
439 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
441 // Initialize lastInsertOID
446 UpdateOutputParameters();
449 // If nothing is returned, just return -1.
450 if(Connector.Mediator.CompletedResponses.Count == 0)
455 // Check if the response is available.
456 String firstCompletedResponse = (String)Connector.Mediator.CompletedResponses[0];
458 if (firstCompletedResponse == null)
461 String[] ret_string_tokens = firstCompletedResponse.Split(null); // whitespace separator.
464 // Check if the command was insert, delete, update, fetch or move.
465 // Only theses commands return rows affected.
466 // [FIXME] Is there a better way to check this??
467 if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
468 (String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
469 (String.Compare(ret_string_tokens[0], "DELETE", true) == 0) ||
470 (String.Compare(ret_string_tokens[0], "FETCH", true) == 0) ||
471 (String.Compare(ret_string_tokens[0], "MOVE", true) == 0))
475 if (String.Compare(ret_string_tokens[0], "INSERT", true) == 0)
476 // Get oid of inserted row.
477 lastInsertedOID = Int32.Parse(ret_string_tokens[1]);
479 // The number of rows affected is in the third token for insert queries
480 // and in the second token for update and delete queries.
481 // In other words, it is the last token in the 0-based array.
483 return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
491 private void UpdateOutputParameters()
493 // Check if there was some resultset returned. If so, put the result in output parameters.
494 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "UpdateOutputParameters");
497 ArrayList resultSets = Connector.Mediator.ResultSets;
499 if (resultSets.Count != 0)
501 NpgsqlResultSet nrs = (NpgsqlResultSet)resultSets[0];
503 if ((nrs != null) && (nrs.Count > 0))
505 NpgsqlAsciiRow nar = (NpgsqlAsciiRow)nrs[0];
508 Boolean hasMapping = false;
510 // First check if there is any mapping between parameter name and resultset name.
511 // If so, just update output parameters which has mapping.
513 foreach (NpgsqlParameter p in Parameters)
515 if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
526 foreach (NpgsqlParameter p in Parameters)
528 if (((p.Direction == ParameterDirection.Output) ||
529 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
531 Int32 fieldIndex = nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1));
535 p.Value = nar[fieldIndex];
544 foreach (NpgsqlParameter p in Parameters)
546 if (((p.Direction == ParameterDirection.Output) ||
547 (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
561 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
562 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
563 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
565 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
566 IDataReader IDbCommand.ExecuteReader()
568 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader");
570 return (NpgsqlDataReader) ExecuteReader();
574 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
575 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
576 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
577 /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
579 /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
580 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
581 IDataReader IDbCommand.ExecuteReader(CommandBehavior cb)
583 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader", cb);
585 return (NpgsqlDataReader) ExecuteReader(cb);
589 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
590 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
591 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
593 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
594 public NpgsqlDataReader ExecuteReader()
596 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader");
598 return ExecuteReader(CommandBehavior.Default);
602 /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
603 /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
604 /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
605 /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
607 /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
608 /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
609 /// <remarks>Currently the CommandBehavior parameter is ignored.</remarks>
610 public NpgsqlDataReader ExecuteReader(CommandBehavior cb)
612 // [FIXME] No command behavior handling.
614 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader", cb);
615 commandBehavior = cb;
619 UpdateOutputParameters();
621 // Get the resultsets and create a Datareader with them.
622 return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, cb, this);
626 /// This method binds the parameters from parameters collection to the bind
629 private void BindParameters()
632 if (parameters.Count != 0)
634 Object[] parameterValues = new Object[parameters.Count];
635 Int16[] parameterFormatCodes = bind.ParameterFormatCodes;
637 for (Int32 i = 0; i < parameters.Count; i++)
639 // Do not quote strings, or escape existing quotes - this will be handled by the backend.
640 // DBNull or null values are returned as null.
641 // TODO: Would it be better to remove this null special handling out of ConvertToBackend??
643 // Do special handling of bytea values. They will be send in binary form.
644 // TODO: Add binary format support for all supported types. Not only bytea.
645 if (parameters[i].TypeInfo.NpgsqlDbType != NpgsqlDbType.Bytea)
648 parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
652 parameterFormatCodes[i] = (Int16) FormatCode.Binary;
653 parameterValues[i]=(byte[])parameters[i].Value;
656 bind.ParameterValues = parameterValues;
657 bind.ParameterFormatCodes = parameterFormatCodes;
660 Connector.Bind(bind);
662 // See Prepare() method for a discussion of this.
663 Connector.Mediator.RequireReadyForQuery = false;
667 connector.CheckErrorsAndNotifications();
671 /// Executes the query, and returns the first column of the first row
672 /// in the result set returned by the query. Extra columns or rows are ignored.
674 /// <returns>The first column of the first row in the result set,
675 /// or a null reference if the result set is empty.</returns>
676 public Object ExecuteScalar()
678 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
682 // Now get the results.
683 // Only the first column of the first row must be returned.
686 ArrayList resultSets = Connector.Mediator.ResultSets;
688 // First data is the RowDescription object.
689 // Check all resultsets as insert commands could have been sent along
690 // with resultset queries. The insert commands return null and and some queries
691 // may return empty resultsets, so, if we find one of these, skip to next resultset.
692 // If no resultset is found, return null as per specification.
694 NpgsqlAsciiRow ascii_row = null;
695 foreach( NpgsqlResultSet nrs in resultSets )
697 if( (nrs != null) && (nrs.Count > 0) )
699 ascii_row = (NpgsqlAsciiRow) nrs[0];
708 /// Creates a prepared version of the command on a PostgreSQL server.
710 public void Prepare()
712 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");
714 // Check the connection state.
715 CheckConnectionState();
717 // reset any responses just before getting new ones
718 Connector.Mediator.ResetResponses();
720 // Set command timeout.
721 connector.Mediator.CommandTimeout = CommandTimeout;
723 if (! connector.SupportsPrepare)
725 return; // Do nothing.
728 if (connector.BackendProtocolVersion == ProtocolVersion.Version2)
730 NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connector );
731 command.ExecuteNonQuery();
738 connector.StopNotificationThread();
740 // Use the extended query parsing...
741 planName = connector.NextPlanName();
742 String portalName = connector.NextPortalName();
744 parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
746 connector.Parse(parse);
748 // We need that because Flush() doesn't cause backend to send
749 // ReadyForQuery on error. Without ReadyForQuery, we don't return
750 // from query extended processing.
752 // We could have used Connector.Flush() which sends us back a
753 // ReadyForQuery, but on postgresql server below 8.1 there is an error
754 // with extended query processing which hinders us from using it.
755 connector.Mediator.RequireReadyForQuery = false;
758 // Check for errors and/or notifications and do the Right Thing.
759 connector.CheckErrorsAndNotifications();
763 NpgsqlDescribe describe = new NpgsqlDescribe('S', planName);
766 connector.Describe(describe);
770 Npgsql.NpgsqlRowDescription returnRowDesc = connector.Mediator.LastRowDescription;
772 Int16[] resultFormatCodes;
775 if (returnRowDesc != null)
777 resultFormatCodes = new Int16[returnRowDesc.NumFields];
779 for (int i=0; i < returnRowDesc.NumFields; i++)
781 Npgsql.NpgsqlRowDescriptionFieldData returnRowDescData = returnRowDesc[i];
784 if (returnRowDescData.type_info != null && returnRowDescData.type_info.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Bytea)
787 resultFormatCodes[i] = (Int16)FormatCode.Binary;
791 resultFormatCodes[i] = (Int16)FormatCode.Text;
797 resultFormatCodes = new Int16[]{0};
799 bind = new NpgsqlBind("", planName, new Int16[Parameters.Count], null, resultFormatCodes);
803 // See ExecuteCommand method for a discussion of this.
810 connector.ResumeNotificationThread();
820 /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
822 protected override void Dispose (bool disposing)
827 // Only if explicitly calling Close or dispose we still have access to
828 // managed resources.
829 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
830 if (connection != null)
832 connection.Dispose();
834 base.Dispose(disposing);
840 /// This method checks the connection state to see if the connection
841 /// is set or it is open. If one of this conditions is not met, throws
842 /// an InvalidOperationException
844 private void CheckConnectionState()
846 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");
849 // Check the connection state.
850 if (Connector == null || Connector.State != ConnectionState.Open)
852 throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
857 /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
858 /// to their actual values.
859 /// The parameter name format is <b>:ParameterName</b>.
861 /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
862 internal String GetCommandText()
864 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");
866 if (planName == String.Empty)
867 return GetClearCommandText();
869 return GetPreparedCommandText();
873 private String GetClearCommandText()
875 if (NpgsqlEventLog.Level == LogLevel.Debug)
876 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
878 Boolean addProcedureParenthesis = false; // Do not add procedure parenthesis by default.
880 Boolean functionReturnsRecord = false; // Functions don't return record by default.
882 Boolean functionReturnsRefcursor = false; // Functions don't return refcursor by default.
884 String result = text;
886 if (type == CommandType.StoredProcedure)
889 if (Parameters.Count > 0)
890 functionReturnsRecord = CheckFunctionReturn("record");
892 functionReturnsRefcursor = CheckFunctionReturn("refcursor");
894 // 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.
895 if ((!result.Trim().EndsWith(")")) && (!result.Trim().EndsWith(";")))
897 addProcedureParenthesis = true;
901 if (Connector.SupportsPrepare)
902 result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
904 result = "select " + result; //Only a single result return supported. 7.2 and earlier.
906 else if (type == CommandType.TableDirect)
907 return "select * from " + result; // There is no parameter support on table direct.
909 if (parameters == null || parameters.Count == 0)
911 if (addProcedureParenthesis)
915 // If function returns ref cursor just process refcursor-result function call
916 // and return command which will be used to return data from refcursor.
918 if (functionReturnsRefcursor)
919 return ProcessRefcursorFunctionReturn(result);
922 if (functionReturnsRecord)
923 result = AddFunctionReturnsRecordSupport(result);
926 result = AddSingleRowBehaviorSupport(result);
928 result = AddSchemaOnlyBehaviorSupport(result);
934 // Get parameters in query string to translate them to their actual values.
936 // This regular expression gets all the parameters in format :param or @param
937 // and everythingelse.
938 // This is only needed if query string has parameters. Else, just append the
939 // parameter values in order they were put in parameter collection.
942 // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
943 if (!addProcedureParenthesis)
945 StringBuilder sb = new StringBuilder();
947 string[] queryparts = parameterReplace.Split(result);
949 foreach (String s in queryparts)
951 if (s == string.Empty)
954 if ((s[0] == ':' || s[0] == '@') &&
955 Parameters.TryGetValue(s, out p))
957 // It's a parameter. Lets handle it.
958 if ((p.Direction == ParameterDirection.Input) ||
959 (p.Direction == ParameterDirection.InputOutput))
962 // adding the '::<datatype>' on the end of a parameter is a highly
963 // questionable practice, but it is great for debugging!
964 sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
966 // Only add data type info if we are calling an stored procedure.
968 if (type == CommandType.StoredProcedure)
971 sb.Append(p.TypeInfo.Name);
973 if (p.TypeInfo.UseSize && (p.Size > 0))
974 sb.Append("(").Append(p.Size).Append(")");
983 result = sb.ToString();
989 for (Int32 i = 0; i < parameters.Count; i++)
991 NpgsqlParameter Param = parameters[i];
994 if ((Param.Direction == ParameterDirection.Input) ||
995 (Param.Direction == ParameterDirection.InputOutput))
998 result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
1002 // Remove a trailing comma added from parameter handling above. If any.
1003 // Maybe there are only output parameters. If so, there will be no comma.
1004 if (result.EndsWith(","))
1005 result = result.Remove(result.Length - 1, 1);
1010 if (functionReturnsRecord)
1011 result = AddFunctionReturnsRecordSupport(result);
1013 // If function returns ref cursor just process refcursor-result function call
1014 // and return command which will be used to return data from refcursor.
1016 if (functionReturnsRefcursor)
1017 return ProcessRefcursorFunctionReturn(result);
1020 result = AddSingleRowBehaviorSupport(result);
1022 result = AddSchemaOnlyBehaviorSupport(result);
1029 private Boolean CheckFunctionReturn(String ReturnType)
1031 String returnRecordQuery = "select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where prorettype = ( select oid from pg_type where typname = :typename ) and proargtypes=:proargtypes and proname=:proname and n.nspname=:nspname";
1033 StringBuilder parameterTypes = new StringBuilder("");
1036 // Process parameters
1038 foreach(NpgsqlParameter p in Parameters)
1040 if ((p.Direction == ParameterDirection.Input) ||
1041 (p.Direction == ParameterDirection.InputOutput))
1043 parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
1048 // Process schema name.
1050 String schemaName = String.Empty;
1051 String procedureName = String.Empty;
1054 String[] schemaProcedureName = CommandText.Split('.');
1056 if (schemaProcedureName.Length == 2)
1058 schemaName = schemaProcedureName[0];
1059 procedureName = schemaProcedureName[1];
1063 schemaName = "public";
1064 procedureName = CommandText;
1070 NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection);
1072 c.Parameters.Add(new NpgsqlParameter("typename", NpgsqlDbType.Text));
1073 c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Text));
1074 c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
1075 c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
1077 c.Parameters[0].Value = ReturnType;
1078 c.Parameters[1].Value = parameterTypes.ToString();
1079 c.Parameters[2].Value = procedureName;
1080 c.Parameters[3].Value = schemaName;
1083 Boolean ret = (Boolean) c.ExecuteScalar();
1085 // reset any responses just before getting new ones
1086 connector.Mediator.ResetResponses();
1088 // Set command timeout.
1089 connector.Mediator.CommandTimeout = CommandTimeout;
1097 private String AddFunctionReturnsRecordSupport(String OriginalResult)
1100 StringBuilder sb = new StringBuilder(OriginalResult);
1104 foreach(NpgsqlParameter p in Parameters)
1106 if ((p.Direction == ParameterDirection.Output) ||
1107 (p.Direction == ParameterDirection.InputOutput))
1109 sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
1113 String result = sb.ToString();
1115 result = result.Remove(result.Length - 2, 1);
1127 /// This methods takes a string with a function call witch returns a refcursor or a set of
1128 /// refcursor. It will return the names of the open cursors/portals which will hold
1129 /// results. In turn, it returns the string which is needed to get the data of this cursors
1130 /// in form of one resultset for each cursor open. This way, clients don't need to do anything
1131 /// else besides calling function normally to get results in this way.
1134 private String ProcessRefcursorFunctionReturn(String FunctionCall)
1136 NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
1138 NpgsqlDataReader dr = c.ExecuteReader();
1140 StringBuilder sb = new StringBuilder();
1144 sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
1148 sb.Append(";"); // Just in case there is no response from refcursor function return.
1150 // reset any responses just before getting new ones
1151 connector.Mediator.ResetResponses();
1153 // Set command timeout.
1154 connector.Mediator.CommandTimeout = CommandTimeout;
1156 return sb.ToString();
1163 private String GetPreparedCommandText()
1165 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");
1167 if (parameters.Count == 0)
1168 return "execute " + planName;
1171 StringBuilder result = new StringBuilder("execute " + planName + '(');
1174 for (Int32 i = 0; i < parameters.Count; i++)
1176 result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
1179 result = result.Remove(result.Length - 1, 1);
1182 return result.ToString();
1188 private String GetParseCommandText()
1190 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");
1192 Boolean addProcedureParenthesis = false; // Do not add procedure parenthesis by default.
1194 String parseCommand = text;
1196 if (type == CommandType.StoredProcedure)
1198 // 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.
1199 if (!parseCommand.Trim().EndsWith(")"))
1201 addProcedureParenthesis = true;
1202 parseCommand += "(";
1205 parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
1207 else if (type == CommandType.TableDirect)
1208 return "select * from " + parseCommand; // There is no parameter support on TableDirect.
1210 if (parameters.Count > 0)
1212 // The ReplaceParameterValue below, also checks if the parameter is present.
1214 String parameterName;
1217 for (i = 0; i < parameters.Count; i++)
1219 if ((parameters[i].Direction == ParameterDirection.Input) ||
1220 (parameters[i].Direction == ParameterDirection.InputOutput))
1223 if (!addProcedureParenthesis)
1225 //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1226 parameterName = parameters[i].ParameterName;
1227 //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
1228 parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
1231 parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
1237 if (addProcedureParenthesis)
1238 return parseCommand + ")";
1240 return parseCommand;
1245 private String GetPrepareCommandText()
1247 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");
1249 Boolean addProcedureParenthesis = false; // Do not add procedure parenthesis by default.
1251 planName = Connector.NextPlanName();
1253 StringBuilder command = new StringBuilder("prepare " + planName);
1255 String textCommand = text;
1257 if (type == CommandType.StoredProcedure)
1259 // 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.
1260 if (!textCommand.Trim().EndsWith(")"))
1262 addProcedureParenthesis = true;
1266 textCommand = "select * from " + textCommand;
1268 else if (type == CommandType.TableDirect)
1269 return "select * from " + textCommand; // There is no parameter support on TableDirect.
1272 if (parameters.Count > 0)
1274 // The ReplaceParameterValue below, also checks if the parameter is present.
1276 String parameterName;
1279 for (i = 0; i < parameters.Count; i++)
1281 if ((parameters[i].Direction == ParameterDirection.Input) ||
1282 (parameters[i].Direction == ParameterDirection.InputOutput))
1285 if (!addProcedureParenthesis)
1287 //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
1288 parameterName = parameters[i].ParameterName;
1289 // The space in front of '$' fixes a parsing problem in 7.3 server
1290 // which gives errors of operator when finding the caracters '=$' in
1292 textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
1295 textCommand += " $" + (i+1);
1300 //[TODO] Check if there are any missing parameters in the query.
1301 // For while, an error is thrown saying about the ':' char.
1303 command.Append('(');
1305 for (i = 0; i < parameters.Count; i++)
1307 // command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
1308 command.Append(parameters[i].TypeInfo.Name);
1310 command.Append(',');
1313 command = command.Remove(command.Length - 1, 1);
1314 command.Append(')');
1318 if (addProcedureParenthesis)
1321 command.Append(" as ");
1322 command.Append(textCommand);
1325 return command.ToString();
1330 private static String ReplaceParameterValue(String result, String parameterName, String paramVal)
1333 String quote_pattern = @"['][^']*[']";
1334 String pattern = "[- |\n\r\t,)(;=+/]" + parameterName + "([- |\n\r\t,)(;=+/]|$)";
1336 String withoutquote = result;
1337 Boolean found = false;
1339 // Suppress quoted string from query (because we ave to ignore them)
1340 MatchCollection results = Regex.Matches(result,quote_pattern);
1341 foreach (Match match in results)
1343 start = match.Index;
1344 end = match.Index + match.Length;
1345 String spaces = new String(' ', match.Length-2);
1346 withoutquote = withoutquote.Substring(0,start + 1) + spaces + withoutquote.Substring(end - 1);
1350 // Now we look for the searched parameters on the "withoutquote" string
1351 results = Regex.Matches(withoutquote,pattern);
1352 if (results.Count == 0)
1353 // If no parameter is found, go out!
1355 // We take the first parameter found
1357 Match match = results[0];
1358 start = match.Index;
1359 if ((match.Length - parameterName.Length) == 2)
1360 // If the found string is not the end of the string
1361 end = match.Index + match.Length - 1;
1363 // If the found string is the end of the string
1364 end = match.Index + match.Length;
1365 result = result.Substring(0, start + 1) + paramVal + result.Substring(end);
1366 withoutquote = withoutquote.Substring(0,start + 1) + paramVal + withoutquote.Substring(end);
1370 throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"),
1376 private String AddSingleRowBehaviorSupport(String ResultCommandText)
1379 ResultCommandText = ResultCommandText.Trim();
1381 // Do not add SingleRowBehavior if SchemaOnly behavior is set.
1383 if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1384 return ResultCommandText;
1386 if ((commandBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)
1388 if (ResultCommandText.EndsWith(";"))
1389 ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1390 ResultCommandText += " limit 1;";
1396 return ResultCommandText;
1400 private String AddSchemaOnlyBehaviorSupport(String ResultCommandText)
1403 ResultCommandText = ResultCommandText.Trim();
1405 if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
1407 if (ResultCommandText.EndsWith(";"))
1408 ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
1409 ResultCommandText += " limit 0;";
1414 return ResultCommandText;
1419 private void ExecuteCommand()
1421 // Check the connection state first.
1422 CheckConnectionState();
1424 // reset any responses just before getting new ones
1425 Connector.Mediator.ResetResponses();
1427 // Set command timeout.
1428 connector.Mediator.CommandTimeout = CommandTimeout;
1431 connector.StopNotificationThread();
1436 connector.Query(this);
1439 connector.ResumeNotificationThread();
1441 // Check for errors and/or notifications and do the Right Thing.
1442 connector.CheckErrorsAndNotifications();
1454 connector.Execute(new NpgsqlExecute(bind.PortalName, 0));
1456 // Check for errors and/or notifications and do the Right Thing.
1457 connector.CheckErrorsAndNotifications();
1461 // As per documentation:
1462 // "[...] When an error is detected while processing any extended-query message,
1463 // the backend issues ErrorResponse, then reads and discards messages until a
1464 // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
1465 // So, send a sync command if we get any problems.
1473 connector.ResumeNotificationThread();
1479 private void SetCommandTimeout()
1481 if (Connector != null)
1482 timeout = Connector.CommandTimeout;
1484 timeout = ConnectionStringDefaults.CommandTimeout;