aspose file tools*
The moose likes JDBC and the fly likes JDBC Newbie Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Newbie" Watch "JDBC Newbie" New topic
Author

JDBC Newbie

Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Hi All.
I'm in a muddle and not sure how to put all the pieces together. ( I have been through the sun java JDBC tutorial)
I need to produce ONE class, which will access an Access database, containing two tables. I know I need to create a JDBC/ODBC connection, then open the data base. I then need to open a screen, enabling the users to make multi queries (3), and the option to quit. Then their input will be used to do a sql query with the two tables to produce the result. The results will be displayed and the option for the user to conduct another query or to quit.
Now, I'm ok with sql queries, cuz I have done a few of them using ASP, so that's ok. Most of the programs I am using for inspiration, have all the different steps in different classes. I am unsure how to merge it all together. I think I know the various coding, so I'm not asking for that. Just not sure how to lay it all out.
Try not to laugh too much ok
Hope this makes sense.
Cheers
Sue
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Sue,
The Sun examples give a standard way to layout the code. Why don't you post what you have and people can give you feedback on it?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Thanks Jeanne
Once I put some codes down on paper, I'll be back.
Been told by the tutor, that there is to be only one class and one main method.....
Cheers
Sue
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
OK, here we go...4 hours of coding...not compiling...basically just some ideas, thrown together....
We have 2 tables, within one database. Employee, Car, Company.mdb.
Have to prompt the user for 3 queries.
1. Give number of employees
2. List of employees earning over X amount.
3. List of employees who drive a make of car, choosen by the user.
4. Quit.
The query answer should be a single letter.
here it goes...don't be too rough now *grin*...I know you're not.

Thanks for any help...if anyone is willing to tread through this
Cheers
Sue
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
It looks like you have most of the code in place; The main piece I see you're missing is the code to parse the user's input and process the approprite query based on it.
Something to consider... although your tutor instructed you to use one class for everything, you don't have to put everything in your "main" method, correct? I would recommend spliting your main up into logical functionality, perhaps one method for each query. Typically, main is just used as an entry point into an application that just creates an instance of some object and invokes a method that kicks things off.
Consider something like this psuedocode:

You seem to have a pretty good grasp on things so far. I would recommend that you attempt to fill in the piecese, compile it, debug it, and then if you're still stuck, ask for some specific help.
Good luck


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Blake
Thanks for that. Shall spend today, going through and setting it up in the manner you suggested. Was just trying to get as much down on paper yesterday as I could.
Hopefully, will be able to come back with more specific questions
Seems the tutor managed to get this all done in 40 lines....
This may seem like an off the wall question, but is it possible to create a display screen, where the user just gets to hit a button, i.e. no. of employee, ford, chrysler, salary over 15000, etc. Or would this just be a long winded way of doing it? Could it all be done within one class?
Cheers
Sue
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Hi all.
I have been staring at this for too long....
I've managed to get the errors down to 20...but the first couple I can't see what it doesn't like. It's telling me that it doesn't like my System.out.println statements. (Blake, i've started merging your bits and bobs in with mine, was finding it very useful.
Below is the large code. I've also thought, I should also try and do a mini program for console input, which is the second set of codes.

The next set of code, is working, well as much as it prompts the user and then prints the users choice, however, it doesn't seem to want to work through the if statements...

I feel I should try and take this week off work to get it sorted...
Thanks for your patience and understanding....
Sue
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

Sue,
The if statement should be:

Strings are objects which need to be compared using .equals().
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Jeanne thanks for that.
I've managed to get it to compile and it's loading the connection to the database and I have set up the odbc connection in the control panel.
It's running through the program, prompting the user to input, however, I am unable to get it to return any value from the database.
Also, it doesn't like the line query=cn.createStatement(), however, if I comment it out, the program, does compile.

Really happy it's compiling...
Cheers
Sue
Sadanand Murthy
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
Originally posted by Sue Hunt:
Jeanne thanks for that.
I've managed to get it to compile and it's loading the connection to the database and I have set up the odbc connection in the control panel.
It's running through the program, prompting the user to input, however, I am unable to get it to return any value from the database.
Also, it doesn't like the line query=cn.createStatement(), however, if I comment it out, the program, does compile.
Really happy it's compiling...
Cheers
Sue

Sue, you happiness won't last without uncommenting that line
You need a Statement to executeQuery() on. It wil help us if you can post what error the compiler gives you on this line.
I suspect the compiler is complaining that it cannot find CreateStatement() method. It can't find it because the method name is createStatement() (lower case c).
On a side note : You don't need a break statement in if, else, else-if blocks.
Added:
I'd also recommend adding a finally{} block to close the db connection. Make sure that you check to see the cn != null before closing the connection otherwise you will get NPE.
[ May 04, 2004: Message edited by: Sadanand Murthy ]

