Code: Select all
select case
when :ANALYZER_VALUE_1 is not null then
'and ana.analyzer_id in ('||:ANALYZER_VALUE_1||')'
end
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();
}
}
}
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
Any help you can provide would be appreciated.
Bill.