SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
cannot insert multiple commands into prepared statement

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
cannot insert multiple commands into prepared statement
Author Message
michalk



Joined: 29 Aug 2014
Posts: 149

Post cannot insert multiple commands into prepared statement Reply with quote
I attempting to get used to SA Editor.

But I hit a wall with following issue:
1. I try to execute simple code:

Code:
BE GIN;
INS ERT INTO table...;
SEL ECT * FROM table...;
COMMIT;


resulting with: cannot insert multiple commands into prepared statement
error message.

I know it can be solved depending on the language/connector enabling special mode of the connection. Is it possible for SA Editor?

In addition I would like to ask how transaction work. Is there auto-commit if there is no explicit transaction started by the code? Could I affect commit mode?

PS. I put spaces into example to dodge stupid sql injection filter (probably in our network)

with regards
Tue Jan 23, 2018 8:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6573

Post Reply with quote
Please excuse my lack of knowledge, but what kind of language is that ?

Code:
BE GIN;
INS ERT INTO table...;
SEL ECT * FROM table...;
COMMIT;

Tue Jan 23, 2018 9:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6573

Post Reply with quote
Please disregard, my previous post. It occured to me what you meant by "inserted spaces"

Let me try your code sample first. I don't see anything special in it.
Tue Jan 23, 2018 9:55 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 149

Post Reply with quote
:)
I forgot to mention I experienced it being connected to postgresql (9.6) using libpg.

I mentioned I'm familiar with such error because I experienced it writing PHP application with use of PDO. And it's true, prepare statement works only for single SQL queries. This is why the error is rised. Point is, sending plain SQLs prepare statements are never used until executed explicitly by SQL syntax.
In case of my application I found a solution by enabling following option of the connector: PDO::ATTR_EMULATE_PREPARES.


Last edited by michalk on Tue Jan 23, 2018 10:12 am; edited 1 time in total
Tue Jan 23, 2018 10:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6573

Post Reply with quote
Got it. You have two options to get it work with SASE

Execute BEGIN; separately to start a transaction, after that run the rest of logical code block. Or you can use plpgsql DO anonymous blocks. E.g.

Code:
DO
$$
BEGIN
  INSERT INTO city VALUES (999, 'dummy', 1, current_timestamp);
  -- UPDATE ...
  -- SELECT ... INTO ...some var ...  FROM
  -- RAISE  NOTICE 'text of % vars', ...
  COMMIT;
END;
$$

SELECT * FROM city;
Code:

Tue Jan 23, 2018 10:11 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6573

Post Reply with quote
3rd option: don't use shortcut syntax BEGIN; use BEGIN TRANSACTION; as below

Code:
BEGIN TRANSACTION;
INSERT INTO city VALUES (998, 'dummy', 1, current_timestamp);
SELECT * FROM city;
COMMIT;

Tue Jan 23, 2018 10:14 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 149

Post Reply with quote
Yes I know both ways. But it doesn't match my needs.
We work with a bit bigger multi-line scripts, which are composed with anonymous blocks as well as simple queries like calling functions etc. And whole script has to be executed in single transaction of fail being automatically rolled back.
It is how it works using pgAdmin and other tools. Without it, SA Editor is no go to us.

I't would be very useful to have ability to run multi command SQLSs without need of dodging app limitations. In that particular case we don't expect result of all subsequent queries (As SAEditor does). The result of the last one, and total time spend on the script is obviously enough.

However I appreciate your fast response.

With regards
Tue Jan 23, 2018 10:24 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 149

Post Reply with quote
SysOp wrote:
3rd option: don't use shortcut syntax BEGIN; use BEGIN TRANSACTION; as below


Yeah, it does the trick.
Thank you very much.
Tue Jan 23, 2018 10:28 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.