IsArray doesn't work with array var populated with xxx.GetRows()

[Laphan]

Hi All

I'm using .getRows() with a local var array instead of doing a recursive
loop so that I'm being a good ASP newvbie and closing my object i/o's (the
recordset in this case) as quick as possible.

My problem is that I can't seem to use this to complete good effect because
the IsArray statement doesn't seem to work with a local var array that has
or has not been populated with the .getRows() property.

To explain, I used to do the following recursive loop (simplified to show a
concise example):

.... create/open objects

IF NOT oRSv.EOF THEN
Do while not oRSv.EOF and not ii > cMax
Response.Write "<TR><TD>" & oRSv("Product") & "</TD></TR>"
oRSv.MoveNext
ii = ii + 1
Loop
END IF

.... close objects

I was told that the above is a bit of a resource hogger because I am
constantly calling the object and leaving it open whilst I do the loop, so I
moved on to using the .GetRows() method. This does seem quicker, but the
following has a problem if the recordset basically doesn't bring anything
back:

IF NOT oRSv.EOF THEN arrSQLData = oRSv.GetRows
oRSv.close

IF IsArray(arrSQLData) THEN << SEE *** BELOW
call ShowAboutUsContent()
ELSE
call NoAboutUsContent()
END IF

*** = if there is nothing in the oRSv.GetRows the IsArray() test still goes
through as true and then fouls up the 'ShowAboutUsContent()' sub because
there is nothing to display. I want to use this quick and easy test because
it is clean and easy to read, ie if there is some content then do
ShowAbout... sub if not then do NoAboutUs..., but this test doesn't seem to
work. To get round this for now I have had to do the following:

IF NOT oRSv.EOF THEN
arrSQLData = oRSv.GetRows
call ShowAboutUsContent()
ELSE
call NoAboutUsContent()
END IF
oRSv.close

Although the above works I'm not being efficient because one of the above
subs has to go through to completion before I can close the oRSv.close (in
this case).

Can somebody explain what I am doing wrong with the IsArray. Am I using it

in the way that it is intended?

Thanks

Laphan

 

[dlbjr]

'Laphan,
'If you are doing the expese of pulling the data into a recordset
'then purging into a multi demision array through the rs.GetRows,
'then why not use a disconnected recordset. This allows disconnecting,
'then using the recordset for what ever.

'Get Data
Set Conn = CreateObject("ADODB.Connection")
Conn.Open 'Connection string here
set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.CursorType = adOpenStatic
rs.Open strSQL,Conn,adOpenForwardOnly,adLockReadOnly

'Close and Disconnect
Set Conn = Nothing

'Do what ever with data
If Not rs.EOF Then

Do While Not rs.EOF

rs.MoveNext
Loop

End If

Set rs = Nothing

'dlbjr
'Pleading sagacious indoctrination!

 

[dlbjr]

expese = EXPENSE

'dlbjr
'Pleading sagacious indoctrination!

 

[David Morgan]

set rs = CreateObject("ADODB.Recordset") = Expense.

3 x 'rs.' without 'With' = Expense.

Set objRs = objConn.Execute(stSql, , adCmdText)
With objRs
If Not .EOF Then
arrResults = .GetRows
iResults = UBound(arrResults, 2)
bHasResults = True
End If
.Close
End With
Set objRs = Nothing

If bHasResults Then
For i = 0 To iResults
' Do Whatever with Data
Next
End If

Not saying there isn't more efficient ways than the above, especially things
like GetString with "<tr>" and "<td>" as row/column delimiters, but Set
objRs = Server.CreateObject("ADODB.RecordSet") is bad and wholly unncessary
in this scenario.

 

[Bob Barrows [MVP]]

Asked and answered on .asp.db.

This is the second request I've made for you to stop multiposting. Welcome
to my killfile.

plonk

 

[Bob Barrows [MVP]]

'Laphan,
'If you are doing the expese of pulling the data into a recordset
'then purging into a multi demision array through the rs.GetRows,

Because that is just as processor-intensive as using a connected recordset.
see the timing results at http://www.aspfaq.com/show.asp?id=2467

The numbers speak for themselves. Looping through an array is MUCH more
efficient than looping through a recordset, connected or disconnected. It is
much less expensive to use an array.

Bob Barrows

 

[dlbjr]

I agree looping through an array is quicker than looping through a recordset, but don't forget the
cost of getting the array from the already existing recordset.

Also, This allows you to filter and sort on the data if needed.
Cache the data into xml and store in a application or session object for reuse no reconnect between
hits.
You can push the XML output steam to the client if needed.

How would you do any of these functions easily with the array?

'dlbjr
'Pleading sagacious indoctrination!

 

[dlbjr]

By the way in the sample code you link to:

In the recordset function - The hit to loop thru the fields (With .Fields) and the two hits to get
(.Item(0).Value ) add unnecessary expense on this code for comparison to the GetRows and GetString
functions. One can write a function to loop through a client side recordset to hang with the GetRows
method.

'dlbjr
'Pleading sagacious indoctrination!

 

[Chris Hohmann]

By the way in the sample code you link to:

In the recordset function - The hit to loop thru the fields (With .Fields) and the two hits to get
(.Item(0).Value ) add unnecessary expense on this code for comparison to the GetRows and GetString
functions. One can write a function to loop through a client side

recordset to hang with the GetRows

method.

The recordset example does not loop through the fields collection. The
"With" statement is simply a way to establish scope for that particular
section of code. Here's a quote from the article explaining that section of
code:

Lines 54-68:
Iterate through each row in the Recordset object. The With statement is
employed throughout the iteration process to minimize object model traversal
and improve performance. An explicit reference to the Field value is made to
eliminate any ambiguity that can arise from the context sensitive nature of
using default methods/properties and again to improve performance.


If you feel that there is a more efficient way to code the recordset
function, I encourage you to do so and then share your findings with Aaron
and this group.

-Chris Hohmann

 

[Bob Barrows [MVP]]

I agree looping through an array is quicker than looping through a
recordset, but don't forget the cost of getting the array from the
already existing recordset.

Huh? The data needs to be brought to the client in either case. The GetRows
method does not involve a klunky loop, so it is not very expensive at all.

Also, This allows you to filter and sort on the data if needed.
Cache the data into xml and store in a application or session object
for reuse no reconnect between hits.

A disconnected recordset is not needed to put data into xml. Populating te
xml document from an array has got to be more efficient than looping through
a recordset. Again, the numbers speak for themselves.

You can push the XML output steam to the client if needed.

How would you do any of these functions easily with the array?

I do it every day. I put the data into xml and am able to use xpath queries
against the xml data.

Bob Barrows

 

[Chris Hohmann]

Replies inline.

I agree looping through an array is quicker than looping through a

recordset, but don't forget the

cost of getting the array from the already existing recordset.

The execution cost for the call to GetRows() is included in the overall
execution cost. All three methods use the same exact method to retreive the
recordset. If you disclude the overhead for retreiving the recordset, the
performance efficiencies of GetString/GetRows over Recordset iteration are
even greater. Here's a quote from the "Recordset Cons" section article:

[begin]
It is important to note that it is not the Recordset itself that degrades
performance and resource utilization. In each of the three (3) examples, the
same Recordset object is being instantiated in an identical manner. The
performance of the Recordset iteration method is attributable to its heavy
use of properties and methods of the Recordset object and repeated traversal
of the object model.
[end]

If you were to persist the recordset to Application/Session scope, the
retreival process for the recordset would still be the same for each method.
The execution overhead for retreiving the recordset would be lower, but
again, this would only serve to amplify the performance efficiencies of
GetString/GetRows over Recordset iteration.

Also, This allows you to filter and sort on the data if needed.

The recordset sort and filter properties are discussed in the "GetRows Cons"
section of the article. Here's the quote:

[begin]
Another downside to the GetRows method is that the resultant array is
specific to the VBScript language. While other scripting languages recognize
VBArrays, additional steps must be taken to transform the VBArray into, for
example a native JScript array. VBArrays also lack many of the methods and
properties available to JScript array. For example, JScript arrays provide a
powerful method that by default sorts an array lexically but also accepts a
custom function as a parameter to the method call. In the absence of such
functionality one must sort the data prior to calling the GetRows method by
setting the Recordset Sort property or altering the underlying data source.
Or in the alternative, build a custom function to reproduce the sorting
capabilities of JScript arrays.
[end]

I guess the point to take away here is that you can use the filter/sort
properties of the recordset object in conjunction with the
GetString/GetRows, just as you could with Recordset iteration. What I'm
lamenting in the above quote is that if GetRows was not restricted to VB
Safe Arrays, on could take advantage of the powerful sorting capabilitirs
inherent in JScript/Javascript arrays. The quote also mentions another
alternative for sorting which is to incorporate the sort into the underlying
datasource. While it was beyond the scope of the article, it bears
mentioning that therer are many compelling reasons to perform the sort via
the database engine, not the least of which is the database engine's ability
to utilize indexes, hashes and statisitics to perform optimized sorting
based on the actual characteristic of the data being queried.

Cache the data into xml and store in a application or session object for reuse no reconnect between
hits.
You can push the XML output steam to the client if needed.

Persisting the data to XML and streaming it to the Response object is
discussed in the "Recordset Pros" section of the article. Quote:

[begin]
For example, the Save method of the Recordset object allow the underlying
data to be converted into XML format. The XML data can be saved to a file or
more interestingly be sent to any object that implements the OLE DB IStream
interface, such as the ASP Response object. While outside the scope of this
article, this technique provides exciting possibilities to move from simply
displaying Recordset data to publishing that data in a format that can be
utilized by any XML aware application.
[end]

I'd like to clarify that persisting the recordset to XML is not limited to
recordset iteration. It can be employed by any of the three approaches. The
point is that the recordset object has many powerful
methods/properties/members that arrays and strings do not. But I can't
emphasisze strongly enough that a distinction must be drawn between
"objects" (recordset, array, string) and processes (recordset iteration,
array looping, string concatenation).

How would you do any of these functions easily with the array?

You wouldn't do them with an array, but per my above rant, you would do them
exactly the same way with the array looping PROCESS. Here's some pseudo
code:

1.Retrieve recordset from XML persisted in session scope.
2.Set filter and sort properties of the recordset.
3.Call the GetRows method of the recordset
4.Close and deallocate recordset.
5.Do array looping

 

[StephenMcC]

I'm not going to post a solution cause you've got plenty here, all I'm gonna
say is that .GetRows is proabably just not returning an array, and a way to
test this is to use the VarType to tell you what data type it is, for example
if it is an array it should pass 8192 (or there abouts) or 8200 for a string
array. Check out VarType function and VbVarType enum, these are VB functions
but are also available in ASP. Build ur debugging skills as well as ur
development skills. I've included an (vb) example below:

Dim oArray(0) As Variant
oArray(0) = "Data1"
Debug.Print oArray(0)
Debug.Print VarType(oArray) '8204
Debug.Print VarType(oArray(0)) '8