Java SQL Exception Invalid Cursor State - no current row

Question

I've been having a hard time trying to figure this out. First I have an insertProduct(Product p) method that is supposed to check if a product with the specified code exists in the database. If so, this method will display an error message. Otherwise, it should add the product to the database and print it to the console. I'm not sure if I'm doing that correctly.

Second, the deleteProduct(Product p) method is supposed to delete the product that was added by the insertProduct method. Now the thing is that I keep getting a SQL Exception when I try to add the product and then the deleteProduct method just keeps deleting the products that are in the database one by one each time the program is run until non are left. I'm not sure what's wrong with both of these methods.

Console Output:

Derby has been started.

Product list:
bvbn    Murach's Beginning Visual Basic .NET        $49.50
cshp    Murach's C#                                 $49.50
java    Murach's Beginning Java                     $49.50
jsps    Murach's Java Servlets and JSP              $49.50
mcb2    Murach's Mainframe COBOL                    $59.50
sqls    Murach's SQL for SQL Server                 $49.50
zjcl    Murach's OS/390 and z/OS JCL                $62.50

First product:
bvbn    Murach's Beginning Visual Basic .NET        $49.50

Last product:
zjcl    Murach's OS/390 and z/OS JCL                $62.50

Product by code: cshp
cshp    Murach's C#                                 $49.50

Insert test: 
java.sql.SQLException: Invalid cursor state - no current row.
Product list:
bvbn    Murach's Beginning Visual Basic .NET        $49.50
cshp    Murach's C#                                 $49.50
java    Murach's Beginning Java                     $49.50
jsps    Murach's Java Servlets and JSP              $49.50
mcb2    Murach's Mainframe COBOL                    $59.50
sqls    Murach's SQL for SQL Server                 $49.50
zjcl    Murach's OS/390 and z/OS JCL                $62.50

Delete test: 
zjcl    Murach's OS/390 and z/OS JCL                $62.50

Product list:
bvbn    Murach's Beginning Visual Basic .NET        $49.50
cshp    Murach's C#                                 $49.50
java    Murach's Beginning Java                     $49.50
jsps    Murach's Java Servlets and JSP              $49.50
mcb2    Murach's Mainframe COBOL                    $59.50
sqls    Murach's SQL for SQL Server                 $49.50

Derby has been shut down.

Code:

   import java.sql.*;

public class DBTesterApp
{
private static Connection connection = null;
private static Product p = null ;
public static void main(String args[])
{
    // get the connection and start the Derby engine
    connection = MurachDB.getConnection();
    if (connection != null)
        System.out.println("Derby has been started.\n");

    // select data from database
    printProducts();
    printFirstProduct();
    printLastProduct();
    printProductByCode("cshp");

    // modify data in the database
     p = new Product("test", "Test Product", 49.50);        
    insertProduct(p);
    printProducts();

    deleteProduct(p);
    printProducts();

    // disconnect from the database
    if (MurachDB.disconnect())
        System.out.println("Derby has been shut down.\n");
 }

 public static void printProducts()
 {

   // Product p = null ;
    try (Statement statement = connection.createStatement();
         ResultSet rs = statement.executeQuery("SELECT * FROM Products"))
    {            


        System.out.println("Product list:");
        while(rs.next())
        {
            String code = rs.getString("ProductCode");
            String description = rs.getString("Description");
            double price = rs.getDouble("Price");

         p = new Product(code, description, price);

            printProduct(p);
        }
        System.out.println();
    }
    catch(SQLException e)
    {
        e.printStackTrace();  // for debugging
    }
  }

  public static void printFirstProduct()
  {

     try(Statement statement = connection.createStatement(
             ResultSet.TYPE_SCROLL_SENSITIVE,
             ResultSet.CONCUR_UPDATABLE);

         ResultSet rs = statement.executeQuery("SELECT * FROM Products")){

         System.out.println("First product:");

         rs.first();
         rs.last();

         if(rs.isFirst() == false){
          rs.previous();
         }

         if(rs.isLast() == false){
             rs.next();
         }

         rs.absolute(1);
         String code = rs.getString(1);
         String description = rs.getString(2);
         double price = rs.getDouble(3);

         p = new Product(code , description , price);
         printProduct(p);
         System.out.println();
     }

     catch(SQLException e){
          e.printStackTrace();
         }
  }

 public static void printLastProduct()
 {
     try(Statement statement = connection.createStatement(
             ResultSet.TYPE_SCROLL_SENSITIVE,
             ResultSet.CONCUR_UPDATABLE);

         ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
         System.out.println("Last product:");


         rs.first();
         rs.last();

         if(rs.isFirst() == false){
          rs.previous();
         }

         if(rs.isLast() == false){
             rs.next();
         }

         rs.absolute(7);
         String code = rs.getString(1);
         String description = rs.getString(2);
         double price = rs.getDouble(3);

         p = new Product(code, description, price);
         printProduct(p);
         System.out.println();



     }

     catch(SQLException e){
         e.printStackTrace();
     }
}

