SEO

Hi Folks. I have a large number of records that are deliverd for web
view.
This may sound a bit wierd but its a user requirement.

I will have 100,000 records for view to the user. Currently I send
records to a temp tableand view them in a web page in java. There I use
the "<< < 1 2 3 4 5... 99 100 > >>" method at the bottom of the
page to allow the user to view the chunks.

select * from tablename where rowid > first chunk and rowid < next
chunk.

Now the user wishes to do a sort on up to 3 columns. This ruins my old
method of doing things. I cant scroll out chunks, because a select (
Select * from table name order by col8, col6, col3 ) does not reorder
the rowid.

So, then I thought of doing a 'Select into' a new temp table. But,
then I have to keep track of the new temp table name each time. I read
some where, and I hope its correct, that when you create a temp table
any new records are stored in order of the select into.

Every time the user chooses a new way to sort the data from the web
page, I think I should create a new Temptable, unless there is some way
to update the rowid on the original table. The sorted data order must
be maintailed over multiple sessions. there will be only one user, but
when he is done with this I can ditch the temp table till he creates
new data.


Im not sure this is the way to go. But, I need to finish it soon.
Suggestions? solutions?

In ASE 15 , you could use scrollable cursors for this. In pre-15, you'll
have to handle the scrolling yourself, for example through a temporary
table.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"runescience" <runescience@yahoo.com> wrote in message
news:1138629334.581707.173070@z14g2000cwz.googlegr oups.com...
> Hi Folks. I have a large number of records that are deliverd for web
> view.
> This may sound a bit wierd but its a user requirement.
>
> I will have 100,000 records for view to the user. Currently I send
> records to a temp tableand view them in a web page in java. There I use
> the "<< < 1 2 3 4 5... 99 100 > >>" method at the bottom of the
> page to allow the user to view the chunks.
>
> select * from tablename where rowid > first chunk and rowid < next
> chunk.
>
> Now the user wishes to do a sort on up to 3 columns. This ruins my old
> method of doing things. I cant scroll out chunks, because a select (
> Select * from table name order by col8, col6, col3 ) does not reorder
> the rowid.
>
> So, then I thought of doing a 'Select into' a new temp table. But,
> then I have to keep track of the new temp table name each time. I read
> some where, and I hope its correct, that when you create a temp table
> any new records are stored in order of the select into.
>
> Every time the user chooses a new way to sort the data from the web
> page, I think I should create a new Temptable, unless there is some way
> to update the rowid on the original table. The sorted data order must
> be maintailed over multiple sessions. there will be only one user, but
> when he is done with this I can ditch the temp table till he creates
> new data.
>
>
> Im not sure this is the way to go. But, I need to finish it soon.
> Suggestions? solutions?
>

Can we access rowid in Sybase?

In ASE, you can use an identity column to simulate the concept of a rowid.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Dhannya" <dhannyasm@gmail.com> wrote in message
news:1138631475.735956.308070@z14g2000cwz.googlegr oups.com...
> Can we access rowid in Sybase?
>

i think so we can, i am using sybase IQ 12.

How would a scrollable cursor help here?

I would hae to make a new cursor each time for each time the sort is
re-done.

The cursor is not persistant from java session to java session, I think
I would have to resort them each time.

I need to have the rowID updated into a new temp table each time.

Am I missing something? could you maybe re-read the original post?

On Mon, 30 Jan 2006 05:55:34 -0800, runescience wrote:

> Hi Folks. I have a large number of records that are deliverd for web view.
> This may sound a bit wierd but its a user requirement.
>
> I will have 100,000 records for view to the user. Currently I send records
> to a temp tableand view them in a web page in java. There I use the "<<
> < 1 2 3 4 5... 99 100 > >>" method at the bottom of the page to allow
> the user to view the chunks.
>
> select * from tablename where rowid > first chunk and rowid < next chunk.
>
> Now the user wishes to do a sort on up to 3 columns. This ruins my old
> method of doing things. I cant scroll out chunks, because a select (
> Select * from table name order by col8, col6, col3 ) does not reorder the
> rowid.
>
> So, then I thought of doing a 'Select into' a new temp table. But, then I
> have to keep track of the new temp table name each time. I read some
> where, and I hope its correct, that when you create a temp table any new
> records are stored in order of the select into.

I would use a secondary table that only has the original row id (or
identity value) for the row, and a second column that has the row id for
the sorted data. Creating this second table should be pretty fast, and you
can then do the retrieves based on the two tables (a simple join with an
order by on the second table's row id).

You can also simplify your code by always using the two table method. For
the original sort you simply have the secondary table with identical
column values.

Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html