how do I determine the number of records before I access the data?

[Walter Moore]

I need to load an array with the results of a query. Is there a way to
know the number of records before I try to load up the data? I know that

select count (*) from mytable where field1 = 'whatever'
will return the number of records that meet my conditions, and then I
can use
select * from mytable where field1 = 'whatever'
and load the data, but I need to be sure the number of records does not
change between reads.

Is there a way to lock these records (or I suppose the table), then
count them, then select the records, then unlock them? or is there a
better way?

thanks,
Walter

[Ivan Santhumayor]

You can set the isolation level to 3 and run the sql within a tran.

set transaction isolation level 3

begin tran

select count (*) from mytable where field1 = 'whatever'

select * from mytable where field1 = 'whatever'

commit tran

This way, no data in this table is updated, but you allow others to read
from it.

[Indra Puri]

Another way to do it would be to get a scrollable ResultSet (I am
assuming you have a JDBC 2 compliant driver), you can do the
following:

Statement statement = connection.createStatement(

ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

ResultSet rs = ...
rs.last();
int noOfRows = rs.getRow();

Indra

[Carl Rosenberger]

Use
select max(pkey) into :myMaxPkey
first to make the moment atomic.

Then add
where pkey <= :myMaxPkey
to your whereClause for your two statements.

select count(*) ...
and
select * ...

If you want to be sure, that nobody deletes any records,
while you read your array, you might want to lock the records.
select for update
before you
select count(*)

Carl

Walter Moore <wal...@clover.c2d.fedex.com> schrieb in im Newsbeitrag:
3890804C...@clover.c2d.fedex.com...

[Michael Peppler]

Walter Moore wrote:
>
> I need to load an array with the results of a query. Is there a way to
> know the number of records before I try to load up the data? I know that
>
> select count (*) from mytable where field1 = 'whatever'
> will return the number of records that meet my conditions, and then I
> can use
> select * from mytable where field1 = 'whatever'
> and load the data, but I need to be sure the number of records does not
> change between reads.
>
> Is there a way to lock these records (or I suppose the table), then
> count them, then select the records, then unlock them? or is there a
> better way?

In general this is difficult to do.

It's better to modify your program logic so that you don't need to know
the number of rows in advance. I'm not a java programmer but I'm sure
that there exists things like linked lists or dynamic arrays that you
could use...

Michael
--
Michael Peppler -||- Data Migrations Inc.
mpep...@peppler.org -||- http://www.mbay.net/~mpeppler
Int. Sybase User Group -||- http://www.isug.com
Sybase on Linux mailing list: ase-lin...@isug.com

[Dave Wolf [Sybase]]

I couldnt agree with Michael more. There are plenty of ways to accomplish
handling the pending result set without needing to know in advance how many
rows there are.

Vector v = new Vector();
// Vectors are literally an unbounded collection and great for uses where
// you dont know how much data you will have

ResultSet rs = stmt.executeQuery()
while(rs.next())
{
// i.e. read ever row, now I dont need to know how many rows there are
}

Dave Wolf
Internet Applications Division

p.s. Its great to see you on here Michael. I used to do alot of sybperl
many moons ago. Id be glad to repay my programming debts in Java hints :)

Michael Peppler <mpep...@peppler.org> wrote in message
news:3890D92D...@peppler.org...