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);
}

score 5 · Answer 1 · edited May 23 '17 at 11:51

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'");

score 5 · Accepted Answer · answered Sep 14 '11 at 13:42

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)

`SELECT * FROM sys.systables` gave me what I needed. – Robert Tupelo-Schneck Nov 20 '14 at 05:04 — Robert Tupelo-Schneck, Nov 20 '14 at 05:04

armin · Answer 3 · 2018-03-30T15:06:53.630

3

A very similar output to SHOW TABLES; can be produced by using the following jdbc compliant query:

    SELECT TABLENAME, (SELECT SCHEMANAME  
            FROM SYS.SYSSCHEMAS  
            WHERE SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) 
    AS SCHEMANAME
    FROM SYS.SYSTABLES WHERE TABLETYPE='T'

It also shows you the probably useful SCHEMA information for each TABLE entry. Skip TABLETYPE='T' if you also want to see the system tables of your database as the user before has mentioned already.

edited Mar 30 '18 at 15:06

answered Mar 29 '18 at 21:13

armin

31
3

1

Your answer would be more helpful to others (and more likely to attract upvotes) if you explain what you did and why someone might choose your approach over the other 2 answers – divibisan Mar 29 '18 at 21:57

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

3 Answers3