DBX driver for Postgres -> GetIndexNames

DBX driver for Postgres -> GetIndexNames

Postby dany40 » Tue 30 Jun 2009 22:59

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
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby Plash » Wed 01 Jul 2009 07:44

We cannot reproduce the problem.
Please specify your Delphi version.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby dany40 » Wed 01 Jul 2009 10:42

Thank you. I use Delphi 2007 - DBX4.
Can you give me an example that works?
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby dany40 » Wed 01 Jul 2009 11:23

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".
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby dany40 » Wed 01 Jul 2009 12:37

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
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby Plash » Thu 02 Jul 2009 08:39

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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby dany40 » Thu 02 Jul 2009 12:43

Devart's driver for Firebird and Postgress are not supporting uppercased objects?. What is the solution?, ... becasuse I can't change my customers databases.
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby Plash » Fri 03 Jul 2009 09:21

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
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby dany40 » Fri 03 Jul 2009 23:18

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.
dany40
 
Posts: 17
Joined: Fri 04 Jul 2008 12:42

Postby Plash » Mon 06 Jul 2009 13:06

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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09


Return to dbExpress driver for InterBase & Firebird