Overview

A program, by invoking different methods of java.sql.DatabaseMetaData class, dynamically obtains information about the database like the structure of a table accessed through the data source connection.

This essay explains how to get information of tables (including ctx, btx, csv, txt, xlsx and xls) using DatabaseMetaData class via esProc JDBC in Java.

Configure onlyServer option in esProc JDBC. Information about data files under the local main directory will be returned by default or when the option is set as false, and that of data files under the server’s main directory will be returned when the option is true.

getTables()

Description:

Return information of data files under the local or the server’s main directory.

Syntax:

dbmd.getTables(String catalog,String schema,String tableNamePattern,String[] types)

Parameters:

tableNamePattern: Specify the pattern with which the returned tables should match and support fuzzy search. Get information of all tables when the parameter is absent. Use different file separators under different operating systems when getting data files in subdirectories.

catalog/schema/types: There aren’t attribute concepts like catalog, schema and types when connecting to esProc JDBC; just assign null to them.

Example:

public void getTablesTest() throws ClassNotFoundException, SQLException{

Connection con = null;

// Establish the connection

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true");

DatabaseMetaData dbmd = con.getMetaData();

ResultSet rs =dbmd.getTables(null, null, null, null);

while(rs.next()) {

System.out.println(rs.getString("TABLE_NAME"));

… …

}

… …

}

getColumns()

Description:

Return information about fields of the table in ResultSet. The field type is an integer matching the type constant in java.sql.Types.

Syntax:

dbmd.getColumns(String catalog, String schemaPattern,String tableNamePattern, String columnNamePattern)

Parameters:

tableNamePattern: Specify name of table whose field information is to be obtained. Get field information of all tables when the parameter is absent. Use different file separators under different operating systems when getting data files in subdirectories.

columnNamePattern: Specify the name of columns. Get information of all fields when the parameter is absent; support fuzzy search.

catalog/schemaPattern: There aren’t attribute concepts like catalog and schemaPattern when connecting to esProc JDBC; just assign null to them.

Example:

public void getColumnsTest() throws ClassNotFoundException, SQLException{

Connection con = null;

// Establish the connection

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true");

DatabaseMetaData dbmd=con.getMetaData();

ResultSet rs =dbmd.getColumns(null, null, “test.txt”, null);

List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>();

while(rs.next()) {

Map<String, Object> map = new HashMap<String, Object>();

map.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));

map.put("DATA_TYPE", rs.getString("DATA_TYPE"));

map.put("TYPE_NAME", rs.getString("TYPE_NAME"));

map.put("COLUMN_SIZE", rs.getString("COLUMN_SIZE"));

map.put("NULLABLE", rs.getString("NULLABLE"));

tempList.add(map);

… …

}

… …

}

Note:
If a meta.txt file is already configured under the local or the server’s main directory, the method will return field types configured in this file, otherwise it will identify the field type according to the data to return. The encoding for meta.txt should be in consistent with that for esProc JDBC.

Below is the format of meta.txt, where file names and column names cannot be changed:

Table: Define table names for corresponding files under File. You can directly use the table names under Table for query when accessing JDBC, like select * from tt1.

File: Data file names that are full paths relative to the main directory. Use different file separators under different operating systems when data files are located in subdirectories, like ctx// table1.txt for Windows and ctx/ table1.txt for Linux.

Column: Specify names of columns for which field types need to be configured.

Type: Set data type number for esProc (0: Default/Auto-identify; 1: 32-bit integer; 2: 64-bit long integer; 3: 16-bit short integer; 4: large integer; 5: 32-bit floating-point number; 6: 64-bit double-precision floating-point number; 7: Decimal long real number; 8: Date; 9: Time; 10: Datetime; 11: String; 12: Boolean; 62: Byte array).