I am using Visual Studio 2017, C#, .NET 4.5.1, dotConnect for PostgreSQL 7.11.1202.0, and PostgreSQL 10.
I am getting a "Parameter name is missing" error when executing a specific query. I cannot reproduce this issue in a sample application. Dozens of queries are run during start-up and when a user logs in to the application. All queries are executed by the same method. The query that is causing the problem is executed when the user selects a specific menu option in the application. The problem always occurs when the menu option is selected, however dozens of other similar menu-driver queries execute with no errors.
To verify that there is not a problem with the query itself, I added code during start-up to execute the query exactly as it is executed when the problem occurs. The early call executes successfully and returns the expected data table.
I have looked at PgSqlException - "Parameter name is missing", and can provide the answers below to questions asked in that thread.
1) Do you use TransactionScope or PgSqlTransaction? - No, there is no transaction for this query, however, we do use transactions elsewhere in our code.
2) PgSqlConnection uses connection pooling. By default connection pooling is used. Is the issue reproduced, if you turn off connection pooling for your connections? This can be done by adding the 'Pooling=false'; parameter to the connection string. - Yes, the issue still occurs when Pooling=false.
3) Do you work with large objects anywhere in your application? - Yes, we do use LOBs. We use columns of type text to store large notes, and columns of type bytea to store PDF's, JPG's, and other binary files.
4) Do you use protocol 3 or protocol 2? - I do not know for certain, but I expect that it is protocol 3.
5) Do you use PgSqlAlerter in your application? - No, we are not using PgSqlAlerter.
6) Do you work with cursors in your application? - Yes, we do use cursors, but not for this query.
7) Does your application work with stored procedures? - Yes, we do use stored procedures, and the query does reference a stored procedure.
The call stack for the error is:
Code: Select all
Devart.Data.PostgreSql.PgSqlException
HResult=0x80004005
Message=Parameter name is missing.
Source=Devart.Data.PostgreSql
StackTrace:
at Devart.Data.PostgreSql.PgSqlCommand.a(ae[] A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.PostgreSql.PgSqlCommand.ExecuteReader()
at CGM.Lab.DAL.PgSql.ReconnectHelper.Execute(ExecuteType executeType, PgSqlCommand command) in C:\_Dev.NET\Lab\DAL\Lab.DAL.PgSql\ReconnectHelper.cs:line 72
[code]
The specific SQL that is being executed is:
[code]
select *
from vw_daily_checked_out
where action_date > pkg_date_time__local_today_start_utc() - (interval '1 day' * :DAYS)
order by requisition_code, specimen_code, rack_name
The code snippet below is from the Execute method of our ReconnectHelper class. This method is used to execute all queries in our application.
Code: Select all
private static object Execute(ExecuteType executeType, PgSqlCommand command)
{
...code removed...
switch (executeType) // this is line 67
{
case ExecuteType.NonQuery:
result = command.ExecuteNonQuery();
break;
case ExecuteType.Reader:
result = command.ExecuteReader(); // this is line 72
break;
case ExecuteType.Scalar:
result = command.ExecuteScalar();
break;
}
...code removed...
return result;
}
I have tried:
- removing the order by clause
- explicitly listing the columns to be returned by the query
- excluding the text column from the list of columns to be returned
- creating a new PgSqlConnection instance
- reducing the query to "select :DAYS"
- replacing the parameter in the query with a literal value (this randomly causes one of two errors, more information below)
Code: Select all
Devart.Data.PostgreSql.PgSqlException
HResult=0x80004005
Message=bind message has 9 result formats but query has 15 columns
Source=Devart.Data.PostgreSql
StackTrace:
at Devart.Data.PostgreSql.PgSqlDataReader.f(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.PostgreSql.PgSqlCommand.ExecuteReader()
at CGM.Lab.DAL.PgSql.ReconnectHelper.Execute(ExecuteType executeType, PgSqlCommand command) in C:\_Dev.NET\Lab\DAL\Lab.DAL.PgSql\ReconnectHelper.cs:line 72
Code: Select all
System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=Devart.Data.PostgreSql
StackTrace:
at Devart.Data.PostgreSql.e.c()
at Devart.Data.PostgreSql.v.a(Char A_0, Boolean A_1, Boolean A_2)
at Devart.Data.PostgreSql.e.a(Char A_0, Boolean A_1, Boolean A_2)
at Devart.Data.PostgreSql.v.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
at Devart.Data.PostgreSql.e.g(r A_0)
at Devart.Data.PostgreSql.e.f(r A_0)
at Devart.Data.PostgreSql.r.n()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.PostgreSql.PgSqlCommand.ExecuteReader()
at CGM.Lab.DAL.PgSql.ReconnectHelper.Execute(ExecuteType executeType, PgSqlCommand command) in C:\_Dev.NET\Lab\DAL\Lab.DAL.PgSql\ReconnectHelper.cs:line 72
Curiously, at least for the "Parameter name is missing" and "bind message" errors, if I drag the execution point back up to line 67 and then just let the application run, the query then executes successfully and returns the expected data table.
Please let me know if there is any additional information that I can provide to help resolve this issue.