I can execute the stored procedure fine and collect the results when there is more than 1 row returned. However when the procedure returns 1-row ResultSet.next() always returns false so I can't get that data. Any ideas as to why this happens?
Here is my code:
List<Panel> panels = new ArrayList<>();
try {
String SPsql = "EXEC [dbo].[CR_Pick_Trim_Route] ?";
Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://database", "username", "password");
CallableStatement ps = conn.prepareCall(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(300);
ps.setString(1, route);
boolean hadResults = ps.execute();
while (hadResults)
{
ResultSet reset = ps.getResultSet();
while (reset.next()) {
String route_name = reset.getString("route_name");
String order_no = reset.getString("order_no");
String product = reset.getString("pick_comp");
String drop_no = reset.getString("drop_no");
String desc = reset.getString("long_description");
String analysisA = reset.getString("analysis_a");
String consumer = reset.getString("comp_cons");
String warehouse = reset.getString("pick_wh");
String key = reset.getString("Key");
int total = reset.getInt("pick_qty");
String scanned = reset.getString("Scanned");
String picked = reset.getString("picked");
String original = reset.getString("original");
Panel p = new Panel(route_name, order_no, drop_no, product, desc, analysisA, consumer, warehouse, (!picked.equals("No")), key, total, (scanned.contains("Y")), original);
panels.add(p);
}
hadResults = ps.getMoreResults();
}
