Page 1 of 1
DBX driver for Postgres -> GetIndexNames
Posted: Tue 30 Jun 2009 22:59
by dany40
Hi;
I can't get index list for the database tables using TSQL.Connecton.GetIndexNames; it always returns an empty list. For example:
S := TStringList.Create;
Con.GetIndexNames('T1', SL);
I tested to get the list of table names with TSQL.Connecton.GetTableNames and it works fine.
Any help will be appreciated.
Regards,
Dany
Posted: Wed 01 Jul 2009 07:44
by Plash
We cannot reproduce the problem.
Please specify your Delphi version.
Posted: Wed 01 Jul 2009 10:42
by dany40
Thank you. I use Delphi 2007 - DBX4.
Can you give me an example that works?
Posted: Wed 01 Jul 2009 11:23
by dany40
Let me add, GetIndexNames is working fine if I run the same program using Devart's DBX driver for Firebird. But the I can't find how to make it working for Postgres.
My tablenames are all uppercase, maybe is something about that?
All the tables are in the schema called "public".
Posted: Wed 01 Jul 2009 12:37
by dany40
I continued more tests with DBX4/Delphi 2007 and Devarts driver for Firebird, and I found that GetIndexNames is not returning all the indexes for each table.
So, Firebird driver is returning "some" indexes, and Postgres driver is returning "no one" indexes.
Dany
Posted: Thu 02 Jul 2009 08:39
by Plash
Metadata for tables with uppercase names is not supported. TableName parameter is treated as case-insensitive. So the PostgreSQL driver always search for a table with lowercase name.
Posted: Thu 02 Jul 2009 12:43
by dany40
Devart's driver for Firebird and Postgress are not supporting uppercased objects?. What is the solution?, ... becasuse I can't change my customers databases.
Posted: Fri 03 Jul 2009 09:21
by Plash
GetIndexNames does not support lowercase table names for the Firebird driver and uppercase table names for the PostgreSQL driver.
The following query is used in PostgreSQL driver to get indexes. You can execute it yourself.
Code: Select all
SELECT
n.nspname AS TABLE_SCHEMA, t.relname AS TABLE_NAME,
n.nspname AS INDEX_SCHEMA, c.relname AS INDEX_NAME,
a.attname AS COLUMN_NAME, a.attnum AS COLUMN_POSITION,
i.indisunique AS UNIQUE
FROM pg_index i
INNER JOIN pg_class c ON c.oid = i.indexrelid
INNER JOIN pg_class t ON t.oid = i.indrelid
INNER JOIN pg_namespace n ON n.oid = t.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = i.indrelid and a.attnum = any (i.indkey)
WHERE
n.nspname = 'public' AND t.relname = 'T1'
ORDER BY n.nspname, t.relname, c.relname, a.attnum
Posted: Fri 03 Jul 2009 23:18
by dany40
OK, thank you, I will give it a try. BTW, on the Firebird database the tables are all uppercase, but GetIndexNames is not returning me all the indexnames; it is returning only some.
Posted: Mon 06 Jul 2009 13:06
by Plash
We could not reproduce the problem with the Firebird driver. Please send to dbx*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.