Thought inc.

The Dynamic O/R Mapping Company
     

CocoBase Technical Support Forums
For access to developer site with software download and doc's, please request CocoBase download and password
will be emailed to you. Response to posts will appear when answered by THOUGHT Support team.
NOTE:You must register separately with forum in order to post your questions, please click on register icon below.
Home   Frequently Asked Questions   Search   Edit your profile   Registration is free!  
Email This Page to Someone!
Show a Printable Version
CocoBase Enterprise O/R Forums > CocoBase Questions
How to implement block/range queries 4.5
< Previous Thread     Next Thread >
Author
Thread    Post New Thread     Post A Reply
BHLossini
Member

Registered: Jun 2005
Posts: 7

Our installation is using CocoBase 4.5 tied to an Oracle 9.i database running under iPlanet 6.5 application server.

We have several web pages that retrieve potentially 10's of thousands of rows resulting from a single select statement. We have implemented ogic to display a reasonable number of rows of data per page (i.e. pagination). The bulk of the application code that implements this paging logic retrieves all of the resulting rows and then returns the subset of that result to satisfy the paging requirements. My question is whether 4.5 provides the option of selecting a specific range of rows based on rownum or similar. For example, we may want to retrieve rows 1,000 - 1,100 out of 10,000 existing rows. If this is possible can you please point me to documentation that illustrates how this is implemented? Thanks

05-03-2006 10:05 AM
Click Here to See the Profile for BHLossini    Find more posts by BHLossini        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Absolutely. There is scrollable cursor support built into the querying system of CocoBase. Whether you issue a QBE or an EJBQL Query, by passing in a 'page size', you'll be able to return a CBCursor to your application, and that will automatically load pages (of the size you specify) internally as you page through with a cursor.next();

You can also jump to a location without ever loading that object with code such as:

CBCursor cursor = cbf.loadAll(myQueryObject, myMapName, 100);
// Jump to location before issuing next() minimizes number
// of objects processed into application.
cursor.setPos(1000);
ArrayList returnBlock = new ArrayList();
for(int i=0; i<100; i++) {
if(cursor.hasNext())
returnBlock.add(cursor.next());
else break;
}
cursor.close();

There are other lower level methods that can be used to limit the result size, etc., but this approach is generally sufficient for most developers.

Be aware that calling size() can be an inefficient method for determining the result set size on Oracle because of its jdbc driver architecture. If you need to determine the result set size for paging as part of this query, please consult us for the best option for your particular usage requirements.

Does this answer your question?

THOUGHT Support

05-03-2006 12:00 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
BHLossini
Member

Registered: Jun 2005
Posts: 7

As a standard in all of our CCB implementation today, we use the following syntax:

Collection = pm.selectInstances(myQueryObject, myMapName)

I'll implement your recommended code when a select block of rows is required(i.e. pagination).

What about error checking? For instance, what if for some reason I execute cursor.setPos(1000) and there are only 999 rows? Although the client will be sending the mid-tier the next block of rows to retrieve, it is plausible for some other user to change that count via other processes which could change the original result set size.


(And yes, I definitely need to determine the result set size for paging as part of this query. So please advise. If can determine the full result set size, I can implement my own error checking before implementing the block retrieval)


Thank you!

05-03-2006 02:02 PM
Click Here to See the Profile for BHLossini    Find more posts by BHLossini        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Here are some general answers and guidance for using scrollable cursors in an environment such as yours.

1) You'll want to tell the CocoBase runtime that you need to use cursors, by adding the proper :cocoprop qualifier to your url - such as:

jdbc:oracle:thin:@localhost:1521:ORA:cocoprop=dynamic.querying=true,preserve.statements=true,usescrollablecursor=true,cocobase.cursorScrollType=type_scroll_sensitive,cocobase.cursorConcurrency=concur_updatable

2) You'll need to connect with CocoPowderPlugin20 instead of CocoPowder as your SQL runtime class.

3) If you call setPos() past the end of the result set, it will simply set the cursor to the end of the result set - so no more records would be returned, and hasNext() would return false.

4) The result set is a snapshot of the database at the point the query was executed, so changes to the database would not be reflected in that result set until a new query is issued.

5) Result set size can be determined in different ways, each of which has different implications.

