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?
Error: "Inconsistent types deduced for parameter $1"
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Error: "Inconsistent types deduced for parameter $1"
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?
Could you please post the DDL script for the 'cliente' table here?
Re: Error: "Inconsistent types deduced for parameter $1"
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;
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Error: "Inconsistent types deduced for parameter $1"
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:
Or
Or, actually, you can even delete the line
This will not influence the result.
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)");
Code: Select all
sql.Append("id_crm = :id_crm");
->
sql.Append("id_crm = :id_crm::varchar(20)");
Code: Select all
sql.Append("id_crm = :id_crm");
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Error: "Inconsistent types deduced for parameter $1"
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:
3. Not using the following line in the command:
This line does not influence the result, as you execute the update only for the rows fulfilling the condition
Thus, you assign such value to the id_crm field that is stored in it.
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;
Code: Select all
sql.Append("id_crm = :id_crm");
Code: Select all
"where id_crm = :id_crm".