calling derby (java db) 'show tables' from jdbc

Question

I need to enumerate the tables in a Derby (aka Java DB) database using JDBC in a Java program. All I am aware of for doing this is the SHOW TABLES command.

I first tried with something similar to this...

String strConnectionURL = "jdbc:derby:/path/to/derby/database;create=false";
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection connection = DriverManager.getConnection(strConnectionURL);
Statement statement = connection.createStatement();
boolean boResult = statement.execute("SHOW TABLES");
if (boResult) {
    System.out.println("yay!");
}

...but that throws an exception:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "SHOW" at line 1, column 1.

So next I thought maybe I needed to use a CallableStatement so I tried this...

String strConnectionURL = "jdbc:derby:/path/to/derby/db;create=false";
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection connection = DriverManager.getConnection(strConnectionURL);
CallableStatement statement = connection.prepareCall("SHOW TABLES");
boolean boResult = statement.execute();
if (boResult) {
    System.out.println("yippee!");
}

...but that throws the same exception:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "SHOW" at line 1, column 1.

So, can anyone help me enumerate the tables in my Derby (Java DB) database from JDBC?

EDIT: I'm looking around and starting to get a feeling this may be a general JDBC question. In other words, one could/would enumerate all a db's tables with the DatabaseMetaData object that can be retrieved from the Connection object. Looking into that (and looking forward to responses)...

EDIT 2: I found a pure JDBC solution, but am still happy to hear alternatives...

String strConnectionURL = "jdbc:derby:/path/to/db;create=false";
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection connection = DriverManager.getConnection(strConnectionURL);
DatabaseMetaData dbmd = connection.getMetaData();
ResultSet resultSet = dbmd.getTables(null, null, null, null);
while (resultSet.next()) {
    String strTableName = resultSet.getString("TABLE_NAME");
    System.out.println("TABLE_NAME is " + strTableName);
}

Answer 1

Show Tables is an ij command, not a base SQL statement, so you can't directly execute it. As you noted in your "EDIT 2", you can use the DatabaseMetaData to do this. Two other ways to do it are: you can select from the system catalogs (see http://db.apache.org/derby/docs/10.8/ref/rrefsistabs24269.html) , or you can use the "ij.runScript" method to run the ij tool from within your program, and pass it the "show tables" command (see http://db.apache.org/derby/docs/10.8/publishedapi/jdbc3/org/apache/derby/tools/ij.html)

Answer 2

As Bryan suggested ij.runScript - the code would look like this:

public void showTbls() throws Exception{
    String sqlIn = "SHOW TABLES;";
    InputStream stream = new ByteArrayInputStream(sqlIn.getBytes(StandardCharsets.UTF_8));
    ij.runScript(conn,stream,StandardCharsets.UTF_8.name(), System.out,"UTF-8");
    stream.close();
}

assumming conn is a opened derby Connection

But the disadvantage is that you are getting only string output. Not an ResultSet as you would get from:

Statement stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("SELECT * FROM sys.systables");

or if you want only user table names you can use following SQL:

ResultSet results = stmt.executeQuery("SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='T'");