JDBC-ODBC - Listing Tables with meta.GetTables()

JDBC for SQL Server - Herong's Tutorial Examples

∟JDBC-ODBC Bridge Driver - Flat Text Files

∟JDBC-ODBC - Listing Tables with meta.GetTables()

This section describes how to get a list of tables in flat text file database connected by an ODBC driver.

From the previous tutorial, we know that the database represented by the ODBC driver based on flat text files is read-only. So I need to create some text files in the mapped directory. Each text file will be presented as a table by the ODBC driver.

To create 2 tables in the database represented by the DSN, HY_FLAT, I added two empty files in C:\local\ODBC_Flat_DB:

04/01/2007  06:31 PM                 0 Address.txt
04/01/2007  06:31 PM                 0 User.txt

The program below uses the getTables() method of the DatabaseMetaData to list all existing tables:

/* OdbcFlatListTables.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class OdbcFlatListTables {
  public static void main(String [] args) {
    Connection con = null;
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con = DriverManager.getConnection("jdbc:odbc:HY_FLAT");

      DatabaseMetaData meta = con.getMetaData();
      ResultSet res = meta.getTables(null, null, null,
         new String[] {"TABLE"});
      System.out.println("List of tables: ");
      while (res.next()) {
         System.out.println(
            "   "+res.getString("TABLE_CAT")
           + ", "+res.getString("TABLE_SCHEM")
           + ", "+res.getString("TABLE_NAME")
           + ", "+res.getString("TABLE_TYPE")
           + ", "+res.getString("REMARKS"));
      }
      res.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

The output looks good:

herong> progra~1\java\jdk1.7.0_45\bin\javac
   OdbcFlatListTables.java

herong> progra~1\java\jdk1.7.0_45\bin\java OdbcFlatListTables

List of tables:
   C:\LOCAL\ODBC_FLAT_DB, null, Address.txt, TABLE, null
   C:\LOCAL\ODBC_FLAT_DB, null, User.txt, TABLE, null

So Microsoft ODBC Text Driver manages flat text files as a database with the following rules:

The catalog name (catalog name) is the directory name, where the text files are located.
The database is read-only.
There is no schema names.
Each .txt or .csv file represents a table. The file name is used as the table name.