PostgreSQL : Inconsistent type inferred for the parameter $1

[swierzbicki]

Hello,

A simple update raise this error : Inconsistent type inferred for the parameter $1

I basically do this thing :

I have a Datamodule with my uniconnection component created at design time.
I create an object within the Datamodule Oncreate event and sets it's TUniConnection property. This object does have several private method that update SQL Data.

Code: Select all

//Datamodule On create
MyObject:= TMyObject.Create;
MyObject:= MyObject.Connection := Datamodule.UniConnection1;

//MyObject
TMyObject= class(TThread)
  private
  FConnection: Tuniconnection;
  ...
  public
  property Connection: Tuniconnection read FConnection write FConnection;
  ...
End;

procedure TMyObject.Sauver_PosteEnCours(const Value: Poste);
var
  FUniQuery: TuniQuery;
begin
  FUniQuery := TuniQuery.Create(nil);
  FUniQuery.Connection := FConnection;
  try

    FUniQuery.SQL.Text := Poste_Update;
    FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
    FUniQuery.ParamByName('Id_Poste').AsInteger := Value.Id_Poste;
    FUniQuery.ParamByName('Date_Poste').AsDate := Value.Date_Poste;

    if Value.Date_Debut_Poste = 0 then
      FUniQuery.ParamByName('Date_Debut_Poste').clear
    else
      FUniQuery.ParamByName('Date_Debut_Poste').AsDateTime :=
        Value.Date_Debut_Poste;
    FUniQuery.ParamByName('Id_Pilote_1').AsString := Value.Id_Pilote_1;
    FUniQuery.ParamByName('Id_Pilote_2').AsString := Value.Id_Pilote_2;
    FUniQuery.ParamByName('Ordre_Fabrication_En_Preparation').AsString :=
      Value.Ordre_Fabrication_En_Preparation;
    FUniQuery.ParamByName('Ordre_Fabrication_En_Cours').AsString :=
      Value.Ordre_Fabrication_En_Cours;
    FUniQuery.Prepare; //<------ error occurs here
    FUniQuery.Execute;

    if FUniQuery.RowsAffected = 0 then
    begin
      FUniQuery.SQL.Text := Poste_Insert;
      FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
      FUniQuery.ParamByName('Id_Poste').AsInteger := Value.Id_Poste;
      FUniQuery.ParamByName('Date_Poste').AsDate := Value.Date_Poste;
      FUniQuery.ParamByName('Id_Pilote_1').AsString := Value.Id_Pilote_1;
      FUniQuery.ParamByName('Id_Pilote_2').AsString := Value.Id_Pilote_2;
      FUniQuery.ParamByName('Ordre_Fabrication_En_Preparation').AsString :=
        Value.Ordre_Fabrication_En_Preparation;
      FUniQuery.ParamByName('Ordre_Fabrication_En_Cours').AsString :=
        Value.Ordre_Fabrication_En_Cours;
      if Value.Date_Debut_Poste = 0 then
        FUniQuery.ParamByName('Date_Debut_Poste').clear
      else
        FUniQuery.ParamByName('Date_Debut_Poste').AsDateTime :=
          Value.Date_Debut_Poste;

      FUniQuery.Prepare;
      FUniQuery.Execute;
    end;

  finally
    FUniQuery.Free;
  end;
end;

Prepare statement or/and execute statement raise an EpgError message : Inconsistent type inferred for the parameter $1

SQL Statement used is really simple :

Code: Select all

  
Poste_Update = 
'Update Productions_Postes set ' +
'Ligne=:Ligne,'+
'date_poste=:date_poste,'+
'date_debut_poste=:date_debut_poste,'+
'Id_Poste=:Id_Poste,'+
'Id_Pilote_1=:Id_Pilote_1,'+
'Id_Pilote_2=:Id_Pilote_2,'+
'Ordre_Fabrication_En_Preparation=:Ordre_Fabrication_En_Preparation,'+ 'Ordre_Fabrication_En_Cours=:Ordre_Fabrication_En_Cours'+
'where Ligne=:Ligne;';

SQL Table :

Code: Select all

