Using ASP to connect to an Oracle DB

[NatuScape]

Hello everyone!

I'm trying to use ASP (VBScript) to connect to an Oracle Database and it's not quite working out the way I thought it would. There's not a lot of information out there, and I tried googling several options of keywords and phrases, but without much luck.

First question: does anyone know good online resources where I can learn about this?

My second question, in case anyone here has experience doing it, is whether you know why I keep getting the following error message:

Oracle Automation error '800a01b8'
Unable to make connection, ORA-12535: TNS: operation timed out
/incidents/email.asp, line 43

This is the code I'm using currently:

<%

Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("something.world", "username/password",Cint(0))

Set OraDynaset = OraDatabase.DbCreateDynaset( _
"REALLY LONG AND COMPLICATED SQL STATEMENT HERE, WHICH IS DOUBLE-CHECKED AND ACCURATE", cint(0))

Do While(osRecordset.EOF = FALSE)
Response.write(osRecordset.Fields("fu.USER_NAME") & "<BR>")
osRecordSet.MoveNext
Loop

Set OraDynaset = nothing
Set OraDatabase = nothing
Set OraSession = nothing

%>


Thanks for your time!!

Me :)

[lmf232s]

NatuScape,
I hit a oracle DB everyday with asp, although i do not connect the way you do, I use ADO to connect. I will supply you with all the code i use to connect. I cant help you with the way you are attempting it as i have never seen that but im sure someone on these boards will be able to help but any way here is the code i use to hit a oracle DB w/ ASP.


<%
'Common.asp (this is one of my include files that i call this sub from)
Public Sub Glovia_CN()
Connect_String = "DSN=?;UID=?;PWD=?"
Set cnGlovia = Server.CreateObject("ADODB.Connection")
cnGlovia.Open Connect_String
Set objRS = Server.CreateObject("ADODB.recordset")
End Sub
%>



'Test.asp
<%
Dim Connect_String
Dim cnGlovia
Dim objRS
Dim SQL
'call the sub to create the connection to the DB.
Glovia_CN()

SQL = "SELECT * FROM ?"
set objRS = cnGlovia.Execute(SQL)
Do While not objRS.EOF
'DO something with the data
objRS.MoveNext
Loop
objRS.Close

Set objRS = Nothing
cnGlovia.Close
Set cnGlovia = Nothing
%>


This works for me and i have no issues with it, have been doing it like this for almost 2 years. We have seen no problems on the oracle side of things, as long as you close your connection when your done, if not oracle will hold on to the connection for some time befor it will time out. Thats about it.

Let me know if you have any problems if you decide to use this.

[NatuScape]

Hi there, thanks for your reply! I tried connecting using ADO like you showed me, but I get this error message:

Microsoft JET Database Engine error '80004005'
Could not find installable ISAM.
/incidents/email.asp, line 45


Here's the code I used:

Connect_String = "DSN='';UID=;PWD="
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open Connect_String
Set objRS = Server.CreateObject("ADODB.recordset")



Why is it happening? Any ideas?

Thanks a bunch!

Natalia

[lmf232s]

Remove this line

conn.Provider="Microsoft.Jet.OLEDB.4.0"

Then see what it does

[NatuScape]

Hi, thanks for answering! Here's what happens if I take out that line...


Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/incidents/email.asp, line 44

Thoughts?? :)

[lmf232s]

ya im trying to think of what i did to not specify a provider. It may be the way we have our oracle db set up, or the way that i have linked to oracle through sql server.

Ill have to check into this.

[lmf232s]

try this provider, the provider you used in your post above was for a access DB i do believe. I think Jet is for Access but i could be wrong, but you need a oracle provider.

This one is from oracle
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword"

This one is from microsoft.

"Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword"


Try those and see if that helps

[NatuScape]

Hi there! I keep getting errors... now it says:


ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.
/incidents/email.asp, line 43

I'm trying to get a hold of a DBA that can help me out... this looks like a server problem rather than a connection problem, right?

Thanks again!
Natalia

[lmf232s]

well I will assume this
Your oracle DB resides on a different server then your application lives on.

You would need to go to oracles site and download their client ( i think this is what it is called). You need to install this on the server that you are running your app on. This way that server can communicte with the Oracle DB residing on a different server.

Its like going to Microsoft and installing MDAC(microsoft data access components) with the JET Drive in it. This way you are able to connect to a access DB. Any way.

If your Oracle DB is on the same server as your application you should not need to anything else and in that case i dont know whats wrong.

But give the first one a shot if that applies to you.

Let me know.

[lmf232s]

not sure what version of oracle you are working with but heres the site

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html

[NatuScape]

Hi again!

Yes, your hunch was right, the server where I'm hosting the ASP is not the same as the Oracle server. I'm hosting the ASP on a Windows server with IIS 5.0, so I would need to download and install the client for Oracle connectivity, right?

I clicked on the link you sent me and it doesn't have a version for our current Oracle server, which is 11i. Do you think I could use Oracle 10g's client?

Thanks so much!
Natalia

[lmf232s]

This i cant say for sure, this might be a question that you would want to ask oracle or someone else. Im not sure if there is a big difference between each version. In a quick look on the web 9i might give you what you need. It looks like 9i can coexist with 11i applications, with that said i would assume you could use the same client, but agian this is going beyond me.

I would email oracle or post a new post somewhere about the compabilities of each version and see what someone w/ expertiese in this area has to say.

I would think that 9i would work for you needs but not 100% sure.

Sorry i cant help any more than that.

Good luck.

again if you figure that out and still have problems connecting on the asp page let me know but i think once you get the client installed you should be good to go.