Ever Existing, Ever Conscious, Ever-new Bliss
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Deep Bowing.
Thanks for pointing out the c in CreateStatement...should be createStatement. That now compiled.
I've added another try towards the end, before the catch statement.

It compiles nicely, and then it gives me the prompts, however, when I select C, it then gives me the choices of cars to pick from, when I pick BMW, it gives me this
Problem accessing the database java.sql.SQLException: [Microsfot][ODBC Microsoft Access Driver] The Microsoft Jet database enging cannot find the input table or query �Employee�. Make sure it exists and that its name is spelled correctly.
If I pick A, I get this error.
Problem accessing the database java.sql.SQLException: Column not found
If I pick B, it prompts me for the salary amount, and then I get this error.
Problem accessing the database java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
If I pick Q, I get this error.
Problem accessing the database java.sql.SQLException: Invalid handle
(I'm not overly good at Access, either....)
You guys have patience of saints. I know I keep saying it, but you all help to keep me sane, and I really do appreciate it.
I'm wondering, although I mapped the ODBC to go to the folder that is holding the company database, I'm wondering if it should be in the same folder as the class.
Sue
Sadanand Murthy
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
Originally posted by Sue Hunt:
Deep Bowing.
Thanks for pointing out the c in CreateStatement...should be createStatement. That now compiled.
I've added another try towards the end, before the catch statement.

It compiles nicely, and then it gives me the prompts, however, when I select C, it then gives me the choices of cars to pick from, when I pick BMW, it gives me this
Problem accessing the database java.sql.SQLException: [Microsfot][ODBC Microsoft Access Driver] The Microsoft Jet database enging cannot find the input table or query �Employee�. Make sure it exists and that its name is spelled correctly.
If I pick A, I get this error.
Problem accessing the database java.sql.SQLException: Column not found
If I pick B, it prompts me for the salary amount, and then I get this error.
Problem accessing the database java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
If I pick Q, I get this error.
Problem accessing the database java.sql.SQLException: Invalid handle
(I'm not overly good at Access, either....)
You guys have patience of saints. I know I keep saying it, but you all help to keep me sane, and I really do appreciate it.
I'm wondering, although I mapped the ODBC to go to the folder that is holding the company database, I'm wondering if it should be in the same folder as the class.
Sue

Evidently you are not using the correct table names. In one query (all) you have used Employees. Elsewhere you have Employee (singular). Similarly, in the query for C, your table names in the from clause are both singular where as they are plural in the select clause & a combination in the where clause.
Try to debug your queries first. Print the generated query; copy it & run it in MS Access directly to ensure that the sql is correct. If not, debug the sql in access & correct your code. If you have problems with sql, you may want to invest in an sql book.
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Sadanand
Thanks for your help. Found most of the little niggles, and the sql did work in access. Now, when I try running the program, it does output the various correct results.
It seems that the quit does stop the processing, however, when you select q, it will give you the close message, however, it then gives you the final catch message,
Problem accessing the database java.sql.SQLException: Invalid handle
Not sure how to get it so it doesn't print that line, when quit is selected.
Probably something glaringly obvious, but it's been over a week and almost 8 hours a day, trying to sort it out, and I'm brain dead...
Thanks again for the assistance.
Sue
[ May 05, 2004: Message edited by: Sue Hunt ]
Sadanand Murthy
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
Originally posted by Sue Hunt:
Sadanand
It seems that the quit does stop the processing, however, when you select q, it will give you the close message, however, it then gives you the final catch message,
Problem accessing the database java.sql.SQLException: Invalid handle
Not sure how to get it so it doesn't print that line, when quit is selected.
Probably something glaringly obvious, but it's been over a week and almost 8 hours a day, trying to sort it out, and I'm brain dead...
Thanks again for the assistance.
Sue
[ May 05, 2004: Message edited by: Sue Hunt ]

Can you post your latest code? Include the entire method that has this code (so that I can see what comes after your while loop & the try-catch block.
Sue Hunt
Ranch Hand

Joined: Jan 26, 2004
Posts: 50
Keep forgetting to do that...re: posting the code again

Thanks
Sue
Sadanand Murthy
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
Originally posted by Sue Hunt:
Keep forgetting to do that...re: posting the code again
Thanks
Sue

When option = q, you are closing the connection & the code is falling through the rest of the while loop which tries to execute the query.
Other observations:
  • If there is an exception when running a query, the connection will not get closed, the way it is written now.
  • The outer while() loop is an infinite loop. There is no exit criterion.
  • The String object has an equalsIgnoreCase() method.
  • You should break your code down into multiple, private methods that get called from main().

  • Sue Hunt
    Ranch Hand

    Joined: Jan 26, 2004
    Posts: 50
    Thanks for all your help.
    This site is a lifesave, keep up the great work
    Cheers
    Sue
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: JDBC Newbie