Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

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

6 posts in JConnect version 3.0 Last posting was on 2000-01-29 23:05:29.0Z
Walter Moore Posted on 2000-01-29 00:14:21.0Z
From: Walter Moore <walter@clover.c2d.fedex.com>
Subject: how do I determine the number of records before I access the data?
Date: Fri, 28 Jan 2000 19:14:21 -0500
Organization: Federal Express
Lines: 17
Message-ID: <3890804C.92FC7348@clover.c2d.fedex.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: is1.network.fedex.com 948994645 25545 199.81.71.202 (27 Jan 2000 17:37:25 GMT)
X-Complaints-To: news-admin@network.fedex.com
NNTP-Posting-Date: 27 Jan 2000 17:37:25 GMT
X-Mailer: Mozilla 4.5 [en] (Win98; U)
X-Accept-Language: en,es-MX
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: d41-207.hcvlny.optonline.net 24.188.41.207
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!boomer.icehouse.com!news.optonline.net!uunet!nyc.uu.net!newsfeed.mathworks.com!news.new-york.net!newspeer1.nac.net!news.lightning.net!abq.news.ans.net!news-w.ans.net!news.network.fedex.com!not-for-mail
Xref: forums-1-dub sybase.public.jconnect40:225 sybase.public.jconnect30:1694
Article PK: 256516

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


Michael Peppler Posted on 2000-01-29 00:14:23.0Z
From: Michael Peppler <mpeppler@peppler.org>
Subject: Re: how do I determine the number of records before I access the data?
Date: Fri, 28 Jan 2000 19:14:23 -0500
Organization: Data Migrations, Inc
Lines: 29
Message-ID: <3890D92D.BBF1B7F9@peppler.org>
References: <3890804C.92FC7348@clover.c2d.fedex.com>
X-Complaints-To: newsabuse@supernews.com
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: d41-207.hcvlny.optonline.net 24.188.41.207
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!boomer.icehouse.com!news.optonline.net!uunet!nyc.uu.net!newsfeed.mathworks.com!arclight.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!remarQ70!rQdQ!supernews.com!remarQ.com!corp.supernews.com!not-for-mail
Xref: forums-1-dub sybase.public.jconnect40:221 sybase.public.jconnect30:1690
Article PK: 255139


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.
mpeppler@peppler.org -||- http://www.mbay.net/~mpeppler
Int. Sybase User Group -||- http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com


Dave Wolf [Sybase] Posted on 2000-01-29 23:05:29.0Z
From: "Dave Wolf [Sybase]" <dwolf@sybase.com>
References: <3890804C.92FC7348@clover.c2d.fedex.com> <3890D92D.BBF1B7F9@peppler.org>
Subject: Re: how do I determine the number of records before I access the data?
Date: Sat, 29 Jan 2000 18:05:29 -0500
Lines: 55
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <AgRFJ3qa$GA.85@forums.sybase.com>
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: 158.159.8.11
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.jconnect40:220 sybase.public.jconnect30:1689
Article PK: 255140

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 <mpeppler@peppler.org> wrote in message
news:3890D92D.BBF1B7F9@peppler.org...
> 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.
> mpeppler@peppler.org -||- http://www.mbay.net/~mpeppler
> Int. Sybase User Group -||- http://www.isug.com
> Sybase on Linux mailing list: ase-linux-list@isug.com


Carl Rosenberger Posted on 2000-01-29 00:14:22.0Z
From: "Carl Rosenberger" <carl.rosenberger@t-online.de>
Subject: Re: how do I determine the number of records before I access the data?
Date: Fri, 28 Jan 2000 19:14:22 -0500
Organization: T-Online
Lines: 43
Message-ID: <86qa1s$q5c$1@news08.btx.dtag.de>
References: <3890804C.92FC7348@clover.c2d.fedex.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: news08.btx.dtag.de 949005180 26796 08936100717-0001 000127 20:33:00
X-Complaints-To: abuse@t-online.de
X-Sender: 08936100717-0001@t-dialin.net
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: d41-207.hcvlny.optonline.net 24.188.41.207
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!boomer.icehouse.com!news.optonline.net!uunet!nyc.uu.net!newsfeed.mathworks.com!newsfeed.tli.de!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!newsmm00.btx.dtag.de!t-online.de!news.btx.dtag.de!not-for-mail
Xref: forums-1-dub sybase.public.jconnect40:223 sybase.public.jconnect30:1692
Article PK: 256514

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 <walter@clover.c2d.fedex.com> schrieb in im Newsbeitrag:
3890804C.92FC7348@clover.c2d.fedex.com...

> 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 Posted on 2000-01-29 00:14:22.0Z
From: Ivan Santhumayor <ivan.santhumayor@gs.com>
Subject: Re: how do I determine the number of records before I access the data?
Date: Fri, 28 Jan 2000 19:14:22 -0500
Organization: Goldman Sachs & Co.
Lines: 33
Message-ID: <3890997D.A986F189@gs.com>
References: <3890804C.92FC7348@clover.c2d.fedex.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.03 [en]C-CPT_1.1.0 (WinNT; I)
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: d41-207.hcvlny.optonline.net 24.188.41.207
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!boomer.icehouse.com!news.optonline.net!uunet!nyc.uu.net!ffx.uu.net!news.gs.com!nocppsw01.wan.gs.com!not-for-mail
Xref: forums-1-dub sybase.public.jconnect40:224 sybase.public.jconnect30:1693
Article PK: 256515

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.

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?
>
> thanks,
> Walter


Indra Puri Posted on 2000-01-29 00:14:23.0Z
From: indra@rdmcorp.com (Indra Puri)
Subject: Re: how do I determine the number of records before I access the data?
Message-ID: <3890a946.7585807@nntp.uunet.ca>
References: <3890804C.92FC7348@clover.c2d.fedex.com> <3890997D.A986F189@gs.com>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 52
Date: Fri, 28 Jan 2000 19:14:23 -0500
X-Trace: nnrp1.uunet.ca 949004842 204.225.180.18 (Thu, 27 Jan 2000 15:27:22 EST)
NNTP-Posting-Date: Thu, 27 Jan 2000 15:27:22 EST
Organization: UUNET Canada News Reader Service
Newsgroups: fedex.databases.sybase,comp.databases.sybase,comp.lang.java.databases,sybase.public.jconnect40,sybase.public.jconnect30
NNTP-Posting-Host: d41-207.hcvlny.optonline.net 24.188.41.207
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!boomer.icehouse.com!news.optonline.net!uunet!nyc.uu.net!newsfeed.mathworks.com!sunqbc.risq.qc.ca!torn!cyclone.bc.net!news.uunet.ca!nnrp1.uunet.ca.POSTED!not-for-mail
Xref: forums-1-dub sybase.public.jconnect40:222 sybase.public.jconnect30:1691
Article PK: 256513

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

On Thu, 27 Jan 2000 14:16:13 -0500, Ivan Santhumayor

<ivan.santhumayor@gs.com> wrote:

>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.
>
>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?
>>
>> thanks,
>> Walter
>
>
>