Results 1 to 7 of 7
  1. #1
    ZeCute is offline Member
    Join Date
    May 2010
    Posts
    14
    Rep Power
    0

    Default Problem with PreparedStatement?

    Trying to use PreparedStatement but it don´t work. I have looked through a lot of different tutorials for this but do not find the answer to my problem. If anyone can see what I do wrong, please feel free to write the answer. I enclose the codes.

    This is the Servlet:

    public class HittaArtNr extends HttpServlet {
    public static String paramValue;
    public String Pris;

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

    response.setContentType("text/plain");

    PrintWriter out = response.getWriter();

    paramValue=request.getParameter("test");




    DBConnector db = new DBConnector();
    try {
    db.openDBConnector()
    } catch (SQLException ex) {
    ex.printStackTrace();//Kastar undantag
    }

    response.setContentLength(paramValue.length());





    String sql = "SELECT Vara, Pris FROM Varor where ArtikelNr="+ paramValue;

    ResultSet rs = db.getResultSet(sql);


    try{

    while(rs.next()){
    String lastName = rs.getString(1);
    int test = rs.getInt(2);
    System.out.println(lastName + test);

    }


    }


    catch(SQLException sqlE){
    out.println("Databas fel!");//Kastar undantag


    }
    //Stänger databasuppkoppling
    db.closeDBConnector();
    out.close();


    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
    * Handles the HTTP <code>GET</code> method.
    * @param request servlet request
    * @param response servlet response
    * @throws ServletException if a servlet-specific error occurs
    * @throws IOException if an I/O error occurs
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /**
    * Handles the HTTP <code>POST</code> method.
    * @param request servlet request
    * @param response servlet response
    * @throws ServletException if a servlet-specific error occurs
    * @throws IOException if an I/O error occurs
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /**
    * Returns a short description of the servlet.
    * @return a String containing servlet description
    */
    public String getServletInfo() {
    return "Short description";
    }// </editor-fold>

    }



    DBConnector class:



    public DBConnector (String driver, String url){

    this.driver=driver;//Tilldelar variabeln
    this.url=url;//Tilldelar variabeln

    }
    public DBConnector(){
    pointBaseDriver="com.mysql.jdbc.Driver";
    pointBaseUrl="jdbc:mysql://localhost:8889/Matlista";
    }

    public void openDBConnector() throws SQLException{
    try{
    Class.forName(pointBaseDriver);
    con=DriverManager.getConnection(pointBaseUrl, "root", "root");


    }catch(ClassNotFoundException cnfExp){
    System.out.print(cnfExp.getMessage());

    }catch(SQLException sqlExp){
    System.out.println(sqlExp.getMessage());
    }

    }

    public ResultSet getResultSet(String SQLString){
    ResultSet rs=null;
    try{
    if(con==null){
    openDBConnector();
    }

    PreparedStatement prepStmt = con.prepareStatement(SQLString);
    rs = null;
    int varde = Integer.parseInt(HittaArtNr.paramValue);
    prepStmt.setInt(1, varde);

    rs = prepStmt.executeQuery();

    }
    catch(SQLException sqlExp){
    System.out.println(sqlExp.getMessage());//Kastar undantag

    }

    return rs;

    }

    public void getResultSet1(String SQLString){
    ResultSet rs=null;
    try{
    if(con==null){
    openDBConnector();
    }
    Statement st = con.createStatement();
    boolean rs1=st.execute(SQLString);
    }
    catch(SQLException sqlExp){
    System.out.println(sqlExp.getMessage());

    }



    }




    public void closeDBConnector(){
    try{
    con.close();
    }

    catch(SQLException sqlExp){
    System.out.println(sqlExp.getMessage());
    }

    }

    }



    This is the error message I get:

    INFO: Parameter index out of range (1 > number of parameters, which is 0).

    VARNING: StandardWrapperValve[HittaArtNr]: PWC1406: Servlet.service() for servlet HittaArtNr threw exception

    java.lang.NullPointerException

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    You are trying to set a parameter that doesn't exist. You are sending a complete sql string (although probably badly formed if "paramValue" is a String) and then trying to set a aprameter on it. Where in that sql string do you have a question mark?

  3. #3
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,519
    Blog Entries
    7
    Rep Power
    20

    Default

    Your text representation of your SQL PreparedStatement doesn't contain any parameters at all (i.e. no question marks in there) while your DBConnector class wants to fill in a first parameter with an int.

    kind regards,

    Jos

  4. #4
    ZeCute is offline Member
    Join Date
    May 2010
    Posts
    14
    Rep Power
    0

    Default

    Quote Originally Posted by masijade View Post
    You are trying to set a parameter that doesn't exist. You are sending a complete sql string (although probably badly formed if "paramValue" is a String) and then trying to set a aprameter on it. Where in that sql string do you have a question mark?
    Yes paramValue is a string. I have a question mark in the SQL string, I have a ? in the end of that String.

  5. #5
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,519
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by ZeCute View Post
    Yes paramValue is a string. I have a question mark in the SQL string, I have a ? in the end of that String.
    Your String:

    Java Code:
    String sql = "SELECT Vara, Pris FROM Varor where ArtikelNr="+ paramValue;
    Where is that question mark?

    kind regards,

    Jos
    Last edited by JosAH; 06-12-2010 at 01:42 PM.

  6. #6
    ZeCute is offline Member
    Join Date
    May 2010
    Posts
    14
    Rep Power
    0

    Default

    Thanks for all your help, the problem is solved.

  7. #7
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,519
    Blog Entries
    7
    Rep Power
    20

    Default

    The spammer is gone already, thanks.

    kind regards,

    Jos
    Last edited by JosAH; 06-13-2010 at 01:22 PM.

Similar Threads

  1. Regarding PreparedStatement
    By adeeb in forum JDBC
    Replies: 0
    Last Post: 06-09-2008, 09:07 PM
  2. Using PreparedStatement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-22-2007, 11:24 AM
  3. PreparedStatement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-05-2007, 03:56 PM
  4. Help me. PreparedStatement
    By Felissa in forum JDBC
    Replies: 2
    Last Post: 06-28-2007, 05:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •