Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: SQL query with several commands



Permlink Replies: 7 - Last Post: Jul 6, 2017 10:55 PM Last Post By: Dmitry Vavilov
Dmitry Vavilov

Posts: 21
Registered: 2/22/15
SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 3, 2017 3:46 AM
Hi!
This query does not work for PostgreSQL:
insert into SpPol(ID, Name, FullName)
  select MAX(ID), 'C', 'C';
select * from SpPol;


Error:
[FireDAC][Phys][PG][libpq] ОШИБКА: в подготовленный оператор нельзя вставить несколько команд


The same query works on MSSQL without errors.

Edited by: Dmitry Vavilov on Jul 6, 2017 10:25 PM
Francesco Marano

Posts: 59
Registered: 11/18/02
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 3, 2017 6:16 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
Dmitry Vavilov

Posts: 21
Registered: 2/22/15
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 3, 2017 9:40 PM   in response to: Francesco Marano in response to: Francesco Marano
The problem is solved with DirectExecute = True.
Thank you.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 3, 2017 11:50 PM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
The problem is solved with DirectExecute = True.

DirectExecute = True will not work with command returning a result set.
It is a bug / limitation in FireDAC.

--
With best regards,
Dmitry
Dmitry Vavilov

Posts: 21
Registered: 2/22/15
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 4, 2017 12:25 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
This query is opened (FDQuery+PostgreSQL):
insert into SpPol(ID, Name, FullName)
  select MAX(ID)+1, 'C', 'C' from SpPol;
select Name, ID, FullName from SpPol;

But the ID field values are incorrect. Field type is smallint. Value "23" returned as "12851", Value "24" returned as "12852" and so on.
I really should not use DirectExecute if the query returns a result set of data?

Edited by: Dmitry Vavilov on Jul 4, 2017 12:26 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 4, 2017 12:34 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
I really should not use DirectExecute if the query returns a result set of data?

No.

--
With best regards,
Dmitry
Dmitry Vavilov

Posts: 21
Registered: 2/22/15
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 4, 2017 2:54 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Thank you
Dmitry Vavilov

Posts: 21
Registered: 2/22/15
Re: SQL query with several commands
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 6, 2017 10:30 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
How then to execute query?
insert into SpPol(ID, Name, FullName)
  select MAX(ID)+1, 'C', 'C' from SpPol;
select Name, ID, FullName from SpPol;


Examples by URL
http://docs.embarcadero.com/products/rad_studio/firedac/frames.html?frmname=topic&frmfile=Command_Batches.html
do not work

Edited by: Dmitry Vavilov on Jul 6, 2017 10:54 PM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02