/// <summary> /// Creates an array list from pg represenation of an array. /// Multidimensional arrays are treated as ArrayLists of ArrayLists /// </summary> private ArrayList ToArrayList(NpgsqlBackendTypeInfo TypeInfo, String BackendData, Int16 elementTypeSize, Int32 elementTypeModifier) { ArrayList list = new ArrayList(); //remove the braces on either side and work on what they contain. string stripBraces = BackendData.Trim().Substring(1, BackendData.Length - 2).Trim(); if (stripBraces.Length == 0) { return list; } if (stripBraces[0] == '{') //there are still braces so we have an n-dimension array. Recursively build an ArrayList of ArrayLists { foreach (string arrayChunk in ArrayChunkEnumeration(stripBraces)) { list.Add(ToArrayList(TypeInfo, arrayChunk, elementTypeSize, elementTypeModifier)); } } else //We're either dealing with a 1-dimension array or treating a row of an n-dimension array. In either case parse the elements and put them in our ArrayList { foreach (string token in TokenEnumeration(stripBraces)) { //Use the NpgsqlBackendTypeInfo for the element type to obtain each element. list.Add(_elementConverter.ConvertToNative(token, elementTypeSize, elementTypeModifier)); } } return list; }
/// <summary> /// Creates an array from pg representation. /// </summary> public object ToArray(NpgsqlBackendTypeInfo TypeInfo, String BackendData, Int16 TypeSize, Int32 TypeModifier) { //first create an arraylist, then convert it to an array. return ToArray(ToArrayList(TypeInfo, BackendData, TypeSize, TypeModifier), _elementConverter.Type); }
///<summary> /// /// This method is reponsible to derive the command parameter list with values obtained from function definition. /// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown. /// Parameters name will be parameter1, parameter2, ... /// For while, only parameter name and NpgsqlDbType are obtained. ///</summary> /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param> public static void DeriveParameters(NpgsqlCommand command) { // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where 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 { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } String[] names = null; String[] types = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (!rdr.IsDBNull(1)) { types = rdr.GetString(1).Split(); } } } if (types == null) { throw new InvalidOperationException( String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } command.Parameters.Clear(); for (Int32 i = 0; i < types.Length; i++) { // skip parameter if type string is empty // empty parameter lists can cause this if (!string.IsNullOrEmpty(types[i])) { NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo)) { command.Parameters.Clear(); throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } if (names != null && i < names.Length) { command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType)); } else { command.Parameters.Add(new NpgsqlParameter("parameter" + i + 1, typeInfo.NpgsqlDbType)); } } } } }
/// <summary> /// Create a new ArrayBackendToNativeTypeConverter /// </summary> /// <param name="elementConverter"><see cref="NpgsqlBackendTypeInfo"/> for the element type.</param> public ArrayBackendToNativeTypeConverter(NpgsqlBackendTypeInfo elementConverter) { _elementConverter = elementConverter; }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connector.ServerVersion; String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } else { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where 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 { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"; } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; } procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; int[] types = null; string[] modes = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as int[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as String[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetString(1) == "") { return; // Parameterless function } types = rdr.GetString(1).Split().Select(int.Parse).ToArray(); } } else { throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo)) { throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } param.NpgsqlDbType = typeInfo.NpgsqlDbType; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case "i": param.Direction = ParameterDirection.Input; break; case "o": param.Direction = ParameterDirection.Output; break; case "b": param.Direction = ParameterDirection.InputOutput; break; case "v": throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); case "t": throw new NotImplementedException("Cannot derive function parameter of type TABLE"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }