Feature request: odbc query should return table names

[Diogo Pereira]

Please make odbc query return a list of table names. I have used a hack suggested by Joseph Coveney to do this (see below), but I wish there was an easier and cleaner way of iterating through the tables in a database.

* Log the output of odbc query to a temporary file
tempfile tmpfil0
quietly log using `tmpfil0', text
odbc query "MS Access Database;DBQ=Northwind.mdb"
quietly log close

* Extract the table names from the log
infix str244 a 1-244 using `tmpfil0', clear
generate byte keep = sum(strpos(a, "---") == 1)
keep if keep == 1
drop in 1

* Create the list of tables
levelsof a, local(tables)

[Dimitriy V. Masterov]

Another option is to load a list of tables into Stata from the DB. The details of the SQL statement will depend on the type of DB, but in Teradata it looks something like this:

odbc load, exec("SELECT tablename, databasename FROM dbc.tables;") dsn("mydsn") clear

In MS Access, that might be something like:

odbc load, exec("SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0;") dsn("mydsn") clear

[Joseph Coveney]

Make sure that you can see the metadata tables. In Microsoft Access, I believe it's "hidden" (inaccessible) by default. You can change it to "visible" (or whatever the term is) in the application's settings somewhere.

.ÿlocalÿdsnÿMSÿAccessÿDatabase;DBQ=Data.accdb;DefaultDir=F:\;

.ÿodbcÿqueryÿ"`dsn'"

DataSource:ÿMSÿAccessÿDatabase;DBQ=Data.accdb;DefaultDir=F:\;
Pathÿÿÿÿÿÿ:ÿF:\Data.accdb
-------------------------------------------------------------------------------
Auto
Pig
-------------------------------------------------------------------------------

.ÿodbcÿload,ÿexec("SELECTÿ*ÿFROMÿMSysObjectsÿWHEREÿType=1ÿANDÿFlags=0;")ÿdsn("`dsn'")ÿclear
TheÿODBCÿdriverÿreportedÿtheÿfollowingÿdiagnostics
[Microsoft][ODBCÿMicrosoftÿAccessÿDriver]ÿRecord(s)ÿcannotÿbeÿread;ÿnoÿreadÿpermissionÿonÿ'MSysObjects'.
SQLSTATE=42000
r(682);

.

[wbuchanan]

I think more generally having the odbc commands setup as r class programs that can return other metadata about the query would be fairly helpful in general. A JDBC connector would also be really nice to have and could make it much easier to access databases since the drivers are platform independent.

[Joseph Coveney]

I recall some years ago stumbling across some Stata classes left behind by odbc, which contained arrays with (limited) metadata. I can't remember the particular (peculiar?) circumstances that lead me to the discovery, but you can run classutil dir, all and then classutil describe . . . after running an odbc command. There's not a whole lot there, and I do agree with you that it would be helpful to have more information about the query made accessible in return macros or whatever, for example, when I do a query that returns a scalar, it would be nice not to have to disturb the dataset in memory in order to retrieve that value and use it programmatically.

[Diogo Pereira]

I recall some years ago stumbling across some Stata classes left behind by odbc, which contained arrays with (limited) metadata. I can't remember the particular (peculiar?) circumstances that lead me to the discovery, but you can run classutil dir, all and then classutil describe . . . after running an odbc command.

I followed your suggestion and came up with this:

odbc query "MS Access Database;DBQ=Northwind.mdb"

forvalues i=1/`.__ODBC_INFO.TABLE.arrnels' {
    local tables `tables' `.__ODBC_INFO.TABLE[`i']'
}

It certainly is more elegant than what I had before. Unfortunately, it does not work if you use a connection string instead of a DSN.