update to database problem

[Bob Young]

I could use some help on the following. I am reading up on servlets and JDBC via Java Servlet Programming by Jason Hunter. I have looked at this so long I just can't see my error, but there must be something wrong! I am trying to makeup a simple servlet example using a html form to pick up a parameter from the user and pass it on to the servlet. The servlet should perform an update on the database. When I look at the database nothing is being added. The database is an Access DB and it is hooked up correctly. I can use another app to look at the database and perform a select * from Table1 and see that nothing is added. I am using Tomcat 4.0.1. I have tried doPost and doGet neither worked. I have tried text and int for the parameter. The database is one table called of all things Table1 with the first column SSNum as Number and the next three columns as Parameter1, Parameter2, Parameter3 all as text. Got any ideas? Thanks.

package temp;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class TryDB extends HttpServlet {

String sourceURL = "jdbc:odbc:Db2";

  public void doGet(HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException {
    
    Connection dbConn = null;
    Statement stmt = null;
    
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");      
      dbConn = DriverManager.getConnection(sourceURL);
      stmt = dbConn.createStatement();
      int p1 = Integer.parseInt(req.getParameter("param1"));
      int rowsAdded = stmt.executeUpdate("INSERT INTO Table1 (SSNum) VALUES (p1)");
    }
    catch(ClassNotFoundException cnfe)
    {
      System.err.println(cnfe);
    }
    catch(SQLException sqle)
    {
      System.out.println("SQLException: " + sqle.getMessage());
      sqle.printStackTrace();
    
    }
    finally {
    try {
    if (dbConn != null) dbConn.close();
    }
    catch(SQLException ignored) {}
    }
  }
}

(Marilyn added code tags and disabled smilies)
[ September 29, 2003: Message edited by: Marilyn de Queiroz ]

[Marilyn de Queiroz]

Your code would be easier to read if you use code tags. Also, I suggest that you decide whether you want to use

the K&R style of curly braces
  
xxxxxx{
    stuff
}
   
 or
  
xxxxxxx
{
    stuff
}

and not intermix the two styles.

[Sainudheen Mydeen]

Hi
Can you try this
replace

Connection dbConn = null;    
      Statement stmt = null;        
      try {      
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                  
            dbConn = DriverManager.getConnection(sourceURL);      
            stmt = dbConn.createStatement();      
            int p1 = Integer.parseInt(req.getParameter("param1"));      
            int rowsAdded = stmt.executeUpdate("INSERT INTO Table1 (SSNum) VALUES (p1)");    
      }  

to

<font size="2"> Connection dbConn = null;    
        PreparedStatement pstmt = null;        
        try {      
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");            
             dbConn = DriverManager.getConnection(sourceURL);  
             int p1 = Integer.parseInt(req.getParameter("param1"));    
             pstmt = dbConn.prepareStatement("INSERT INTO Table1 (SSNum) VALUES (?)");
             pstmt.setInt(1,p1);                         
             int rowsAdded = pstmt.executeUpdate();    
        } </font>

-------------
Sainudheen
[ September 29, 2003: Message edited by: Sainudheen Mydeen ]

[Bob Young]

Thanks for replying Sainudheen Mydeen. I was able to get this going by using a preparedstatement, just as you suggested. I know that not all databases are able to use preparedstatement, in particular MySql. I was hoping that somebody could suggest a way to make this work without using a preparedstatement.
However I am about to become bald from pulling out my hair trying to understand this database stuff. Can somebody explain why the following is happening. I am using Access as the database.
This code from a book almost runs but leaves out the last row of data.

import java.sql.*;
public class BuildTables {
public static void main(String[] args) {
try {
String username = "guest";
String password = "guest";
String url = "jdbc :-o	 dbc :mrgreen: b2";
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

String[] SQLStatements = { 
"CREATE TABLE testTable (Num int, name char(20))", 
"INSERT INTO testTable VALUES(123, 'name1')", 
"INSERT INTO testTable VALUES(987, 'name2')", 
"INSERT INTO testTable VALUES(555, 'name3')"
};

Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
for(int i = 0; i < SQLStatements.length; i++) {
stmt.executeUpdate(SQLStatements[i]);
System.out.println(SQLStatements[i]);
}
}
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe);
}
catch (SQLException sqle) {
System.err.println(sqle);
}
}
}

The data for insert values 123, name1 and 987, name2 are added to the database but 555, name3 is not! Now I would not mind this happening with my checking account on the debit side, but it would definitely not be good for the credit side. Now if I try much the same code with a servlet, it does not work at all.

package temp;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class TryDB2 extends HttpServlet {

String username = "guest";
String password = "guest";
String url = "jdbc :-o	 dbc :mrgreen: b2";
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

  public void doPost(HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException {
  
    Connection dbConn = null;
    Statement stmt = null;
    try {
    Class.forName(driver);
    dbConn = DriverManager.getConnection(url, username, password);
    stmt = dbConn.createStatement();
    stmt.executeUpdate("CREATE TABLE testTable (Num int, name char(20))");
      stmt.executeUpdate("INSERT INTO testTable VALUES (123, 'name1')");
      stmt.executeUpdate("INSERT INTO testTable VALUES (987, 'name2')");
      stmt.executeUpdate("INSERT INTO testTable VALUES (555, 'name3')");
    }
    catch(ClassNotFoundException cnfe){
      System.err.println(cnfe);
    } 
    catch(SQLException sqle) {
      System.out.println("SQLException: " + sqle.getMessage());
      sqle.printStackTrace();
    }
    finally {
    try {
    if (dbConn != null) dbConn.close();
    }
    catch(SQLException ignored) { }
    }
    
  }
}

Why oh why will this almost work as in the first code example and utterly fail on the second? The only real difference that I see is the servlet

[Sainudheen Mydeen]

Hi Bob Young
I tried to run your program connecting to my Oracle datbase. Both work fine. All three rows inserted. If you are calling your servlet by typing the URL in browser's address bar, change the method name to doGet.
-----------------
Sainudheen