Code error

[Paul Wright]

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.

//AddPatientServlet.java
 
   import java.io.*;
   import javax.servlet.*;
   import javax.servlet.http.*;
   import java.util.*;
   import java.sql.*;
 
   public class AddPatientServlet extends HttpServlet {
      private Statement statement = null;
      private Connection connection = null;
      private String URL = "jdbc :-o	 dbc :-P	 GH";
 
      public void init( ServletConfig config )
      throws ServletException
      {
         super.init( config );
 
         try {
            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
            connection =
               DriverManager.getConnection( URL, "", "" );
         }
            catch ( Exception e ) {
               e.printStackTrace();
               connection = null;
            }
      }
 
      public void doPost( HttpServletRequest req,
                        HttpServletResponse res )
      throws ServletException, IOException
      {
 
         String name, address, username, password, medicareNo, telethonMemNo,
         privateMem, telethonSub;
 
           name = req.getParameter("name");
           address = req.getParameter("address");
           medicareNo = req.getParameter("medicareNo");
           privateMem = req.getParameter("privateMem");
           telethonSub = req.getParameter("telethonSub");
           telethonMemNo = req.getParameter("telethonMemNo");
           username = req.getParameter("username");
           password = req.getParameter("password");
 
           PrintWriter output = res.getWriter();
           res.setContentType("text/html");
 
           if (name.equals("") ||
               address.equals("") ||
               medicareNo.equals("") ||
               privateMem.equals("") ||
               telethonSub.equals("") ||
               username.equals("") ||
               password.equals("")) {
             output.println("<H3> Please click the back " +
                            "button and fill in all " +
                            "fields.</H3>");
             output.close();
             return;
           }
 
           boolean success = insertIntoDB(
               "'" + name + "','" + address + "','" + medicareNo +
               "','" + privateMem + "','" + telethonSub +
               "','" + telethonMemNo + "','" + username +
               "','" + password + "'");
           if (success)
             output.print("<H2>Thank you " + name +
                          " for registering.</H2>");
           else // THIS IS THE ERROR THAT IS DISPLAYED SO THE PROBLEM MUST                     //                 BE AROUND HERE ?????????
             output.print("<H2>An error occurred. " +
                          "Please try again later.</H2>");
           output.close();
       }
 
     private boolean insertIntoDB( String stringtoinsert )
     {
        try {
           statement = connection.createStatement();
           statement.execute(
                            "INSERT INTO Patient values (" +
                            stringtoinsert + ");" );
           statement.close();
        }
           catch ( Exception e ) {
              System.err.println(
                                "ERROR: Problems with adding new entry" );
              e.printStackTrace();
              return false;
           }
 
        return true;
     }
 
     public void destroy()
     {
        try {
           connection.close();
        }
           catch( Exception e ) {
              System.err.println( "Problem closing the database" );
           }
     }
  }
 

[Srinivasa Raghavan]

Whats the exception are you getting ?
Srini

[Shailesh Chandra]

Problem seems to be with your insert statement.

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

[David O'Meara]

"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]

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]

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:

INSERT INTO foo (bar, baz, bal) VALUES ("foo", 17, "bar);

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

And follow David O's suggest.

[Paul Wright]

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]

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]

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)

[M. Gagnon]

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]

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

[Paul Wright]

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]

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]

That version above is an earlier version. This is the lastest version with the primary key.

