Help wtih multiple queries in a single class file but different methods - cursor

I have 3 Classes that i'm using, two of which are utility classes with single method and one of which is a "Processing" class that i'm doing my programs main work in.

Here's some of my code so you can see what i'm doing:

First Utility Class: is Connection object to Access Database using JDBC

Code:

---

import java.sql.*;

public class SQLConnAccessDb {
        static Connection con = null;
        public static void connDb() {
                String dataSourceName = "AMZ_TEST_DB";
                String dbURL = "jdbc:odbc:" + dataSourceName;
                try {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        con = DriverManager.getConnection(dbURL, "","");
                }
                catch (Exception err) {
                        System.err.println("Error: " + err);
                }
        }
}

---

Here's my second utility class that pulls from this connection object, and actually does queries against my Access Database:

Code:

---

import java.sql.*;

public class SQLQueryAccessDb {
        static ResultSet rs = null;
        static Statement s = null;
        static SQLConnAccessDb conDb = null;
       
        public static String sqlQuery(String queryString, int returnColumn) {
                rs = null;
                String returnSku = null;
                try {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        conDb = new SQLConnAccessDb();
                        SQLConnAccessDb.connDb();
                        s = SQLConnAccessDb.con.createStatement();
                        s.execute(queryString);
                        rs = s.getResultSet();
                        rs.next();
                        returnSku = rs.getString(returnColumn);
                        closeDB();
                }
               
                catch (Exception err) {
                        System.err.println("Error: " + err);
                        err.printStackTrace();
                }
                return returnSku;
               
        }
        public static void closeDB() {
                try {
                        s.close();
                } catch (SQLException e1) {
                        e1.printStackTrace();
                }
                try {
                        SQLConnAccessDb.con.close();
                } catch (SQLException e) {
                        e.printStackTrace();
                }
        }
}

---

And finally here's some of my "Processing" class that does the work. I've stripped some unrelated parts in an attempt to focus on the problem:

Code:

---

import org.apache.commons.io.filefilter.WildcardFileFilter;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.math.*;

