Link to home
Start Free TrialLog in
Avatar of paduka
paduka

asked on

database connection...

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();
       }
   
   }
}

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Do you get any error from the jsp or the bean file?
Avatar of paduka
paduka

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of paskal
paskal
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of paduka

ASKER

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

Glad I could help :-)
Avatar of paduka

ASKER

I still have some confusion, what is the different between "executeQuery" and "executeUpdate".
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.
Avatar of paduka

ASKER

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.....

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 :-)