Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

java.sql.SQLException: Function sequence error

Posted on 2014-02-25
11
Medium Priority
?
1,446 Views
Last Modified: 2014-02-26
I am getting "Function sequence error " when trying to run this code:

 
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class  Prep  {


	private static final  String  USERNAME = "JDBC";
	private static final  String  PASSWORD =  "PASSWORD";

	private static final String  jdbcstr = "jdbc:as400:/COM.COM.COM";	



    public static void main(String[] args) {
 
		String salesku  = "78111";
		PreparedStatement stmt  = null;
		Connection con = null;
		
	    try 
        {
                                
	     DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
		 con  = (Connection) DriverManager.getConnection(jdbcstr, USERNAME,PASSWORD);	

         
	     String sql  = "SELECT  * from   MYLIB.SALES  WHERE  ((YEAR = 2012 AND MONTH >=5)  OR (YEAR = 2014 AND MONTH <= 02)) AND DIST in (1) AND SKU  =  ?";

		 System.out.println(sql);
	     	
	     stmt =  con.prepareStatement(sql);   
		 stmt.setString(1,"salessku");

	     ResultSet rs = stmt.executeQuery(sql);
	
	           // Get MetaData for Db2 table
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();
 
            // Iterate through the Db2 data in the result set
            while (rs.next()) {
                for (int i = 0; i < colCount; i++) {
                    System.out.println(metaData.getColumnName(i + 1) + ": " + rs.getString(i+1));
                }  
            }
    	
       
        }


             catch (Exception ex) {  
                
           			ex.printStackTrace(System.err);     
   
          }

           finally {
            if (stmt != null) try { stmt.close(); } catch(Exception e) {}
            if (con != null) try { con.close(); } catch(Exception e) {}
        }    

     }
        
 }  

Open in new window

0
Comment
Question by:sniger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 36

Expert Comment

by:mccarl
ID: 39887495
Can you please provide the full stack trace that you are getting?
0
 

Author Comment

by:sniger
ID: 39888456
mccarl:

java.sql.SQLException: Function sequence error.                              
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:408)  
        at com.ibm.as400.access.AS400JDBCPreparedStatement.executeQuery(AS400
JDBCPreparedStatement.java:1615)                                             
        at Prep.main(Prep.java:35)                                           

Open in new window

0
 
LVL 36

Expert Comment

by:mccarl
ID: 39888519
A couple of comments to make...

1) You can use the following connection string which should be able to give you more details on the exception being thrown. If you run the code with this, post the updated stack trace and we can go from there.
	private static final String  jdbcstr = "jdbc:as400:/COM.COM.COM;errors=full";

Open in new window

2) Rather than the call to DriverManager.registerDriver, the generally accepted way of loading the driver is as below. It probably won't make a difference to your current issue, but is something you might want to change, just in case. The difference is that in your code, the driver will actually get registered twice, once when the class is loaded and again due to the registerDriver call. The below only registers it once, when the class is loaded.
    Class.forName("com.ibm.as400.access.AS400JDBCDriver");

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sniger
ID: 39888642
mmcarl,

I modified as suggested, and the error stack is still the same
0
 
LVL 36

Accepted Solution

by:
mccarl earned 2000 total points
ID: 39888668
Ahhh, I just found what the problem is (I hope). Change line 35 of your code above to this...
	     ResultSet rs = stmt.executeQuery();

Open in new window

The change is that we are calling a different version of .executeQuery, the one WITHOUT the sql as the argument, because the sql was already given to con.prepareStatement(sql);
0
 

Author Comment

by:sniger
ID: 39888677
I removed SQL, it executes fine, but rs is null, and I know for sure I have data
0
 

Author Comment

by:sniger
ID: 39888683
ok, to be exact rs is CRSR0001

which i don't know what it means
0
 

Author Comment

by:sniger
ID: 39888768
actually it works , it did not like  * in the query. Thanks for pointing me into the right direction
0
 
LVL 36

Expert Comment

by:mccarl
ID: 39890541
it did not like  * in the query
That's strange because I'm sure that I have done similar before, maybe it is an AS400 specific limitation. However, it is generally a good idea to list the columns you want returned anyway.

ok, to be exact rs is CRSR0001
I'm not sure what you meant here, were you debugging the code to get this value or did you "print" it to console or something. "rs" is an object that has methods that you call on it, I don't know how it can have a "value" as such.

actually it works ..... Thanks for pointing me into the right direction
Not a problem, glad to help! :)
0
 

Author Comment

by:sniger
ID: 39890669
I don't know either,

so summerizing, removing sql  from executeQuery eliminated the run time error, then to get any results back I had to explicitly name the columns. Thanks again
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question