Html Input to oracle through servlet and JSP output

Hi everyone,

The main aim is to use Html input text, which has to go into the oracle
Data base through the servlet and create a table with different columns corresponding
To the texts entered in the html and the output should be in the Jsp with the data inside the tables of the oracle database.

What I did , I created a servlet and through the servlet I can connect the oracle database and create the tables and can retrive the data on the console. But I want to connect through the Html to oracle through servlet and output in Jsp and I am using all this in eclipse and the server is tomcat.

Thanks
[ January 21, 2008: Message edited by: jumpa lahari ]

HTML can not connect directly with an Oracle database.
Why don't you want to go through a servlet?

Originally posted by Ben Souther:
HTML can not connect directly with an Oracle database.
Why don't you want to go through a servlet?

Hi Ben,

Yes i am connecting through the servlet and getting connected to the database and getting the data but i want to connect the Html form input to the servlet to the oracle database.

I am unable to make a link between the html and the servlet.

The code below is the servlet code to connect the jsp as well as connectio n to the oracle database.

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class DataBase extends HttpServlet {

public void doPost(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException
    
    {
 
     String name = request.getParameter("title");
     String name1=request.getParameter("body");
     
     request.setAttribute("name", name);
     request.setAttribute("name1",name1);
 
 getServletContext().getRequestDispatcher("/NotesDBJsp.jsp").forward(request, response);
 
 
    }

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException
    {
        getServletContext().getRequestDispatcher("/Jsp.jsp").forward(request, response);
    }

public static void main(String[] args) {

try {
      
  Connection con=null;
      
      System.out.println("Database Connecting...");
      
      Class.forName("oracle.jdbc.driver.OracleDriver");
      
      con=DriverManager.getConnection( "jdbc :-o	 racle:thin:@127.0.0.1:1521:XE","scott","tiger" );
     
      System.out.println("Data Base Connected");      
      
      Statement s1 = con.createStatement();
     
                             
      
            
      s1.executeUpdate("INSERT INTO Database (ID,TITLE,BODY) VALUES ('1','tera','map')"); 
      
        
     
   
 
      
          ResultSet rs = s1.executeQuery("SELECT  *  from Database");
   
      while (rs.next()) {
      
      String x = rs.getString("ID");
      
      String s = rs.getString("TITLE");
      
  String f = rs.getString("BODY");
     
     
      
     System.out.println("ID is : "+x);
         
     System.out.println("Title is : "+s);
         
     System.out.println("Body is : "+f);
           
          
               
                              }
          } 
    
 catch(Exception e)   {

e.printStackTrace();
 
                              }
                                                         }
                                 }

<form name="input" action="/Database/finish" method="post">

Can you post your servlet-entry and your servlet-mapping from your web.xml file.

Originally posted by Ben Souther:
<form name="input" action="/Database/finish" method="post">

Can you post your servlet-entry and your servlet-mapping from your web.xml file.

And your contextPath is "Database"?

If you're not sure, post the full path to your servlet's class file, starting with the drive letter.

Originally posted by Ben Souther:
And your contextPath is "Database"?

If you're not sure, post the full path to your servlet's class file, starting with the drive letter.

I dint get you can you please precise your question.

Thanks

What is the full path to your servlet's class file on your machine?

Example:
c:\tomcat\webapps\MyApp\WEB-INF\classes\us\souther\MyServlet.class

Originally posted by Ben Souther:
What is the full path to your servlet's class file on your machine?

Example:
c:\tomcat\webapps\MyApp\WEB-INF\classes\us\souther\MyServlet.class

Hi,

As i am using the eclipse , There is no root with the application name in the tomcat.

Originally posted by Ben Souther:
What is the full path to your servlet's class file on your machine?

Example:
c:\tomcat\webapps\MyApp\WEB-INF\classes\us\souther\MyServlet.class

Hi,

As i am using the eclipse , There is no root with the application name in the tomcat.

Can you connect to your HTML page with a browser?
If so, what URL are you using to do so?

Originally posted by Ben Souther:
Can you connect to your HTML page with a browser?
If so, what URL are you using to do so?

Hi,

This is the http://localhost:8082/DataBaseResult/DataBasehtml.html
[ January 21, 2008: Message edited by: jumpa lahari ]

Originally posted by jumpa lahari:

Hi,

This is the http://localhost:8082/DataBaseResult/DataBasehtml.html

OK, so your contextPath is DataBaseResult.
The URL in your HTML form doesn't match your contextPath:
<form name="input" action="/Database/finish" method="post">

Try:
<form name="input" action="/DataBaseResult/finish" method="post">

Originally posted by Ben Souther:

OK, so your contextPath is DataBaseResult.
The URL in your HTML form doesn't match your contextPath:
<form name="input" action="/Database/finish" method="post">

Try:
<form name="input" action="/DataBaseResult/finish" method="post">

Ok contextPath is nothing but the name of the application ok,

I even gave like form name="input" action="/DataBaseResult/finish" method="post"

but when I enter some text in the form only the Jsp is executing not the database.

Uhmmm.
Why is your database code in your servlet's main method?
Why does your servlet have a main method?

The container is never going to call your main method.

Originally posted by Ben Souther:
Uhmmm.
Why is your database code in your servlet's main method?
Why does your servlet have a main method?

The container is never going to call your main method.

Hi,

Ya servlet wont have the main method , thats the reason i thought placing the code to connect the database make no difference.

ok i will keep the database connection in a different java class.

The main problem is how to connect the data from the form text to the servlet.

So here I am able to connect the database but how to send the getParameter() into the database is the main problem.

You would either build up your SQL query with the values retrieved from the form parameters (not the best way):

Or learn about Prepared Statements.
The first method is error prone, can cause you grief if apostrophes or quotes are present in the form parameters, and opens you up to SQL Injection hacks.

Try Googling for "JDBC Tutorial PreparedStatement" to find tutorials and example code.
[ January 21, 2008: Message edited by: Ben Souther ]

Originally posted by Ben Souther:

You would either build up your SQL query with the values retrieved from the form parameters (not the best way):

String title = request.getParameter("title"); String sql = "SELECT * FROM books WHERE title = '" + title + "'"; statement.execute(sql);

Or learn about Prepared Statements.
The first method is error prone, can cause you grief if apostrophes or quotes are present in the form parameters, and opens you up to SQL Injection hacks.

Try Googling for "JDBC Tutorial PreparedStatement" to find tutorials and example code.

[ January 21, 2008: Message edited by: Ben Souther ]

Thanks Ben,

I go through them and come back to you in a day.

Originally posted by jumpa lahari:

Thanks Ben,

I go through them and come back to you in a day.

Hi Ben,

I am using the statement in the java class, I mean in the class which is able to access the oracle database and which is updating and also retrieving the data on to the console.

But my problem is how to make connection between the HTML form Input to the Oracle Database through the java or a servlet class.

As we are getting the parameters from the html text form into servlet by the

But I want this parameters to be added in the oracle database connection class

For example in the below statement I want my parameters be added to the values , so that I can insert data directly into the database from the html form through the servlet but i dont know how to make it can you please bring some light.

Thanks.

In my earlier post, I mentioned two ways.

1.) Use the variables created from getParameter calls when building up the SQL string.

