Sign in to follow this  
Followers 0
jazzyjeff

.GetRows help

16 posts in this topic

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.

Share this post


Link to post
Share on other sites



Hmm.. Maybe:

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

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by jazzyjeff

Share this post


Link to post
Share on other sites
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

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

 

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)

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Tried this but the array doesn't load.

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

Thanks for the help on this!

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 :geek:

Edited by MikahS

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

No worries. Thanks MikahS

Share this post


Link to post
Share on other sites

Anytime jazzyjeff ;)

I will do some research on the subject as well.


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0