- You can use a different map with a select count(*) as the field entries, and issue that query before initiating the actual one. This approach requires a second map that matches the same query criteria, which isn't optimal from a maintenance perspective.
- You can use a CBQuery or CBQueryBuilder facility to issue an equivalent query to your current QBE/EJBQL, and those advanced Query APIs have CBQueryBuilder.selectCount() and CBQuery.setExtentIsCountOnly(true) which both modify the generated query to return a select count(*).
- You can use EJBQL select count(...) syntax such as:
SELECT COUNT( c )
FROM Customers AS c
WHERE c.address.state = 'CA'
- You can also call CBCursor.size() - but in the case of Oracle it might produce a very slow result. If you intend to page through the entire list, or are likely to jump to the end of the list, this is not an unreasonable choice. If you typically start near the beginning of the list, and only read small pages, then this is likely to be the slowest option.

These are some of your options, and you might want to prototype the choices that seem most suitable to your application to see which performs best for your particular application usage. You may find you use different options for different queries if your application requirements are diverse enough.

THOUGHT Support

05-03-2006 03:27 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
malkinzon
Member

Registered: Apr 2006
Posts: 4
OutOfMemory Paginating Large ResultSets

Currently I am using Sybase ASE 12 and JConnect 5.4 driver. If the results set is large, (150,000 rows) and I would like to skip the first 100,000 rows and retrieve a block of 100 I get OutOfMemory because the resultset is streamed locally. Is there any settings I can use to avoid streaming the entire result just to get 100 records.

Thanks

Mike

05-23-2006 11:27 AM
Click Here to See the Profile for malkinzon    Find more posts by malkinzon        Edit/Delete Message    Reply w/Quote    IP: Logged
support
Super Moderator

Registered: Apr 2001
Posts: 1
OutOfMemory Paginating Large ResultSets

Hi,

If you are using the CBFacade api, you can create a query that has a page size and returns a CBCursor. Then you can process one object at a time, and unload() that object to release memory resources allocated by it - for example:

CBCursor cbcursor = cbfacade.executeQLQuery(
"select Object(c) from com.foo.Customer c", 100);
while (cbcursor.hasNext()){
Customer tempCust=(Customer) cbcursor.next();
processCustomer(tempCust);
cbfacade.unload(tempCust);
}

If you are using lower level apis (e.g. CocoDriverInterface), then you can directly use CBQuery() for cursored queries (see chapters 13 and 14 of the Programmer's Guide for more details/alternatives).

The one thing you need to make sure though is that your connection url has the :cocoprop entry usescrollablecursor=true so the internal system can activate that feature through the jdbc driver.

Hope that helps!

THOUGHT Support

05-23-2006 12:55 PM
Click Here to See the Profile for support    Find more posts by support        Edit/Delete Message    Reply w/Quote    IP: Logged
malkinzon
Member

Registered: Apr 2006
Posts: 4

I am using CocoDriverInterface.selectNext and setting the start position at 100000 as my first call. But it looks like the jdbc driver does not support serverside cursor and the size of the Sybase TDS Stream grew to large. I actually got around it by using a jTDS driver (had server side cursor support).

05-24-2006 06:43 AM
Click Here to See the Profile for malkinzon    Find more posts by malkinzon        Edit/Delete Message    Reply w/Quote    IP: Logged
support
Super Moderator

Registered: Apr 2001
Posts: 1

Yes, that makes sense. Thanks for the feedback. Some drivers not supporting server side cursors is not unusual. Also, pay attention to the performance of the cursor.size() function calls (note that cursor.hasNext() internally issues a size() query the first time it is invoked) - some drivers cause the server to load each and every entry in the query result before they can return a count for the size of the query, which is of course insane when you are dealing with thousands of records.

Hope this helps!

THOUGHT Support

05-24-2006 09:28 AM
Click Here to See the Profile for support    Find more posts by support        Edit/Delete Message    Reply w/Quote    IP: Logged
All times are PST (US)    Post New Thread     Post A Reply
Forum Jump:
< Previous Thread     Next Thread >

Forum Rules:
Who Can Read The Forum? Any registered user or guest.
Who Can Post New Topics? Any registered user.
Who Can Post Replies? Any registered user.
Changes: Messages can be edited by their author. Messages can be deleted by their author.
Posts: HTML code is OFF. Smilies are OFF. vB code is OFF. [IMG] code is OFF.

Admin Options:
Open / Close Thread
Move Thread
Delete Thread
Edit Thread

< Contact Us - THOUGHT Inc. >

Copyright © Jelsoft Enterprises Limited 2000.
Copyright 2001 All Rights Reserved, THOUGHT Inc.