2.) Use the methods provided by PreparedStatement (better route).
Have you looked into these?

Hi
Copy the code from the servlet into a new method in another new class, send all the parameters you fetch from request from the browser to this method, before that create an object of this new class in the servlet doGet or doPost whatever and then call this method with all the parameters. so 1. enter the values in the browser,2. servlet receives the request and creates an object of new class and sends the parameters to this method,3. this method creates a conn, stmt or pstmt, resultset and does the database part what ever you return back from the databse for either select or insert or update iterate through result set if any and store it in an vector or so and return this vector,4. this vector is used to create the jsp response and so use the printwriter out for writing the response onto the jsp page
thanks

Originally posted by Ben Souther:
In my earlier post, I mentioned two ways.

1.) Use the variables created from getParameter calls when building up the SQL string.

2.) Use the methods provided by PreparedStatement (better route).
Have you looked into these?

Hi Ben,

I tried to look into the preparedstatement, but i am unable to understand how to send the parameters from the html file.

Thanks

Originally posted by KALYAN ANAND:
Hi
Copy the code from the servlet into a new method in another new class, send all the parameters you fetch from request from the browser to this method, before that create an object of this new class in the servlet doGet or doPost whatever and then call this method with all the parameters. so 1. enter the values in the browser,2. servlet receives the request and creates an object of new class and sends the parameters to this method,3. this method creates a conn, stmt or pstmt, resultset and does the database part what ever you return back from the databse for either select or insert or update iterate through result set if any and store it in an vector or so and return this vector,4. this vector is used to create the jsp response and so use the printwriter out for writing the response onto the jsp page
thanks

