Problem with sime COUNT(*) query (JDBC-ODBC)

Jason Hindle

unread,

Feb 25, 1998, 3:00:00 AM2/25/98

to

Hi All

I've just started playing with JDBC and have come across a problem
getting a very simple "SELECT COUNT(*)" query working on an Access
database. In the following code, the first query on our Call Log
database works fine, but the it seems to fall over excecuting the second
simpler query:

String theurl = "jdbc:odbc:SUPPORT2";
String sql = "SELECT * FROM call_log WHERE system = 'PSION'";
String sql2 = "SELECT count(*) FROM call_log";
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection(theurl);
Statement s = c.createStatement();
ResultSet rs = s.executeQuery(sql);
ResultSet rs2 = s.executeQuery(sql2);

I've tested the count query in Access. When using via JDBC, the
following run time error is thrown:

ava.sql.SQLException: [Microsoft][ODBC Microsoft Access 97
Driver]Invalid column number
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Compiled Code)
at sun.jdbc.odbc.JdbcOdbc.standardError(Compiled Code)
at sun.jdbc.odbc.JdbcOdbc.SQLColAttributes(Compiled Code)
at sun.jdbc.odbc.JdbcOdbcResultSet.getColAttribute(Compiled
Code)
at sun.jdbc.odbc.JdbcOdbcResultSet.getColumnType(Compiled Code)
at sun.jdbc.odbc.JdbcOdbcResultSet.getMaxCharLen(Compiled Code)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(Compiled Code)
at UITest.MyClass1.main(Compiled Code)

What am I doing wrong?

Many thanks in advance
Jason Hindle

PS Email replies to ja...@hindle.demon.co.uk

Filip Hanik

unread,

Feb 27, 1998, 3:00:00 AM2/27/98

to

You can only have one live resultset per statement.

Now you are trying to have two resultset on the same statement.
Make sure you use the first one and close it before you execute the second
query.

You can also try to create two statements if your driver allows that. Not
all do.

Filip

Jason Hindle wrote in message ...

Filip Hanik

unread,

Feb 27, 1998, 3:00:00 AM2/27/98

to

You can only have one live resultset per statement.

You have to close the first one before executing the second query.

Filip
fi...@vergesoft.com

Jason Hindle

unread,

Mar 1, 1998, 3:00:00 AM3/1/98

to

In article <6d7pg0$6lj$1...@usenet49.supernews.com>, Filip Hanik
<d4f...@dtek.chalmers.se> writes

>You can only have one live resultset per statement.
>

>Now you are trying to have two resultset on the same statement.
>Make sure you use the first one and close it before you execute the second
>query.
>
>You can also try to create two statements if your driver allows that. Not
>all do.
>
>Filip

Hello

You were indeed right. After I'd corrected a silly mistake (not using
the next method before an initial getString), the program was working
(e.g. not crashing out) but not working. Adding an extra statement
object got everything up and running. I'm surprised that not all
drivers allow this kind of thing because it's pretty fundemental (e.g.
displaying a record on screen from the cursor and then having to do
another select on the same connection to display a coded list based on
one of the fields that has been changed).

Jason Hindle

Email jason at hindle dot demon dot co dot uk

PS The above notation is no joke. I don't often post to UNSENT
and inspite of taking precautions got a lot of spam selling
everything from Bulk Email Packages to a Russian Dating
Agency based in St Petersburg.