Re: vbscript and oracle connection freezing.

From: Eltschinger Markus <do_not_email_at_home.com>
Date: Sun, 18 May 2003 17:46:49 +0200
Message-ID: <1053272814.349541@exnews>

In your example you're using the Oracle Provider for OLE DB. Here's a small extract of an ASP script which runs on my server running Microsoft Windows2000, IIS 5.0, MDAC 2.7 and the latest Oracle9i R2 client: (besides: an ODBC connection string looks different

sConnStr = "Provider=OraOLEDB.Oracle;" & _
           "Data Source=xxxxx;" & _
           "OSAuthent=0;" & _
           "PLSQLRSet=0;" & _
           "FetchSize=128;" & _
           "CacheType=Memory;" & _
           "Persist Security Info=False;" & _
           "User Id=xxxxx;" & _
           "Password=xxxxx;"
strSQL  =  " ... put your SQL code here ..."

Set objCn = Server.CreateObject("ADODB.Connection") objCn.Open sConnStr

Set objRs = Server.CreateObject("ADODB.Recordset")

objRs.ActiveConnection = objCn
objRs.CursorType = adOpenForwardOnly
objRs.CursorLocation = adUseServer
objRs.LockType = adLockReadOnly

objRs.Open strSQL
... process the returned data here ...
objRs.Close
set objRs = Nothing
objCn.Close
set objCn = Nothing

Here are also two interesting links dealing with Oracle Provider for OLE DB:

Sample ADO connection strings for ODBC, OLE DB Provider etc. http://www.able-consulting.com/ADO_Conn.htm

Oracle Provider for OLE DB 9.2.0.2.0 (software): http://otn.oracle.com/software/tech/windows/ole_db/content.html

Oracle9i Release 2 Provider for OLE DB Developer's Guide: http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/win.920/a95498.pdf

Kind regards,
Markus Eltschinger

Swisscom IT Services Ltd
Data Warehouse Development
1752 Villars-Sur-Glâne FR
Switzerland
http://www.swisscom.com/it/content/index_EN.html

"bigbinc" <bigbinc_at_hotmail.com> wrote in message news:d1b33313.0305161511.1f0b72ca_at_posting.google.com...
> Is there any reason why this code will not connect to my oracle
> database through a odbc connection. I tested the connection through
> an odbc connection and it was successful, but the code below keeps
> locking when I try to open the connection. The only problem that I
> can see is that my webserver and oracle database are on seperate
> machines and there may be a firewall problem. Could there be
> something wrong my tnsnames.ora/sqlnet.ora? Also, I normally use
> provider=? as opposed to driver=, what is the provider name for an
> oracle odbc connection with oracle 9.000.
>
> Berlin Brown
> bigbinc_at_hotmail.com
>
> <%@ Language=VBSCRIPT %>
>
> <%
>
> '
> ' For public involvement (3/28/2003)
> '
> option explicit
> Response.Expires = -1440 ' This is to ensure that the
> ' map logic executes for each
> ' and every request -- that it
> ' is never read from browser
> ' cache.
>
> %>
>
> <meta http-equiv="Content-Type" content="text/html;
> charset=ISO-8859-1">
> <HTML>
> <body>
>
> <%
>
>
> 'Dimension variables
> Dim adoCon
> Dim rsDistance
> Dim strSQL
> Dim lastObjectID
>
> Dim connectStr
> Dim x_ctr
>
> Dim theTime
> Dim theDate
>
> 'Create an ADO connection object
> Set adoCon = Server.CreateObject("ADODB.Connection")
>
> ' The code below may fix unexplained errors
> adoCon.Mode = 3 ' Mode read/write
>
> 'connectStr = "Provider=OraOLEDB.Oracle.1;Data Source=gis;;User
> ID=dorsey;Password=dorsey;"
> connectStr = "Driver={Oracle in OraHome90};Data
> Source=gisoracle;;User ID=x;Password=x;"
>
> adoCon.Open connectStr
>
> 'Create an ADO recordset object
> Set rsDistance = Server.CreateObject("ADODB.Recordset")
> strSQL = "SELECT * FROM DORSEY.REHABTAB ORDER BY OBJECTID"
>
> Do While not rsDistance.EOF
>
> Response.Write rsDistance(0).Value & "<br>"
>
> Loop
>
>
> 'Open the tblComments table using the SQL query held in the strSQL
> varaiable
> rsDistance.Open strSQL, adoCon
>
> 'Reset server objects
> rsDistance.Close
> Set rsDistance = Nothing
> Set adoCon = Nothing
>
>
> %>
>
> </BODY>
> </HTML>
Received on Sun May 18 2003 - 10:46:49 CDT