Error: "Inconsistent types deduced for parameter $1"

[ephillipe]

Hi, I need some help.

I'm trying to execute this query:


StringBuilder sql = new StringBuilder();
sql.Append("update cliente set ");
sql.Append("id_crm = :id_crm");
sql.Append(", codigo_crm = :codigo_crm");
sql.Append(", nome = :nome");
sql.Append(", nome_curto = :nome_curto");
sql.Append(", cnpj = :cnpj");
sql.Append(", bairro = :bairro");
sql.Append(", cidade = :cidade");
sql.Append(", uf = :uf");
sql.Append(", ultima_atualizacao = :ultima_atualizacao");
sql.Append(", inadimplente = :inadimplente");
sql.Append(" where id_crm = :id_crm");

PgSqlCommand updateCommand = new PgSqlCommand(sql.ToString());
updateCommand.ParameterCheck = false;
updateCommand.Parameters.Add("id_crm", PgSqlType.Text).Value = cliente.ID_CRM;
updateCommand.Parameters.Add("codigo_crm", PgSqlType.VarChar).Value = cliente.Codigo_CRM;
updateCommand.Parameters.Add("nome", PgSqlType.VarChar).Value = cliente.Nome;
updateCommand.Parameters.Add("nome_curto", PgSqlType.VarChar).Value = cliente.NomeCurto;
updateCommand.Parameters.Add("cnpj", PgSqlType.VarChar).Value = cliente.CNPJ;
updateCommand.Parameters.Add("bairro", PgSqlType.VarChar).Value = cliente.Bairro;
updateCommand.Parameters.Add("cidade", PgSqlType.VarChar).Value = cliente.Cidade;
updateCommand.Parameters.Add("uf", PgSqlType.VarChar).Value = cliente.UF;
updateCommand.Parameters.Add("ultima_atualizacao", PgSqlType.TimeStamp).Value = DateTime.Now;
updateCommand.Parameters.Add("inadimplente", PgSqlType.Boolean).Value = cliente.Inadimplente;



Then, i get the following error:


Message: {Error: 42P08: Inconsistent types deduced for parameter $1}
Detail Message: text versus character varying
Error Code: -2147467259
ProcedureName: variable_coerce_param_hook
StackTrace: at Devart.Data.PostgreSql.s.e(ag A_0)
at Devart.Data.PostgreSql.ag.ah()
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 System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Repository.Banco.ComunicacaoPostgre.ExecutarComando(PgSqlDataAdapter dataAdaper) in C:\dsn\net\Update Center\WebService\Repositorio\Banco\ComunicacaoPostgre.cs:line 88
at Repository.Banco.ComunicacaoPostgre.ExecutarComandoUpdate(PgSqlCommand updateCommand) in C:\dsn\net\Update Center\WebService\Repositorio\Banco\ComunicacaoPostgre.cs:line 123
at Repositorio.ClienteRepositorio.AtualizarCliente(Cliente cliente) in C:\dsn\net\Update Center\WebService\Repositorio\ClienteRepositorio.cs:line 118
at wcfUpdateCenter.Classes.RetornoDownload.AtualizarDadosCliente(Cliente cliente) in C:\dsn\net\Update Center\WebService\wcfUpdateCenter\Classes\RetornoDownload.cs:line 121
at wcfUpdateCenter.Classes.RetornoDownload.VerificarVersao(Aplicativo aplicativo, String codigoCliente) in C:\dsn\net\Update Center\WebService\wcfUpdateCenter\Classes\RetornoDownload.cs:line 168
at wcfUpdateCenter.UpdateService.VerificarAtualizacao(Aplicativo aplicativo, String codigoCliente) in C:\dsn\net\Update Center\WebService\wcfUpdateCenter\UpdateService.svc.cs:line 22
at SyncInvokeVerificarAtualizacao(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)



If I execute the Update Command with the following parameters the error not ocurrs:

sql.Append(" where id_crm = :id_crm2");

...

updateCommand.Parameters.Add("id_crm2", PgSqlType.Text).Value = cliente.ID_CRM;


My questions:

1º Why this happens?
2º How to solve this?

[Pinturiccio]

We could not reproduce the issue. We try reproducing it on another table and everything works fine.
Could you please post the DDL script for the 'cliente' table here?

[ephillipe]

Here is my DDL:

Code: Select all

-- Table: cliente

-- DROP TABLE cliente;

CREATE TABLE cliente
(
  id bigserial NOT NULL,
  id_crm character varying(10),
  codigo_crm character varying(6),
  nome character varying(100),
  nome_curto character varying(80),
  cnpj character varying(18),
  bairro character varying(80),
  cidade character varying(100),
  uf character varying(2),
  cadastro timestamp with time zone DEFAULT now(),
  ultima_atualizacao timestamp with time zone DEFAULT now(),
  inadimplente boolean,
  CONSTRAINT pk_cliente_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cliente
  OWNER TO postgres;

[Pinturiccio]

We are investigating the issue. We will notify you about the results as soon as possible.

As a temporary workaround, you can change some parts of the code:

Code: Select all

sql.Append(" where id_crm = :id_crm");
->
sql.Append(" where id_crm = :id_crm::varchar(20)");

Or

Code: Select all

sql.Append("id_crm = :id_crm");
->
sql.Append("id_crm = :id_crm::varchar(20)");

Or, actually, you can even delete the line

Code: Select all

sql.Append("id_crm = :id_crm");

This will not influence the result.

[Pinturiccio]

This is a PostgreSQL server exception. When server prepares such query, it cannot define the type of the parameter as it is used twice. Besides, one time it is used in a comparison.

There are several ways to solve this issue.
1. The way described in the previous answer, that is explicit type cast;
2. Executing the command as UnpreparedExecute:

Code: Select all

updateCommand.UnpreparedExecute = true;

3. Not using the following line in the command:

Code: Select all

sql.Append("id_crm = :id_crm");

This line does not influence the result, as you execute the update only for the rows fulfilling the condition

Code: Select all

"where id_crm = :id_crm".

Thus, you assign such value to the id_crm field that is stored in it.