Re: "View" to a large schema

Hi Hypermommy, thanks for writing this:
> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.19748657318ea7f09897e6_at_news.la.sbcglobal.net>...
>
> <snipped for brevity>
>
>
> > Hmm ... I see your point. If you can't change the ASP without opening
> > another can of worms (maintenance nightmares) and you can't change the
> > schema (the ePortfolio application's Oracle userid that owns all the
> > tables), then you don't have many options. Bad news.
>
> Hi again, Karsten,
>
> I've got a bit of a follow up on this but don't know if this is an
> Oracle related problem or ASP or my machine or what at this point.
>
> I got permission from mgmt to possibly make *small* changes in the ASP
> code. So I got to looking at the code that was giving me problems.
> Basically, what it does is opens a recordset listing information about
> the tables defined in the catalog that are accessible to a given user.
>
> set objTableRS = objConn.OpenSchema(adSchemaTables)
>
> That sounds like it would do what we were talking about -- the
> selection of only tables that have had rights granted to the user that
> logs in.
>
> But then I got to wondering... becuase this is all stemming from the
> idea that the page is hanging and we thought it was due to the number
> of records. So I put replaced the "do until rs.EOF" with a for loop
> that would let it run 10 times. That worked like a charm (except for
> one thing, which I'll get to in a moment). And then I said to myself,
> well, how many tables do I have in here so I tried to print out a
> recordcount of the obTableRS. Well, turns out I didn't have any. So
> I checked for BOF and EOF and sure enough, I seem to have an empty
> recordset. Which would explain why objTableRS("Table_Name") kept
> coming back blank.
>
> So I've got an empty recordset it says...... but then there's one more
> twist. I can set my upper limit on the for loop to 1000 and
> everything runs just fine. However, if I go back to my "do until
> rs.EOF" loop, the darn page hangs up. Can't be something else that's
> hanging the page becuase when I replace the do loop with a for loop,
> all works well. Can't be that it's not moving becuase I did
> double-check that I've got an objTableRS.MoveNext in there.
>
> I'm just stumped..... do you see anything in here, anyone, that you
> can clue me into?
>
> Thanks.
>

Again, without the code in front of me, I'm just taking a shot in the dark. Since it seems to work with a FOR loop, but not a DO-UNTIL loop, it's beginning to sound like you are trying to open a connection AFTER you've already reached the rs.EOF. Do you see anywhere that you close the connection? If the code already detects an EOF when it comes to the DO-UNTIL statement, then the DO won't get executed and you'll have an empty RS. The FOR would execute, however, because it loops a specific number of times.

I'm not an ASP expert (we use the similar JSP), so I hope someone else will jump in here.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]