Re: "View" to a large schema

Hi Hypermommy, thanks for writing this:
> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1978d39f9c07107f9897ed_at_news.la.sbcglobal.net>...
>
> <snipped for brevity>
>
> > 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.
>
> Hi all,
>
> I'm posting the code, now that I kind of understand what's going on.
> For a quick recap, I've found that if I put in a for loop instead of
> the do while not loop then I can iterate up to 1000 times and the page
> doesn't freeze up. However, if I use the do loop, it just freezes. A
> couple of things I don't understand that I hope you can help me
> with.....
>
> 1) The OpenSchema method doesn't appear to be working since I get back
> an empty recordset (as evidenced by the fact that when I check for BOF
> and EOF they're both true). Any clue why? It does work with a SQL
> server table... shouldn't it also work with Oracle? I know that the
> login being used in the DSN has full rights to at least see the
> tables.
>
> 2) If EOF is true, why would the do while cause things to freeze up.
> I can use a 1000 iteration for loop, but not a do while but if it's
> EOF then the do while should never come into play, right?
>
> Thanks in advance for any help you can give me.
>
>
>
>
> Function listTablesWeb(nDSN)
> dim objConn, objTableRS, objNoneSupportedTableRS
> dim bFound, aNoneSupportedTablesArray, sTempText
> dim nCounter
> Response.Write "<br>" & nDSN
>
> on error resume next
> Set objConn = Server.CreateObject("ADODB.Connection")
> 'objConn.open nDSN
> objConn.Open sDSN, sUsername, sPassword', sDriveOptions
>
> If Err.Number <> 0 Then
> localError = Err.Description
> else
> sTempText = ""
> on error resume next
> set objTableRS = objConn.OpenSchema(adSchemaTables)
>
>
> objTableRS.movelast
> objTableRS.movefirst
> ' response.write ("<option>There are " & objTableRS.recordcount & "
> tables.</option>")
> if objTables.BOF then
> response.write("<option>We are BOF</option>")
> end if
> if objTables.EOF then
> response.write("<option>We are EOF</option>")
> end if
>
>
>
>
>
>
> ' Do While Not objTableRS.EOF
> ' If LCase(objTableRS("TABLE_TYPE").Value) = LCase("TABLE") or
> LCase(objTableRS("TABLE_TYPE").value) = LCase("VIEW") Then
> ' Response.Write("<option value='" &
> trim(objTableRS("Table_Name").Value) & "'>" &
> trim(objTableRS("Table_Name").Value) & "</option>" & vbCrLf)
> ' End If
> ' objTableRS.MoveNext
> ' Loop
> objTableRS.Close
> objConn.Close
> end if
>
> Set objTableRS = Nothing
> Set objConn = Nothing
> end function
>

Again, I profess to NOT being an ASP wizard. However, a Google search for "oracle asp movelast" turns up a number of hits, some of which might be useful in your pursuit.

One url (shown below, which will wrap) says that Oracle does not expose a scrollable server cursor, which I think means it can go in the forward direction only. If that's true, then you can't do a movelast followed by a movefirst.

http://msdn.microsoft.com/library/default.asp?url=/library/enus /dnoledbp/html/msdn_ole4orcl.asp

Some other sites that showed up in the Google search complain that recordcount doesn't work in all ADO connections and suggest something like this to get the recordcount:

objRS.Open "select count(*) from user_tables", objConn

You might want to check out some of the other Google hits ... but it looks like Oracle does not behave like Microsoft SQL Server (no big surprise). So writing an any-database ASP looks like it's difficult.

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