How to enumerate tables based on a selected database

[avatar]

Hi,

I'm starting a new project that provides a way for the user to customize which databases or table to work on...IIRC i should be able to enumerate the databases via the GetCatalogNames method of TZConnection and GetTablesNames method for the tables. However it seems that only GetCatalogNames worked.

ZConn.GetTableNames('',lstTables) gives me an exception.

Code: Select all

SQL Error: You have an error in your SQL syntax;
Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE'%" at line1'.

changing the pattern parameter to 'a*' also gives the same exception.
Is this a bug? any workarounds?

[avatar]

still can't make GetTablesNames to work right but in case anyone has the same problem as mine, I made a workaround by running the following query

SELECT table_name FROM information_schema.tables
WHERE table_schema='<database taken from GetCatalogNames() result>'

not a portable code i'm afraid but works for mysql.

[mdaems]

Hi,

Can you show what statement is sent to the server exactly that's returning this error by using a TZSQLMonitor?
If you want tot debug : set a debug point on TZMySQLDatabaseMetadata.UncachedGetTables to see what happens exactly.

Mark

[avatar]

@mdaems: thanks for the tip that would really be useful for my next debugging session. Anyway it seems I have to add an additional parameter to the GetTableNames method...

Code: Select all

ZConn.GetTablesNames('',lstTables) 

should be

Code: Select all

ZConn.GetTablesNames(ZConn.GetTableNames('%',ZConn.Database,lsTables);

I assumed that since I already assigned a value to ZConn.Database it would have just read the value from the there...could be a bug?

[mdaems]

So with this parameter it works like expected? Shouldn't be necesssary, however. Did you try to debug the case using TZSQLMonitor or debug points as indicated?
(Sorry, altough this is mysql I don't have much time last months to test it myself)

Mark