sniger
asked on
java.sql.SQLException: Function sequence error
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) {}
}
}
}
Can you please provide the full stack trace that you are getting?
ASKER
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)
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. er, 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.
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";
2) Rather than the call to DriverManager.registerDriv Class.forName("com.ibm.as400.access.AS400JDBCDriver");
ASKER
mmcarl,
I modified as suggested, and the error stack is still the same
I modified as suggested, and the error stack is still the same
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I removed SQL, it executes fine, but rs is null, and I know for sure I have data
ASKER
ok, to be exact rs is CRSR0001
which i don't know what it means
which i don't know what it means
ASKER
actually it works , it did not like * in the query. Thanks for pointing me into the right direction
it did not like * in the queryThat'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 CRSR0001I'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 directionNot a problem, glad to help! :)
ASKER
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
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