import org.w3c.dom.*;
import javax.xml.parsers.*;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class AswIUConverter {
        // for testing only
        public static void main(String[] args) {
                readIUXml();
        }
       
        public static File[] loadIUXml() {
                String iuDir = "C:\\Documents and Settings\\jason.s\\Desktop\\AMZ_TEST_DATA\\outbox\\";
                File dir = new File(iuDir);
                FileFilter fileFilter = new WildcardFileFilter("ASW_ItemUpdates_*.xml");
                File[] files = dir.listFiles(fileFilter);
                return files;
        }
       
        public static String readIUXml() {
                int numberOfItems = 0;
                //String itemSku = null;
                //String itemStatus = null;
                //String itemAvailability = null;
           
                File[] iuFiles = loadIUXml();
                int len = iuFiles.length;
               
                // iterate through the found IU files
                for (int q = 0; q < len; q++) {
                       
                        // debug
                        System.out.println(iuFiles[q]);
                        // debug
                       
                        String curIUFile = null;
                DataInputStream in = null;
              try {
                    File f = iuFiles[q];
                    byte[] buffer = new byte[(int) f.length()];
                    in = new DataInputStream(new FileInputStream(f));
                    in.readFully(buffer);
                    curIUFile = new String(buffer);
              } catch (IOException e) {
                      throw new RuntimeException("IO problem in fileToString", e);
              } finally {
                      try {
                              in.close();
                      } catch (IOException e) {
                              System.err.println("Error: " + e);
                  }
              }
              try {
                      DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
                      DocumentBuilder db = dbf.newDocumentBuilder();
                      InputSource inS = new InputSource();
                      inS.setCharacterStream(new StringReader(curIUFile));
                      Document doc = db.parse(inS);
                     
                      NodeList itemNodeList = doc.getElementsByTagName("Item");
                      numberOfItems = itemNodeList.getLength();
                     
                      // debug
                      System.out.println(numberOfItems);
                      // debug

                      for (int j = 0; j < numberOfItems; j++) {

                              Node itemNode = itemNodeList.item(j);
                             
                              if (itemNode.getNodeType() == Node.ELEMENT_NODE) {
                                      // set start node for iterations
                                      Element eElement = (Element) itemNode;
                                     
                                      // fetch Item node value
                                      String itemSku = eElement.getFirstChild().getNodeValue().trim();
                                      // fetch current item status for this IU xml file
                                      String itemStatus = getTagValue("Status", eElement).trim();
                                      // fetch current item Availability Quantity from IU xml file
                                      String itemAvailability = getTagValue("Availability", eElement).trim();
                                      // fetch sales unit of current item from IU xml file
                                      String itemSalesUnit = getTagValue("Unit", eElement).trim();
                                      // debug
                                      System.out.println(itemSku + " " + itemStatus + " " + itemAvailability + " " + itemSalesUnit);
                                      // debug
                                      // pass data to conversionsData() method for conversions to be done
                                      conversionsData(itemSku, itemStatus, itemAvailability, itemSalesUnit);
                              }
                      }
                     
              } catch (ParserConfigurationException pCE) {
                      System.err.println("Error: " + pCE);
              } catch (IOException iOE) {
                      System.err.println("Error: " + iOE);
              } catch (SAXException sAXE) {
                      System.err.println("Error: " + sAXE);
              }
                }
                return null;
        }
        public static void conversionsData(String itemSku, String itemStatus, String itemAvailability, String itemSalesUnit) {

                itemSku = sqlSku(itemSku);
                // if item status is Inactive "Delete" then set to 0 stock
                if (itemStatus == "DELETE") {
                        itemAvailability = "0";
                }
                // call sqlConversion to get ConversionFactor for stockUnits to salesUnits conversion
                String conversionFactor = sqlConversion(itemSku);
                // stock level 20% reduction algorithm
                // if item has negative "-" stock level, set it to 0 stock
                if (itemAvailability.indexOf('-') > -1) {
                        itemAvailability = "0";
                }
                // if not 0 stock, then reduce by 20% and set value
                else {
                        itemAvailability = (new BigDecimal(itemAvailability).multiply(new BigDecimal(0.80)).toBigInteger().toString());
                }
                // debug
                System.out.println(itemSku + " " + itemStatus + " " + itemAvailability + " " + itemSalesUnit);
                // debug
        }
        public static String sqlSku(String itemSku) {
                itemSku = SQLQueryAccessDb.sqlQuery("SELECT jdbSku, aswSku FROM xref WHERE aswSku = '" + itemSku + "'", 1);
                System.out.println("itemSku: " + itemSku);
                return itemSku;
        }
        public static String sqlConversion(String itemSku) {
                String conversionFactor = SQLQueryAccessDb.sqlQuery("SELECT Conversion FROM UnitConversionTable WHERE (Sku = '" + itemSku + "')", 1);
                System.out.println("queryResult: " + conversionFactor);
                return conversionFactor;
        }
        public static void convertToAmzXml(String itemSku, String itemStatus, String itemAvailability) {
               
        }
        private static String getTagValue(String sTag, Element eElement) {
                NodeList nlList = eElement.getElementsByTagName(sTag).item(0).getChildNodes();
                Node nValue = (Node) nlList.item(0);
                return nValue.getNodeValue();
        }
}

---

Ok, The Problem:

In my conversionsData() method i make two Queries, first using my sqlSku() method and secondly my sqlConversion() method. The problem is that when i call both of these methods from the same method

Code:

---

conversionsData()

---

, i end up with a "Invalid cursor state" error. However, if I run just one of those sql methods, I get the expected result (either one, but only one). If i move one of my calls to another method, say up into my

Code:

---

readIUXml()

