"View" to a large schema

#11

Hi Hypermommy, thanks for writing this:

Quote:

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

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/de...l=/library/en-
us/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]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

"View" to a large schema

comp.databases.oracle.misc comp.databases.oracle.misc

Re: "View" to a large schema - 07-14-2003 , 04:31 PM