Re: "View" to a large schema

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1978d39f9c07107f9897ed_at_news.la.sbcglobal.net>...
> 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.

As for jumping in, I hope so too, but I do want to thank you for all the valliant help you're giving me.

I'll post the code Monday when I'm back at my work computer. Now that I've been able to dig into it a little bit I can certainly post it. I just didn't do that Friday becuase it was right before leaving time and I had friends to do and places to see :-).

thanks again for all your help!!! Have a great weekend. Received on Sat Jul 12 2003 - 18:16:12 CDT