Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Feature request: odbc query should return table names

    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.

    Code:
    * 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)

  • #2
    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:

    Code:
    odbc load, exec("SELECT tablename, databasename FROM dbc.tables;") dsn("mydsn") clear
    In MS Access, that might be something like:

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

    Comment


    • #3
      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);

      .

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #6
            Originally posted by Joseph Coveney View Post
            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:
            Code:
            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.

            Comment

            Working...
            X