DEVFYI - Developer Resource - FYI

How do I extract SQL table column type information?

JDBC Interview Questions and Answers

(Continued from previous question...)

How do I extract SQL table column type information?

Use the getColumns method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table. Note that most arguments to the getColumns method (pinpointing the column in question) may be null, to broaden the search criteria. A code sample can be seen below:

public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]",
"[login]", "[passwd]");

// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();

// Get all column types for the table "sysforeignkeys", in schema
// "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");

// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);

// Printout
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName
+ " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable +
", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition
+ ", Decimal digits: " + dbDecimalDigits);
}

// Free database resources
rs.close();
conn.close();
}

(Continued on next question...)

Other Interview Questions