PostgreSQL: result set R/W status depends on capitalization

[marsupilami]

Hello

I tried my first steps on PostgreSQL with Zeos 7.0.3-stable, Delphi XE2:

create table test (
id integer not null,
name varchar(50),
constraint pk_test primary key (id)
)

The command select * from test yields in a Dataset, that can be read and written to. Doing a select * from TEST (table name all capitals) yields in a Dataset where all fields are readonly. Does anybody know why this is the case? This behaviour is new for Zeos 7.0. With Zeos 6.6.6-stable this works as expected - I can modify both Datasets.
Best regards,

Jan

[EgonHugeist]

marsupilami,

Jan, PostgreSQL is case sensitive by default.

Use:

Code: Select all

create table "test" (
id integer not null,
name varchar(50),
constraint pk_test primary key (id)
) 

or

Code: Select all

select * from TEST

Am i wrong? Which changes do you plan? AFIAK did Mark commit a patch for some RDMS accordingly case sensitive behavior. Have a look to the SVN commits, Jan.

[marsupilami]

Hello EgonHugeist,

this is from the Postgres 9.2 manual:

Key words and unquoted identifiers are case insensitive. Therefore:
UPDATE MY_TABLE SET A = 5;
can equivalently be written as:
uPDaTE my_TabLE SeT a = 5;

also there is some more on that:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower
case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but
"Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to
lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names
should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the
standard. If you want to write portable applications you are advised to always quote a particular name
or never quote it.)

I don't know for older versions of PostgreSQL though. I did not take a look at the PostgreSQL driver yet as I have little time for that now

Best regards,

Jan

[marsupilami]

Hello Michael,

I did some digging around and it seems like the PostgreSQL driver is doing something odd, when it comes to setting the properties of the TZColumnInfos. It silently sets the source table of all fields in a result set to empty. But there seems to be a possibility call to get the table name.
And Zeos seems to do some "guessing" as to what the correct table name is, although I don't yet understand where it is doing this.
So - I could do an implementation for getting the table and schema name from PostgreSQL, if possible. But will the upper layers of Zeos honor this or will they silently ignore this?

[EgonHugeist]

marsupilami,

imagine we've two MetaData types: The DataBaseMetadata and the ResultSetMetadata. The ResultsetMetada contains minor data and mostly we're not able to determine if a field is writable or readonly. That's why the DatabaseMetadate has higher precedence.

The AbstractRODataSet descendants can use both but if we can determine the tables with the DatabaseMetadate, we overwrite the previously copied ResultsetMetadate informations on the TZColumnInfos.

Don't hesitate to make a patch. I can test it with our tests and see what happens, Jan.