JDBC-ODBC - Executing Queries on Flat Files

JDBC for SQL Server - Herong's Tutorial Examples

∟JDBC-ODBC Bridge Driver - Flat Text Files

∟JDBC-ODBC - Executing Queries on Flat Files

This section describes how to execute SELECT queries on flat text files through the ODBC driver.

With several rows and columns entered in my flat text files as described in the previous tutorial, I am ready to run a SELECT query statement on those data rows as shown in this sample Java program:

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

// Running a query that joins two flat files
      ResultSet res = sta.executeQuery(
        "SELECT u.FirstName, u.BirthDate, a.City"
        + " FROM User.txt u, Address.txt a"
        + " WHERE u.AddressID = a.ID");
      System.out.println("List of Users: ");
      while (res.next()) {
         System.out.println(
            "   "+res.getString("FirstName")
           + " born on "+res.getDate("BirthDate")
           + " from "+res.getString("City")
           );
      }
      res.close();

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

Here is the result I got from this program:

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

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

List of Users:
   R. Morgan born on 1985-09-09 from Bellevue
   Danielle born on 1979-11-05 from Hull
   Alexander born on 1977-04-12 from W. York
   Rosmarie born on 1983-07-01 from Paris
   Christopher born on 1989-08-11 from Portland

This is perfect. My program and flat files worked as expected:

The ODBC driver picked up the column names correctly from the first row of my data files.
The ODBC driver detected the BirthDate column as date type column.
The ODBC allowed to make a join between two data files.