Composite Type + UnpreparedExecute => Exceptions and AV's

[dschuch]

Hy,

we have some trouble with Composite Types in unprepared Mode. We just acutalized to the current version.

In Delphi => PgQuery => set Propertie "Options=>UnpreparedExecute" to true.
We do this because we often use temporary querys, that are created before and destroyed after execution.

Set of NonStandard Properties: FullRefresh=True, OIDasInt=True, RemoveOnRefresh=False, SetFieldsReadOnly=False, StrictUpdate=False, UnknownAsString=True, UnpreparedExecute=True

Connect to a DataBase and a DBGrid.

TestCases:

A Error with Varchar

Code: Select all

DROP TABLE IF EXISTS test;
DROP TYPE IF EXISTS testtype;

CREATE TYPE testtype AS (a VARCHAR, b INTEGER);
CREATE TABLE test (c TESTTYPE);

"SELECT * FROM test" => Working fine. Change Column "c.b" is Working "c.a" is Failing.

"Cannot Convert Type" in "PgObjects.pas"


B Error with Date

Code: Select all

DROP TABLE IF EXISTS test;
DROP TYPE IF EXISTS testtype;

CREATE TYPE testtype AS (a DATE, b DATE);
CREATE TABLE test (c TESTTYPE);

SELECT is working as long as there is no Record. Insert and Update are Working.
Select and Refresh (or Reopen) if there are Records failing => AV "Variant of Type Unicode could not convert to Double"

[DemetrionQ]

Hello.

Thank you for the information. We have reproduced the problem and are investigating it. We will inform you as soon as we have any results.

[lgerlach]

Hello.

I'm working in the same firm as dschuch and wanted to ask, if there's any news on this topic. We plan to move to PostgreSQL 9.3 as soon as possible and want to re-investigate some of our infrastructure, which possibly includes the use of Composite Types. So ... we really need that stuff.

Thanks,
Lutz

[AlexP]

Hello,

We have fixed the problem, but when working with such types, you should explicitly specify the key field with a type different from Composite Type, since when generating an Update query, Composite Type fields are not supported in the WHERE clause

[dschuch]

Hi,

nearly working: the insert case is fine now, please try to update a varcharcolumn.

"input of anonymous composite types is not implemented." sometimes "cannot convert type"
(seems it depends if you try to insert a Int to the varcharcolumn or a varchar)

DROP TABLE IF EXISTS test;
DROP TYPE IF EXISTS testtype;

CREATE TYPE testtype AS (a VARCHAR, b INTEGER);
CREATE TABLE test (id SERIAL PRIMARY KEY, c TESTTYPE);

=> set KeyField

Insert => Working fine => try to Update Column C.A (i tried to switch from "A" to "B") => Error from above.

BTW i accidently switched the property PgQuery.ObjectView (never used) to True => Another Exception. But this does not matter to us.

[AlexP]

Hello,

We cannot reproduce the problem - the field is edited with no errors. Please provide the code abstract that leads to the specified problem.