Link to home
Start Free TrialLog in
Avatar of override_y2k
override_y2k

asked on

Creating a JDBC connection in a jsp

when accessing a mysql db directly from a jsp are try and catch loops required within the jsp?

Heres my code:

****************************
dbUpdateQuery.jsp
****************************



<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>


<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">
  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <input type="text" name="title" value = "hello" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Description:</b></td>
    <td align="center"><div align="left">
        <input type="text" name="description" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td width="127"><b>Location: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="location" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Weather: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="weather" size="20" maxlength="20" class="input">
    </div></td>
  </tr>
  <tr>
    <td><b>Mood: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="mood" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Date: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="date" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Time: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="time" size="20" maxlength="10">
    </div></td>
  </tr>
  <tr valign="middle">
    <td><b>InOut:</b></td>
    <td><select name="inout">
        <option selected> Indoors
          <option> Outdoors
          <option> Both
          </select></td>
  </tr>
</table>
<tr><td colspan="2">
<table width="222">

<tr>
  <td width="252" colspan="2" align="center"><input type=submit  value="Submit Details!"></td>
</tr>
<tr><td colspan="2" align="center"><input type=reset value=" Reset Form "></td></tr>

</table> </FORM><br><br>
</body>

</html>
Avatar of override_y2k
override_y2k

ASKER

That works fine, but when i add this line it throws an error:

<% while(results.next()){ %>

***************************
File that isn't working.jsp
***************************



<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>

<%    while(results.next()){ %>


<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">
  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <input type="text" name="title" value = "hello" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Description:</b></td>
    <td align="center"><div align="left">
        <input type="text" name="description" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td width="127"><b>Location: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="location" size="20" maxlength="50">
    </div></td>
  </tr>
  <tr>
    <td><b>Weather: </b> </td>
    <td align="center"><div align="left">
        <input type="text" name="weather" size="20" maxlength="20" class="input">
    </div></td>
  </tr>
  <tr>
    <td><b>Mood: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="mood" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Date: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="date" size="20" maxlength="20">
    </div></td>
  </tr>
  <tr>
    <td><b>Time: </b> </td>
    <td align="center"><div align="left">
        <input type =" text" name="time" size="20" maxlength="10">
    </div></td>
  </tr>
  <tr valign="middle">
    <td><b>InOut:</b></td>
    <td><select name="inout">
        <option selected> Indoors
          <option> Outdoors
          <option> Both
          </select></td>
  </tr>
</table>
<tr><td colspan="2">
<table width="222">

<tr>
  <td width="252" colspan="2" align="center"><input type=submit  value="Submit Details!"></td>
</tr>
<tr><td colspan="2" align="center"><input type=reset value=" Reset Form "></td></tr>

</table> </FORM><br><br>
</body>

</html>
Avatar of override_y2k
override_y2k

ASKER

These are the errors it throws:


Generated servlet error:
    [javac] Compiling 1 source file
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:205: 'catch' without 'try'
    [javac]     } catch (Throwable t) {
    [javac]       ^
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:32: 'try' without 'catch' or 'finally'
    [javac]     try {
    [javac]     ^
    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:215: '}' expected
Avatar of girionis
girionis
Flag of Greece image
Hi override_y2k,

> <% while(results.next()){ %>

you need to close the while loop. You will also need to try catch any SQL excpetions that might occur.

Regards
Avatar of sompol_kiatkamolchai
sompol_kiatkamolchai
Flag of Thailand image
Yes girionis is right.

No matching } with while loop and I found that your source code does not make sense in business logic. you make a query from database but you didn't use the resultset to render data.

you may do

<table>
<tr>
  <td>column_name1</td>
  <td>column_name2</td>
</tr>
<%
while(results.next()) {
%>
<tr>
  <td><%=results.getString("column_name1")%></td>
  <td><%=results.getString("column_name2")%></td>
</tr>
<%
}
%>
</table>
Avatar of override_y2k
override_y2k

ASKER

Sorry typo in my question, i had the while loop closed.

I do have functionality with the result set.

Heres a snippet for ease of use:

*********************

<%@ page import="java.sql.*" %>
<%@ page import="dbAddQuery.*" %>


<html>




<head><title>Please enter search criteria</title>

<script type="text/javascript">
<!--



// -->
</script>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>


<FORM  action=" dbAddQuery.jsp" method="post">

<table width="240" border="0" cellpadding="5" cellspacing="0" background="backdrop.jpg">
<tr>
<td>
<br>



<table width="81%" border="0" cellpadding="0" cellspacing="2" align="center">
<tr><td colspan="2" height="20" align="center"><div align="left"><font size="3" color="#0000aa"><b> Enter Search Criteria</b></font></div></td></tr>


<tr><td>





</td></tr></table>



<br>

<table width="100%" border="0" cellpadding="0" cellspacing="2" align="center">

<%    

      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://csserver.ucd.ie/imcdonnell";    
      String userName = "imcdonnell";    
      String passWord = "tuesday";    
      Connection con;    
      con = DriverManager.getConnection(url, userName, passWord);
      String sql = "select * from queries";  
      Statement stmt = con.createStatement();    
      ResultSet results = stmt.executeQuery(sql);
      
%>

<%    while(results.next()){ %>


  <tr>
    <td><b>Title:</b></td>
    <td width="632" align="center"><div align="left">
        <%input type="text" name="title" value = results.getString(1) size="20" maxlength="50"%>
    </div></td>
  </tr>
<% } %>








************************************************

The problem seems to be with this line:

<%input type="text" name="title" value = results.getString(1) size="20" maxlength="50"%>
Avatar of override_y2k
override_y2k

ASKER

And this is the error report:

Generated servlet error:    [javac] Compiling 1 source file    [javac] /usr/local/jakarta-tomcat-4.1.29/work/Standalone/localhost/~imcdonnell/dbAddQuery/dbUpdateQuery_jsp.java:102: ';' expected    [javac] input type="text" name="title" value = results.getString(1) size="20" maxlength="50"    [javac]                   ^    [javac] 1 error
Avatar of girionis
girionis
Flag of Greece image
> <%input type="text" name="title" value = results.getString(1) size="20"
> maxlength="50"%>

change it with

<input type="text" name="title" value = results.getString(1) size="20" maxlength="50">

it is HTML code not Java. Yo do not need the scriplet tags <% and %>
ASKER CERTIFIED SOLUTION
Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of override_y2k
override_y2k

ASKER

Excellent thx a mill. Thats where i was goin wrong alright!

Cheers for that, much appreciated!
Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image
NO problem.
Glad I could help.
Ghost
Avatar of override_y2k
override_y2k

ASKER

1 quick small problem, when it displays result.getString in the field, it only shows the 1st word, where as many of the fields should have several words.

Any ideas?
Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image
umm are all of the words in the same field in the results resultset? you code is saying to pull only the first coloum.
does that help?
Ghost
Avatar of override_y2k
override_y2k

ASKER

well i have results.getString(2) etc all the way up to the number of fields that i have in my db table/html form.

Like say for example my db field called title has value blah blah.

When i display the details in the html form, it has title : blah
rather than title blah blah

Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image
maybe something to do with your size or length?
Ghost
Avatar of override_y2k
override_y2k

ASKER

what are you trying to say :)

I dont think its anything to do with that, its only minor prob anyway, so i should be able to sort it.

Thx again for the help.
Avatar of copyPasteGhost
copyPasteGhost
Flag of Canada image
sure. no problem
Ghost