database connection...

paduka
paduka used Ask the Experts™
on
Hi All,
I'm a beginner in JSP and I've problem to connect to a database. For your information, I use <jsp useBean> syntax for the connection, should I need additional file or tool if I want to use this syntax? I'm using Tomcat 4.0, j2sdk1.4.0 and MS SQL 2000 database. Or maybe anybody can tell we what wrong with my coding. I've try  to change the syntax many times but still fail to write to a database. My coding is like following....

1. ServerTest.jsp

<html>
<head>
<title>Server Test</title>
</head>
<body>
<center>
<b><font size="5" color="#000066">Server Test</font></b>
<br>
<form name = "form2" method = "POST" action="InsertServer.jsp">
<p><table border = "0">
<tr>
      <td>Server name</td>
      <td><input type = "text" name = "serv" size = "20"></td>
</tr>
<tr>
      <td>IP Address</td>
      <td><input type = "text" name = "ip" size = "20"></td>
</tr>
</table>
<input type = "submit" name = "submit" value = "Add">
</form>
</body>
</html>


2. InsertServer.jsp

<jsp:useBean class="database.DBConnect1" id="DC1" scope="session" />
<HEAD>
<TITLE>
Add Server
</TITLE>
</HEAD>
<BODY>
<H1>
Add Server1
</H1>
<%
String s = request.getParameter("serv");
String i = request.getParameter("ip");

if ( (s != null) && (i != null) )
 {  
  String aSQL = "INSERT INTO server (host, ipAddress) VALUES ('" + s + "','" + i + "')" ;
            
    try
    {
     System.out.println("SQL=" + aSQL);
     DC1.executeUpdate(aSQL);
     %>Server information inserted: <BR>Server Name: <%
     out.print(s);
     %>, IP Address: <%
     out.print (i) ;
     }
    catch (Exception e)
    {
    %>There was a problem with your insert, please go back and check your values.<%
    e.printStackTrace();
    }
 }
%>
<BR>
<BR>
Back to <a href="ServerTest.jsp">Add another Server</a>
</BODY>
</HTML>


3. DBConnect1.java

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.ResultSetMetaData;


public class DBConnect1
{
 private ResultSet  rs  = null;
 private ResultSet  rs_date = null;
 private Connection con = null;
 private Statement stmt = null;
 private ResultSet result = null;
 private String driverName =  "com.microsoft.jdbc.sqlserver.SQLServerDriver"; // JDBC driver
 private String connectionURL = "jdbc:microsoft:sqlserver://TESTSERVER1:1433;User=sa;Password=bnmckl1;DatabaseName=elms";// JDBC connection
   
  // Constructor
 public DBConnect1() throws java.sql.SQLException, java.lang.ClassNotFoundException{}

 // Execute SQL Statement
 public ResultSet executeQuery(String aSql) throws java.sql.SQLException, java.lang.ClassNotFoundException {
   try{
        result = stmt.executeQuery(aSql);
   }
   catch (Exception ex){
   }
   return result;
 }
 
 // Execute SQL Statement
 public void executeUpdate(String aSql) throws java.sql.SQLException, java.lang.ClassNotFoundException {
   try{
        stmt.executeUpdate(aSql);
   }
   catch (Exception ex){
   }
 }  

 // Open Database Connection
 public void openCon() throws java.sql.SQLException, java.lang.ClassNotFoundException {
      Class.forName(driverName);
      con = DriverManager.getConnection(connectionURL);
      stmt = con.createStatement();
    }

    // Close Database Connection
 public void closeCon() throws java.sql.SQLException {
       

                if (rs != null)
                {
         result.close();
       }
       
       if (stmt != null)
       {
         stmt.close();
                }
               
       if (!con.isClosed())
       {
            con.close();
       }
   
   }
}

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
Do you get any error from the jsp or the bean file?

Author

Commented:
ryancys,
I've don't get any error from JSP bean file. Actually, when I run the program, it can display the data that I entered seems the program has been executed correctly but when I check the database the data not there. I've try to impliment the connection directly into my jsp program without using the connection class (<jsp:useBean class="database.DBConnect1" id="DC1" scope="session" />) and it's work. But this method is not effective because I've to include the connection syntax everytime I want to manipulate the database. So I think maybe I've a problem with the connection class or maybe I dont have the file or tool to support the class or bean ussage. Do you have any sollution for the problem?
Commented:

You should print the errorStackTraces in your DBConnect class.
I think errrors occur when your update is executed but they don't show in ypour screen or anywhere else.

Furthermore, with a quick scan I couldn't see where the openCon was called so is the connection made in first place?
Otherwise the update() will raise a nullpointer that doesn't showup.....

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Author

Commented:
paskal,
finally my program can run properly, thank you......:)

Commented:

Glad I could help :-)

Author

Commented:
I still have some confusion, what is the different between "executeQuery" and "executeUpdate".

Commented:
This is what the javadoc says:

executeQuery:

public ResultSet executeQuery(String sql)
                       throws SQLException
Executes an SQL statement that returns a single ResultSet object.
Parameters:
sql - typically this is a static SQL SELECT statement
Returns:
a ResultSet object that contains the data produced by the given query; never null
Throws:
SQLException - if a database access error occurs

--------------------------------------------------------------------------------

executeUpdate
public int executeUpdate(String sql)
                  throws SQLException
Executes an SQL INSERT, UPDATE or DELETE statement. In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed.
Parameters:
sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing
Returns:
either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
Throws:
SQLException - if a database access error occurs


Hope that helps to understand.
Main difference is the returning value.

Author

Commented:
I have another question, I have a problem to retrieve data from the database, can I use the same class that I use for insert data into database, or should I put other command in my program. I found the example but it use Vactor and I've try to impliment it in my program but fail.....

Commented:

Yes, you can use the same class for inserts, as far as I can see it.

You should post the code you have and the error you are geting. ANd preferably in a new question so this one can be closed :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial