Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 10 Nov 2008 13:21:19 +0100
From: Dyre.Tjeldvoll@Sun.COM
Subject: Re: DatabaseMetaData.getTables() resultset empty?
In-reply-to: <491796AA.1070808@gmail.com>
Sender: Dyre.Tjeldvoll@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <x1o37i7bhiv4.fsf@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <491796AA.1070808@gmail.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/22.1 (usg-unix-v)

Ole Ersoy <ole.ersoy@gmail.com> writes:

> Hi,
>
> I'm trying to use DatabaseMetaData.getTables() to get a list of tables contained in a derby database.  I run the statement like this (I made sure tables are present by browsing using the eclipse Data Source explorer first):
>
> ResultSet resultSet = metaData.getTables(null, null, "%", null);
>
> But resultSet is empty.  I also tried it like this:
>
> ResultSet resultSet = metaData.getTables(null, null, "EXISTING_TABLE_NAME", null);
>
> Still no love.  Anyone have any ideas on what I could be doing better?  

How do verify that the result set is empty? Fww. the following works:

import java.sql.*;

public class repro {
	public static void main(String[] args) {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			Connection con = DriverManager.getConnection("jdbc:derby:/tmp/reprodb;create=true");	
			Statement s = con.createStatement();
			try {
				s.execute("create table foobar (i int, j int)");
				s.execute("create view fv as select * from foobar");
			} catch (SQLException sqle) {
				System.out.println(sqle);
			}
			System.out.println("--- tables ---");
			DatabaseMetaData m = con.getMetaData();
			ResultSet tables = m.getTables(null, null, "%", args);
			while (tables.next())  {
				System.out.println(tables.getString("TABLE_NAME")+" ("+tables.getString("TABLE_TYPE")+")");
			}
		} catch (Exception sqle) {
			sqle.printStackTrace();
		}
	}
}

dt136804@khepri32~/java$ java -classpath /home/dt136804/derby-10.4_sane_jars/derby.jar:. repro TABLE VIEW
java.sql.SQLException: Table/View 'FV' already exists in Schema 'APP'.
--- tables ---
FOOBAR (TABLE)
FV (VIEW)

-- 
dt