PgSqlException - "Parameter name is missing"

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Welton3
Posts: 20
Joined: Thu 04 Sep 2014 15:36

PgSqlException - "Parameter name is missing"

Post by Welton3 » Tue 04 Dec 2018 20:22

This issue is critical, as we need to resolve it before our application can be released.

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 pkg_date_time__local_today_start_utc() stored procedure returns UTC midnight converted to local time. The vw_daily_checked_out view contains a column of type text.

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 many things, attempting to resolve this issue, all of them unsuccessful.

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)
Using the parameterless SQL, whenever I select the menu option that executes the query, one of following two errors occurs. Which error I get appears to be random.

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
or

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
I have Visual Studio set to break on all CLR exceptions, so when the error occurs, the debugger kicks in, and the execution point is on 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.

Pinturiccio
Devart Team
Posts: 2355
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlException - "Parameter name is missing"

Post by Pinturiccio » Wed 05 Dec 2018 13:55

Please perform the following actions:
1. Debugg your application.
2. When the error occurs in Watch Window, find the following information:
2.1. command.CommandText -send the result to us.
2.2. command.Parameters.Count - send the result to us.
2.3. command.Parameters[0].ParameterName, Parameters[1].ParameterName ... Parameters["Count from point 2.2"-1].ParameterName - send the result to us.
2.4. command.Connection.ProtocolVersion - send the result to us.

Welton3
Posts: 20
Joined: Thu 04 Sep 2014 15:36

Re: PgSqlException - "Parameter name is missing"

Post by Welton3 » Wed 05 Dec 2018 17:08

Here is the information that you requested.

Code: Select all

command.CommandText = 
                   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

command.Parameters.Count = 1
command.Parameters[0].ParameterName = DAYS
command.Parameters[0].Value = 0
command.Connection.ProtocolVersion = Ver30
Please let me know if you need anything further.

Pinturiccio
Devart Team
Posts: 2355
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlException - "Parameter name is missing"

Post by Pinturiccio » Thu 06 Dec 2018 14:35

We could not reproduce the issue. Please create a new console application with the following code:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();

PgSqlCommand comm = new PgSqlCommand(@"
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", conn);


comm.Parameters.Add("DAYS",0);
var reader = comm.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
Is the issue reproduced with this code? Please also provide your connection string (roughly, without credentials).

Welton3
Posts: 20
Joined: Thu 04 Sep 2014 15:36

Re: PgSqlException - "Parameter name is missing"

Post by Welton3 » Mon 10 Dec 2018 14:49

I was unable to reproduce the issue using the console application. However, as I mentioned in my original post, I was successfully able to execute the query during startup of our application. I only causes the error later on, when attempting to execute the query via its menu option.

My connection string is "User Id=username;Password=password;Host=host;Port=5432;Database=cgmlab;Connection Timeout=0;Pooling=False;Max Pool Size=20;ApplicationName=CGM\NOWM000065*LabDAQ.exe"

Would it be possible to do an online debugging session? This issue is important, so I can be available any time.

Pinturiccio
Devart Team
Posts: 2355
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlException - "Parameter name is missing"

Post by Pinturiccio » Mon 10 Dec 2018 17:20

Please perform the following actions first:
1. Add the following line at the beginning of your application:

PgSqlMonitor mon = new PgSqlMonitor() { IsActive = true };

2. Start the dbMonitor application. You can download it from our site: https://www.devart.com/dbmonitor/download.html
3. Reproduce the issue. You will see an error in the dbMonitor window: http://prntscr.com/lt2v5o
4. Click on the red line with an error (prepare or execute).
5. Switch to the Error tab. You should see the "Parameter name is missing." error: http://prntscr.com/lt2wl2
6. Click on the Parameters tab: http://prntscr.com/lt2wy0
7. Send us a screenshot of the whole dbMonitor window when the Parameters tab is selected.

Welton3
Posts: 20
Joined: Thu 04 Sep 2014 15:36

Re: PgSqlException - "Parameter name is missing"

Post by Welton3 » Tue 11 Dec 2018 13:31

After some additional research, I believe that I have discovered the problem: the two queries (from vw_daily_checked_out, on which Visual Studio threw the exception, and vw_rack_warning, for which DbMonitor reported the error) were each being run asynchronously using the same PgSqlConnection instance.

E.g.

Code: Select all

Task<List<VwRackWarningItem>> task = Task.Run(() => _SpecimenStorageManager.GetRackWarnings());
Task<List<VwDailyCheckedOutItem>> task = Task.Run(() => _SpecimenStorageManager.GetCheckedOutItems());
I have implemented logic to create a new PgSqlConnection for each of the queries that are run via a Task and that seems to have solved the issue.

Thank you for your help.

Post Reply