• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Could be either a Programming or an Database program...help!!

I am currently using Java to connect to, query and retrieve values from a MYSQL database. After doing this, I am trying to return a value of type "CatalogItem" at the end of this function...and it is always returning null.  I haven't the slighest idea of why this is happening.  I have tested the program using print statements...after accessing the database and within the function, the values are there.  But, for some reason, I am not able to return them.  I have included my code...does anyone have any ideas?  

Side Note:  This function is called through a static function call in another function:  Catalog.getItem("stringValue");

/***********code*****************/

public static CatalogItem getItem(String itemID){
            
            String Id=null;
            String shortDesc=null;
            String longDesc=null;
            double cost;;
            
            CatalogItem item;
            
            if (itemID == null) {
                        return(null);
                }//close if
   
                 Connection connection = getConnection();
                      if (connection == null) {
                        System.out.println("Connection failed.");
                        return null;
                      }
          try {
                  Statement statement = connection.createStatement();
                  String query = "SELECT * FROM chickBooks where itemId=\'" + itemID +"\';" ;
            try{
     
                  ResultSet resultSet = statement.executeQuery(query);
            
            if(resultSet.next()){
            
                  Id=resultSet.getString("itemId");
                  shortDesc=resultSet.getString("shortDesc");
            longDesc=resultSet.getString("longDesc");
            cost=resultSet.getDouble("cost");
            
      }
            finally{
                  statement.close();
            }
            
            } catch(SQLException sqle) {
            System.err.println("Error executing query: " + sqle);
          return null;
          } finally {
            closeConnection(connection);
            }
          return (new CatalogItem(Id,shortDesc,longDesc,cost));
          
 }//close function
0
cbshell
Asked:
cbshell
  • 3
  • 2
  • 2
  • +1
1 Solution
 
cookreCommented:
I'm not much of a Java person, but

return (new CatalogItem(Id,shortDesc,longDesc,cost));

looks a bit odd to me.  Is the scope of the new'ed object limited to that expression?
0
 
cbshellAuthor Commented:
No it is not a local variable...it was just something that I was trying.  This was my original code:

***********code*****************/

public static CatalogItem getItem(String itemID){
         
          String Id=null;
          String shortDesc=null;
          String longDesc=null;
          double cost;;
         
          CatalogItem item;
         
          if (itemID == null) {
                     return(null);
              }//close if
   
               Connection connection = getConnection();
                   if (connection == null) {
                     System.out.println("Connection failed.");
                     return null;
                   }
         try {
                Statement statement = connection.createStatement();
                String query = "SELECT * FROM chickBooks where itemId=\'" + itemID +"\';" ;
           try{
     
                ResultSet resultSet = statement.executeQuery(query);
           
          if(resultSet.next()){
         
                Id=resultSet.getString("itemId");
                shortDesc=resultSet.getString("shortDesc");
          longDesc=resultSet.getString("longDesc");
          cost=resultSet.getDouble("cost");
         
     }
           finally{
                statement.close();
           }
           
           } catch(SQLException sqle) {
           System.err.println("Error executing query: " + sqle);
         return null;
         } finally {
           closeConnection(connection);
           }
         return (new CatalogItem(Id,shortDesc,longDesc,cost));
         
 }//close function

0
 
cbshellAuthor Commented:
I clicked submit too quickly...this is the original code:

/***********code*****************/

public static CatalogItem getItem(String itemID){
         
          String Id=null;
          String shortDesc=null;
          String longDesc=null;
          double cost;;
         
          CatalogItem item;
         
          if (itemID == null) {
                     return(null);
              }//close if
   
               Connection connection = getConnection();
                   if (connection == null) {
                     System.out.println("Connection failed.");
                     return null;
                   }
         try {
                Statement statement = connection.createStatement();
                String query = "SELECT * FROM chickBooks where itemId=\'" + itemID +"\';" ;
           try{
     
                ResultSet resultSet = statement.executeQuery(query);
           
          if(resultSet.next()){
         
                Id=resultSet.getString("itemId");
                shortDesc=resultSet.getString("shortDesc");
          longDesc=resultSet.getString("longDesc");
          cost=resultSet.getDouble("cost");
         
          item=new CatalogItem(itemId, shortDesc, longDesc, cost);          
     }
           finally{
                statement.close();
           }
           
           } catch(SQLException sqle) {
           System.err.println("Error executing query: " + sqle);
         return null;
         } finally {
           closeConnection(connection);
           }
         return (item);
         
 }//close function

0
Independent Software Vendors: 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!

 
cookreCommented:
I'd have instantiated 'item' up top instead of within the scope of the if.

To wit,

CatalogItem item = new CatalogItem();
....

if (resultSet.next())
   {
   item.itemid=resultSet.getString("itemId");
   item.shortDesc=resultSet.getString("shortDesc");
   item.longDesc=resultSet.getString("longDesc");
   item.cost=resultSet.getDouble("cost");
   }



0
 
sigmaconCommented:
You show a

try { } finally { }

block inside another try-catch block. This should even compile ... ?

The line

item = new CatalogItem(itemId, shortDesc, longDesc, cost);

would not compile either, because itemId is unknown, it should be 'Id'. The only reason I could see you getting back null is if the resultset really did not have any rows. But just to be save, I moved the return statement right after you created the item and made sure I close the DB connection first. Is there a particular reason for the ( ) around variables in the return statements?:

      public static CatalogItem getItem(String itemID){

            String Id = null;
            String shortDesc = null;
            String longDesc = null;
            double cost;
            
            if (itemID == null) {
                  return null;
            }
            
            Connection connection = getConnection();
            if (connection == null) {
                  System.out.println("Connection failed.");
                  return null;
            }
            try {
                  Statement statement = connection.createStatement();
                  String query = "SELECT * FROM chickBooks where itemId=\'" + itemID +"\';" ;

                  ResultSet resultSet = statement.executeQuery(query);
                        
                  if (resultSet.next()) {
                        Id = resultSet.getString("itemId");
                        shortDesc = resultSet.getString("shortDesc");
                        longDesc = resultSet.getString("longDesc");
                        cost = resultSet.getDouble("cost");
                        closeConnection(connection);

                        return new CatalogItem(Id, shortDesc, longDesc, cost);
                  }
            } catch(SQLException sqle) {
                  System.err.println("Error executing query: " + sqle);
                  return null;
            } finally {
                  closeConnection(connection);
            }
            return null;
      }

0
 
Rick_TownsendCommented:
itemId is known.  It is passed into the method as a parameter.

Offhand, I don't see any problems with the method.  I assume you've checked to see if itemId is null?  That would cause your method to return null immeadiately.

You say in the original question that you tested the values of the resultSet.  That's the only exit point I can see that would cause your application to return null instead of a new object.  (ie: If the resultSet was empty of results, then that whole section of code in your original method source would be skipped, and the object would be null.)  However, your test code in the question creates the object no matter what.
0
 
sigmaconCommented:
Rick, you're right, the itemId does exit - that was silly of me to say. But the code still didn't compile because of the try-finally block. So I fixed the compile errors and moved related things closer to each other - if not for anything else then for clarity. So, cbshell, where are we at with this? Does it work now?
0
 
cbshellAuthor Commented:
Actually, I did solve the problem....it was a mixture of things.  I had to move my connector jar file to a shared lib folder....and after that.....I used sigmacon's alterations...and it finally worked!

Thanks guys!
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!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now