db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lyon, Bruce" <Bruce.L...@xerox.com>
Subject getImportedKeys() and getTables() return no rows
Date Thu, 03 Jan 2008 20:05:37 GMT
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"));

                        }     

                  

                  }

 


Mime
View raw message