import java.io.*;
   import javax.servlet.*;
   import javax.servlet.http.*;
   import java.util.*;
   import java.sql.*;
   
   public class AddPatientServlet extends HttpServlet {
      private Statement statement = null;
      private Connection connection = null;
      private String URL = "jdbc :-o	 dbc :-P	 GH";
 
      public void init( ServletConfig config )
      throws ServletException
      {
         super.init( config );
 
         try {
            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
            connection =
               DriverManager.getConnection( URL, "", "" );
         }
            catch ( Exception e ) {
               e.printStackTrace();
               connection = null;
            }
      }
 
      public void doPost( HttpServletRequest req,
                        HttpServletResponse res )
      throws ServletException, IOException
      {
         String name, address, medicareNo, privateMem,
         telethonSub, telethonMemNo, username, password;
         String patientId;
 
         patientId = req.getParameter( "PatientId" );
         name = req.getParameter( "Name" );
         address = req.getParameter( "Address" );
         medicareNo = req.getParameter( "MedicareNo" );
         privateMem = req.getParameter( "PrivateMem" );
         telethonSub = req.getParameter( "TelethonSub" );
         telethonMemNo = req.getParameter( "TelethonMemNo" );
         username = req.getParameter( "Username" );
         password = req.getParameter( "Password" );
 
         PrintWriter output = res.getWriter();
         res.setContentType( "text/html" );
 
         if ( name.equals( "" ) ||
            address.equals( "" ) ||
            medicareNo.equals( "" ) ) {
            output.println( "<H3> Please click the back " +
                          "button and fill in all " +
                          "fields</H3>" );
            output.close();
            return;
         }
 
         boolean success = insertIntoDB(
                                        patientId+ ", '"+ name +"', '" +
                                      address + "', '" + medicareNo + "', '" +
                                     privateMem + "', '" + telethonSub + "', '" +
                                    telethonMemNo + "', '" + username + "', '" +
                                   password + "'");
 

 
         if ( success )
            output.print( "<H2>Thank you " + name +
                        " for registering.</H2>" );
         else {
            output.println( "<H2>An error occurred. Please try again later.</H2>");
            // To find out what data is being processed
            output.println( "Insert Statement result :" + success );
            output.println( "<br>" + patientId + "<br>" + name + "<br>" );
            output.println( address + "<br>" + medicareNo + "<br>" );
            output.println( privateMem + "<br>" + telethonSub + "<br>" );
            output.println( telethonMemNo + "<br>" + username +"<br>" );
            output.println( password + "<br>" + statement );
            
         }
         output.close();
      }
 
      private boolean insertIntoDB( String stringtoinsert )
      {
         try {
            statement = connection.createStatement();
            statement.execute(
            "INSERT INTO Patient values (" + stringtoinsert + ")" );
            statement.close();
         }
            catch ( Exception e ) {
               System.err.println(
                                 "ERROR: Problems with adding new entry" );
               e.printStackTrace();
               return false;
            }
 
         return true;
      }
 
      public void destroy()
      {
         try {
            connection.close();
 
         }
            catch( Exception e ) {
               System.err.println( "Problem closing the database" );
            }
      }
   }

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:

else {
            output.println( "<H2>An error occurred. Please try again later.</H2>");

[Paul Sturrock]

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:

e.printStackTrace();

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:

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

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]

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]

This will compile:

private boolean insertIntoDB( String stringtoinsert ) {
    try {
      statement = connection.createStatement();
      String sqlStatement = "INSERT INTO Patient values (" + stringtoinsert + ");" );
      System.out.println("SQL Statement => " + sqlStatement);
      statement.execute(sqlStatement);
      statement.close();
    } catch ( Exception e ) {
       e.printStackTrace();               
       return false;            
    }
 return true;      
}

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:

e.printStackTrace();

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

[Paul Wright]

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]

Ive adjusted the statement to this.

boolean success = insertIntoDB("INSERT INTO Admin VALUES ('"+name+"','"+address+"','"+
                                      username+"','" + password + "'," + adminId + ");");
// And added this to the error message
        if ( success )
            output.print( "<H2>Thank you " + name +
                        " for registering.</H2>" );
         else {
            output.println( "<H2>An error occurred. Please try again later.</H2>");
            output.println( "INSERT INTO Admin VALUES ('"+name+"','"+address+"','"+ username+"','" + password + "'," +
                                   adminId + ");");

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]

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:

e.printStackTrace();

is outputting. Do that and post it.

[Paul Wright]

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]

Ive added this code:

private boolean insertIntoDB( String stringtoinsert )
      {
         try {
           
           statement = connection.createStatement();
           String sqlStatement = "INSERT INTO Patient values (" + stringtoinsert + ");" ;
           System.out.println("SQL Statement => " + sqlStatement);
           statement.execute(sqlStatement);
           statement.close();
           //System.out.println( stringtoinsert ); 
           //statement = connection.createStatement();
           //statement.execute("INSERT INTO Patient VALUES (" + stringtoinsert + ");" );
           //statement.close();
         }

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]

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:

statement = connection.createStatement();

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]

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]

public void init(ServletConfig config) throws ServletException{
     super.init( config );          
     try {            
           Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
           connection = DriverManager.getConnection(
                                 URL, "", "" );         
         } catch ( Exception e ) {
            e.printStackTrace();               
            connection = null;            
      }
}

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:

public void init(ServletConfig config) throws ServletException{
     super.init( config );          
     try {            
           Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
           connection = DriverManager.getConnection(
                                 URL, "", "" );         
         } catch ( Exception e ) {
            throw new ServletException(e);
      }
}

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]

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

[Paul Wright]

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 ]