.GetRows help

[jazzyjeff]

Hello,

I am trying to limit how much data of a database is put into an array. I am using a Microsoft SQL database and I can extract all the rows of data into an array without problems using the .GetRows.

I am hoping someone can assist me understanding how to use the parameters.

If I do this, I get the first 6000 rows:

$arraySQL = $oRS.GetRows(6000)

What I want is the last 6000 rows, so I have been looking at this site, but I don't understand how to use this.

http://www.w3schools.com/asp/met_rs_getrows.asp#bookmarkenum

i tried doing this, but that doesn't work. 

$arraySQL = $oRS.GetRows(6000, $oRS.adBookmarkLast)

I just can't find any code examples on getting that last directory. Lots of post explaining the same thing as the website above, I can't see how to put that into code.

Thanks.

[MikahS]

Hmm.. Maybe:

$arraySQL = $oRS.GetRows(6000, 2) ; start at the last record

[jazzyjeff]

I tried this, but it doesn't create an array. The only number I can enter to make it work is a 0. That doesn't really make it work, it just continues to produce the array.

The reason I am doing this is because my table as over 40000 records and growing, so the load time in listview is getting longer and longer. If I can restrict the number of records being loaded it will speed thing up. Then I would add a button to load all, or load the next 6000 to get the rest of the information

Edited September 17, 2014 by jazzyjeff

[MikahS]

I'm not sure if it is something to do with the 6000, but I doubt it (my knowledge on this subject is miniscule). This might be worth a try though.

$arraySQL = $oRS.GetRows(-1, 2) ; start at the last record

[j0kky]

 

I'm not sure if it is something to do with the 6000, but I doubt it (my knowledge on this subject is miniscule). This might be worth a try though.

$arraySQL = $oRS.GetRows(-1, 2) ; start at the last record

 

 

You can try:

$arraySQL = $oRS.GetRows(-6000, 2)

[jazzyjeff]

I tried this, but the array is not generated. 

$arraySQL = $oRS.GetRows(-1, 2) ; start at the last record

If I remove the ",2" then all the rows are loaded (takes 24 seconds to build the array and about another 30 seconds to add them into the listview)

[jazzyjeff]

Tried this but the array doesn't load.

$arraySQL = $oRS.GetRows(-6000, 2)

Thanks for the help on this!

[MikahS]

Give a try to j0kky's solution and post results

I think you'll have to wait for someone with more knowledge on this subject to show up

Edited September 17, 2014 by MikahS

[jazzyjeff]

No worries. Thanks MikahS

[MikahS]

Anytime jazzyjeff

I will do some research on the subject as well.

[jchd]

What is your SQL query? It's always better to restrict or constraint the query itself than giggle to pick only part of the resultset.

[jazzyjeff]

$sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("Provider=SQLOLEDB; Server=10.1.1.1; database=DB; User ID=Username; Password=" & $sPW & ";")

    $oRS = ObjCreate("ADODB.RecordSet")
    $sQuery = "SELECT * FROM Table"
    $oRS.Open($sQuery, $sqlCon, 2)

    If Not $oRS.EOF Then
        $timer = TimerInit()
        $arraySQL = $oRS.GetRows()
        ;_ArrayDisplay($arraySQL, "$avResults")
    Else
        MsgBox(0, "", "Error: No Results could be found.")
    EndIf
    $sqlCon.Close

[j0kky]

If nobody can help you, your last chance is to load the array with all the rows, then count the total number of elements

Ubound() 

and then delete the first x elements that you don't need

_ArrayDelete

This way is extremely slow and you should leave it as last alternative.

[jdelaney]

If you only want 6000 records, why not limit it in the query?

No use using the overhead to grab ALL data, to only use 6k.

 

You can create loop to select consecutive amounts (this isn't a loop, but an example of how):

; grab last 6000
Select top 6000 * from Table order by 1 desc
; grab second group of 6000 (last records)
Select top 6000 * from Table where id in (select top 12000 id from Table order by 1 desc) order by 1 asc

Edited September 17, 2014 by jdelaney

[jchd]

Your request doesn't make sense: you say you want to retrieve the last 600 rows but you don't give an ORDER BY clause. "Last" without order is meaningless.

Remember that SQL tables have no order and unless you explicit specify one in your select statement the engine is free to return rows in any order it finds convenient and even that isn't guaranteed to be stable accross several invokations of the same query.

Now, assuming you want ordering by rowid, you can use this to retrieve the last 6000 rows (less if the tables doesn't have 600 rows):

select * from (select top 6000 * from Table order by rowid desc) order by rowid;

Then use .getrows and the resulting array will have 6000 rows (or less if table is smaller)

This way the rowid order is preserved. If you accept rows in descending order, simplify the query to:

select top 6000 * from Table order by rowid desc;

Untested as I don't use MS SQL myself.

Edited September 17, 2014 by jchd

[jazzyjeff]

Sorry for the delay in getting back to you, I have been out of the office. Thanks for all your replies. 

The idea of using SQL to retrieve the relevant data makes sense. I will take this route to get the information I need.

Thanks!