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 "Lyon, Bruce" <Br...@xerox.com> on 2008/01/03 21:05:37 UTC
getImportedKeys() and getTables() return no rows
This is driving me spare. I have been trying to access Derby metadata
and have been unable to get some rather basic functions to work. In
particular, I cannot get getImportedKeys() or getTables() to return any
rows. I can get a DatabaseMetaData object and perform various function
calls, such as getCatalogs() or getSchemas() on it. However, when I call
getTables() or getImportedKeys() I get a ResultSet with no rows. I can
print the columns of the ResultSet, but it has no rows. Why is this? The
following code in a servlet can print out the catalog/sechema data, but
it cannot get any table names; and when I try to print the foreign keys
from the "policy" table (which does exist in this database) the
ResultSet has no rows, even though the "policy" table has two columns
that are foreign keys.
I am using Derby network server 10.2.2.1, Glassfish 9.1, Java 1.6.0_03,
and openSUSE 10.2 on Intel.
Code snippet:
ResultSet mrs=null;
DatabaseMetaData dbm=null;
Map<String,String> keyMap=null;
int fkCount=0;
// get database metadata, need to check for foreign
keys
dbm=conn.getMetaData();
mrs=dbm.getCatalogs();
if (mrs==null) {
out.println("<br>No catalogs");
} else {
while (mrs.next()) {
out.println("<br>Found catalog:
"+mrs.getString("TABLE_CAT"));
}
}
mrs=null;
mrs=dbm.getSchemas();
if (mrs==null) {
out.println("<br>No schemas");
} else {
while (mrs.next()) {
out.println("<br>Found schema with
catalog: "+mrs.getString("TABLE_CATALOG")+" schema:
"+mrs.getString("TABLE_SCHEM"));
}
}
// get table names
mrs=null;
mrs=dbm.getTables(null,"app",null,null);
if (mrs==null) {
out.println("<br>No table names");
} else {
out.println("<br>Returned a ResultSet");
ResultSetMetaData rsmd = mrs.getMetaData();
int cols = rsmd.getColumnCount();
out.println("<br>There are "+cols+" columns");
// Display the result set data.
out.println("<br><table border='1'>");
// print column names
out.println("<br><tr>");
for (int i = 1; i <= cols; i++) {
out.println("<td>"+rsmd.getColumnName(i)+"</td>");
}
out.println("</tr>");
// Display the result set data.
out.println("<br><table border='1'>");
while(mrs.next()) {
out.println("<br><tr>");
for (int i = 1; i <= cols; i++) {
out.println("<td>"+mrs.getString(i)+"</td>");
}
out.println("</tr>");
}
out.println("<br></table>");
}
// get foreign key info
mrs=null;
mrs=dbm.getImportedKeys("","APP","policy");
if (mrs==null) {
out.println("<br>No foreign keys");
} else {
out.println("<br>Returned a ResultSet");
ResultSetMetaData rsmd = mrs.getMetaData();
int cols = rsmd.getColumnCount();
out.println("<br>There are "+cols+" columns");
// Display the entire result set data.
out.println("<br><table border='1'>");
out.println("<br><tr>");
for (int i = 1; i <= cols; i++) {
out.println("<td>"+rsmd.getColumnName(i)+"</td>");
}
out.println("</tr>");
while(mrs.next()) {
out.println("<br><tr>");
for (int i = 1; i <= cols; i++) {
out.println("<td>"+mrs.getString(i)+"</td>");
}
out.println("</tr>");
}
out.println("<br></table>");
// store foreign key info
mrs=null;
mrs=dbm.getImportedKeys(null,"APP","policy");
while (mrs.next()) {
fkCount++;
out.println("<br>FK "+fkCount+" found:");
out.println("<br>Foreign key info:
"+mrs.getString("FKCOLUMN_NAME")+" "+mrs.getString("PKTABLE_NAME")+"
"+mrs.getString("PKCOLUMN_NAME")+"<br>");
keyMap.put(mrs.getString("FKCOLUMN_NAME"),
mrs.getString("PKTABLE_NAME")+" "+ mrs.getString("PKCOLUMN_NAME"));
}
}
Re: getImportedKeys() and getTables() return no rows
Posted by Peter Yuill <py...@objectix.com.au>.
Bruce,
I think the problem is that you are assuming schema and table
names are case insensitive, and that null equals "". Try:
mrs=dbm.getTables(null,"APP",null,null); // not
(null,"app",null,null)
and
mrs=dbm.getImportedKeys(null,"APP","POLICY"); //
not ("","APP","policy")
Regards,
Peter Yuill
Re: getImportedKeys() and getTables() return no rows
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> mrs=dbm.getTables(null,"app",null,null);
> mrs=dbm.getImportedKeys(null,"APP","policy");
Check the case of your names. Possibly these should be "APP" and
"POLICY", respectively.
Schema names, table names, column names, etc. are case sensitive
when passed to the DatabaseMetaData calls.
thanks,
bryan
Re: getImportedKeys() and getTables() return no rows
Posted by Daniel John Debrunner <dj...@apache.org>.
blyon wrote:
> Well, first of all, the "ij" tool that derby provides for interactive
> command-line SQL is case-insensitive for table and column names; if you
> execute all lower-case sql (as I did when I created my tables) the table and
> column names in derby come out all upper case.
>
> Second, when I execute sql commands using Statement.executeQuery(String), I
> get the correct answer using all lower-case table and column names.
So both ij and executeQuery are executing SQL statements and here Derby
is following the SQL standard rules for SQL identifiers. Unquoted
identifies (e.g. mytable, MyTable) correspond to the upper-case version
of the identifier (i.e. MYTABLE), whereas quoted identifies (e.g.
"myTable") correspond to the value without quotes and no case
modification (i.e. myTable). Quoted identifiers also allow characters
not allowed in unquoted identifiers, such as space, at-sign, exclamation
point etc., e.g. "my Table !".
> I can
> also get the derby column names from the result set meta-data after a query
> such as "select * from service order by name" when the derby table name is
> apparently really SERVICE and the column name really NAME.
Here Derby is returning the actual column name, which is the converted
form of the SQL identifier used in create table.
> So I was programming against derby for quite some time before I found that
> the case of the table or column names mattered; hence my bafflement when my
> attempts to get database meta-data (as opposed to result set meta-data) did
> not work.
It is true that column names in ResultSet getter methods are
case-insensitive, whereas table, column, etc. names in DatabaseMetaData
must match the stored form the identifier. This is defined by the JDBC
specification, no idea why it is different, but Derby is just following
the spec.
Dan.
Re: getImportedKeys() and getTables() return no rows
Posted by blyon <Br...@xerox.com>.
Well, first of all, the "ij" tool that derby provides for interactive
command-line SQL is case-insensitive for table and column names; if you
execute all lower-case sql (as I did when I created my tables) the table and
column names in derby come out all upper case.
Second, when I execute sql commands using Statement.executeQuery(String), I
get the correct answer using all lower-case table and column names. I can
also get the derby column names from the result set meta-data after a query
such as "select * from service order by name" when the derby table name is
apparently really SERVICE and the column name really NAME.
So I was programming against derby for quite some time before I found that
the case of the table or column names mattered; hence my bafflement when my
attempts to get database meta-data (as opposed to result set meta-data) did
not work.
--
View this message in context: http://www.nabble.com/getImportedKeys%28%29-and-getTables%28%29-return-no-rows-tp14606770p14668786.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: getImportedKeys() and getTables() return no rows
Posted by Daniel John Debrunner <dj...@apache.org>.
Donald McLean wrote:
> At least one case was documented on the Wiki:
>
> http://wiki.apache.org/db-derby/DerbySQLroutines
>
> (under Common Problems, at the bottom)
If you mean SYSCS_IMPORT_DATA (which is a SQL routine) then it seems to
follow the same rules as the Java methods, case sensitive, I was asking
for Java methods that are case insensitive for table names. Ie. passing
in mytable would match the table created by any of
create table mytable ...
create table MYTABLE ...
create table myTable ...
create table "MYTABLE" ...
All of which would create a table with the name MYTABLE.
JDBC DatabaseMetadata methods require that the passed in table name
match the actual table name, in this case MYTABLE.
Dan.
> On Jan 4, 2008 2:27 PM, Daniel John Debrunner <dj...@apache.org> wrote:
>> blyon wrote:
>>> Thanks for the advice, it works as expected when I use ALL UPPER CASE. But,
>>> isn't it rather odd that some functions are case-sensitive for table names
>>> and others are not?
>> I'm curious, which methods are case-insensitive for table names?
>
Re: getImportedKeys() and getTables() return no rows
Posted by Donald McLean <dm...@gmail.com>.
At least one case was documented on the Wiki:
http://wiki.apache.org/db-derby/DerbySQLroutines
(under Common Problems, at the bottom)
On Jan 4, 2008 2:27 PM, Daniel John Debrunner <dj...@apache.org> wrote:
> blyon wrote:
> > Thanks for the advice, it works as expected when I use ALL UPPER CASE. But,
> > isn't it rather odd that some functions are case-sensitive for table names
> > and others are not?
>
> I'm curious, which methods are case-insensitive for table names?
Re: getImportedKeys() and getTables() return no rows
Posted by Daniel John Debrunner <dj...@apache.org>.
blyon wrote:
> Thanks for the advice, it works as expected when I use ALL UPPER CASE. But,
> isn't it rather odd that some functions are case-sensitive for table names
> and others are not?
I'm curious, which methods are case-insensitive for table names?
Thanks,
Dan.
Re: getImportedKeys() and getTables() return no rows
Posted by blyon <Br...@xerox.com>.
Thanks for the advice, it works as expected when I use ALL UPPER CASE. But,
isn't it rather odd that some functions are case-sensitive for table names
and others are not? It caused >me< to waste time, at any event; and I think
it is quite likely that I am not the only one to have this problem...
--
View this message in context: http://www.nabble.com/getImportedKeys%28%29-and-getTables%28%29-return-no-rows-tp14606770p14622812.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: getImportedKeys() and getTables() return no rows
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Lyon, Bruce wrote:
> This is driving me spare. I have been trying to access Derby metadata
> and have been unable to get some rather basic functions to work. In
> particular, I cannot get getImportedKeys() or getTables() to return any
> rows. I can get a DatabaseMetaData object and perform various function
> calls, such as getCatalogs() or getSchemas() on it. However, when I call
> getTables() or getImportedKeys() I get a ResultSet with no rows. I can
> print the columns of the ResultSet, but it has no rows. Why is this? The
> following code in a servlet can print out the catalog/sechema data, but
> it cannot get any table names; and when I try to print the foreign keys
> from the “policy” table (which does exist in this database) the
> ResultSet has no rows, even though the “policy” table has two columns
> that are foreign keys.
Did you quote the name of the "policy" table when you created it? If
not, I think you need to write the name in upper case, when using
getImportedKeys().
--
Øystein