Solved

How to write a simple JSP page to add records to DB?

Posted on 2001-07-26
13
929 Views
Last Modified: 2013-12-24
My question is based more on JavaServer Pages area, however, CF has its own JRun server, and you guys have been great at answering many web application questions in the past, that I though I would post this question here as well.

Normally I write web apps in ColdFusion, but our group has decided to focus on JAVA.  So, I am trying
to get to the point where I can make a gradual leap to JSP and do both.

In doing so, I am trying to write a simple web app in JSP that connects to a database (db2), enters
new records, and edits these records.

I have the database connection down fine.  

Now I need to write new records to the db table, and I am stuck.  Can someone show some simple code
of writing/editing a new record to a database?  

Any assistance is appreciated!

This is my simple "add a record" page:

/***************************************/
<html>
<head>
<title>form test</title>
</head>
<body>
<h1>test</h1>

<form action="/app/temp/processform.jsp" method="post">

<br>
Enter your first name:
<input type="text" name="firstname"><br>

Enter your last name:
<input type="text" name="lastname"><br>

What is your IQ:&nbsp;&nbsp;&nbsp;&nbsp;
<input type="text" name="iq"><br>

<input type="submit" name="submit">
</p>
</form>
</body>
</html>
0
Comment
Question by:cgttsp01
  • 7
  • 5
13 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 6323058
First CF doesn't have a JRUN product Allaire does.. CF and JRUN can co-exist but really aren't the same.

Now for your sample code:
<%@ page import="java.sql.*"  language="java" %>
<%
    Connection conn = null;
    try {
      // Load Oracle driver
      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
      // Connect to the local database
       conn = DriverManager.getConnection
        ("jdbc:db2:thin:@dbhostname.local:portnum:dbname","usrName", "passwd");
      // turn off auto-commiting
      conn.setAutoCommit(false);
      String myQuery = "insert into yourTable (field1, field2) values(? , ?)";
      PreparedStatement ps = conn.prepareStatement(myQuery);
      // Perform insert
      int rowsAffected = stmnt.executeUpdate();
      ps.close();
      conn.commit();
      conn.close();
    } catch (Exception e) {
      e.printStackTrace(System.out);
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException sqle) { e.printStackTrace(System.out); }
 %>

I would recommend doing this as a servlet and limiting the display to the JSP to follow the well known MVC architecture/model.

HTH,
CJ
0
 

Author Comment

by:cgttsp01
ID: 6323991
No, that does not fix it.  I am still getting a "Compilation failed" error.

Any suggestions?
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6324012
could you post your JSP code?
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 

Author Comment

by:cgttsp01
ID: 6324256
This is the "action processing page" (for the lack of a better name) that is called from the simple form page I listed above in my initial question.

I have taken the code cheekycj suggested above and pluged in my stuff.
Thanks!

/************************/
<html>
<head>
     <title>test add</title>
</head>

