Unidac 8.4.4 problem in query parameters

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
badmood
Posts: 37
Joined: Thu 29 Jan 2009 23:28
Location: Italy

Unidac 8.4.4 problem in query parameters

Post by badmood » Thu 29 Jul 2021 07:59

Dear all,

here we go with another problem in 8.4 series with the same code working correctly on 8.3.2:

Code: Select all

CREATE TABLE public.motrici_assicurazioni (
  mas_targa char(10) NOT NULL,
  mas_anno_competenza integer NOT NULL DEFAULT 0,
  mas_compagnia integer NOT NULL,
  mas_nr_documento varchar(80) NOT NULL,
  mas_data_inizio date,
  mas_data_scadenza date,
  mas_importo_01 numeric(12,2) DEFAULT 0,
  mas_importo_02 numeric(12,2) DEFAULT 0,
  mas_importo_03 numeric(12,2) DEFAULT 0,
  mas_importo_04 numeric(12,2) DEFAULT 0,
  mas_importo_05 numeric(12,2) DEFAULT 0,
  mas_importo_06 numeric(12,2) DEFAULT 0,
  mas_importo_07 numeric(12,2) DEFAULT 0,
  mas_importo_08 numeric(12,2) DEFAULT 0,
  mas_importo_09 numeric(12,2) DEFAULT 0,
  mas_importo_10 numeric(12,2) DEFAULT 0,
  mas_importo_11 numeric(12,2) DEFAULT 0,
  mas_importo_12 numeric(12,2) DEFAULT 0,
  mas_tipo_assicurazione char(4),
  mas_note text,
  CONSTRAINT pk_motrici_assicurazioni PRIMARY KEY (mas_targa, mas_anno_competenza, mas_compagnia, mas_nr_documento)
)
;

Code: Select all

    qryDummy.SQL.Text :=
      'INSERT INTO motrici_assicurazioni ( ' +
      '  mas_targa ' +
      ', mas_anno_competenza ' +
      ', mas_compagnia ' +
      ', mas_nr_documento ' +
      ', mas_data_inizio ' +
      ', mas_data_scadenza ' +
      ', mas_importo_01 ' +
      ', mas_importo_02 ' +
      ', mas_importo_03 ' +
      ', mas_importo_04 ' +
      ', mas_importo_05 ' +
      ', mas_importo_06 ' +
      ', mas_importo_07 ' +
      ', mas_importo_08 ' +
      ', mas_importo_09 ' +
      ', mas_importo_10 ' +
      ', mas_importo_11 ' +
      ', mas_importo_12 ' +
      ', mas_tipo_assicurazione ' +
      ', mas_note ' +
      ') VALUES ( ' +
      '  :targa ' +
      ', :anno_competenza ' +
      ', :fornitore ' +
      ', :nr_documento ' +
      ', :data_inizio ' +
      ', :data_scadenza ' +
      ', :importo_01 ' +
      ', :importo_02 ' +
      ', :importo_03 ' +
      ', :importo_04 ' +
      ', :importo_05 ' +
      ', :importo_06 ' +
      ', :importo_07 ' +
      ', :importo_08 ' +
      ', :importo_09 ' +
      ', :importo_10 ' +
      ', :importo_11 ' +
      ', :importo_12 ' +
      ', :tipo_assicurazione ' +
      ', :note ' +
      ') ' +
      'ON CONFLICT (mas_targa, mas_anno_competenza, mas_compagnia, mas_nr_documento) ' +
      'DO UPDATE SET ' +
      '  mas_importo_01 = EXCLUDED.mas_importo_01 ' +
      ', mas_importo_02 = EXCLUDED.mas_importo_02 ' +
      ', mas_importo_03 = EXCLUDED.mas_importo_03 ' +
      ', mas_importo_04 = EXCLUDED.mas_importo_04 ' +
      ', mas_importo_05 = EXCLUDED.mas_importo_05 ' +
      ', mas_importo_06 = EXCLUDED.mas_importo_06 ' +
      ', mas_importo_07 = EXCLUDED.mas_importo_07 ' +
      ', mas_importo_08 = EXCLUDED.mas_importo_08 ' +
      ', mas_importo_09 = EXCLUDED.mas_importo_09 ' +
      ', mas_importo_10 = EXCLUDED.mas_importo_10 ' +
      ', mas_importo_11 = EXCLUDED.mas_importo_11 ' +
      ', mas_importo_12 = EXCLUDED.mas_importo_12 ' +
      '; ';
      
    qryDummy.ParamByName('targa').AsString := targa;
    qryDummy.ParamByName('anno_competenza').AsInteger := annoCompetenza + 1;
    qryDummy.ParamByName('fornitore').AsInteger := cdFornitore;
    qryDummy.ParamByName('nr_documento').AsString := nrDocumento;
    qryDummy.ParamByName('data_inizio').AsDate := dataInizio;
    qryDummy.ParamByName('data_scadenza').AsDate := dataFine;
    qryDummy.ParamByName('importo_01').AsCurrency := importo[01];
    qryDummy.ParamByName('importo_02').AsCurrency := importo[02];
    qryDummy.ParamByName('importo_03').AsCurrency := importo[03];
    qryDummy.ParamByName('importo_04').AsCurrency := importo[04];
    qryDummy.ParamByName('importo_05').AsCurrency := importo[05];
    qryDummy.ParamByName('importo_06').AsCurrency := importo[06];
    qryDummy.ParamByName('importo_07').AsCurrency := importo[07];
    qryDummy.ParamByName('importo_08').AsCurrency := importo[08];
    qryDummy.ParamByName('importo_09').AsCurrency := importo[09];
    qryDummy.ParamByName('importo_10').AsCurrency := importo[10];
    qryDummy.ParamByName('importo_11').AsCurrency := importo[11];
    qryDummy.ParamByName('importo_12').AsCurrency := importo[12];
    qryDummy.ParamByName('tipo_assicurazione').AsString := cdTipoAssicurazione;
    qryDummy.ParamByName('note').AsWideString := nota;
      
