Solved

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

Posted on 2001-07-26
13
930 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
What You Need to Know when Searching for a Webhost Provider
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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