Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Connection from ASP to Oracle 10g database.

Status
Not open for further replies.

tayorolls

MIS
May 8, 2007
42
US
Hi,

I am trying to connect from ASP to an Oracle 10g database. I got a sample connection string from
I get an error message which I have attached along with this post. It is alert box with the message that says - ""
In the browser, I get the following error:

Code:
The Oracle (tm) client and networking components were not found. These components are supplied by Oracle Corporation and are a part of the Oracle Version 7.3 (or greater) client software installation.

You will be unable to use this driver until these components have been installed.

Code:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
/test/Oracle_DB.asp, line 7

My code is:

Oracle_DB.asp
Code:
<%@Language="VBScript"%>
<%
' Create the Connection Object	
Set objConn = Server.CreateObject("ADODB.Connection")

' Set the connection string. Connecting to a SQL Server Database
objConn.open "Driver={Microsoft ODBC for Oracle};Server=skydev;Uid=apps;Pwd=apps;"

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head>
    <title>Oracle DB Connection</title>
</head>
<body>
<form name="frmNorthwind" id="frmNorthwind" action="getProductResults.asp" method="post">
	
<h1>Product Search</h1>
<h4>Search Product</h4><input type=text size=20 id="txtProduct" name="txtProduct">


<input type="submit" name="btnSubmit" id="btnSubmit">

</form>


</body>
</html>

<%
objConn.Close
%>

Line 7 is:

Code:
objConn.open "Driver={Microsoft ODBC for Oracle};Server=skydev;Uid=apps;Pwd=apps;"

Note: On my local machine, I have been able to connect to the same database from a JSP page.

Any ideas how to get the connection right from ASP page?

Thanks.
 
Hi,
Do you have the Oracle client software installed?
ODBC ( at least for that ODBC driver) needs the Oracle client ( at least the SqlNet parts)


Java uses a direct connect method that does not require any Oracle client..

There are ODBC drivers out there that use that same method to connect to Oracle ( they are often called'wire protocol' drivers )

DataDirect has the most used ones..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks for the response. I have Oracle Ora Home on my desktop. Within Application Development, I have SQL * Plus. I have used SQl * Plus to connect to the development environment.

Thanks,
 
If you can connect on that machine via SQL*Plus with the same credentials then you have the listener files setup right?

Did you try the OleDb provider by Oracle? It's much better than the outdatted ODBC?

What version of the client do have have installed? does it match or at least newer than the db servers

It also depends I believe on how you connect to Oracle from Java on if you need it or not. There are providers for the ASP that do not require it either.

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
I just tried connection to the the table:

select * from per_all_people_f

through my SQL*Plus and I was able to see all the records. I shall try the OLEDB connection to Oracle instead of ODBC.

However since this is an intranet web-page, does this mean that all users of intranet need to have the Oracle Client installed on their machines? That is not a feasible solution.

I shall look for OLEDB provider by Oracle.

Thanks

 
I looked into the MSDN documentation: I tried this example.

Code:
<%@ Language=VBScript %>
   <html>
   <head>
   <title>Oracle Test</title>
   </head>
   <body>
   <center>
   <%
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "Provider=MSDAORA;Data Source=SYKDEV;User Id=apps;Password=apps;"

     Set objRs = objConn.Execute("select * from per_all_people_f where Person_ID = 61")

     Response.Write "<table border=1 cellpadding=4>"
     Response.Write "<tr>"

     For I = 0 To objRS.Fields.Count - 1
       Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
     Next

     Response.Write "</tr>"

     Do While Not objRS.EOF
       Response.Write "<tr>"

       For I = 0 To objRS.Fields.Count - 1
         Response.Write "<td>" & objRS(I) & "</td>"
       Next

       Response.Write "</tr>"

       objRS.MoveNext
     Loop

     Response.Write "</table>"

     objRs.Close
     objConn.Close
   %>
   </center>
   </body>
   </html>

Above example is from
I still get the error:

Code:
Error Type:
Microsoft OLE DB Provider for Oracle (0x80004005)
Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.
/test/Oracle_DB_new.asp, line 10

I also followed the steps from this MSDN article:

1. I verified that I am able to connect the table using the SQl*Plus.
2. This client has been installed 2 months back and the machine has been rebooted several times.
3. Located only 1 instance of the OCIW32.dll file in the C:\OracleHome\Bin drive. There is no such dll file anywhere else in my local desktop. (I am trying to work with a connection to the Oracle Database from the Local IIS Server which is on my machine)
4. The documentation mentions of looking into the system variables on the IIS computer and check the Path Variable and make sure that the \Bin folder is in the PATH environment variable. How do I acess the environment variable in my local machine?

Thanks.
 
OK, I tried this.

Code:
Set objConn = Server.CreateObject("ADODB.Connection")
     'objConn.Open "Provider=MSDAORA;Data Source=SYKDEV;User Id=apps;Password=apps;"
     objConn.Open "Provider=OraOLEDB.Oracle.1;User ID=apps;Password=apps"

I still cannot connect. I get the error:

Code:
Error Type:
ADODB.Connection (0x800A0E7A)
Provider cannot be found. It may not be properly installed.
/test/oracle_DB_New.asp, line 11
 
Hi,
A couple of ideas/questions:

Is there more than one Oracle_Home on that machine?

Have any previous versions of the Oracle software been installed and then updated to this version?

To test the OleDb capability, right click on your desktop and choose New..Text File
Name it test.udl

Once created dbl-click it and look under the Provider tab to see which ones are installed...Try creating a connection with one of the Oracle ones ( Oracle's or MS) and test the connection


[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Hi,
I hit submit too soon..

If the connection you create with the .udl file works, open the udl file in a text editor and use the connection string shown there..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks for the response.

Could you elaborate on this Provider tab. Which window dialog would have this provider tab?

Once created dbl-click it and look under the Provider tab to see which ones are installed...Try creating a connection with one of the Oracle ones ( Oracle's or MS) and test the connection
 
There is only folder called OracleHome on my local machine - C:\OracleHome
 
That was a good way to testing the connection. I followed your instructions to build the Connection String and used the connection string in my ASP page. However I still get the error.

Connection String I used which was successful:

Code:
objConn.Open "Provider=MSDAORA.1;User ID=apps;Data Source=SYKDEV;Persist Security Info=False"

Error I get is:
Code:
Error Type:
Microsoft OLE DB Provider for Oracle (0x80004005)
Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.
/test/oracle_DB_New.asp, line 11

Oracle Client on my machine is of version 91 ( I think).

Thanks,
 
Hi,
Did any Oracle Native providers appear in the Provider list?

If not, the Oracle client is not fully or correctly installed...Rerun the Client Installation and be sure the OleDb options are selected -
Get the Provider here if not on your client install disk.
Exact version is important




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
I had selected Microsoft OLE DB Provider for Oracle as the Provider. Should I be looking for some other providers?

I shall try to re-run the client installation.

Thanks.
 
Hi,
Using the Oracle OleDb provider is preferred - I suspect your Microsoft one is expecting an earlier version of the Oracle client and, not finding the DLLs it expects, gives that error..

You might also try installing the latest version of the MDAC components from Microsoft..They may include a correct version of their Oracle provider..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top