Unidac 8.4 PostgreSQL problem with RETURNING

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 PostgreSQL problem with RETURNING

Post by badmood » Wed 03 Mar 2021 17:31

Hi,

inserting a row with a returning clause in 8.4 gives an error while for 8.3 is ok.

Code: Select all

  with qryDummy do
  begin
      SQL.Text :=
        'INSERT INTO log_attivita (cd_tipo_log, cd_stato_attivita) ' +
        'VALUES (:cd_tipo_log, :cd_stato_attivita) ' +
        'RETURNING cd_log_attivita ;';
      ParamByName('cd_tipo_log').AsString := cdTipoLog;
      ParamByName('cd_stato_attivita').AsString := cdStatoAttivita;
      Execute;

      result := FieldByName('cd_log_attivita').AsInteger;
    end
Gives an error executing the query: "field_a NOT FOUND".

Every time I update the UniDac version it's a mess, I lose time and I must revert to previous one.


Sergio

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by oleg0k » Mon 05 Apr 2021 17:50

Hello,
Please accept our sincere apologies caused by the delay in response.
Unfortunately, we couldn't reproduce the issue.
Please send us an example that uses UniDAC and demonstrates the incorrect behavior, along with DDL scripts for creating and populating the database objects, using the form: https://devart.com/company/contactform.html
We've released a new version (8.4.2) of the UniDAC. Please try to install the new version and let us know if this helps.

wbr, Oleg
Devart Team

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

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by badmood » Tue 06 Apr 2021 08:42

It seems to be solved in 8.4.2. I'm checking it now.

Sergio

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

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by badmood » Tue 06 Apr 2021 16:50

No, it's not ok. Now I'm having other problems that were not present on 8.3 in other query execution.
Sorry but I revert again to old version.
And no, I'm not your beta tester, I have no time to install, uninstall, install again and create samples of code.
You broke, you repair.

FCS
Posts: 176
Joined: Sat 23 Feb 2013 18:46

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by FCS » Tue 06 Apr 2021 19:38

Hello,

Could you describe the problem after upgrading to 8.4.2 version ?

I use PostgreSQL too and I plan upgrade my old UniDac into newer version.
But after releasing the 8.4.1 version, the other users started report problems.

I often read this forum, waiting for stable version, confirmed by users which reported problems.

Regards
Michal

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

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by badmood » Wed 07 Apr 2021 08:08

Dear Michael,

as in my first post, if you create a table :

Code: Select all

CREATE TABLE log_attivita (
  cd_log_attivita SERIAL NOT NULL,
  cd_tipo_log VARCHAR(4) NOT NULL,
  cd_stato_attivita VARCHAR(4) NOT NULL,
  CONSTRAINT log_attivita_pk PRIMARY KEY (cd_log_attivita)
);
and you try to insert something like in my example, the RETURNING clause is not working, with the message "cd_log_attivita NOT FOUND" when you try to catch the result as in the line after the Execute

Code: Select all

  with qryDummy do
  begin
      SQL.Text :=
        'INSERT INTO log_attivita (cd_tipo_log, cd_stato_attivita) ' +
        'VALUES (:cd_tipo_log, :cd_stato_attivita) ' +
        'RETURNING cd_log_attivita ;';
      ParamByName('cd_tipo_log').AsString := '*LOG';
      ParamByName('cd_stato_attivita').AsString := '*ATT';
      Execute;

      result := FieldByName('cd_log_attivita').AsInteger; // this line!
    end
It was not so difficult to test by Devart programmers... Anyway, in 8.4.1 this is broken.
In 8.4.2 it works but now I have problems in some other kind of SQL statements. Something seems to be related to SQL composed by hand (but working on 8.3.2). Now I'm converting to SQL composed with ParamByName statements but for the moment I consider to wait until some new version is released because this is not the only problem I got with 8.4. I'm investigating on this, but I've reverted to 8.3.2 because I must make my customers do their job without problems and I can't install/recompile/test and uninstall/install/recompile every time.

Regards.

Sergio

FCS
Posts: 176
Joined: Sat 23 Feb 2013 18:46

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by FCS » Wed 07 Apr 2021 09:06

Hello,

Did you try use the Prepare method before Execute ?
There is the AutoPrepare property too.

As I remember in documentation (v 6.1) is set, that Execute method may not give results.

"Call the Execute method to execute a SQL statement on the server. I f SQL statement is a query, Execute calls the
Open method. Execute implicitly prepares SQL statement by calling the TCustomDADataSet.Prepare method i f the
TCustomDA DataSet.Options option is set to True and the statement has not been prepared yet. To speed up the
performance in case of multiple Execute cal ls, an application should call Prepare before calling the Execute method
for thefirst time."


But it does not explain the difference of behaviour between 8.3.2 and 8.4.x.

Regards
Michal

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

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by badmood » Thu 08 Apr 2021 08:26

This problem from 8.4.2 is resolved, so I'm reviewing my SQL for other possible problems.

Regards.


Sergio

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Unidac 8.4 PostgreSQL problem with RETURNING

Post by oleg0k » Fri 09 Apr 2021 09:31

Hello,
Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

wbr, Oleg
Devart Team

Post Reply