Poco MySQL - Unable to use "SHOW TABLES" etc.

[buzzdx]

hi folks,

i'm building a small database maintenance program which lets the user execute commands on a database ( sqlite or mysql right now )
and also show the database tables and table structures.

i'm using poco 1.4.3.

when i try to execute a statement like "SHOW TABLES" or "DESCRIBE [tblname]" it executes without any exceptions but i get zero
rows of data back all the time.

it has nothing to do with missing priviliges, i checked that.

for testing purposes i accessed the database directly using mysql_real_connect() and so on , and i actually got the data back as
expected.

now i could use raw access within my application to get the desired data, but i wonder why these statements do not work using
the poco interface.

(note: getting normal data from tables within my database works fine (i.e select * from xyz), so the problem has to be somewhere else)

thanks in advance,
buzzdx

[buzzdx]

too bad no one's got a hint for me.

yet i found another problem, this time for sqlite as well as mysql.

when doing a SELECT i get back the number of rows returned as expected.

when doing something like INSERT or DELETE i always get back 0 (zero).
i tried it many times and also assured there were rows of data actually inserted or deleted in to the table.


maybe one can give me a clue or something?

[rakesh]

I believe statements like show tables, describe table etc are not part of SQL standard and are implemented in ways that are internal to the particular database. I have not used Poco::Data, but have seen this issue with JDBC. If you wish to query the tables, columns etc. the portable technique (as long as you are not using Oracle) is to use the information_schema.

[buzzdx]

thanks for your answer.

i think you are right about show tables etc., it's not standard sql and handled by mysql internally in
a way that gives out the requested information.

but the result set it returns is just a normal data table, so it shouldn't be a problem to just let this data
run through? maybe i'm missing something?

poco seems to acknowledge that sending such a statment is no error, hence theres no exception or whatever.


i took a look at the information_schema you mentioned.

in fact i think this is the source where SHOW TABLES gets it data from internally, but unfortunately not every user
can access this table. i tried it with some databases, including a web database of mine, and i couldn't get access to it.


so for now i have to stick with this:

when needing table or field information i connect through native means and send SHOW TABLES or DESCRIBE xyz
statements, save the information i need and close native connection.

i'm currently working on an additional odbc connection system for my application, which will use the same "native" approach.

any other queries use poco interface and result sets.