  public static void printProductByCode(String productCode)
  {

       String sql  = 
           "SELECT ProductCode, Description, Price " + 
           "FROM  Products "  +    
           "WHERE ProductCode = ?";

   try(PreparedStatement ps = connection.prepareStatement(sql);){

       ps.setString(1, productCode);
       ResultSet rs = ps.executeQuery();

       if(rs.next()){   
         String description = rs.getString("Description");
         double price = rs.getDouble("Price");
         p = new Product(productCode, description, price);
         System.out.println("Product by code: " + productCode);
         printProduct(p);
        }
          else{
          rs.close();
              }

   }


   catch(SQLException e){
    System.err.println(e);

   }


    System.out.println();
  }

  public static void insertProduct(Product p)
  {
    System.out.println("Insert test: ");


    //check if product code exists in database
   try(Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Products")){

   String code =   rs.getString(1);


  if (p.getCode().equals(code) ){
       System.out.println("Error: This product is already in the database!");
       } 


    else{
    String sql = 
            "INSERT INTO Products (productCode, Description, Price) " +
            "VALUES (?, ?, ?)";

    try(PreparedStatement ps = connection.prepareStatement(sql)){


        ps.setString(1, p.getCode());
        ps.setString(2, p.getDescription());
        ps.setDouble(3, p.getPrice());
        ps.executeUpdate();
       }


     catch(SQLException e){
          System.err.println(e);
      } 


    } //end else

      printProduct(p);
    System.out.println();

   }//end try


        catch(SQLException e ){
        System.out.println(e);
         } 
}

 private static void deleteProduct(Product p)
{
    System.out.println("Delete test: ");

    String sql = "DELETE FROM Products " +
                 "WHERE ProductCode = ?";

    try(PreparedStatement ps = connection.prepareStatement(sql)){
        ps.setString(1, p.getCode());
        ps.executeUpdate();

    }

    catch(SQLException e){
       System.err.println(e);
    }
    // add code that deletes the specified product from the database
    // if a product with the specified code doesn't exist, display an error message

    printProduct(p);
    System.out.println();
}

// use this method to print a Product object on a single line
private static void printProduct(Product p)
{
    String productString =
        StringUtils.padWithSpaces(p.getCode(), 8) +
        StringUtils.padWithSpaces(p.getDescription(), 44) +
        p.getFormattedPrice();

    System.out.println(productString);
}
}

Tessellation

Answer 1

You need to call ResultSet.next() before you can retrieve a column value.

// check if product code exists in database
try(Statement statement = connection.createStatement();
     ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
if (rs.next()) // THIS is MISSING!
    String code =   rs.getString(1);

The above code would run without exceptions but would still fail logically since you're selecting all the products and just checking the code for the first one returned by the database. The correct way to check if the product already exists is

// check if product code exists in database
try(Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(
        "SELECT * FROM Products WHERE ProductCode = '" + p.getCode() + "'")){
if (rs.next()) {
   System.out.println("Error: This product is already in the database!");
   return;
}

Infundibuliform

I tried that but now I get this: java.sql.SQLSyntaxErrorException: Column 'TEST' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TEST' is not a column in the target table. – Tessellation

You've still missed removing rs.getString(1);. It's just above where you've added the rs.next(). Checking the rest. – Infundibuliform

yes I removed it and it still gives the same error. I don't understand how rs.next would work if it doesn't insert the product I created in the main method. – Tessellation

Put quotes around product code in SELECT query. Check update. (I assumed it to be an int.) – Infundibuliform

Still no luck :(. It seems that the try method gives an exception because there is no Column 'Test' yet. The method is supposed to check if the p object that's passed to the method is already in the database. Is there a way I can only get the product code from the p object that's passed to the method and do an if statement that checks if that product code is in the database already and if not, insert the product? – Tessellation

Paste your updated file on pastebin. Did you add the quotes? – Infundibuliform

"WHERE ProductCode = " + p.getCode() + "" should be "WHERE ProductCode = '" + p.getCode() + "'" Please note the single quotes around p.getCode(). – Infundibuliform

Wow this amazing! At first I thought I correctly added those single quotes. Why does this work with the single quotes? Thanks so much! – Tessellation

Without the quotes your DB runs this query as SELECT * FROM Products WHERE ProductCode = test and assumes test is just another column in Products table whose value should be compared with ProductCode. In other words it's not treating what you passed as string. – Infundibuliform

With the quotes your DB runs this query as SELECT * FROM Products WHERE ProductCode = 'test' and hence correctly interprets test as a string. – Infundibuliform

It compiles without error now! but it actually it doesn't quite work as it should. I need to run the program twice. The first time the product should be added to the database. The second time I run the program the product should appear in the list of products, but then an error message should appear indicating that the product already exists and no product should be added. I run it twice but I don't get the error message to appear: if(rs.next(){System.out.println("Error: This product is already in the database!");} – Tessellation

That's because you're passing the same Product to deleteProduct() as well! So, the next time the program runs it inserts it again. :) – Infundibuliform

let us continue this discussion in chat – Tessellation

Answer 2

In your insertProduct method, you need to call the next() method on your ResultSet before fetching data from it.

Primateship