Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-07-26
13
Medium Priority
?
936 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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 400 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

885 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