File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC help" Watch "JDBC help" New topic

JDBC help

roma janssens

Joined: Dec 18, 2010
Posts: 2
hello everyone, I am new to JDBC and have some questions.

How can I use a query that uses two tables: in my database I have a table Employee and a table Works_on

WORKS_ON has the columns ESSN (refering to the SSN in employee table) PNO and HOURS

i need a query that given a certain projectnumber (PNO) i get the firstname (FNAME), last name (LNAME) and the number of hours worked on a project (HOURS) for the different employees working on that project, i tried the following query but i think it is wrong:

String query2string = "SELECT FNAME, LNAME, HOURS FROM eployee_db.works_on,employee_db.employee WHERE PNO=? AND ESSN=SSN + ORDER BY LNAME;";

thanks for your help!
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

Welcome to the Ranch!

It looks really close, the join part is correct. There is a stray "+" before order by, but that's probably a typo. What are your doubts about it?

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Tom Reilly

Joined: Jun 01, 2010
Posts: 618
There's also a ';' at the end of the SQL which shouldn't be there.
roma janssens

Joined: Dec 18, 2010
Posts: 2
i use eclipse for my project and when i ask to run it gives the error

SQL Exception:
ORA-00936: missing expression

when i change this query to

String query2string = "SELECT HOURS FROM eployee_db.works_onWHERE PNO=?";

the error changes to

SQL Exception:
ORA-00911: invalid character

(i suspect there are more errors in my project so i think this means the first error is due to the query being wrong)

i really should be able to get this to workasap but due to my poor knowledge of SQL i cannot understand what i am doing wrong, if anyone could help me with this i would really appreciate it
Tom Reilly

Joined: Jun 01, 2010
Posts: 618
SELECT HOURS FROM eployee_db.works_onWHERE PNO=?

There is no space between works_on and WHERE.
eployee_db is also spelled wrong.

One thing you can do to identify these types of errors is copy the SQL into a tool such as sqlplus or toad and run it from there. Of course you'll have to change the '?' into a real value.
I agree. Here's the link:
subject: JDBC help
It's not a secret anymore!