---

, then both will work because they aren't in the same method. I really want to encapsulate both calls into my

Code:

---

conversionsData()

---

method so that i can keep similar functions grouped together a little better.

Can someone help me understand why this is happening? I"m reletively new to Java and pretty new to JDBC and working with databases in java... so i'm sure i've gone about something wrong. Thanks in advance!

Please post the full exception (with stack trace).
The only time I can think you would get that exception is if the previous query had thrown an exception and so closeDB wasn't called, however I can't even see how that would result in that error as you recreate the connection and statement, so it wouldn't be the same statement anyway.

Need the stack trace, and any other errors thrown by your code.

I would recommend putting the closeDB() call into a finally block, though. At the moment if you get an exception in the querying code you won't close anything off and will suffer a resource leak (connections in this case).

Hello Tolls!

Thanks for helping so far, here's my error from the console. BTW, i'm developing in Eclipse if that matters...

Code:

---

Error: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
        at com.amazonaws.mws.samples.SQLQueryAccessDb.sqlQuery(SQLQueryAccessDb.java:32)
        at com.amazonaws.mws.samples.AswIUConverter.sqlConversion(AswIUConverter.java:145)
        at com.amazonaws.mws.samples.AswIUConverter.conversionsData(AswIUConverter.java:124)
        at com.amazonaws.mws.samples.AswIUConverter.readIUXml(AswIUConverter.java:93)
        at com.amazonaws.mws.samples.AswIUConverter.main(AswIUConverter.java:17)

---

i'm going to try to move the closeDb() to a finally and will post back my results... in the meantime, if my error code above makes any sense, please advise!

i've also tried creating new local objects from the SQLQueryAccesDb class with the same exact result... i also thought that since I was recreating or calling a fresh instance of the object that i should not be getting this error... especially since if i comment out one of the calls to the SQLQueryAccessDb class then i get an expected result... as in the one query that does run thats not commented out gets the result i'm expecting, if i comment that one out and then uncomment the other, then it works fine as well. the problem is when i have both uncommented, then i get the invalid cursor state --- this is how i know the cursor is on the correct "line" of the resultset and has passed the result to a String, which should have no problem at all being passed back to my "processing" class (AswIUConverter).

thanks again!

EDIT: I should add, that in my console out, i can see that my firs query runs and returns the correct data (in this case a cross-refrenced product SKU number)... but the SQLException gets thrown on the second query. if I reverse the order, same thing, whichever query is run first, works, the second fails. i even tried rebooting my computer (which has the sample Access databse stored on it locally in order to ensure i didn't have any hung or open connections that could cause the invalid cursor error... not sure if that would matter or not). .. thanks again

SOLVED!

I feel like a total moron. I was querying the database and converting my product SKU to its cross-refrenced SKU number, then sending that new SKU number to the second query, instead of the original SKU, so it was returning no results because it never found a match in the query. My question is, shouldn't I have gotten a different Exception instead of invalid cursor state? I get that i can't call a rs.next() on a resultset that contains zero results, but I would think that my query would have returned something invalid and threw its own excpetion... maybe i'm wrong.

So i can prevent this going forward... what is a good way to check for invalid query results? a simple

Code:

---

if (rs.next() == null) {
// do something here
}

---

or is there a better way to handle this?

thanks again for your help!

Ah yes.
I should have seen that.
Strictly speaking the cursor is in an invalid state for trying to get data from it, as it is not pointing at a row. You'd get the same exception if you tried to call getString() before ever calling next().
The usual structure (for a single row) is:

Code:

---

if (rs.next()) {
// get data
}

---

or multiple rows:

Code:

---

while (rs.next()) {
// process row
}

---

ah... i think i'll go with your first example... since i should only be returning a single row for this program...

didn't know that a rs.next() returns a boolean value... thats much simpler than what I was trying to do...

thanks for the insight Tolls, and thanks for your help!