Solved

Oracle DSN example?

Posted on 2001-07-20
9
1,518 Views
Last Modified: 2010-02-26
I?ve just been tasked with building an application that extracts data from an Oracle database and then populates several fields on an ASP page. I do not need to make any updates to the Oracle database. I?ve been able to do this in the past using an Access database, but now I need to get the information from Oracle and I will have to use a DSN because the Oracle database is on a different server. I will also need to use a username and password to access the Oracle database. I have no experience with using a DSN, so I?m at a total loss as how to accomplish this task.

Can someone show me an example of how to do this?

Thanks!
0
Comment
Question by:Rougie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:mattyk
ID: 6302146
0
 
LVL 33

Expert Comment

by:hongjun
ID: 6302147
Syntax of connection strings
http://www.able-consulting.com/ADO_Conn.htm

hongjun
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6302171
What driver will you use ODBC or OLEDB? If ODBC, I strongly advise that you get an ODBC driver from Oracle, the MS version is a bit buggy.

Then its something like...

DSN..
Conn.Open "DSN=wipost32_ora" ' note, the id and pass are usually defined in the dsn

OLEDB..
Conn.Open Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=nigel;Data Source=sant
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Expert Comment

by:AvocadoIsle
ID: 6302178
Here's the code we're using with an oracle database:

Dim strConn, strRecSet, strSQLQuery
Set strConn = Server.CreateObject("ADODB.Connection")
strConn.Open "DSN=YourDSNName;UID=YourOracleUserName;PWD=YourOraclePassword"
Set strRecSet = Server.CreateObject("ADODB.Recordset")
strSQLQuery = "Your Query Here"
strRecSet.Open strSQLQuery, strConn

And you need to set up the DSN properly on your NT server running the web application.  At this point I'm a little unclear but you need to add a system DSN using Oracle drivers (there is a Microsoft ODBC driver for Oracle included with the system).  Go to control panel/ODBC data sources/system DSN/Add and select the proper Oracle driver and supply the DSN name & description.

The username and password should be the one supplied by your Oracle DBA.

HTH
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6302179
sorry, that should be

OLEDB..
Conn.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=nigel;Data Source=sant"

The latest Oracle ODBC drivers can be obtained from ...

http://otn.oracle.com/software/utilities/software_index.htm
0
 
LVL 20

Accepted Solution

by:
jitganguly earned 200 total points
ID: 6302467
I am surprised none of you guys explaied about tnsnames.ora. Using Oracle db with a DSN connection is very different than SQLServer or access.
Now here are the steps

1. You should have Oracle client installed on your web server.
2. Look for a file called TNSNAMES.ORA. It should be in \orant\network\admin
3. You should have a TNS connection in tnsnames.ora. Generally the name ends with .ora. A typical example would be

mydb.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ls0167.abc.xxx.com)(PORT = 10007))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = abc.def.COM)
    )
  )

4. Now go to Control panel, odbc32. Click on System DSN tab, click ADD and choose Microsoft ODBC for oracle. You should use Microsoft ODBC for oracle as Oracle ODBC is full of bugs. If you can not see Microsoft ODBC, download MDAC 2.5 (yes 2.5 not 2.6) from MS site (www.microsoft.com/data). The version for MS odbc for oracle shoudl be 2.573xx. I had problems using MDAC 2.6 with oracle driver as well Oracle ODBC driver. When you chosse MS ODBC fro oracle and click Finish, system will prompt you with Data Source name, specify any name (thats your dsn name), username should be equal to Oracle user name and password is oracle passowrd. Now in server filed specify the tns name from tnsnames.ora. THE SERVER NAME MUST BE EQUAL TO THE TNSNAME.

5. You are all set now to use this DSN in your ASP pages.
A typical example would be

Set Application("SecConn") = Server.CreateObject("ADODB.Connection")
Application("Oracle_DSN") = "mydsn"
Application("Oracle_Username") = "myuserid"
Application("Oracle_Password") = "mypassword"
Application("SecConn").Open Application("Oracle_DSN"), Application("Oracle_Username"), Application("Oracle_Password")


Remember the date manipulations in oracle is different than Access. You don't use # sign to compare dates. It has some excellent function like TO_DATE, TO_CHAR etc.

Let me know
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6302620
Well, I was assuming that this would have been carried out by the DB administrator if they changed the DB from Access to Oracle
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6302664
No, why ? Is creating a dsn DBA job ? I do it myself. I don't know ...
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6307527
No it's no a DBA job, but you have to have either ODBC or OLEDB installed. If you don't, you'll have to ask DBA what type of connection to use.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question