#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30

    Showing blank page when using WHILE() in JSP


    Hi Guys,

    I've just started using JSP from PHP, I am returning a result set from a class which does my query for me..

    However when i try to :

    WHILE(r.next())
    {
    ..
    }

    It seems to break my page - I commented alll the parts out on my page, and found it down here that break the page..

    the page is below..

    JAVA Code:
     
     
    <%@page errorPage="/inc/error.jsp"%>
    <%@page import="java.sql.*" %>
    <%@page import="uPhoto.Framework.db.q"%>
     
    <% q Q = new q();
    ResultSet r;
     
    r = Q.query("call Latest;"); 
     
     
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
    <link rel="stylesheet" href="/uPhoto/css/index.css" type="text/css"/>
    <title>uPhoto
    &trade;
    >> Home
    </title>
    </head>
    <body>
    <div class="wrapper">
    <jsp:include flush="true" page="inc/header.jsp"></jsp:include>
      <div class="curvy">
        <em id="ctl">
          <b>&bull;</b>
        </em>
        <em id="cbl">
          <b>&bull;</b>
        </em>
        <em id="ctr">
          <b>&bull;</b>
        </em>
        <em id="cbr">
          <b>&bull;</b>
        </em>
        <div align="center">
          <% while(r.next())
         {
          // r.getString(1);
         }
     
         %>
        </div>
      </div>
    </div>
    </body>
    </html>
  2. #2
  3. No Profile Picture
    Participant
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Location
    Hawaii
    Posts
    376
    Rep Power
    21

    Try this


    Code:
    r = Q.query("call Latest;");
    I'm assuming "Call Latest" is your sql query?

    Either way, a result set object will store the results of an Executed Query.

    Try this:

    Code:
    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.ResultSet;
    
    
    public class RunQuery {
    
     private final String dbURL = "jdbc:oracle:thin:@your.database.name:port:databaseSID";
      private final String username = "myAccount";
      private final String password = "myPass"; 
    
    Connection connection = null;
      Statement stmt = null;
    
    connection = DriverManager.getConnection(dbURL, username, password);
            stmt = connection.createStatement();
    
            query = "select column1, column2, whatever....";
            recs = stmt.executeQuery(query);
    
            while (recs.next()) {
               String columnOne = recs.getString(column1);
               ...
            }
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Oct 2004
    Location
    Brussels
    Posts
    1,001
    Rep Power
    845

    Exclamation


    [QUOTE=punkboii]
    JAVA Code:
     
     
    <%@page errorPage="/inc/error.jsp"%>
    <%@page import="java.sql.*" %>
    <%@page import="uPhoto.Framework.db.q"%>
     
    <% q Q = new q();
    ResultSet r;
     
    r = Q.query("call Latest;");
    %>

    </quote>

    Question: With "call Latest" are you trying to call a stored procedure/function?
    Because if you are, IMO the syntax is wrong:

    Search "JDBC calling stored procedure"

    I am pretty sure the method query(String) from uPhoto.Framework.db.q class (yikes! what a name! can you change it ?) returns you a null object in case of DBMS error.
    "Problem" is just a bleak word for challenge. -- Richard Fish
    Javalanche
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30
    [QUOTE=valined]
    Originally Posted by punkboii
    JAVA Code:
     
     
    <%@page errorPage="/inc/error.jsp"%>
    <%@page import="java.sql.*" %>
    <%@page import="uPhoto.Framework.db.q"%>
     
    <% q Q = new q();
    ResultSet r;
     
    r = Q.query("call Latest;");
    %>

    </quote>

    this shouldn't return a error, I tested this in Query Analyser for MySQL and it showed fine, however you may be right, is there a way i could return my ex when calling my funct? perhaps adding a throw to the class?
    and yeh I can change it, it just defines it how I know what is in for the future, - however I'll take that in mind!

    Question: With "call Latest" are you trying to call a stored procedure/function?
    Because if you are, IMO the syntax is wrong:

    Search "JDBC calling stored procedure"

    I am pretty sure the method query(String) from uPhoto.Framework.db.q class (yikes! what a name! can you change it ?) returns you a null object in case of DBMS error.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30
    [QUOTE=punkboii][QUOTE=valined][/QUOTE

    Hi Guys,

    Looked into that, and it makes sense. I've amened my query class, to take SP's based on a true or false param that is passed to the method.

    But looking on the net, it doesn't appear I can do it this way... Care to advise? I'm still getting the same errors too..>


    JAVA Code:
     
    package uPhoto.Framework.db;
     
    import java.sql.*;
     
     
    public class q {
      private static Connection Conn;
      private static Statement stmt;
      private static CallableStatement Cstmt;
      private static ResultSet results;
     
      public void q() {
      }
     
      public void connect() {
        try {
     
          String userName = "***********";
          String password = "*******";
          String url = "jdbc:mysql://localhost/snap";
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          Conn = DriverManager.getConnection(url, userName, password);
        }
        catch (SQLException ex) {
    //change to write to log!
          System.out.println(ex.getMessage());
        }
        catch (ClassNotFoundException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (IllegalAccessException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (InstantiationException ex) {
          System.out.println(ex.getMessage());
        }
     
      }
     
      public ResultSet query(String query, int proc) throws SQLException {
        connect();
        if (proc == 0) { //if its not a SP then...
          try {
     
            stmt = Conn.createStatement();
          }
          catch (SQLException ex) {
            ex.getMessage();
          }
     
          try {
            if (stmt.execute(query)) {
     
              results = stmt.getResultSet();
            }
          }
          catch (SQLException ex) {
     
          }
     
          return results;
        }
     
        else {
          //Must be a stored proc
          try{
            Cstmt = Conn.prepareCall("{"+query+ "}");
            results =Cstmt.getResultSet();
          }
          catch(SQLException ex)
          {
     
          ex.getMessage();
          }
     
          return results;
        }
     
      }
    }
  10. #6
  11. Feelin' Groovy
    Devshed Supreme Being (6500+ posts)

    Join Date
    Aug 2001
    Location
    Chicago, IL
    Posts
    10,131
    Rep Power
    5058
    In addition to numerous other problems with your design, you swallow any exceptions that may be thrown while trying to create a statement. Since you don't handle and don't report the exceptions, there is nothing to return as a result.

    Here are some things to address before you go any further.
    Java Code:
    package uPhoto.Framework.db;
     
    import java.sql.*;
     
    /*
     * NAME YOUR CLASSES SOMETHING MORE DESCRIPTIVE THAN "Q". ALSO,
     * CLASS NAMES SHOULD START WITH A CAPITAL LETTER.
     */
    public class q {
     
      /*
       * RETAINING REFERENCES TO THESE OBJECTS IS NOT A GOOD IDEA.
       */
      private static Connection Conn;
      private static Statement stmt;
      private static CallableStatement Cstmt;
      private static ResultSet results;
     
      public void q() {
      /*
       * THIS METHOD DOES NOTHING. IT'S NOT CONSTRUCTOR, IF THAT'S
       * WHAT YOU WERE SHOOTING FOR. GET RID OF IT.
       */
      }
     
      public void connect() {
        try {
     
          String userName = "***********";
          String password = "*******";
          String url = "jdbc:mysql://localhost/snap";
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          Conn = DriverManager.getConnection(url, userName, password);
          /*
           * WHEN DO YOU EVER CLOSE THESE CONNECTIONS?
           */
        }
        catch (SQLException ex) {
    //change to write to log!
          System.out.println(ex.getMessage());
        }
        catch (ClassNotFoundException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (IllegalAccessException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (InstantiationException ex) {
          System.out.println(ex.getMessage());
        }
     
      }
     
      /*
       * RESULT SETS SHOULD NOT BE PASSED AROUND. PROCESS THE RESULT SET AND 
       * RETURN A COLLECTION OR OBJECT.
       */
      public ResultSet query(String query, int proc) throws SQLException {
        connect();
        /*
         * MAGIC NUMBERS ARE A BAD PROGRAMMING PRACTICE, AS IS USING AN 
         * INT VALUE FOR BOOLEAN BEHAVIOR.
         */
        if (proc == 0) { //if its not a SP then...
          try {
     
            stmt = Conn.createStatement();
          }
          catch (SQLException ex) {
             /*
              * THIS CALL RETURNS A STRING REFERENCE, WHICH YOU IMMEDIATELY THROW AWAY.
              */
            ex.getMessage(); 
          }
     
          try {
            /*
             * IF THE PREVIOUS BLOCK OF CODE FAILS, NOT ONLY WILL YOU NOT KNOW WHY, 
             * THIS CALL WILL THROW A NULLPOINTEREXCEPTION.
             */
            if (stmt.execute(query)) {
     
              results = stmt.getResultSet();
            }
          }
          catch (SQLException ex) {
            /*
             * ANOTHER SWALLOWED EXCEPTION. YOU WILL HAVE NO WAY OF KNOWING IF
             * ANYTHING WENT WRONG AND WHY.
             */
          }
     
          /*
           * VIOLATES ENCAPSULATION. AND PASSES AROUND AN EXPENSIVE RESOURCE, WHICH,
           * BY ALL APPEARANCES NEVER GETS CLOSED.
           */
          return results;
        }
     
        else {
          //Must be a stored proc
          try{
            Cstmt = Conn.prepareCall("{"+query+ "}");
            results =Cstmt.getResultSet();
          }
          catch(SQLException ex)
          {
            /*
             * SAME PROBLEM AS ABOVE. THIS LINE DOES NOTHING.
             */ 
          ex.getMessage();
          }
            /*
             * SAME ISSUE AS ABOVE. YOU'RE NOT BUILDING A JDBC API, SO THERE'S NO NEED TO 
             * RETURN A RESULT SET. PROCESS IT INSTEAD.
             */ 
     
          return results;
        }
     
      }
    }

    Also note that passing SQL queries around like that is a recipe for SQL injection exploits.

    I'm not trying to pick on you, it any of that sounded harsh. These are just fundamental problems that should be fixed before proceeding. It may help to study the code examples here: http://java.sun.com/products/jdbc/codeExamples_3.0.zip

    ~
    Last edited by Yawmark; June 16th, 2007 at 03:59 PM.
    Yawmark
    class Sig{public static void main(String...args){\u0066or(int
    \u0020$:"v\"ʲ\"vΤ\"".to\u0043h\u0061rArray()
    )System./*goto/*$/%\u0126//^\u002A\u002Fout.print((char)(($>>
    +(~'"'&'#'))+('<'>>('\\'/'.')/\u002Array.const(~1)\*\u002F)));}}
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30
    Thanks for the in-depth analysis on my code, I have now created a XML error log, which now shows:

    "Callable statments not supported." For which I am going to google and fix,

    in regards to my result sets, I am aiming for this to be a class to exec my queries, and process them in the context their needed, hence I can't process them in my Query servlet.

    I am also going to be writing a validation class and methods to check my SQL queries before they are processed, to prevent SQL injection.

    Thanks for the help!!

    stu

    Originally Posted by Yawmark
    In addition to numerous other problems with your design, you swallow any exceptions that may be thrown while trying to create a statement. Since you don't handle and don't report the exceptions, there is nothing to return as a result.

    Here are some things to address before you go any further.
    Java Code:
    package uPhoto.Framework.db;
     
    import java.sql.*;
     
    /*
     * NAME YOUR CLASSES SOMETHING MORE DESCRIPTIVE THAN "Q". ALSO,
     * CLASS NAMES SHOULD START WITH A CAPITAL LETTER.
     */
    public class q {
     
      /*
       * RETAINING REFERENCES TO THESE OBJECTS IS NOT A GOOD IDEA.
       */
      private static Connection Conn;
      private static Statement stmt;
      private static CallableStatement Cstmt;
      private static ResultSet results;
     
      public void q() {
      /*
       * THIS METHOD DOES NOTHING. IT'S NOT CONSTRUCTOR, IF THAT'S
       * WHAT YOU WERE SHOOTING FOR. GET RID OF IT.
       */
      }
     
      public void connect() {
        try {
     
          String userName = "***********";
          String password = "*******";
          String url = "jdbc:mysql://localhost/snap";
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          Conn = DriverManager.getConnection(url, userName, password);
          /*
           * WHEN DO YOU EVER CLOSE THESE CONNECTIONS?
           */
        }
        catch (SQLException ex) {
    //change to write to log!
          System.out.println(ex.getMessage());
        }
        catch (ClassNotFoundException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (IllegalAccessException ex) {
     
          System.out.println(ex.getMessage());
        }
        catch (InstantiationException ex) {
          System.out.println(ex.getMessage());
        }
     
      }
     
      /*
       * RESULT SETS SHOULD NOT BE PASSED AROUND. PROCESS THE RESULT SET AND 
       * RETURN A COLLECTION OR OBJECT.
       */
      public ResultSet query(String query, int proc) throws SQLException {
        connect();
        /*
         * MAGIC NUMBERS ARE A BAD PROGRAMMING PRACTICE, AS IS USING AN 
         * INT VALUE FOR BOOLEAN BEHAVIOR.
         */
        if (proc == 0) { //if its not a SP then...
          try {
     
            stmt = Conn.createStatement();
          }
          catch (SQLException ex) {
             /*
              * THIS CALL RETURNS A STRING REFERENCE, WHICH YOU IMMEDIATELY THROW AWAY.
              */
            ex.getMessage(); 
          }
     
          try {
            /*
             * IF THE PREVIOUS BLOCK OF CODE FAILS, NOT ONLY WILL YOU NOT KNOW WHY, 
             * THIS CALL WILL THROW A NULLPOINTEREXCEPTION.
             */
            if (stmt.execute(query)) {
     
              results = stmt.getResultSet();
            }
          }
          catch (SQLException ex) {
            /*
             * ANOTHER SWALLOWED EXCEPTION. YOU WILL HAVE NO WAY OF KNOWING IF
             * ANYTHING WENT WRONG AND WHY.
             */
          }
     
          /*
           * VIOLATES ENCAPSULATION. AND PASSES AROUND AN EXPENSIVE RESOURCE, WHICH,
           * BY ALL APPEARANCES NEVER GETS CLOSED.
           */
          return results;
        }
     
        else {
          //Must be a stored proc
          try{
            Cstmt = Conn.prepareCall("{"+query+ "}");
            results =Cstmt.getResultSet();
          }
          catch(SQLException ex)
          {
            /*
             * SAME PROBLEM AS ABOVE. THIS LINE DOES NOTHING.
             */ 
          ex.getMessage();
          }
            /*
             * SAME ISSUE AS ABOVE. YOU'RE NOT BUILDING A JDBC API, SO THERE'S NO NEED TO 
             * RETURN A RESULT SET. PROCESS IT INSTEAD.
             */ 
     
          return results;
        }
     
      }
    }

    Also note that passing SQL queries around like that is a recipe for SQL injection exploits.

    I'm not trying to pick on you, it any of that sounded harsh. These are just fundamental problems that should be fixed before proceeding. It may help to study the code examples here: http://java.sun.com/products/jdbc/codeExamples_3.0.zip

    ~
  14. #8
  15. Feelin' Groovy
    Devshed Supreme Being (6500+ posts)

    Join Date
    Aug 2001
    Location
    Chicago, IL
    Posts
    10,131
    Rep Power
    5058
    in regards to my result sets, I am aiming for this to be a class to exec my queries, and process them in the context their needed
    There is not much reason to do so, since such a class already exists (i.e., Statement and PreparedStatement). The "best practices" way to write a data layer is to have a Data Access Object (DAO) for each of those contexts. In other words, each DAO corresponds to a particular class. If you have a SomeCustomObject class, there would be a SomeCustomObjectDAO that would be responsible for handling the datastore transactions, such as returning a list of SomeCustomObject references, creating a SomeCustomObject object in the datastore, reading a particular SomeCustomObject, etc. The application layer shouldn't have to worry about whether the underlying data layer uses stored procedures, straight SQL, or even JDBC at all.

    Here's a *VERY* rudimentary example of what I mean (there are ways to make this even better, but this is just a start to demonstrate encapsulation):
    Java Code:
    package yawmark.jdbc;
     
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
     
    import javax.sql.DataSource;
     
    public interface SomeCustomClassDAO {
    	List<SomeCustomClass> getAll() throws DaoException;
    }
     
    class SomeCustomClassDAOImpl implements SomeCustomClassDAO {
     
    	private DataSource datasource;
     
    	public SomeCustomClassDAOImpl(DataSource datasource) {
    		this.datasource = datasource;
    	}
     
    	public List<SomeCustomClass> getAll() throws DaoException {
    		Connection conn = null;
    		try {
    			conn = this.datasource.getConnection();
    			PreparedStatement ps = conn.prepareStatement("select some_field, some_other_field from some_table");
    			ResultSet rs = ps.executeQuery();
    			List<SomeCustomClass> results = new ArrayList<SomeCustomClass>();
    			while (rs.next()) {
    				String someField = rs.getString("some_field");
    				String someOtherField = rs.getString("some_other_field");
    				SomeCustomClass scc = new SomeCustomClass(someField, someOtherField);
    				results.add(scc);
    			}
    			rs.close();
    			ps.close();
    			return results;
    		} catch (SQLException e) {
    			// handle error
    			throw new DaoException(e);
    		} finally {
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// handle error
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    }


    It sounds like you have a great attitude about learning; keep up the good work!

    ~

    Comments on this post

    • Nemi agrees : Excellent example! SomeCustomClassDAOImpl should implement SomeCustomClassDAO. I am sure Yawmark just typoed that.
    Last edited by Yawmark; June 17th, 2007 at 11:33 AM.
    Yawmark
    class Sig{public static void main(String...args){\u0066or(int
    \u0020$:"v\"ʲ\"vΤ\"".to\u0043h\u0061rArray()
    )System./*goto/*$/%\u0126//^\u002A\u002Fout.print((char)(($>>
    +(~'"'&'#'))+('<'>>('\\'/'.')/\u002Array.const(~1)\*\u002F)));}}
  16. #9
  17. Feelin' Groovy
    Devshed Supreme Being (6500+ posts)

    Join Date
    Aug 2001
    Location
    Chicago, IL
    Posts
    10,131
    Rep Power
    5058
    I am sure Yawmark just typoed that.
    Indeed, thanks for catching that.

    ~
    Yawmark
    class Sig{public static void main(String...args){\u0066or(int
    \u0020$:"v\"ʲ\"vΤ\"".to\u0043h\u0061rArray()
    )System./*goto/*$/%\u0126//^\u002A\u002Fout.print((char)(($>>
    +(~'"'&'#'))+('<'>>('\\'/'.')/\u002Array.const(~1)\*\u002F)));}}

IMN logo majestic logo threadwatch logo seochat tools logo