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

Discussion in 'ASP General' started by Laphan, Nov 20, 2004.

  1. Laphan

    Laphan Guest

    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
     
    Laphan, Nov 20, 2004
    #1
    1. Advertising

  2. Laphan

    dlbjr Guest

    '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, Nov 20, 2004
    #2
    1. Advertising

  3. Laphan

    dlbjr Guest

    expese = EXPENSE

    'dlbjr
    'Pleading sagacious indoctrination!
     
    dlbjr, Nov 20, 2004
    #3
  4. Laphan

    David Morgan Guest

    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.


    "dlbjr" <> wrote in message
    news:...
    > '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!
    >
    >
     
    David Morgan, Nov 20, 2004
    #4
  5. Asked and answered on .asp.db.

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

    plonk

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Nov 21, 2004
    #5
  6. dlbjr wrote:
    > '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


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Nov 21, 2004
    #6
  7. Laphan

    dlbjr Guest

    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, Nov 21, 2004
    #7
  8. Laphan

    dlbjr Guest

    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!
     
    dlbjr, Nov 21, 2004
    #8
  9. "dlbjr" <> wrote in message
    news:ursAgS%...
    > 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
     
    Chris Hohmann, Nov 21, 2004
    #9
  10. dlbjr wrote:
    > 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
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Nov 21, 2004
    #10
  11. Replies inline.

    "dlbjr" <> wrote in message
    news:OTn6IN%...
    > 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
     
    Chris Hohmann, Nov 21, 2004
    #11
  12. Laphan

    StephenMcC Guest

    Re: IsArray doesn't work with array var populated with xxx.GetRows

    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

    "Bob Barrows [MVP]" wrote:

    > Asked and answered on .asp.db.
    >
    > This is the second request I've made for you to stop multiposting. Welcome
    > to my killfile.
    >
    > plonk
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
     
    StephenMcC, Nov 22, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Åukasz Ligowski

    "xxx.has_key(a)" vs "a in xxx"

    Åukasz Ligowski, Oct 30, 2008, in forum: Python
    Replies:
    0
    Views:
    307
    Åukasz Ligowski
    Oct 30, 2008
  2. Replies:
    0
    Views:
    1,079
  3. Bert Leu

    value of type "xxx" cannot be converted to "xxx"

    Bert Leu, Jun 5, 2007, in forum: ASP .Net Web Services
    Replies:
    2
    Views:
    331
    Jesse Houwing
    Jun 6, 2007
  4. Richard Lionheart
    Replies:
    4
    Views:
    299
    Richard Lionheart
    May 5, 2004
  5. Iñaki Baz Castillo
    Replies:
    5
    Views:
    174
    Iñaki Baz Castillo
    Mar 30, 2008
Loading...

Share This Page