cannot insert multiple commands into prepared statement

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

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;

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.

:)
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

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:

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;

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

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

Yeah, it does the trick.
Thank you very much.