<body>
<%@ page import="java.sql.*"  language="java" %>
<%
   Connection conn = null;
   try {
     // Load Oracle driver
     DriverManager.registerDriver ("ibm.sql.DB2Driver");
     // Connect to the local database
      conn = DriverManager.getConnection
       ("JDBC:db2os390:DB2TSG");
     // turn off auto-commiting
     conn.setAutoCommit(false);
     String myQuery = "INSERT INTO CBSTDTFI.UDT1 values(firstname , lastname , iq)";
     PreparedStatement ps = conn.prepareStatement(myQuery);
     // Perform insert
     int rowsAffected = stmnt.executeUpdate();
     ps.close();
     conn.commit();
     conn.close();
   } catch (Exception e) {
     e.printStackTrace(System.out);
     try {
       if (conn != null)
         conn.close();
     } catch (SQLException sqle) { e.printStackTrace(System.out); }
%>
</body>
</html>
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6324446
try this:
/************************/
<html>
<head>
    <title>test add</title>
</head>

<body>
<%@ page import="java.sql.*"  language="java" %>
<%
  String errorMsg = "";
  Connection conn = null;
  try {
    // Load Oracle driver
    DriverManager.registerDriver ("ibm.sql.DB2Driver");
    // Connect to the local database
     conn = DriverManager.getConnection
      ("JDBC:db2os390:DB2TSG");
    // turn off auto-commiting
    conn.setAutoCommit(false);
    String myQuery = "INSERT INTO CBSTDTFI.UDT1 (firstname , lastname , iq) values(? , ? , ?)";
    PreparedStatement ps = conn.prepareStatement(myQuery);
     ps.clearParameters();
    // Perform insert
     ps.setString(1, request.getParameter("firstname"));
     ps.setString(2, request.getParameter("lastname"));
     ps.setInt(3, Integer.parseInt(request.getParameter("iq")));
    ps.executeUpdate();
    ps.close();
    conn.commit();
    conn.close();
  } catch (SQLException e)
      {
        errorMsg = " Error: SQL error of: " + e.getMessage() + "<BR>";
      }
      catch (Exception e) {
        errorMsg = " Error: Generic error of: " + e.getMessage() + "<BR>";
      }
      finally {
      try {
        if (conn != null)
            conn.close();
      }
      catch(SQLException e) {
        errorMsg = " Error: Closing connection: " + e.getMessage() + "<BR>";
      }
    }
%>
<% if (errorMsg.length() > 0) { %>
<font color="#FF0000"><%= errorMsg %></font><br>
<% } %>
If no error messages display then Update was successful!
</body>
</html>

HTH,
CJ
0
 

Author Comment

by:cgttsp01
ID: 6324799
CJ,

Sorry, that did not work either.
Here is the error message.
Any suggestions?

/*******************************/
Unhandled error! You might want to consider having an error page to report such errors more gracefully
com.sun.jsp.JspException: Compilation failed
     at com.sun.jsp.compiler.Main.compile(Main.java:335)
     at com.sun.jsp.runtime.JspLoader.compile(JspLoader.java:90)
     at com.sun.jsp.runtime.JspLoader.loadJSP(JspLoader.java:221)
     at com.sun.jsp.runtime.JspServlet$JspServletWrapper.loadIfNecessary(JspServlet.java:99)
     at com.sun.jsp.runtime.JspServlet$JspServletWrapper.service(JspServlet.java:111)
     at com.sun.jsp.runtime.JspServlet.serviceJspFile(JspServlet.java:384)
     at com.sun.jsp.runtime.JspServlet.service(JspServlet.java:463)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:627)
     at com.ibm.servlet.engine.webapp.StrictServletInstance.doService(ServletManager.java:571)
     at com.ibm.servlet.engine.webapp.StrictLifecycleServlet._service(StrictLifecycleServlet.java:158)
     at com.ibm.servlet.engine.webapp.IdleServletState.service(StrictLifecycleServlet.java:286)
     at com.ibm.servlet.engine.webapp.StrictLifecycleServlet.service(StrictLifecycleServlet.java:104)
     at com.ibm.servlet.engine.webapp.ServletInstance.service(ServletManager.java:354)
     at com.ibm.servlet.engine.webapp.ValidServletReferenceState.dispatch(ServletManager.java:715)
     at com.ibm.servlet.engine.webapp.ServletInstanceReference.dispatch(ServletManager.java:641)
     at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebApp.java:1124)
     at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.dispatch(WebApp.java:1021)
     at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.forward(WebApp.java:988)
     at com.ibm.servlet.engine.srt.WebAppInvoker.handleInvocationHook(WebGroup.java:692)
     at com.ibm.servlet.engine.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:60)
     at com.ibm.servlet.engine.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:54)
     at com.ibm.servlet.engine.oselistener.OSEListenerDispatcher.service(OSEListener.java:316)
     at com.ibm.servlet.engine.oselistener.inproc.InProcAppServerDispatcher.service(InProcAppServerDispatcher.java:51)
     at com.ibm.servlet.engine.oselistener.inproc.InProcThreadPrivateData.dispatchServletRequest(InProcThreadPrivateData.java:116)
     at com.ibm.servlet.engine.oselistener.inproc.InProcPlugin.service(InProcPlugin.java:575)

