You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Ole Ersoy <ol...@gmail.com> on 2008/11/10 03:04:26 UTC
DatabaseMetaData.getTables() resultset empty?
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?
Thanks,
- Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Ole Ersoy <ol...@gmail.com>.
Hi,
I left the catalog as null:
ResultSet resultSet2 = metaData.getTables(null, "%", "%", types);
but the resultset is still empty. I did manage to duplicate Dyre's test though. I'll comment more in that thread.
Thanks,
- Ole
Suavi Ali Demir wrote:
> Plz leave catalog as null.
> Ali
>
>
> --- On Sun, 11/9/08, Ole Ersoy <ol...@gmail.com> wrote:
>
>> From: Ole Ersoy <ol...@gmail.com>
>> Subject: Re: DatabaseMetaData.getTables() resultset empty?
>> To: "Derby Discussion" <de...@db.apache.org>
>> Date: Sunday, November 9, 2008, 8:29 PM
>> Ali,
>>
>> I had percentage for the tableNamePattern argument, so I
>> assume you mean try it for the other arguments as well:
>>
>> String[] types = {"TABLE", "VIEW"};
>> // ResultSet resultSet2 = metaData.getTables(null, null,
>> "%", types);
>> ResultSet resultSet2 = metaData.getTables("%",
>> "%", "%", types);
>> Still no love.
>>
>> Thanks though,
>> - Ole
>
>
>
>
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Ole Ersoy <ol...@gmail.com>.
Hi Suavi,
Sorry - I take it all back. I was checking the wrong resultset. I had multiple resultsets in my test, and naturally, given that the zen rock on my tables is smarter than I am, I checked resulset, instead of resultset2. I retried all the permutations and they all return the same result:
ResultSet resultSet2 = metaData.getTables(null, "%", "%", types);
ResultSet resultSet2 = metaData.getTables(null, null, "%", types);
ResultSet resultSet2 = metaData.getTables(null, null, null, types);
And even with the schema under which the tables exist:
ResultSet resultSet2 = metaData.getTables(null, "USER1", "%", types);
This returns all tables:
ResultSet resultSet2 = metaData.getTables(null, null, null, null);
So it all works great. Thanks again!
- Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Suavi Ali Demir <de...@yahoo.com>.
Plz leave catalog as null.
Ali
--- On Sun, 11/9/08, Ole Ersoy <ol...@gmail.com> wrote:
> From: Ole Ersoy <ol...@gmail.com>
> Subject: Re: DatabaseMetaData.getTables() resultset empty?
> To: "Derby Discussion" <de...@db.apache.org>
> Date: Sunday, November 9, 2008, 8:29 PM
> Ali,
>
> I had percentage for the tableNamePattern argument, so I
> assume you mean try it for the other arguments as well:
>
> String[] types = {"TABLE", "VIEW"};
> // ResultSet resultSet2 = metaData.getTables(null, null,
> "%", types);
> ResultSet resultSet2 = metaData.getTables("%",
> "%", "%", types);
> Still no love.
>
> Thanks though,
> - Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Ole Ersoy <ol...@gmail.com>.
Ali,
I had percentage for the tableNamePattern argument, so I assume you mean try it for the other arguments as well:
String[] types = {"TABLE", "VIEW"};
// ResultSet resultSet2 = metaData.getTables(null, null, "%", types);
ResultSet resultSet2 = metaData.getTables("%", "%", "%", types);
Still no love.
Thanks though,
- Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Suavi Ali Demir <de...@yahoo.com>.
Try % instead of null.
Ali
--- On Sun, 11/9/08, Ole Ersoy <ol...@gmail.com> wrote:
> From: Ole Ersoy <ol...@gmail.com>
> Subject: Re: DatabaseMetaData.getTables() resultset empty?
> To: "Derby Discussion" <de...@db.apache.org>
> Date: Sunday, November 9, 2008, 8:12 PM
> Emmanuel,
>
> I gave it a go.
>
> String[] types = {"TABLE", "VIEW"};
> ResultSet resultSet = metaData.getTables(null, null,
> "%", types);
>
> But resultset is still empty.
>
> Thanks though,
> - Ole
>
>
>
>
> Emmanuel Cecchet wrote:
> > Ole,
> >
> > It might be that Derby does not like it when the types
> are set to null.
> > In Sequoia, I use something like that and it works:
> > String[] types = {"TABLE",
> "VIEW"};
> > rs = connectionMetaData.getTables(null, null,
> "%", types);
> >
> > Let me know if that works for you,
> > Emmanuel
> >
> >>
> >> 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? Thanks,
> >> - Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Ole Ersoy <ol...@gmail.com>.
Emmanuel,
I gave it a go.
String[] types = {"TABLE", "VIEW"};
ResultSet resultSet = metaData.getTables(null, null, "%", types);
But resultset is still empty.
Thanks though,
- Ole
Emmanuel Cecchet wrote:
> Ole,
>
> It might be that Derby does not like it when the types are set to null.
> In Sequoia, I use something like that and it works:
> String[] types = {"TABLE", "VIEW"};
> rs = connectionMetaData.getTables(null, null, "%", types);
>
> Let me know if that works for you,
> Emmanuel
>
>>
>> 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?
>> Thanks,
>> - Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Emmanuel Cecchet <ma...@frogthinker.org>.
Ole,
It might be that Derby does not like it when the types are set to null.
In Sequoia, I use something like that and it works:
String[] types = {"TABLE", "VIEW"};
rs = connectionMetaData.getTables(null, null, "%", types);
Let me know if that works for you,
Emmanuel
>
> 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?
> Thanks,
> - Ole
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Ole Ersoy <ol...@gmail.com>.
Hi Dyre,
Thanks for the example! After running and examining it a little more I noticed I was checking the wrong ResultSet (resultset, instead of resultset2). I put a list of examples that work in the reply to Suavi, for other's following the thread.
Thanks again!
- Ole
Re: DatabaseMetaData.getTables() resultset empty?
Posted by Dy...@Sun.COM.
Ole Ersoy <ol...@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