Derby (Cloudscape): how to query 2 or more tables in one query-statement?

[juliane gross]

Hi,
I am using Derby (cloudscape) embedded in a Swing-Application.

My question:
How can I process a query similar to this, where I have references
to 2 tables in the same query:

"SELECT Person.Author, Books.Title, Books.Year
FROM Person, Books
WHERE Person.ID = Books.ID";

It seems like Derby lets me connect to one table only at a time.
I searched the Manual and Reference script thoroughly but in vain.
I know that queries like the one I mentioned above are possible,
because I saw similar examples in the reference book, but they did not
show the according connection code.
I suspect the connection code must be different, but how?

Here is, how I query data from one table only, which works fine:

(My tables are within one Databasesystem (DBSytem.Books, DBSystem.Person etc.)).

 
// my Properties props contains the "derby.system.home" variable
 
try{
    Connection conn = DriverManager.getConnection("jdbc:Derby:Person", props);
    Statement stm = conn.createStatement();
 
    ResultSet rs = stm.executeQuery("SELECT Author FROM Person WHERE id = 123");
    if(rs.next()){
        String value = rs.getString("Author ");
    }
    rs.close();
    stm.close();
    dc.close();
}
catch{...}

Any help is very much appreciated, thank you!

Juliane

[ February 12, 2005: Message edited by: juliane gross ]
[ February 12, 2005: Message edited by: juliane gross ]

[Jeanne Boyarsky]

Juliane,
What error message are you getting?

[juliane gross]

The error message is:

ERROR 42X05: the table 'BOOK' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(FromBaseTable.java)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(FromBaseTable.java)
at org.apache.derby.impl.sql.compile.FromList.bindTables(FromList.java)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(SelectNode.java)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(DMLStatementNode.java)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java)
at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(ReadCursorNode.java)
at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java)
at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java)
at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java)
at de.insomanager.DerbyTest.go(DerbyTest.java:159)
at de.insomanager.DerbyTest.main(DerbyTest.java:48)
  

[juliane gross]

I understand the error message, since the getConnection() method never learns about the existence of table BOOK.
I probably need to adapt the getConnection syntax for multiple table queries, but don't know how.
Thank you for any tips!

Juliane

[Jeanne Boyarsky]

Juliane,
getConnection() just gets a connection. It isn't specific to the type of query.

I think there way be a typo in the query. In your SQL, you have "FROM Person, Books", but the error message has "the table 'BOOK' does not exist." One has an "s" at the end and one does not.

You can verify this by trying your single query example with the following two queries:
ResultSet rs = stm.executeQuery("SELECT title FROM Books);
ResultSet rs = stm.executeQuery("SELECT title FROM Book);