Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2001-07-26
13
Medium Priority
?
934 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

688 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