All the values I put into params are ok (dates are TDate, numeric fields are currency and integers are integer), the same code with same values works on 8.3.2

When you execute the insert query you have an invalid input syntax for type numeric "" error message, but DBMonitor shows me that I load the correct param values.

Can you check it please? I really need to start working on PostgreSQL 13.

My system configuration:
Delphi 10.4.2
PostgreSQL 12.7
UniDAC 8.4.4


Regards.

Sergio

evgeniym
Devart Team
Posts: 103
Joined: Thu 13 May 2021 07:08

Re: Unidac 8.4.4 problem in query parameters

Post by evgeniym » Thu 29 Jul 2021 10:01

Hi Sergio,
Please be informed that unfortunately we could not reproduce described behavior, everything works correctly on our environment.
In order to investigate the issue please send us test sample that reproduces described error.
You can send these files to us using contact form on our website:
https://www.devart.com/company/contactform.html

Best Regards,
Evgeniy

badmood
Posts: 37
Joined: Thu 29 Jan 2009 23:28
Location: Italy

Re: Unidac 8.4.4 problem in query parameters

Post by badmood » Thu 29 Jul 2021 16:11

Hi,

I've sent the demo code.

So to speak... yesterday I've lost half day trying to understand the cause of the error, reviewing the code, SQL, DBMonitor, inserting by hand the same values, etc. and then, when I revert to old 8.3.2 version of UniDAC and all worked as expected, I was not happy at all with your product... and this is not the first time I must revert to 8.3.2 to get the job done. It seems to me that 8.4 is a very, very, very bugged version, at least for Postgres data access. Add the time spent today to make a clean working copy of the program and I feel myself as a beta tester more than a licensed user!

Regards.

Sergio

evgeniym
Devart Team
Posts: 103
Joined: Thu 13 May 2021 07:08

Re: Unidac 8.4.4 problem in query parameters

Post by evgeniym » Fri 30 Jul 2021 07:56

Hi there,

We were able to reproduce the issue and fixed it. This fix will be added to the next build of our product and will be available for all clients.
Kindly note that as temp solution, until new build is not released yet, we can provide you with a night build which includes this solution.

For this please send us your license number and IDE version you are interested in using our contact form below:
https://www.devart.com/company/contactform.html

Regards,
Evgeniy

Post Reply