Results 1 to 6 of 6
  1. #1
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

    Question 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
    Java 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:
    Java 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:
    Java 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
    Java 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
    Java Code:
    readIUXml()
    , then both will work because they aren't in the same method. I really want to encapsulate both calls into my
    Java 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!

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,846
    Rep Power
    19

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

    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).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

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

    Hello Tolls!

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

    Java 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
    Last edited by SnakeDoc; 06-27-2012 at 08:39 PM.

  4. #4
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

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

    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
    Java Code:
    if (rs.next() == null) {
    // do something here
    }
    or is there a better way to handle this?

    thanks again for your help!

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,846
    Rep Power
    19

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

    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:
    Java Code:
    if (rs.next()) {
    // get data
    }
    or multiple rows:
    Java Code:
    while (rs.next()) {
    // process row
    }
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    SnakeDoc is offline Senior Member
    Join Date
    Apr 2012
    Posts
    129
    Rep Power
    0

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

    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!

Similar Threads

  1. Replies: 3
    Last Post: 05-07-2012, 06:07 PM
  2. Replies: 5
    Last Post: 05-03-2012, 10:37 AM
  3. Multiple interfaces to a single class?
    By Karenthian in forum New To Java
    Replies: 7
    Last Post: 05-15-2011, 10:10 PM
  4. Replies: 3
    Last Post: 02-28-2011, 05:09 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
  •