?
Solved

JSP and SELECT STATEMENT

Posted on 2003-03-24
22
Medium Priority
?
291 Views
Last Modified: 2010-04-01
The code below is what i have been using to try and get a connection along with some information with the database. The only problem that i can not get passed results = sql.executeQuery(); I have added println statement and i only get up to 3 which is before results = sql.executeQuery(); What is my mistake??

When i run the query, SELECT member_id FROM member WHERE user_name = + '" + name + "'" and hard code the name it does get back one number. So i don't know what the problem is!

Please correct the file below to show me where i have gone wrong..thank-you in advance



html>
head>
%@ page
     import = "java.io.*"
     import = "java.lang.*"
     import = "java.sql.*"
%>
title>
TEST
/title>
/head>
body>
%
     String     name;
     Connection dbconn;
     ResultSet results;
     PreparedStatement sql;
     try
     {
          Class.forName("org.gjt.mm.mysql.Driver").newInstance();
          try
          {
               int mem;
               boolean     doneheading = false;
               out.println("1");
               out.println();
               dbconn = DriverManager.getConnection("jdbc:mysql://localhost/nutrition","saundersk","kevin");
         
               out.println("2");
               name = request.getParameter("name");
               sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE user_name = + '" + name + "'");
               out.println("3");
         
               results = sql.executeQuery();
               
               out.println("4");
               while(results.next())
               {
                    out.println("5");
                    if(! doneheading)
                    {
                         out.println("6");
                         out.println("table border=2>");
                         doneheading = true;
                         
                    }
                                out.println("7");
                                mem = results.getInt("member_id");
                    out.println("tr>td>" + mem);
         

               }
               if(doneheading)
               {
                    out.println("8");
                    out.println("/table>");
               }
               else
               {
                    out.println("9");
                    out.println("No matches for ");
               }
          }          
          catch (SQLException s)
          {
               out.println("SQL Error
");
          }
     }
     catch (ClassNotFoundException err)
     {
          out.println("Class loading error");
        }
%>
/body>
/html>
0
Comment
Question by:KTS
[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
  • 9
  • 6
  • 5
  • +2
22 Comments
 
LVL 1

Expert Comment

by:victorli
ID: 8198851
IF you hard code the name and it works, may be the problem is the value of the premeter "name".

Add an extra line like the follow:

 out.println("2");
 name = request.getParameter("name");
 out.println("name="+name); //add this

YOu can check if the name returned a correct value.

 
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199550
try after modifing these two lines :
    name = request.getParameter("name");
              sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE user_name = + '" + name + "'");

with these:

name = (request.getParameter("name").trim()).toUpperCase();
              sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE UPPER(user_name) = + '" + name + "'");


And also, as suggested by victorli, check whether you are getting the value for name or not ?
0
 

Author Comment

by:KTS
ID: 8199660
Well i've tried both comments and they work...do i need a printWriter out statement?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:KTS
ID: 8199662
Well i've tried both comments and they don't work...do i need a printWriter out statement?
0
 

Author Comment

by:KTS
ID: 8199669
I'm getting the correct name when i output it!!
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199680
You have to remove +;
try this:

name = (request.getParameter("name").trim()).toUpperCase();
             sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE UPPER(user_name) = '" + name + "'");


And also, as suggested by victorli, check whether you are getting the value for name or not ?
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199712
I did not see one quote in my last posting , that's why againg posting this select statement.

sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE user_name = '" + name + "'");
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199720
Did you try to get the value of name ?
Add these lines before Select statement, and check what are you getting as value and length of "name" .

out.println("name= "+request.getParameter("name"));
out.println("length ="+request.getParameter("name").length());

0
 

Author Comment

by:KTS
ID: 8199721
I'm getting the correct name when i output it!!
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199739
There was "+" in select statement remove that.
sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE user_name = '" + name + "'");
0
 

Author Comment

by:KTS
ID: 8199743
I'm getting the correct name when i output it!!
0
 

Author Comment

by:KTS
ID: 8199747
when i add the two following lines of code

out.println("name= "+request.getParameter("name"));
out.println("length ="+request.getParameter("name").length());

I get the name and length of the name correct too!
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8199763
Did you remove + sign from the select statement, that i posted above.
0
 
LVL 19

Accepted Solution

by:
cheekycj earned 140 total points
ID: 8202970
<!DOCTYPE html PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html>
   <head>
      <%@ page import="java.io.*" import="java.lang.*" import="java.sql.*" %>
      <title>
         TEST
      </title>
   </head>
   <body>
<%
    String name;
    Connection dbconn;
    ResultSet results;
    PreparedStatement sql;
    name = request.getParameter("name");

    if (name != null) {

      try {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();

        try {

          int mem;
          boolean doneheading = false;
          out.println("1");
          out.println();
          dbconn = DriverManager.getConnection(
                       "jdbc:mysql://localhost/nutrition", "saundersk",
                       "kevin");
          out.println("2");
          sql = dbconn.prepareStatement(
                    "SELECT member_id FROM member WHERE user_name = ?");
          out.println("3");
          sql.setString(1, name);
          results = sql.executeQuery();
          out.println("4");

          while (results.next()) {
            out.println("5");

            if (!doneheading) {
              out.println("6");
              out.println("<table border=2>");
              doneheading = true;
            }

            out.println("7");
            mem = results.getInt("member_id");
            out.println("<tr>td>" + mem);
          }

          if (doneheading) {
            out.println("8");
            out.println("</table>");
          } else {
            out.println("9");
            out.println("No matches for ");
          }
        } catch (SQLException s) {
          out.println("SQL Error " + s.getMessage());
        }
      } catch (ClassNotFoundException err) {
        out.println("Class loading error:" + err.getMessage());
      }
       catch (Exception e) {
        out.println("General error:" + e.getMessage());
      }
      finally {
        if (results != null) results.close();
        if (sql != null) sql.close();
        if (dbconn != null) dbconn.close();
      }
    } else {
      out.println("Name parameter was NULL!");
    }
%>
   </body>
</html>

CJ
0
 

Expert Comment

by:mythreyiv
ID: 8203622
Hi KTS

here i modified your code.
just try with this code.
it might work.
instead od preppared statemnet i used statement.

<html>
<head>
<%@ page
    import = "java.io.*"
    import = "java.lang.*"
    import = "java.sql.*"
%>
<title>TEST</title>
</head>
<body>
<%
    String     name;
    Connection dbconn;
    ResultSet results;
    Statement stmt;
    PreparedStatement sql;
    try
    {
         Class.forName("org.gjt.mm.mysql.Driver").newInstance();
         try
         {
              int mem;
              boolean     doneheading = false;
              out.println("1");
              out.println();
              dbconn = DriverManager.getConnection("jdbc:mysql://localhost/nutrition","saundersk","kevin");
         
              out.println("2");
              name = request.getParameter("name");
              stmt=dbconn.createStatement();
              //sql = dbconn.prepareStatement("SELECT member_id FROM member WHERE user_name ='" + name + "'");
              String query= "SELECT member_id FROM member WHERE user_name ='" + name + "'";
              out.println(query);
              out.println("3");
         
              results = sql.executeQuery(query);
             
              out.println("4");
              while(results.next())
              {
                   out.println("5");
                   if(! doneheading)
                   {
                        out.println("6");
                        out.println("table border=2>");
                        doneheading = true;
                       
                   }
                               out.println("7");
                               mem = results.getInt("member_id");
                   out.println("<tr><td>" + mem);
         

              }
              if(doneheading)
              {
                   out.println("8");
                   out.println("/table>");
              }
              else
              {
                   out.println("9");
                   out.println("No matches for ");
              }
         }          
         catch (SQLException s)
         {
              out.println("SQL Error
");
         }
    }
    catch (ClassNotFoundException err)
    {
         out.println("Class loading error");
       }
%>
</body>
</html>
0
 

Author Comment

by:KTS
ID: 8204538
IF i wanted to SELECT member_id FROM member WHERE user_name ='" + name + "' from memember how would the code change?
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8204605
Isn't that what the current code is doing?

CJ
0
 

Author Comment

by:KTS
ID: 8205254
What does sql.setString(1, request.getParameter("name")); do?


if i wanted to change my SELECT STATEMENT TO..."SELECT * FROM member WHERE UPPER(user_name) = ?"

how would i change the rest of the code to get all the fields output..there are five fields....username, password1, password2, memeber_id, email_add
0
 

Author Comment

by:KTS
ID: 8205256
What does sql.setString(1, request.getParameter("name")); do?


if i wanted to change my SELECT STATEMENT TO..."SELECT * FROM member WHERE UPPER(user_name) = ?"

how would i change the rest of the code to get all the fields output..there are five fields....username, password1, password2, memeber_id, email_add
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8205325
setString(1, ...)

sets the sql statement's first bind parameter to a string.

This avoids you having to format a string (escape characters, handle special, characters, etc) for the DB.  it is esp useful when setting dates in a query.  I highly recommend using that.

changing your code would be simple:

<!DOCTYPE html PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html>
  <head>
     <%@ page import="java.io.*" import="java.lang.*" import="java.sql.*" %>
     <title>
        TEST
     </title>
  </head>
  <body>
<%
   String name;
   Connection dbconn;
   ResultSet results;
   PreparedStatement sql;
   name = request.getParameter("name");

   if (name != null) {

     try {
       Class.forName("org.gjt.mm.mysql.Driver").newInstance();

       try {

         boolean doneheading = false;
         out.println("1");
         out.println();
         dbconn = DriverManager.getConnection(
                      "jdbc:mysql://localhost/nutrition", "saundersk",
                      "kevin");
         out.println("2");
         sql = dbconn.prepareStatement(
                   "SELECT * FROM member WHERE user_name = ?");
         out.println("3");
         sql.setString(1, name);
         results = sql.executeQuery();
         out.println("4");

         while (results.next()) {
           out.println("5");

           if (!doneheading) {
             out.println("6");
             out.println("<table border=2>");
             doneheading = true;
           }
         
           out.println("7");
           int mem = results.getInt("member_id");
           String username = results.getString("username");
           String password1 = results.getString("password1");
           String password2 = results.getString("password2");
           String email_add = results.getString("email_add");
           out.println("<tr>");
           out.println("<td>" + mem + "</td>");
           out.println("<td>" + username + "</td>");
           out.println("<td>" + password1 + "</td>");
           out.println("<td>" + password2 + "</td>");
           out.println("<td>" + email_add + "</td>");
           out.println("</tr>");
         }

         if (doneheading) {
           out.println("8");
           out.println("</table>");
         } else {
           out.println("9");
           out.println("No matches for ");
         }
       } catch (SQLException s) {
         out.println("SQL Error " + s.getMessage());
       }
     } catch (ClassNotFoundException err) {
       out.println("Class loading error:" + err.getMessage());
     }
      catch (Exception e) {
       out.println("General error:" + e.getMessage());
     }
     finally {
       if (results != null) results.close();
       if (sql != null) sql.close();
       if (dbconn != null) dbconn.close();
     }
   } else {
     out.println("Name parameter was NULL!");
   }
%>
  </body>
</html>

you can also use this as your sql:

"SELECT username, password1, password2, memeber_id, email_add FROM member WHERE UPPER(user_name) = ?"

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8208065
Glad I could help and Thanx for the "A"

CJ
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

765 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