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