0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6326931
I can't debug this unless you post the converted Java code that JRUN creates.

try creating a simple class file with the above code and see if it compiles using javac.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6341301
Any progress/updates?

CJ
0
 

Author Comment

by:cgttsp01
ID: 6341814
cheekycj,

No progress.  I am really, simply frustrated.  I don't know if I have just been so spoiled with CF that JSP and JAVA seems so unfriendly to me or what.

I just want to create a simple add and edit pages, and from there learn more about using JSP.

Disapointing!
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6341826
Are you able to do queries.. not updates or inserts just queries..

CJ
0
 

Author Comment

by:cgttsp01
ID: 6341996
Yes, I can do queries.

this is the code I use to list records.


<html>
<head>
<title>Database</title>
</head>
<%@ page import="java.sql.*" %>
<body>

<h2>test</h2>
<table border="0" bgcolor="whitesmoke">
<tr><th>First Name:<th>Last Name:<th>IQ:</tr>
<%

Class.forName("ibm.sql.DB2Driver");
       Connection con = DriverManager.getConnection("JDBC:db2os390:DB2TSG");
       
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT firstname, lastname, iq FROM CBSTDTFI.UDT1 ");
       
       

while (rs.next())
{
     String firstname = rs.getString("firstname");
     String lastname = rs.getString("lastname");
     String iq = rs.getString("iq");
         
     out.print("<tr>");
     out.print("<td>" + firstname + "</td>");
     out.print("<td>" + lastname + "</td>");
     out.print("<td>" + iq + "</td>");
     out.print("</tr>");
}
/*  where iq = '0' */
con.close();
%>

</table>
</body>
</html>

0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6342061
<html>
<head>
   <title>test add</title>
</head>

<body>
<%@ page import="java.sql.*"  language="java" %>
<%
 String errorMsg = "";
 Connection conn = null;
 try {
   // Load DB2 driver
   DriverManager.registerDriver ("ibm.sql.DB2Driver");
   // Connect to the local database
    conn = DriverManager.getConnection("JDBC:db2os390:DB2TSG");
   String myQuery = "INSERT INTO CBSTDTFI.UDT1 (firstname , lastname , iq) values(? , ? , ?)";
   PreparedStatement ps = conn.prepareStatement(myQuery);
    ps.setString(1, (String) request.getParameter("firstname"));
    ps.setString(2, (String) request.getParameter("lastname"));
    ps.setInt(3, Integer.parseInt(request.getParameter("iq")));
   ps.execute();
   conn.close();
 } catch (SQLException e)
     {
       errorMsg = " Error: SQL error of: " + e.getMessage() + "<BR>";
     }
     catch (Exception e) {
       errorMsg = " Error: Generic error of: " + e.getMessage() + "<BR>";
     }
     finally {
     try {
       if (conn != null)
           conn.close();
     }
     catch(SQLException e) {
       errorMsg = " Error: Closing connection: " + e.getMessage() + "<BR>";
     }
   }
%>
<% if (errorMsg.length() > 0) { %>
<font color="#FF0000"><%= errorMsg %></font><br>
<% } %>
If no error messages display then Update was successful!
</body>
</html>


I removed the autcommit and ps.clearParams and ps.close()..

I read that Allaire's JRUN isn't fully compatible with JDBC 2.0 so I stripped most of the iffy stuff out.

0
 
LVL 1

Accepted Solution

by:
g118481 earned 100 total points
ID: 6811313
send me your e-mail address and I will send you my code for a simple JSP web app.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question