wood burning stoves*
The moose likes JDBC and the fly likes Code error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Code error" Watch "Code error" New topic
Author

Code error

Paul Wright
Ranch Hand

Joined: Oct 05, 2004
Posts: 42
Whats wrong with this code??? The error is just after the sql statement is run as noted below. Im 99% sure its not a database error.
Any help would be much appreciated.

Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Whats the exception are you getting ?
Srini


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Problem seems to be with your insert statement.

can you print sql so that you can find if you are forming correct sql


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"Fatti Stuff",
We're a friendly bunch here at the JavaRanch, and pretty light on rules, but one we do take seriously is the Naming Policy. You can check out the full details here, but the short version is:

We require display names to be two words: your first name, a space, then your last name. Obviously fictitious names are not allowed.

Please update your profile and select a valid display names, since accounts with invalid diaply names get deleted, often without warning.

We appreciate your contribution, so please take the time to update your profile and stick around!

Dave.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Please have a look at PreparedStatements for this type of operation. It turns unreadable code into readable code. I'm not referring to the quality of your code, but the way you have to generate the insert statement. It's a maintenance nightmare.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

I'm astonished not to find a typical primary key.

If you don't fill all columns of the database, you have to specify which column you want to fill:



If this isn't the error, print the error-message.

And follow David O's suggest.


http://home.arcor.de/hirnstrom/bewerbung
Paul Wright
Ranch Hand

Joined: Oct 05, 2004
Posts: 42
IM not really getting an exception error. What im getting is a return of false on the boolean statement. So either the statement isnt being presented properly or its not inserting into the database for another reason.

As for the name(Fatti Stuff) the warning you just gave me was the first ive seen. I will change it now.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

ok ,you are not getting any error

but did you try this already I had mentioned in previous post

System.out.println("INSERT INTO Patient values (" + stringtoinsert + ");" );

try to exceute output of System.out.println on database
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


So either the statement isnt being presented properly or its not inserting into the database for another reason.

Its not a simple as that. Statement.execute(String sql) returns true is the INSERT worked, or false if it didn't, or if it performs an UPDATE. Unless your table is using some DB sepcific PK generation (a trigger, or an autonumber datatype field) then your programming logic is open to error. What is your Primary Key?