Hi kalyan,

Thanks for your reply but i haven't got the result.

[ January 22, 2008: Message edited by: jumpa lahari ]
[ January 22, 2008: Message edited by: jumpa lahari ]

Originally posted by jumpa lahari:

Hi Ben,

I tried to look into the preparedstatement, but i am unable to understand how to send the parameters from the html file.

Thanks

Can you show me what code you've written so far for your prepared statement?

Originally posted by Ben Souther:

Can you show me what code you've written so far for your prepared statement?

Hi ben,

this is the code that i wrote ,

but i am getting error like invalid table name even though i created a table inside the oracle with the table name NOTESDB.

I know this is not sufficent for the connection of the html to the database connection class.
[ January 22, 2008: Message edited by: jumpa lahari ]

Look at whatever tutorial you're using again.
Specifically, look for question marks "?" inside the SQL statement.
Read up on those.

See if that gets you going in the right direction.

Originally posted by Ben Souther:
Look at whatever tutorial you're using again.
Specifically, look for question marks "?" inside the SQL statement.
Read up on those.

See if that gets you going in the right direction.

Ya i looked at the "?" , it is representing the setmethod for the int or string which are invoked by the object created by the preparedstatement, which i did in the code.

Originally posted by jumpa lahari:

Ya i looked at the "?" , it is representing the setmethod for the int or string which are invoked by the object created by the preparedstatement, which i did in the code.

No you didn't.
This is the last SQL statement that you've posted:

There are no question marks in that string.

This thread, at this point, is more about JDBC and PreparedStatements so I'm going to move it to our JDBC forum.

Originally posted by Ben Souther:
This thread, at this point, is more about JDBC and PreparedStatements so I'm going to move it to our JDBC forum.

Thanks for moving into the JDBC,

Ya i havent kept any "?" inside the prestatement now i gave like this

I see a few problems in this code snippet.
1) That is not a valid SQL statement. There is a missing = after the word title.
2) There are only two question marks in the query, but the prepared statement sets three parameters.
3) There is no where clause. Do you really want to update every single row in the table with those values?

Originally posted by Jeanne Boyarsky:
PreparedStatement ps1 = con.preparedStatement("UPDATE NOTESDB SET ID = ? TITLE ? "); ps1.setInt(1, 23); ps1.setString(2, "tom"); ps1.setString(3, "cat"); ps1.executeUpdate();
I see a few problems in this code snippet.
1) That is not a valid SQL statement. There is a missing = after the word title.
2) There are only two question marks in the query, but the prepared statement sets three parameters.
3) There is no where clause. Do you really want to update every single row in the table with those values?

Hi,

1)i gave like title =
2)i commented in my code the third statement from the above code.
3)Ya we have to give the where clause , and i tried to give the like

I dont think this is correct one in the prepared statement.

Originally posted by jumpa lahari:

1)i gave like title =
2)i commented in my code the third statement from the above code.
3)Ya we have to give the where clause , and i tried to give the like

Can you post the new code/prepared statement calls? It's requiring some imagination to figure out what you are looking at.

Originally posted by Jeanne Boyarsky:

Can you post the new code/prepared statement calls? It's requiring some imagination to figure out what you are looking at.

Hi,

I gave the code like

But the main issue is that, i am unable to find a solution to pass the paramaters when i enter a text in the html file into oracle database through the servlet, please bring some light ,

PreparedStatement ps1 = con.prepareStatement("UPDATE NOTESDB SET ID = ? WHERE TITLE =�tera� "); ps1.setInt(1, 23); ps1.setString(2, "tom");

You set two parameters, but the sql statement only contains 1 question mark.
Every ps1.set..() binds a parameter to a question mark. Your line 'ps1.setString(2, "tom")' can't be bound.

Regards, Jan
[ January 24, 2008: Message edited by: Jan Cumps ]