CREATE TABLE public.productions_postes (
  ligne VARCHAR(10) DEFAULT ''::character varying NOT NULL,
  date_poste DATE,
  date_debut_poste TIMESTAMP WITHOUT TIME ZONE,
  id_poste INTEGER,
  id_pilote_1 VARCHAR(2) DEFAULT ''::character varying,
  id_pilote_2 VARCHAR(2) DEFAULT ''::character varying,
  ordre_fabrication_en_preparation VARCHAR(6) DEFAULT ''::character varying,
  ordre_fabrication_en_cours VARCHAR(6) DEFAULT ''::character varying,
  CONSTRAINT productions_postes_pkey PRIMARY KEY(ligne)
) 
WITH (oids = false);

CREATE INDEX productions_postes_ligne ON public.productions_postes
  USING btree (ligne COLLATE pg_catalog."default");

This worked well when my provider was SQLite, but doesn't anymore with PostgreSQL provider.

[swierzbicki]

Some additional information :

My application needs to works under SQLite and PostgreSQL databases. I did have a lot a database lock errors messages while using SQLite when 2 Uniconnections where used (1 on my Datamodule and one on my object). Using 1 uniconnection and sharing it with my object solved the problem under SQLite...

[swierzbicki]

Hello,

This is a real show stopper. Can I get any reply from Devart on this ?

Thank you

[azyk]

We have reproduced this behavior and are investigating it. We will notify you about the results as any are available.

[azyk]

In the sample you have provided, this error is generated by PostgreSQL server, since it can't define parameter types in the query. To avoid this error, set the UseParamTypes property of dataset to True before calling the Prepare method. For example:

Code: Select all

      ...
      if Value.Date_Debut_Poste = 0 then
        FUniQuery.ParamByName('Date_Debut_Poste').clear
      else
        FUniQuery.ParamByName('Date_Debut_Poste').AsDateTime :=
          Value.Date_Debut_Poste;

      FUniQuery.SpecificOptions.Values['UseParamTypes'] := 'True'; // insert code here
      FUniQuery.Prepare;
      FUniQuery.Execute;
      ...

See more details about the UseParamTypes property in our on-line documentation: http://www.devart.com/pgdac/docs/index. ... mtypes.htm

[swierzbicki]

Thank you for your answer.

I need to declare all data type manually (ie parambyname('xxx').datatype := ftdatetime) when using "FUniQuery.SpecificOptions.Values['UseParamTypes'] := 'True';". Is this expected ?


I'm also getting "Invalid parameter specified" then an Assertion failure when executing an SQL query :

Exception 'first chance' at $753D812F. Exception class EAssertionFailed with a message 'Assertion failure (D:\Projects\Delphi\Dac\PostgreSql\Source\PgSQLProtocol.pas, line 2321)'. Process Gestion_MultiCN.exe (5972)

[azyk]

I need to declare all data type manually (ie parambyname('xxx').datatype := ftdatetime) when using "FUniQuery.SpecificOptions.Values['UseParamTypes'] := 'True';". Is this expected ?

When the UseParamTypes property is set to True, if you assign a type value to a parameter using AsDateTime, AsDate, AsString, AsInteger, etc. properties, then you don't need to set any additional value to the DataType parameter. E.g., for such a case:

Code: Select all

FUniQuery.ParamByName('Date_Debut_Poste').AsDateTime := Value.Date_Debut_Poste;

And for cases, when the parameter type is not set in any way, you should set it explicitly. E.g., for such a case:

Code: Select all

if Value.Date_Debut_Poste = 0 then
  FUniQuery.ParamByName('Date_Debut_Poste').clear

parameter type must be set:

Code: Select all

if Value.Date_Debut_Poste = 0 then begin
  FUniQuery.ParamByName('Date_Debut_Poste').DataType := ftDateTime;
  FUniQuery.ParamByName('Date_Debut_Poste').clear

I'm also getting "Invalid parameter specified" then an Assertion failure when executing an SQL query :

Exception 'first chance' at $753D812F. Exception class EAssertionFailed with a message 'Assertion failure (D:\Projects\Delphi\Dac\PostgreSql\Source\PgSQLProtocol.pas, line 2321)'. Process Gestion_MultiCN.exe (5972)

We can't reproduce the error 'Invalid parameter specified' on the provided sample. Please provide the code, that reproduces the error message.