(I'd take note of David O'Meara's advice too)


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
M. Gagnon
Ranch Hand

Joined: Feb 18, 2004
Posts: 68
Actually, it seems your code IS throwing an exception because insertIntoDB() is returning false, which happens in the catch block of your code. I would reiterate what others have said here regarding printing out the SQL statement and checking the fields in the table to make sure your values match them in number, order and type. If you DO have a primary key and it's inserted automatically, you won't be supplying a value, but you will need to specify the fields in the statement to account for the fact that your "value list" does not match the "field list" exactly.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ah yes, I've just properly read your code and M.Gagnon is right. Your logic is still open to error though.
Paul Wright
Ranch Hand

Joined: Oct 05, 2004
Posts: 42
Ive tried matching the column list with the values im inserting and that didnt work either.

Ive tried inserting an output.println(stringtoinsert); and because the statement isnt created until after the insertDb is created then it wont compile sa it says no such variable.

There is a primary key and that is the patientId. It is not auto generated in the database(Access). All values in the database are text except for the patientId which is of number type.

I know nothing about prepared statements and probably dont have enough time to read up on it now anyway. I could post this code on a thousand forums and nobody could get it to work.

I think im gonna just throw the whole assessment in the bin and just cram for the exam. Ive spent far far far too long on this as it is.
[ October 22, 2004: Message edited by: Paul Wright ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You have a PK, but you don't include it in your insert statement and its not generated by Access. That will never work - what you are basically saying telling the DB to do is insert a NULL value for you Primary Key, a field that can never be null. Either get the DB to generate the PK or include it in your insert statement.

I'd suggest you make time to read up on PreparedStatements - its only one class, so doesn't take long. From a coding point of view they are much easier to use. You could "post this code on a thousand forums and nobody could get it to work" as it stands, but there are literaly thousands of people who look into this forum who could get it to work if you follow their advice.

I'd take some time away from the problem - forget about it for a while then come back to it. A clearer head might help.
Paul Wright
Ranch Hand

Joined: Oct 05, 2004
Posts: 42
That version above is an earlier version. This is the lastest version with the primary key.


I have posted in on several forums with no luck. Ive been trying to get this single servlet to work for about 2 weeks now and have taken everyones advice and am still no closer to getting it to work. The result is always the same. I get a false boolean and it generates the Error page that comes from this line of code:
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ok. Your code has a number of points where errors can creap in.
  • You don't check patientId properly - it needs to be a number to be the correct data type for you database field, but getParameters returns a String, so "ABC" is a possible value for you PK.
  • Along the same lines - getParameter returns null if the parameter does not exist, you don't check for that instead you use the value you believe has been returned from this method which is risking a NullPointerException.
  • You don't check your Primary Key already exists - so you are risking constrain violation exceptions from the DB.
  • trust us - this really would benefit from being a PreparedStatement


  • In your case, the most important line is:

    The SQLException will be being logged here, so look at it (and possibly post it if it doesn't make sense). Other people have mentioned this, but you might also want to change your insertIntoDB method to include the line:

    before you try to execute the statement. Then cut and paste the output into Access and run the SQL.

    Try this, and tell us what happens.
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    The compiler wont accept stringtoinsert before the execute statement. Ive tried this before to get an idea of what it is sending to the database.

    How do you run an SQL script in an Access database?

    As for exceptions with duplicate primary keys and wrong input values ill come to that when i can get some data to insert into the db. For now im just careful with my inputs. No use coding an exception handler if i cant get it to work as is.
    [ October 22, 2004: Message edited by: Paul Wright ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    This will compile:



    How do you run an SQL script in an Access database?

    As far as I remember (its been years since I used it), when you create/edit a query in Access, you can switch the view to SQL, which just gives you a text entry pane (Check the Access docs if I'm wrong). Paste your SQL in there and run it. Please be careful - Access will probably try to "correct" the SQL you enter by formatting it differently from how you might expect. Access's idea of what constitues good SQL differs from how everyone else in the world sees it, so its not a good standard to follow.

    And what is being outputed by:

    ?
    [ October 22, 2004: Message edited by: Paul Sturrock ]
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    It will only print the stack trace when there is an exception. And there isnt one. It simply replies to the boolean as false and initiates a web page that tells me an error has occured exactly as in the code:
    If false etc etc.

    Im trying to figure out another one to print the sql script instead of the error page it generates.

    I figured out how to run scripts in Access and can run an Insert statement ok.
    Maybe the servlet is messing with the sql script. It all looks ok though.

    Oh yeah and as for the System.out.printlin thing...... where is that going to print too? Its a servlet. Only output is via the output.println() but that wont work cos inside the insertDb method.
    [ October 22, 2004: Message edited by: Paul Wright ]
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    Ive adjusted the statement to this.

    The result when run is:
    An error occurred. Please try again later.
    INSERT INTO Admin VALUES ('h','h','h','h',66);

    This insert statement works fine when put into Access directly.
    Its not a connectoin problem cos it would throw an exception and print the stack trace. I got no idea whats happening.
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    You are making work for yourself here Paul. Ignore any error message you generate yourself - because you may well be replicating the same error in your error description code, and trying this also tends to mask what is actually going wrong. Don't try to interpret the error in code, just log it and read the message.


    It will only print the stack trace when there is an exception

    Look at the code of your insertIntoDB method - the only way this will return false is if any of your JDBC code throws an Exception. So if its returning false then trust me there is an Exception being thrown.


    Oh yeah and as for the System.out.printlin thing...... where is that going to print too?

    Its going to print to the standard output stream. Typically that will be wherever your servlet container is configured to log to (the console window you are running it from, or a log file specified in your servlet container's documentation etc. ). It is essential you work out where that is, because if you can't workout where the vast majority of your logging is going you may as well just give up.


    This insert statement works fine when put into Access directly

    If that is true and your insert statement looked like this: INSERT INTO Admin VALUES ('h','h','h','h',66); then you must be using an autonumbered PK. If that is the case, then you can't include the patientID in your insert statement.


    The only way you are going to progress with this Paul is by finding out what:

    is outputting. Do that and post it.
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    The INSERT statement i used worked and the PK is not autonumbered. The 66 i put in at the end was the patientId.

    As for the stack i think i found it:

    'k',888,'k','k','k','k','k','k','k'ERROR: Problems with adding new entry
    java.lang.NullPointerException
    at AddPatientServlet.insertIntoDB(AddPatientServlet.java:90)
    at AddPatientServlet.doPost(AddPatientServlet.java:60)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
    at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
    at java.lang.Thread.run(Unknown Source)


    It might as well be in German. But from what i can make out it its not compiling the statement correctly so its not executing it.
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    Ive added this code:


    And now i get this error:
    22/10/2004 22:44:19 org.apache.catalina.core.StandardContext reload
    INFO: Reloading this Context has started
    Problem closing the database
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at AddPatientServlet.init(AddPatientServlet.java:20)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1029)
    at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:687)
    etc
    etc
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    But from what i can make out it its not compiling the statement correctly so its not executing it

    Uncompiled Java applications do not run. And you can consider SQL as something which does not need compiled.

    The actual exception is a NullPointerException, thrown at line 90 of your code, in the insertIntoDB method (look at the StackTrace again - you'll see all that info once you get used to how its formatted).

    Once you know that the exception is a NullPointerException look at the API JavaDoc for the exception - for this one its:

    Thrown when an application attempts to use null in a case where an object is required. These include:

    -Calling the instance method of a null object.
    -Accessing or modifying the field of a null object.
    -Taking the length of null as if it were an array.
    -Accessing or modifying the slots of null as if it were an array.
    -Throwing null as if it were a Throwable value.

    Applications should throw instances of this class to indicate other illegal uses of the null object.


    If you look back at your code you can see that the only one which is a probable cause is the first one (Calling the instance method of a null object). Now I don't have line numbers for your code, but I would guess that its this line:

    thats doing this. So the connection is null. A glance further up your code to see how you create the connection shows me that you try to get the connection from the DriverManager, and should anything go wrong at this point you explicitly set the connection to null. This is a mistake - you need the connection object for you servlet to work.

    OK, if I'm right, your next place to look is the StackTrace which is logged in the servlet's init() method. See what that says.
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    Ok i got rid of the data source problem.

    Your spot on about the error line. It is the statement = connection.createStatement(); as you said.

    This is the latest error message in the stacktrace:

    'h',55,'h','h','h','h','h','h','h'
    ERROR: Problems with adding new entry
    java.lang.NullPointerException
    at AddPatientServlet.insertIntoDB(AddPatientServlet.java:96)
    at AddPatientServlet.doPost(AddPatientServlet.java:60)

    Line 96 is the statement as above.
    Line 60 is the Boolean success = insertDb (.........

    So one error is causiing the other i take it.

    With the init() method im not sure what your after?
    [ October 22, 2004: Message edited by: Paul Wright ]
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    This is your init() method. Look at what you are doing - you ask for a connection from the DriverManager. If this causes an exception you set the connection to null. How are you going to use a null connection?

    What you probably need to do instead of logging an expcetion here is rethrown it as a ServletException like this:

    You also want to look at the exception that is being logged here, since this is the reason why your connection is null.
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61457
        
      67

    Or better yet, throw an UnavailableException to take the servlet out of service.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    Paul Wright
    Ranch Hand

    Joined: Oct 05, 2004
    Posts: 42
    All fixed. Its got soemthing to do with the ODBC on my computer.

    I took it to another computer set it up the same and it works fine.

    Guys thanx for all the help. I appreciate it more than you know.

    Have a good one.
    [ October 22, 2004: Message edited by: Paul Wright ]
     
    wood burning stoves
     
    subject: Code error