Solved

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

Posted on 2001-07-26
13
926 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
 

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
Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

 
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

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now