/// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this); } catch (NpgsqlException) { throw; } // Change the state of connection to open and ready. State = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this)) { ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } } StringBuilder sbInit = new StringBuilder(); // Adjust client encoding. NpgsqlParameterStatus clientEncodingParam = null; if (!ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) || !string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase)) sbInit.AppendLine("SET CLIENT_ENCODING TO UTF8;"); if (!string.IsNullOrEmpty(settings.SearchPath)) { // TODO: Add proper message when finding a semicolon in search_path. // This semicolon could lead to a sql injection security hole as someone could write in connection string: // searchpath=public;delete from table; and it would be executed. if (settings.SearchPath.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET SEARCH_PATH=" + settings.SearchPath + ";"); } if (!string.IsNullOrEmpty(settings.ApplicationName)) { if (!SupportsApplicationName) { //TODO //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported")); throw new InvalidOperationException("ApplicationName not supported."); } if (settings.ApplicationName.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET APPLICATION_NAME='" + settings.ApplicationName.Replace('\'', '-') + "';"); } /* * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it. * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here. * This only works on postgresql servers where the ssl renegotiation settings is supported of course. * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information. */ sbInit.AppendLine("SET ssl_renegotiation_limit=0;"); /* * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3. * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least. * Check bug report #1010992 for more information. */ sbInit.AppendLine("SET extra_float_digits=3;"); try { new NpgsqlCommand(sbInit.ToString(), this).ExecuteBlind(); } catch { foreach (var line in sbInit.ToString().Split(Environment.NewLine.ToCharArray())) { try { if (line.Length > 0) { new NpgsqlCommand(line, this).ExecuteBlind(); } } catch { } } } // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
//TODO: WTF!? /// <summary> /// This method checks if the connector is still ok. /// We try to send a simple query text, select 1 as ConnectionTest; /// </summary> internal Boolean IsValid() { try { // Here we use a fake NpgsqlCommand, just to send the test query string. var testValue = (ValidCounter++).ToString(); string compareValue = string.Empty; using (NpgsqlCommand cmd = new NpgsqlCommand("select '" + testValue + "'", this)) { compareValue = (string)cmd.ExecuteScalar(); } if (compareValue != testValue) return false; // Clear mediator. Mediator.ResetResponses(); this.RequireReadyForQuery = true; } catch { return false; } return true; }
/// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this); } catch (NpgsqlException) { throw; } // Change the state of connection to open and ready. State = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this)) { ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } } StringBuilder sbInit = new StringBuilder(); // Adjust client encoding. NpgsqlParameterStatus clientEncodingParam = null; if (!ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) || !string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase)) { sbInit.AppendLine("SET CLIENT_ENCODING TO UTF8;"); } if (!string.IsNullOrEmpty(settings.SearchPath)) { // TODO: Add proper message when finding a semicolon in search_path. // This semicolon could lead to a sql injection security hole as someone could write in connection string: // searchpath=public;delete from table; and it would be executed. if (settings.SearchPath.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET SEARCH_PATH=" + settings.SearchPath + ";"); } if (!string.IsNullOrEmpty(settings.ApplicationName)) { if (!SupportsApplicationName) { //TODO //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported")); throw new InvalidOperationException("ApplicationName not supported."); } if (settings.ApplicationName.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET APPLICATION_NAME='" + settings.ApplicationName.Replace('\'', '-') + "';"); } /* * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it. * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here. * This only works on postgresql servers where the ssl renegotiation settings is supported of course. * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information. */ sbInit.AppendLine("SET ssl_renegotiation_limit=0;"); /* * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3. * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least. * Check bug report #1010992 for more information. */ sbInit.AppendLine("SET extra_float_digits=3;"); try { new NpgsqlCommand(sbInit.ToString(), this).ExecuteBlind(); } catch { foreach (var line in sbInit.ToString().Split(Environment.NewLine.ToCharArray())) { try { if (line.Length > 0) { new NpgsqlCommand(line, this).ExecuteBlind(); } } catch { } } } // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
public string CheckParameter(string paramName) { NpgsqlParameterStatus ps = null; if (_serverParameters.TryGetValue(paramName, out ps)) return ps.ParameterValue; try { using (NpgsqlCommand cmd = new NpgsqlCommand("show " + paramName, this)) { string paramValue = (string)cmd.ExecuteScalar(); AddParameterStatus(new NpgsqlParameterStatus(paramName, paramValue)); return paramValue; } } /* * In case of problems with the command above, we simply return null in order to * say we don't support it. */ catch (NpgsqlException) { return null; } /* * Original catch handler by Jon Hanna. * 7.3 version doesn't support error code. Only 7.4+ * catch(NpgsqlException ne) { if(ne.Code == "42704")//unrecognized configuration parameter return null; else throw; }*/ }
private Boolean CheckFunctionNeedsColumnDefinitionList() { // If and only if a function returns "record" and has no OUT ("o" in proargmodes), INOUT ("b"), or TABLE // ("t") return arguments to characterize the result columns, we must provide a column definition list. // See http://pgfoundry.org/forum/forum.php?thread_id=1075&forum_id=519 // We would use our Output and InputOutput parameters to construct that column definition list. If we have // no such parameters, skip the check: we could only construct "AS ()", which yields a syntax error. // 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 Boolean seenDef = false; foreach (NpgsqlParameter p in Parameters) { if ((p.Direction == ParameterDirection.Input) || (p.Direction == ParameterDirection.InputOutput)) { parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID.ToString() + " "); } if ((p.Direction == ParameterDirection.Output) || (p.Direction == ParameterDirection.InputOutput)) { seenDef = true; } } if (!seenDef) { return false; } // Process schema name. String schemaName = String.Empty; String procedureName = String.Empty; String[] fullName = CommandText.Split('.'); String predicate = "prorettype = ( select oid from pg_type where typname = 'record' ) " + "and proargtypes=:proargtypes and proname=:proname " // proargmodes && array['o','b','t']::"char"[] performs just as well, but it requires PostgreSQL 8.2. + "and ('o' = any (proargmodes) OR 'b' = any (proargmodes) OR 't' = any (proargmodes)) is not true"; if (fullName.Length == 2) { returnRecordQuery = "select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where " + predicate + " 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 " + predicate; procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower(); } bool ret; using (NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection)) { c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Oidvector)); c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Name)); c.Parameters[0].Value = parameterTypes.ToString(); c.Parameters[1].Value = procedureName; if (schemaName != null && schemaName.Length > 0) { c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Name)); c.Parameters[2].Value = schemaName; } ret = (Boolean)c.ExecuteScalar(); } // reset any responses just before getting new ones m_Connector.Mediator.ResetResponses(); // Set command timeout. m_Connector.Mediator.CommandTimeout = CommandTimeout; return ret; }