execute sql cursor in statement

[johnv20]

Hi,
I'm trying to execute a sql statement containing a cursor within my code, I know the sql is correct as running in query analyzer returns records - however in java its throwing an exception for no ResultSet was produced.

Can anybody throw any light on this please

/* Code

import java.awt.*;
import javax.swing.JFrame;
import java.sql.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
public class fallout extends JFrame


{
private Connection connection;
private JTable table;
private Vector gridColumns;
private Vector gridRows;

public fallout()


{
String dsn = "jdbcdbc:BRMREPORT";
try


{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager.getConnection(dsn, "webserver", "");
}
catch (ClassNotFoundException cnf)


{
System.out.println("failed to connect");
}
catch (SQLException sqlx)


{
System.out.println("no more arecords");
}
showDatabaseTable();
setDefaultCloseOperation(EXIT_ON_CLOSE);
setSize(450,150);
setTitle("Family List");
show();
}
private void showDatabaseTable()


{
try


{
Statement state = connection.createStatement();
String query = " declare @built float, " +
" @fails float, " +
" @percent float, " +
" @tmpError varchar(50) " +
" declare TestFails cursor LOCAL FORWARD_ONLY READ_ONLY for " +
" select distinct a.ErrorText " +
" from SUTErrorSummary a, SUTProperty b, Family c " +
" where a.BarCode = b.BarCode " +
" and b.FamilyID = c.FamilyID " +
" and c.LOB = 1 " +
" and c.Name != 'UNKNOWN' " +
" --and a.TimeStamp > 'July 05 2004' " +
" open TestFails " +
" fetch next from TestFails into @tmpError " +
" while @@FETCH_STATUS=0 " +
" begin " +
" select @built = " +
" ( " +
" select count(distinct a.BarCode) " +
" from SUTProperty a, Family b " +
" where a.FamilyID = b.FamilyID " +
" and b.LOB = 1 " +
" and b.Name != 'UNKNOWN' " +
" --and a.TimeStamp > 'July 05 2004' " +
" ) " +
" select @fails = " +
" ( " +
" select count(distinct a.BarCode) " +
" from SUTErrorSummary a, SUTProperty b, Family c " +
" where a.ErrorText = @tmpError " +
" and a.BarCode = b.BarCode " +
" and b.FamilyID = c.FamilyID " +
" and c.LOB = 1 " +
" and c.Name != 'UNKNOWN' " +
" --and a.TimeStamp > 'July 05 2004' " +
" ) " +
" select @percent = (@fails/@built)*100 " +
" select Error = @tmpError, " +
" Fails = @fails, " +
" Built = @built, " +
" percen = @percent " +
" fetch next from TestFails into @tmpError " +
" end " +
" close TestFails " +
" deallocate TestFails ";
ResultSet result = state.executeQuery(query);
displayDatabaseRecords(result);
state.close();
}
catch (SQLException sqlx)


{
sqlx.printStackTrace();
}
}
private void displayDatabaseRecords(ResultSet rS) throws SQLException


{
boolean moreRecords = rS.next();
if (!moreRecords)
{
System.out.println("no more records");
}
gridColumns = new Vector();
gridRows = new Vector();
try


{
ResultSetMetaData rs = rS.getMetaData();
for (int i = 1; i <= rs.getColumnCount(); ++i)
gridColumns.addElement(rs.getColumnName(i));
do


{
gridRows.addElement(getNextRow(rS,rs));
}while (rS.next());
table = new JTable (gridRows, gridColumns);
JScrollPane scroller = new JScrollPane(table);
getContentPane().add(scroller, BorderLayout.CENTER);
validate();
}
catch(SQLException sqlx)


{
sqlx.printStackTrace();
}
}
private Vector getNextRow(ResultSet rS, ResultSetMetaData rs) throws SQLException


{
Vector currentRow = new Vector();
for (int i = 1; i <= rs.getColumnCount(); ++i)
{
currentRow.addElement(rS.getString(i));
}

return currentRow;
}
public static void main(String[] args)
{
fallout test = new fallout();
}
}

 

[sedj]

post the error ...

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[johnv20]

Error listed below -

java.sql.SQLException: No ResultSet was produced
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at fallout.showDatabaseTable(fallout.java:100)
at fallout.<init>(fallout.java:40)
at fallout.main(fallout.java:161)

Thanks

 

[sedj]

Well, I would say that either :

- your SQL query is not producing any results.
- The SQL is not generating a cursor that the JDBC driver understands as being a ResultSet.

I would probably say it was the second, because even a query that produces no rows returns a valid, if empty, ResultSet.

It probably has something to do with the fact that you not only have a SELECT statement, but also other statements in there too, which is confusing the driver. The kind of SQL transactions you are doing should really be out into a stored procedure, which should then return an actual cursor.
A simple SELECT statement should always return a ResultSet (even if empty) ...

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk