Error: "Inconsistent types deduced for parameter $1"

Error: "Inconsistent types deduced for parameter $1"

Postby ephillipe » Fri 14 Dec 2012 12:07

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?
ephillipe
 
Posts: 19
Joined: Tue 11 Aug 2009 14:54
Location: Teresópolis, Brazil

Re: Error: "Inconsistent types deduced for parameter $1"

Postby Pinturiccio » Mon 17 Dec 2012 16:11

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?
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Error: "Inconsistent types deduced for parameter $1"

Postby ephillipe » Wed 19 Dec 2012 13:09

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;
ephillipe
 
Posts: 19
Joined: Tue 11 Aug 2009 14:54
Location: Teresópolis, Brazil

Re: Error: "Inconsistent types deduced for parameter $1"

Postby Pinturiccio » Fri 21 Dec 2012 14:12

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
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Error: "Inconsistent types deduced for parameter $1"

Postby Pinturiccio » Fri 28 Dec 2012 09:34

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.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL