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

JDBC Tutorials - Herong's Tutorial Notes

Dr. Herong Yang, Version 2.11

JDBC Tutorials - Herong's Tutorial Notes

∟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) 2007 by Dr. Herong Yang. 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 out looks good:

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.

Table of Contents

About This Book

JDBC (Java Database Connectivity) Introduction

Downloading and Installing JDK - Java SE

Installing and Running Java DB - Derby

Derby (Java DB) JDBC Driver

Derby (Java DB) JDBC DataSource Objects

Java DB (Derby) - DML Statements

Java DB (Derby) - ResultSet Objects of Queries

Java DB (Derby) - PreparedStatement

MySQL Installation on Windows

MySQL JDBC Driver (MySQL Connector/J)