using System.Resources;
using System.ComponentModel;
using System.Collections;
+using System.IO;
using NpgsqlTypes;
-#if !__MonoCS__
+using System.Text.RegularExpressions;
+
+#if WITHDESIGN
using Npgsql.Design;
#endif
/// Represents a SQL statement or function (stored procedure) to execute
/// against a PostgreSQL database. This class cannot be inherited.
/// </summary>
- #if !__MonoCS__
+ #if WITHDESIGN
[System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
#endif
-
public sealed class NpgsqlCommand : Component, IDbCommand, ICloneable
{
// Logging related values
private static readonly String CLASSNAME = "NpgsqlCommand";
private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommand));
+ private static readonly Regex parameterReplace = new Regex(@"([:@][\w\.]*)", RegexOptions.Singleline);
private NpgsqlConnection connection;
private NpgsqlConnector connector;
private CommandBehavior commandBehavior;
+ private Int64 lastInsertedOID = 0;
+
// Constructors
/// <summary>
planName = String.Empty;
text = cmdText;
this.connection = connection;
+
if (this.connection != null)
this.connector = connection.Connector;
parameters = new NpgsqlParameterCollection();
- timeout = 20;
type = CommandType.Text;
this.Transaction = transaction;
commandBehavior = CommandBehavior.Default;
+
+ SetCommandTimeout();
+
+
}
/// <summary>
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
+
planName = String.Empty;
text = cmdText;
this.connector = connector;
type = CommandType.Text;
commandBehavior = CommandBehavior.Default;
+
+ parameters = new NpgsqlParameterCollection();
+
+ // Internal commands aren't affected by command timeout value provided by user.
+ timeout = 20;
}
// Public properties.
/// <value>The time (in seconds) to wait for the command to execute.
/// The default is 20 seconds.</value>
[DefaultValue(20)]
- public Int32 CommandTimeout {
+ public Int32 CommandTimeout
+ {
get
{
return timeout;
/// </summary>
/// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
[Category("Data"), DefaultValue(CommandType.Text)]
- public CommandType CommandType {
+ public CommandType CommandType
+ {
get
{
return type;
/// </summary>
/// <value>The connection to a data source. The default value is a null reference.</value>
[Category("Behavior"), DefaultValue(null)]
- public NpgsqlConnection Connection {
+ public NpgsqlConnection Connection
+ {
get
{
NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
if (this.Connection == value)
return;
- if (this.transaction != null && this.transaction.Connection == null)
- this.transaction = null;
- if (this.connection != null && this.Connector.Transaction != null)
+ //if (this.transaction != null && this.transaction.Connection == null)
+ // this.transaction = null;
+
+ if (this.transaction != null && this.connection != null && this.Connector.Transaction != null)
throw new InvalidOperationException(resman.GetString("Exception_SetConnectionInTransaction"));
this.connection = value;
+ Transaction = null;
if (this.connection != null)
connector = this.connection.Connector;
+ SetCommandTimeout();
+
NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
}
}
- internal NpgsqlConnector Connector {
+ internal NpgsqlConnector Connector
+ {
get
{
- if (connector == null && this.connection != null)
+ if (this.connection != null)
connector = this.connection.Connector;
return connector;
/// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
/// </summary>
/// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
- #if !__MonoCS__
+ #if WITHDESIGN
[Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
#endif
- public NpgsqlParameterCollection Parameters {
+ public NpgsqlParameterCollection Parameters
+ {
get
{
NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
/// </summary>
/// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
/// The default value is a null reference.</value>
- #if !__MonoCS__
+ #if WITHDESIGN
[Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
#endif
/// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
/// </summary>
/// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
- #if !__MonoCS__
+ #if WITHDESIGN
[Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
#endif
set
{
- throw new NotImplementedException();
}
}
+ /// <summary>
+ /// 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.
+ /// </summary>
+
+ public Int64 LastInsertedOID
+ {
+ get
+ {
+ return lastInsertedOID;
+ }
+ }
+
+
/// <summary>
/// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
/// </summary>
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");
- // [TODO] Finish method implementation.
- throw new NotImplementedException();
+ try
+ {
+ // get copy for thread safety of null test
+ NpgsqlConnector connector = Connector;
+ if (connector != null)
+ {
+ connector.CancelRequest();
+ }
+ }
+ catch (IOException)
+ {
+ Connection.ClearPool();
+ }
+ catch (NpgsqlException)
+ {
+ // Cancel documentation says the Cancel doesn't throw on failure
+ }
}
/// <summary>
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
+ // Initialize lastInsertOID
+ lastInsertedOID = 0;
+
ExecuteCommand();
UpdateOutputParameters();
String[] ret_string_tokens = firstCompletedResponse.Split(null); // whitespace separator.
- // Check if the command was insert, delete or update.
+ // Check if the command was insert, delete, update, fetch or move.
// Only theses commands return rows affected.
// [FIXME] Is there a better way to check this??
if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
(String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
- (String.Compare(ret_string_tokens[0], "DELETE", true) == 0))
-
+ (String.Compare(ret_string_tokens[0], "DELETE", true) == 0) ||
+ (String.Compare(ret_string_tokens[0], "FETCH", true) == 0) ||
+ (String.Compare(ret_string_tokens[0], "MOVE", true) == 0))
+
+
+ {
+ if (String.Compare(ret_string_tokens[0], "INSERT", true) == 0)
+ // Get oid of inserted row.
+ lastInsertedOID = Int32.Parse(ret_string_tokens[1]);
+
// The number of rows affected is in the third token for insert queries
// and in the second token for update and delete queries.
// In other words, it is the last token in the 0-based array.
return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
+ }
else
return -1;
}
private void UpdateOutputParameters()
{
// Check if there was some resultset returned. If so, put the result in output parameters.
+ NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "UpdateOutputParameters");
// Get ResultSets.
ArrayList resultSets = Connector.Mediator.ResultSets;
foreach (NpgsqlParameter p in Parameters)
{
- try
+ if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
{
- if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
- {
- hasMapping = true;
- break;
- }
+ hasMapping = true;
+ break;
}
- catch(ArgumentOutOfRangeException)
- {}
+
}
if (((p.Direction == ParameterDirection.Output) ||
(p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
{
- try
+ Int32 fieldIndex = nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1));
+
+ if (fieldIndex > -1)
{
- p.Value = nar[nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1))];
+ p.Value = nar[fieldIndex];
i++;
}
- catch(ArgumentOutOfRangeException)
- {}
+
}
}
commandBehavior = cb;
ExecuteCommand();
-
+
+ UpdateOutputParameters();
+
// Get the resultsets and create a Datareader with them.
- return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, connection, cb);
+ return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, cb, this);
}
///<summary>
if (parameters.Count != 0)
{
Object[] parameterValues = new Object[parameters.Count];
+ Int16[] parameterFormatCodes = bind.ParameterFormatCodes;
+
for (Int32 i = 0; i < parameters.Count; i++)
{
// Do not quote strings, or escape existing quotes - this will be handled by the backend.
// DBNull or null values are returned as null.
// TODO: Would it be better to remove this null special handling out of ConvertToBackend??
- parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
+
+ // Do special handling of bytea values. They will be send in binary form.
+ // TODO: Add binary format support for all supported types. Not only bytea.
+ if (parameters[i].TypeInfo.NpgsqlDbType != NpgsqlDbType.Bytea)
+ {
+
+ parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
+ }
+ else
+ {
+ parameterFormatCodes[i] = (Int16) FormatCode.Binary;
+ parameterValues[i]=(byte[])parameters[i].Value;
+ }
}
bind.ParameterValues = parameterValues;
+ bind.ParameterFormatCodes = parameterFormatCodes;
}
Connector.Bind(bind);
+
+ // See Prepare() method for a discussion of this.
Connector.Mediator.RequireReadyForQuery = false;
Connector.Flush();
+
connector.CheckErrorsAndNotifications();
}
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");
- /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
- if (parse == null)
- connection.Query(this);
- else
- {
- BindParameters();
- connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
- }
- else
- throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
- */
-
ExecuteCommand();
+
// Now get the results.
// Only the first column of the first row must be returned.
// Check the connection state.
CheckConnectionState();
+
+ // reset any responses just before getting new ones
+ Connector.Mediator.ResetResponses();
+
+ // Set command timeout.
+ connector.Mediator.CommandTimeout = CommandTimeout;
- if (! Connector.SupportsPrepare)
+ if (! connector.SupportsPrepare)
{
return; // Do nothing.
}
}
else
{
- // Use the extended query parsing...
- //planName = "NpgsqlPlan" + Connector.NextPlanIndex();
- planName = Connector.NextPlanName();
- String portalName = Connector.NextPortalName();
-
- parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
-
- Connector.Parse(parse);
- Connector.Mediator.RequireReadyForQuery = false;
- Connector.Flush();
-
- // Check for errors and/or notifications and do the Right Thing.
- connector.CheckErrorsAndNotifications();
-
- bind = new NpgsqlBind(portalName, planName, new Int16[] {0}, null, new Int16[] {0});
+ try
+ {
+
+ connector.StopNotificationThread();
+
+ // Use the extended query parsing...
+ planName = connector.NextPlanName();
+ String portalName = connector.NextPortalName();
+
+ parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] {});
+
+ connector.Parse(parse);
+
+ // We need that because Flush() doesn't cause backend to send
+ // ReadyForQuery on error. Without ReadyForQuery, we don't return
+ // from query extended processing.
+
+ // We could have used Connector.Flush() which sends us back a
+ // ReadyForQuery, but on postgresql server below 8.1 there is an error
+ // with extended query processing which hinders us from using it.
+ connector.Mediator.RequireReadyForQuery = false;
+ connector.Flush();
+
+ // Check for errors and/or notifications and do the Right Thing.
+ connector.CheckErrorsAndNotifications();
+
+
+ // Description...
+ NpgsqlDescribe describe = new NpgsqlDescribe('S', planName);
+
+
+ connector.Describe(describe);
+
+ connector.Sync();
+
+ Npgsql.NpgsqlRowDescription returnRowDesc = connector.Mediator.LastRowDescription;
+
+ Int16[] resultFormatCodes;
+
+
+ if (returnRowDesc != null)
+ {
+ resultFormatCodes = new Int16[returnRowDesc.NumFields];
+
+ for (int i=0; i < returnRowDesc.NumFields; i++)
+ {
+ Npgsql.NpgsqlRowDescriptionFieldData returnRowDescData = returnRowDesc[i];
+
+
+ if (returnRowDescData.type_info != null && returnRowDescData.type_info.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Bytea)
+ {
+ // Binary format
+ resultFormatCodes[i] = (Int16)FormatCode.Binary;
+ }
+ else
+ // Text Format
+ resultFormatCodes[i] = (Int16)FormatCode.Text;
+ }
+
+
+ }
+ else
+ resultFormatCodes = new Int16[]{0};
+
+ bind = new NpgsqlBind("", planName, new Int16[Parameters.Count], null, resultFormatCodes);
+ }
+ catch (IOException e)
+ {
+ ClearPoolAndThrowException(e);
+ }
+ catch
+ {
+ // See ExecuteCommand method for a discussion of this.
+ connector.Sync();
+
+ throw;
+ }
+ finally
+ {
+ connector.ResumeNotificationThread();
+ }
+
+
+
}
}
private String GetClearCommandText()
{
- NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
+ if (NpgsqlEventLog.Level == LogLevel.Debug)
+ NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");
+
+ Boolean addProcedureParenthesis = false; // Do not add procedure parenthesis by default.
+
+ Boolean functionReturnsRecord = false; // Functions don't return record by default.
+
+ Boolean functionReturnsRefcursor = false; // Functions don't return refcursor by default.
- Boolean addProcedureParenthesis = false; // Do not add procedure parenthesis by default.
-
- Boolean functionReturnsRecord = false; // Functions don't return record by default.
-
String result = text;
if (type == CommandType.StoredProcedure)
{
-
- functionReturnsRecord = CheckFunctionReturnRecord();
-
- // 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.
- if (!result.Trim().EndsWith(")"))
+
+ if (Parameters.Count > 0)
+ functionReturnsRecord = CheckFunctionReturn("record");
+
+ functionReturnsRefcursor = CheckFunctionReturn("refcursor");
+
+ // 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.
+ if ((!result.Trim().EndsWith(")")) && (!result.Trim().EndsWith(";")))
{
addProcedureParenthesis = true;
result += "(";
}
-
+
if (Connector.SupportsPrepare)
result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
else
if (parameters == null || parameters.Count == 0)
{
if (addProcedureParenthesis)
- result += ")";
-
+ result += ")";
+
+
+ // If function returns ref cursor just process refcursor-result function call
+ // and return command which will be used to return data from refcursor.
+
+ if (functionReturnsRefcursor)
+ return ProcessRefcursorFunctionReturn(result);
+
+
if (functionReturnsRecord)
result = AddFunctionReturnsRecordSupport(result);
-
-
+
+
result = AddSingleRowBehaviorSupport(result);
-
+
+ result = AddSchemaOnlyBehaviorSupport(result);
+
return result;
- }
+ }
- //CheckParameters();
+ // Get parameters in query string to translate them to their actual values.
- for (Int32 i = 0; i < parameters.Count; i++)
+ // This regular expression gets all the parameters in format :param or @param
+ // and everythingelse.
+ // This is only needed if query string has parameters. Else, just append the
+ // parameter values in order they were put in parameter collection.
+
+
+ // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
+ if (!addProcedureParenthesis)
{
- NpgsqlParameter Param = parameters[i];
+ StringBuilder sb = new StringBuilder();
+ NpgsqlParameter p;
+ string[] queryparts = parameterReplace.Split(result);
-
- if ((Param.Direction == ParameterDirection.Input) ||
- (Param.Direction == ParameterDirection.InputOutput))
-
-
- // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
- if (!addProcedureParenthesis)
- // FIXME DEBUG ONLY
- // adding the '::<datatype>' on the end of a parameter is a highly
- // questionable practice, but it is great for debugging!
- // Removed as this was going in infinite loop when the parameter name had the same name of parameter
- // type name. i.e.: parameter name called :text of type text. It would conflict with the parameter type name ::text.
- result = ReplaceParameterValue(
- result,
- Param.ParameterName,
- Param.TypeInfo.ConvertToBackend(Param.Value, false)
- );
+ foreach (String s in queryparts)
+ {
+ if (s == string.Empty)
+ continue;
+
+ if ((s[0] == ':' || s[0] == '@') &&
+ Parameters.TryGetValue(s, out p))
+ {
+ // It's a parameter. Lets handle it.
+ if ((p.Direction == ParameterDirection.Input) ||
+ (p.Direction == ParameterDirection.InputOutput))
+ {
+ // FIXME DEBUG ONLY
+ // adding the '::<datatype>' on the end of a parameter is a highly
+ // questionable practice, but it is great for debugging!
+ sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
+
+ // Only add data type info if we are calling an stored procedure.
+
+ if (type == CommandType.StoredProcedure)
+ {
+ sb.Append("::");
+ sb.Append(p.TypeInfo.Name);
+
+ if (p.TypeInfo.UseSize && (p.Size > 0))
+ sb.Append("(").Append(p.Size).Append(")");
+ }
+ }
+
+ }
else
- result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + ",";
+ sb.Append(s);
+ }
+
+ result = sb.ToString();
}
-
-
- if (addProcedureParenthesis)
+
+ else
{
+
+ for (Int32 i = 0; i < parameters.Count; i++)
+ {
+ NpgsqlParameter Param = parameters[i];
+
+
+ if ((Param.Direction == ParameterDirection.Input) ||
+ (Param.Direction == ParameterDirection.InputOutput))
+
+
+ result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
+ }
+
+
// Remove a trailing comma added from parameter handling above. If any.
- // Maybe there are only output parameters.
+ // Maybe there are only output parameters. If so, there will be no comma.
if (result.EndsWith(","))
result = result.Remove(result.Length - 1, 1);
-
+
result += ")";
}
if (functionReturnsRecord)
result = AddFunctionReturnsRecordSupport(result);
-
- return AddSingleRowBehaviorSupport(result);
+
+ // If function returns ref cursor just process refcursor-result function call
+ // and return command which will be used to return data from refcursor.
+
+ if (functionReturnsRefcursor)
+ return ProcessRefcursorFunctionReturn(result);
+
+
+ result = AddSingleRowBehaviorSupport(result);
+
+ result = AddSchemaOnlyBehaviorSupport(result);
+
+ return result;
}
- private Boolean CheckFunctionReturnRecord()
+ private Boolean CheckFunctionReturn(String ReturnType)
{
-
- if (Parameters.Count == 0)
- return false;
-
- String returnRecordQuery = "select count(*) > 0 from pg_proc where prorettype = ( select oid from pg_type where typname = 'record' ) and proargtypes='{0}' and proname='{1}';";
-
+ // Updated after 0.99.3 to support the optional existence of a name qualifying schema and allow for case insensitivity
+ // when the schema or procedure name do not contain a quote.
+ // The hard-coded schema name 'public' was replaced with code that uses schema as a qualifier, only if it is provided.
+
+ String returnRecordQuery;
+
StringBuilder parameterTypes = new StringBuilder("");
+
+
+ // Process parameters
foreach(NpgsqlParameter p in Parameters)
{
if ((p.Direction == ParameterDirection.Input) ||
- (p.Direction == ParameterDirection.InputOutput))
+ (p.Direction == ParameterDirection.InputOutput))
{
parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
}
}
-
+
+
+ // Process schema name.
+
+ String schemaName = String.Empty;
+ String procedureName = String.Empty;
+
+
+ String[] fullName = CommandText.Split('.');
+
+ if (fullName.Length == 2)
+ {
+ 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";
+
+ schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
+ procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
+ }
+ else
+ {
+ // Instead of defaulting don't use the nspname, as an alternative, query pg_proc and pg_namespace to try and determine the nspname.
+ //schemaName = "public"; // This was removed after build 0.99.3 because the assumption that a function is in public is often incorrect.
+ returnRecordQuery = "select count(*) > 0 from pg_proc p where prorettype = ( select oid from pg_type where typname = :typename ) and proargtypes=:proargtypes and proname=:proname";
+
+ procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower();
+ }
- NpgsqlCommand c = new NpgsqlCommand(String.Format(returnRecordQuery, parameterTypes.ToString(), CommandText), Connection);
- Boolean ret = (Boolean) c.ExecuteScalar();
+
+ NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection);
+
+ c.Parameters.Add(new NpgsqlParameter("typename", NpgsqlDbType.Text));
+ c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Text));
+ c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
+
+ c.Parameters[0].Value = ReturnType;
+ c.Parameters[1].Value = parameterTypes.ToString();
+ c.Parameters[2].Value = procedureName;
+
+ if (schemaName != null && schemaName.Length > 0)
+ {
+ c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
+ c.Parameters[3].Value = schemaName;
+ }
+
+
+ Boolean ret = (Boolean) c.ExecuteScalar();
+
// reset any responses just before getting new ones
connector.Mediator.ResetResponses();
- return ret;
-
+ // Set command timeout.
+ connector.Mediator.CommandTimeout = CommandTimeout;
+
+ return ret;
+
+
}
}
+
+ ///<summary>
+ /// This methods takes a string with a function call witch returns a refcursor or a set of
+ /// refcursor. It will return the names of the open cursors/portals which will hold
+ /// results. In turn, it returns the string which is needed to get the data of this cursors
+ /// in form of one resultset for each cursor open. This way, clients don't need to do anything
+ /// else besides calling function normally to get results in this way.
+ ///</summary>
+
+ private String ProcessRefcursorFunctionReturn(String FunctionCall)
+ {
+ NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
+
+ NpgsqlDataReader dr = c.ExecuteReader();
+
+ StringBuilder sb = new StringBuilder();
+
+ while (dr.Read())
+ {
+ sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
+
+ }
+
+ sb.Append(";"); // Just in case there is no response from refcursor function return.
+
+ // reset any responses just before getting new ones
+ connector.Mediator.ResetResponses();
+
+ // Set command timeout.
+ connector.Mediator.CommandTimeout = CommandTimeout;
+
+ return sb.ToString();
+
+
+ }
}
- //[TODO] Check if there is any missing parameters in the query.
+ //[TODO] Check if there are any missing parameters in the query.
// For while, an error is thrown saying about the ':' char.
command.Append('(');
}
- private String ReplaceParameterValue(String result, String parameterName, String paramVal)
+ private static String ReplaceParameterValue(String result, String parameterName, String paramVal)
{
- Int32 resLen = result.Length;
- Int32 paramStart = result.IndexOf(parameterName);
- Int32 paramLen = parameterName.Length;
- Int32 paramEnd = paramStart + paramLen;
+
+ String quote_pattern = @"['][^']*[']";
+ String pattern = "[- |\n\r\t,)(;=+/]" + parameterName + "([- |\n\r\t,)(;=+/]|$)";
+ Int32 start, end;
+ String withoutquote = result;
Boolean found = false;
-
-
- while(paramStart > -1)
+ // First of all
+ // Suppress quoted string from query (because we ave to ignore them)
+ MatchCollection results = Regex.Matches(result,quote_pattern);
+ foreach (Match match in results)
{
- if((resLen > paramEnd) &&
- (result[paramEnd] == ' ' ||
- result[paramEnd] == ',' ||
- result[paramEnd] == ')' ||
- result[paramEnd] == ';' ||
- result[paramEnd] == '\n' ||
- result[paramEnd] == '\r' ||
- result[paramEnd] == '\t'))
- {
- result = result.Substring(0, paramStart) + paramVal + result.Substring(paramEnd);
- found = true;
- }
- else if(resLen == paramEnd)
- {
- result = result.Substring(0, paramStart)+ paramVal;
- found = true;
- }
- else
+ start = match.Index;
+ end = match.Index + match.Length;
+ String spaces = new String(' ', match.Length-2);
+ withoutquote = withoutquote.Substring(0,start + 1) + spaces + withoutquote.Substring(end - 1);
+ }
+ do
+ {
+ // Now we look for the searched parameters on the "withoutquote" string
+ results = Regex.Matches(withoutquote,pattern);
+ if (results.Count == 0)
+ // If no parameter is found, go out!
break;
- resLen = result.Length;
- paramStart = result.IndexOf(parameterName, paramStart);
- paramEnd = paramStart + paramLen;
-
- }//while
- if(!found)
- throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"), parameterName));
-
-
+ // We take the first parameter found
+ found = true;
+ Match match = results[0];
+ start = match.Index;
+ if ((match.Length - parameterName.Length) == 2)
+ // If the found string is not the end of the string
+ end = match.Index + match.Length - 1;
+ else
+ // If the found string is the end of the string
+ end = match.Index + match.Length;
+ result = result.Substring(0, start + 1) + paramVal + result.Substring(end);
+ withoutquote = withoutquote.Substring(0,start + 1) + paramVal + withoutquote.Substring(end);
+ }
+ while (true);
+ if (!found)
+ throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"),
+ parameterName));
return result;
- }//ReplaceParameterValue
-
+ }
+
private String AddSingleRowBehaviorSupport(String ResultCommandText)
{
ResultCommandText = ResultCommandText.Trim();
+
+ // Do not add SingleRowBehavior if SchemaOnly behavior is set.
+
+ if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
+ return ResultCommandText;
- if ((commandBehavior & CommandBehavior.SingleRow) > 0)
+ if ((commandBehavior & CommandBehavior.SingleRow) == CommandBehavior.SingleRow)
{
if (ResultCommandText.EndsWith(";"))
ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
+ return ResultCommandText;
+
+ }
+
+ private String AddSchemaOnlyBehaviorSupport(String ResultCommandText)
+ {
+
+ ResultCommandText = ResultCommandText.Trim();
+
+ if ((commandBehavior & CommandBehavior.SchemaOnly) == CommandBehavior.SchemaOnly)
+ {
+ if (ResultCommandText.EndsWith(";"))
+ ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
+ ResultCommandText += " limit 0;";
+
+ }
+
+
return ResultCommandText;
}
private void ExecuteCommand()
{
+ try
+ {
+
// Check the connection state first.
CheckConnectionState();
// reset any responses just before getting new ones
- connector.Mediator.ResetResponses();
+ Connector.Mediator.ResetResponses();
+
+ // Set command timeout.
+ connector.Mediator.CommandTimeout = CommandTimeout;
+
+
+ connector.StopNotificationThread();
if (parse == null)
{
- Connector.Query(this);
+ connector.Query(this);
+
+ connector.ResumeNotificationThread();
+
// Check for errors and/or notifications and do the Right Thing.
connector.CheckErrorsAndNotifications();
+
+
+
}
else
{
// Check for errors and/or notifications and do the Right Thing.
connector.CheckErrorsAndNotifications();
}
- finally
+ catch
{
// As per documentation:
// "[...] When an error is detected while processing any extended-query message,
// So, send a sync command if we get any problems.
connector.Sync();
+
+ throw;
}
+ finally
+ {
+ connector.ResumeNotificationThread();
+ }
+ }
+
}
+
+ catch(IOException e)
+ {
+ ClearPoolAndThrowException(e);
+ }
+
+ }
+
+ private void SetCommandTimeout()
+ {
+ if (Connector != null)
+ timeout = Connector.CommandTimeout;
+ else
+ timeout = ConnectionStringDefaults.CommandTimeout;
+ }
+
+ private void ClearPoolAndThrowException(Exception e)
+ {
+ Connection.ClearPool();
+ throw new NpgsqlException(resman.GetString("Exception_ConnectionBroken"), e);
+
}
+
+
+
+
}
}
projects / mono.git / blobdiff