Alpha Video Training
Results 1 to 8 of 8

Thread: Return to beginning of resultset

  1. #1
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,845

    Default Return to beginning of resultset

    I have a SQL resultset I need to loop through twice.

    First, I am converting the resultset to JSON using the rs.toJson function.

    I then push that Json through an API which returns a result. From that result, I need to update the data in the original resultset. But the results only have new data - no matching keys. (Of course).

    But it is in the same order as the results were - so if I can loop through both simultaneously I can match things up.

    The issue is that using the toJson function sets the pointer at the end of the resultset, and I can't find a way to get back to the beginning.

    I could redo the SQL query, but that seems like an unnecessary server call.

    I tired the rs.gotorow function, but it didn't appear to do anything - so I may be using it wrong.

    Suggestions?

  2. #2
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,632

    Default Re: Return to beginning of resultset

    One method might be to put the resultset into an array, and then work from the array. Would that work for you?
    -Steve


  3. #3
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,845

    Default Re: Return to beginning of resultset

    I ended up copying the result set into two variables:

    ie:
    rs = cn.resultset
    rs2 = cn.resultset

    Then I worked with the two sets independently.

    But putting it into an array would have worked too!

  4. #4
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,845

    Default Re: Return to beginning of resultset

    Well, I THOUGHT my idea was working, but it doesn't look like it. When I advance one resultset, the other advances also - so it's not really making a new copy of the set.

    That array idea is looking better and better ...

  5. #5
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,845

    Default Re: Return to beginning of resultset

    As a final follow up on this. I ended up running the select statement twice after all. The first time, I run it to collect the IDs and put them into an array. I don't run the full select statement because that has a lot of other fields in it that I don't need in the array.

    Then I run the select statement with all fields and convert it to json. Gives me everything I need without taking up any more server memory than necessary.

  6. #6
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,399

    Default Re: Return to beginning of resultset

    Steve's suggestion of putting the results into an array is absolutely valid.
    It allows multiple methods of accessing the results.

  7. #7
    "Certified" Alphaholic
    Real Name
    unactive
    Join Date
    Dec 2006
    Posts
    1,573

    Default Re: Return to beginning of resultset

    Yes array is way to go.
    Anyway an interesting question is how to access the last element in an array. By the way ColdFusion has nice feature negative indeces so for example in alpha array the last one is alpha[-1] and one before alpha[-2].

  8. #8
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,399

    Default Re: Return to beginning of resultset

    I typically use a simple for/next loop
    Code:
    for loop = 1 to array.size()
    exVar = array[loop].item
    next

    If you absolutely want the last item of the array you could use array[array.size()].item .

Similar Threads

  1. Replies: 3
    Last Post: 08-22-2015, 10:33 AM
  2. Truncate beginning characters
    By John Cunradi in forum Application Server Version 8
    Replies: 1
    Last Post: 06-17-2008, 06:20 PM
  3. Beginning a relational database
    By lbrady in forum Alpha Five Version 5
    Replies: 1
    Last Post: 04-04-2003, 04:49 PM
  4. Files beginning with $
    By Gil Smith in forum Alpha Five Version 4
    Replies: 7
    Last Post: 08-22-2001, 06:53 AM
  5. Beginning Database
    By Chris Stevenson in forum Alpha Five Versions 2 and 3
    Replies: 1
    Last Post: 09-20-2000, 01:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •