Error 42P18: could not determine data type of parameter $1

[wdnichols]

We are attempting to execute the following query using a PgSqlCommand object:

Code: Select all

    select case 
             when :ANALYZER_VALUE_1 is not null then
               'and ana.analyzer_id in ('||:ANALYZER_VALUE_1||')'
           end

However, regardless of the parameter type or value, we get ErrorCode 42P18, Message "42P18: could not determine data type of parameter $1".

We are using Devart.Data.PostgreSql 7.11.1202.0

The following is a unit test method that demonstrates this problem:

Code: Select all

        [TestMethod()]
        public void Error42P18_Test()
        {
            using (var connection = new PgSqlConnection())
            {
                var builder = new PgSqlConnectionStringBuilder();

                builder.Host = "HOST";
                builder.Database = "DATABASE";
                builder.Port = 5432;
                builder.UserId = "USER";
                builder.Password = "PASSWORD";

                connection.ConnectionString = builder.ToString();

                connection.Open();

                const string PARAM_NAME = "ANALYZER_VALUE_1";

                string sql = $@"
                   select case 
                            when :{PARAM_NAME} is not null then
                              'and ana.analyzer_id in ('||:{PARAM_NAME}||')'
                          end";

                try
                {
                    using (var cmd = new PgSqlCommand(sql, connection) { ParameterCheck = true })
                    {
                        cmd.Parameters.Add(new PgSqlParameter(PARAM_NAME, string.Empty));

                        var reader = cmd.ExecuteReader();
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

In practice, we intend to always pass a string as the parameter, or a null value. In all cases, we expect to get a table with one row and one column. When the parameter is null, we expect that row's column to be null.

If we change the query by casting the parameter as text (as follows) then it works without error:

Code: Select all

    select case 
             when :ANALYZER_VALUE_1::text is not null then
               'and ana.analyzer_id in ('||'ANALYZER_VALUE_1'||')'
           end

However, we should not have to change the SQL to get it to respect our parameters.

Any help you can provide would be appreciated.

Bill.

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

As a temporary workaround, you can enclose the first parameter in single quotes. Your query will be the following:

Code: Select all

select case 
             when ':{PARAM_NAME}' is not null then
               'and ana.analyzer_id in ('||:{PARAM_NAME}||')'
           end

[Pinturiccio]

Server cannot determine the parameter type in the "case when $1 is not null" expression. You need to use the cast ::text. In the second case 'and ana.analyzer_id in ('||$1||')' cast is not required, because the concatenation operation is used. This operation can only be used for strings.

If you don't want to modify the query and add ::text to it, you may use cmd.UnpreparedExecute = true; in order not to prepare the query.
For more information, please refer to https://www.devart.com/dotconnect/postg ... ecute.html

[wdnichols]

Your explanation that the "Server cannot determine the parameter type" is not satisfactory. Even though I can get my SQL to work if I change it (by casting the parameter as text), this should not be necessary.

I believe this is a bug, either in "dotConnect for PostgreSQL" or in "PostGreSQL" itself.

Is this issue going to be fixed?

Bill.

[Pinturiccio]

This is the designed behaviour. By default, a dotConnect for PostgreSQL connection uses protocol version 3, and all queries are prepared on the server side. When PostgreSQL prepares a query, it cannot determine a type of a parameter and generates an error. It happens on the PostgreSQL side. If you specify a type by adding ::text, the server will understand which type to use, and no error occurs. Adding "UnpreparedExecute = true" allows disabling query preparation, and the error doesn't occur.