error when executing a query with more than one command

[snorkel]

Hi,
I have this query:

ALTER TABLE "public"."Россия" ADD COLUMN "Россия"
integer;
ALTER TABLE "public"."Россия" ALTER COLUMN "Россия" SET NOT NULL;
ALTER TABLE "public"."Россия" ADD CONSTRAINT Россия_pkey PRIMARY KEY ("Россия")
;

and I have autoprepare set to false and when I run it I get the following error:

First chance exception at $7D4E2366. Exception class EPgError with message 'cannot insert multiple commands into a prepared statement'. Process xxxxxxxx.exe (2832)

It's really important to be able to run queries like this. I also tried setting prepare:=false before it get's executed, but same deal.

oh, I should mention this is with the tpgquery component and doing the same thing works in Mydac, so I am assuming this is a bug.

Thanks,

Snorkel

[snorkel]

FYI,
I don't think this error occured in the earlier builds.

[snorkel]

I should add that getting resutls from a function that returns a refcursor can only be done by sending two commands as a single query:

for example using Zeos (yuck

I did this:

contactdetail_q.SQL.Add('select admin.SPADM_GET_CONTACT_DETAIL(:param1,:param2);');
contactdetail_q.SQL.Add('fetch all from return_cursor;');
contactdetail.execute;

This now fails and I am dead in the water in my porting process.

[Plash]

To execute several statements at once you can use the TPgScript component.

To fetch data from a cursor, you can use the following code:

Code: Select all

PgConnection.StartTransaction;
PgQuery1.SQL.Text := 'SELECT admin.SPADM_GET_CONTACT_DETAIL(:param1,:param2)';
PgQuery1.Open;
PgQuery2.Cursor := TPgCursorField(PgQuery1.Fields[0]).AsCursor;
PgQuery2.Open;

[snorkel]

To execute several statements at once you can use the TPgScript component.

To fetch data from a cursor, you can use the following code:

Code: Select all

PgConnection.StartTransaction;
PgQuery1.SQL.Text := 'SELECT admin.SPADM_GET_CONTACT_DETAIL(:param1,:param2)';
PgQuery1.Open;
PgQuery2.Cursor := TPgCursorField(PgQuery1.Fields[0]).AsCursor;
PgQuery2.Open;

That doesn't help for the the hundreds of areas in my code where I do multiple commands in a single query, this really should be doable, it works in MyDAC, and it works in competing products like Zeos and PostgresDAC. The program I am porting also has a MySQL version(which uses MyDAC since 2006) and I do multiple commands the same way and DO NOT use the script component.

The tpgquery component should support this, and until it does I won't be able to use the product I payed for.

I still maintain this is a bug not a feature.

[snorkel]

I should add, I have been working with PostgreSQL for a long time and have used ALL the other Delphi solutions and every single one of them allows the query component to send two or more commands seperated by a semi colon to the server.

The script component should be use for executing scripts, and should not be needed for executing a couple of arbitray commands.

For example if my program needs to create a database I need to do something like this in a single execution of the query component.

myquery.sql.add('create database "MyTest";');
myquery.sql.add('ALTER TABLE "MyTest" OWNER TO "Myuser";');

So now you are telling me I have to rip/change every instance of tpgquery in my application and replace every occurance with the script component? I have not updated my Mydac compoents in a couple of months, do I have to do this in for the Mydac version of my app as well?


It seems to me this is a bug somehow related to the auto prepare, if I want to prepare the statements I should be able to decide that, and as of right now it seems to be auto preparing everything, even if the property is set to false.

Please advise.

Thanks,

Snorkel

[snorkel]

Apparently if the query is not to be prepared it should be using a
Simple Query http://www.postgresql.org/docs/8.3/stat ... l#AEN73664 which allows multiple commands, and if the query is supposed to be prepared it should be using the extended query.

That seems to be what the issue is here. Maybe add a boolean property called simple query and if true it uses a simple query protocol command instead of the extended.

[snorkel]

I also tried the tpgsql component which is supposed to execute queries that do not return a result set, but this also has the same problem.

[snorkel]

From the docs:

Note: The simple Query message is approximately equivalent to the series Parse, Bind, portal Describe, Execute, Close, Sync, using the unnamed prepared statement and portal objects and no parameters. One difference is that it will accept multiple SQL statements in the query string, automatically performing the bind/describe/execute sequence for each one in succession. Another difference is that it will not return ParseComplete, BindComplete, CloseComplete, or NoData messages.

[snorkel]

It looks like I could get the areas in my code to work with the pgscript component, but there are issues, mainly the script component does not support params directly, only the underlying dataset supports them and the script component passes each command to the underlying dataset.
Prior I could easily do things like this:

thequery.sql.clear;
thequery.sql.add('select pg_file_write(''pg_hba.conf.tmp'',:newtext, false);');
thequery.sql.add('select pg_file_unlink(''pg_hba.conf.bak'');');
thequery.sql.add('select pg_file_rename(''pg_hba.conf.tmp'', ''pg_hba.conf'', ''pg_hba.conf.bak'');');
thequery.parambyname('newtext').AsString := preview_hba.Lines.text;
thequery.execute;

For this I would never need it prepared and a simple query that supports the multiple commands would be fine.

Now I have to use two components and execute the line with the param seperately. I guess I could use the format command and just not use params in a case like this....

[snorkel]

I was able to make the changes to my program so all the spots that did multiple commands now use the pgscript component combined with the dataset (tpgquery) it originally used.
Though you should really look at allowing the regular query component
to be able to do that usng the simpe query option of the 3.0 protocol, it would make moving from Zeos and others much much easier.
It was kind of a pain when I was doing the multiple commands and params.

[Challenger]

Thank you for such a detailed description. We have this option internally(you can find it in the PgClasses unit, the TPgSqlCommand class), but now we don't allow users to change it. The reason is that we have encountered some problems with error processing when executing queries using "simple query" protocol. We will investigate this issue and consider adding a public option once more..

[snorkel]

Thank you for such a detailed description. We have this option internally(you can find it in the PgClasses unit, the TPgSqlCommand class), but now we don't allow users to change it. The reason is that we have encountered some problems with error processing when executing queries using "simple query" protocol. We will investigate this issue and consider adding a public option once more..

No problem, glad to help out in anyway.

All the other solutions use libpq.dll and that is the default behavior for it. For sure the way your guys are doing it via the direct protocol is superior, but I think it would be wise to have the ability to use the simpe query option of the protocol so the tpgquery can do multiple commands in a singe query, it makes things so much easier, not to mention ease of migration from Zeos etc. I am pretty sure that there are many others that do multiple commands in a single query.

[Plash]

In the next build we'll add the UnpreparedExecute boolean option to TPgQuery. If you set this option to True, the query is executed without preparing.

[snorkel]

In the next build we'll add the UnpreparedExecute boolean option to TPgQuery. If you set this option to True, the query is executed without preparing.

Thanks

The best Delphi solution for PostgreSQL keeps getting better