ASP with Oracle 8i backend

Hi,
Are you try to use Microsoft ODBC or Oracle instead to use Oracle ODBC Driver when you create the DSN?
I have bad experience using Oracle driver that was solved using Microsoft driver. I don't know why.

Best Regards
Miguel

Hi,

Like the previous answer you have received I have found that the Microsoft Oracle Drivers are a lot more stable when developing in VB, VBScript or using ASP pages. Its worth noting that MDAC 2.6 is available and that may be worth a try. Also,in order to be able to usefully assist you, could you provide me with the actuall error message you are receiving.

Hope 2 B able 2 help soon!
Ant

try this==

set Con=Server.CreateObject("ADODB.Connection")
Con.Open "Provider=MSDAORA.1;Data Source=PO8;User ID=user;Password=password"

I am not sure that personal oracle permits network connectivity????????

I think you have some faults in your code

*You opened a connection to the db server but you didn't open a recordset to hold the results of the query.

Try this:
Set MyConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open = "UID=uid, PWD=password, DSN=dsn;"
while Not rs.EOF
'do the rest

Note: I think a connection object cannot return EOF so you need to use the recordset. Use a recordset object to hold the results of your query.If this tip does not work, try to install install MS SQL server, then you can be sure the ODBC driver will not give any problems

The Oracle driver, surprisingly, is not the best tool. Use the Microsoft driver for Oracle, and then try :
MyConn.Open "Driver={Microsoft ODBC for Oracle};Server=po8.world;Uid=myUsername;Pwd=myPassword;"

The first, would be to determine if it's an oracle error, ora-xxxx or an code error. As for the drivers, you need to use the universal installer. Try donloading from the oracle site the drivers, wich include the installer with them.

Also, try yo see if you can connect from another program using that DSN.

try this!
<%

sqlQry = "SELECT * FROM emloyee_table;"
set rs = CreateObject("ADODB.Recordset")
rs.Open sqlQry, "DSN=demo[;SERVER=po8.world][;PWD=demo][;UID=demo]"
If Err.Description <> "" Then
Response.Write "DB Err: " + Err.Description + ""
Else
If rs.EOF And rs.BOF Then
Response.Write "No Records Returned"
Else
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
rs.MoveNext
Loop
Response.Write ("

")
End Ifrs.Close
End If
set rs = Nothing

%>

1. Connecting to a oracle database with odbc is not a good idea. It?s absolutely not performant. OLEDB is much faster.
2. When you connect with odbc use the oracle driver. The Microsoft driver does not support CLOB.
3. The driver which want the Installer 1.7.1.3.0 is for Oracle 8.1.7 and not for 8.1.6

Use this connection string:
set Con=Server.CreateObject("ADODB.Connection")

Con.Open "Provider=MSDAORA.1;Data Source=PO8;User ID=user;Password=password"

If you still get an error check out this Microsoft Knowledge Base article. You want thesection under Known Issues for the registry entries. http://www.microsoft.com/data/MDAC25info/MDAC25rtmmanifest.htm

You need to use the Microsoft ODBC driver for Oracle(The version we use is 2.573.6526)
when connecting from ASP pages. Create a system dsn, and use the name that you've given it in your asp page, and everything should work fine...if that doesn't work try this :

Create an Oracle connection using net8 Easy Config,(sounds like you've already created one called 'PO8') test it with SQL Plus to make sure it works,then use this connection string :
"Provider=MSDAORA.1;User ID=demo;Password=demo;Data Source=PO8;"

Good Luck.
Mike McManus

Assuming that you named the DSN connection demo in the ODBC database, have you tried your code without the brackets and without the server name? i.e. "DSN=demo;User ID=demo;Password=;"

On separate but related note, there may be a problem with thedatabase you've set up if the $ORACLE_HOME you set up the 8i database in was created in an earlier version of Oracle. According to a technote on the Oracle website that reads the same as the one you're getting, the error occurs when an Oracle 8 DB has been installed in an $ORACLE_HOME that was created by the 1.7.1.3 installer. It states that in that case, the 1.7.1.3 installer might be required to deinstall components created by the 1.7.1.3 installer, and indicates that a workaround to the problem, is to install the Oracle 8 DB into a new (empty) $ORACLE_HOME.

More related to the ASP code, you also need to open a recordset & run an SQL statement.

Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = con.Execute("SELECT * FROM Tablename")
or rs.open "Tablename"

Paul,

First of all, the Oracle driver is the only one supported by Oracle to give complete functionality with the Oracle database. For a DSN connection, try one of the following solutions:

1. Leave the service name blank on in the DSN configuration
or
2. Configure an IPC service in the Net8 configuration tool.

The problem with the installer version is that you are trying to install an 8.1.7 ODBC driver from Oracle.

Robert Langley
Principal Consultant, Applied Solutions Inc

First of all, you need to create a dsn for this database connection in the OCDB manager. Creat a System DSN called "demodsn" using the "Microsoft ODBC for Oracle" driver.

Next, to connect to the database try:

strConnection = "dsn=demodsn;uid=demo;pwd=demo;"

set objDBConn = Server.CreateObject("ADODB.Connection")
objDBConn.Open strConnection

Finally, to this string only connects to the database. It does not actually pull any data from the database. To pull the data from the database you need to write an SQL select statement. Try this:
<%SQL = "select * from "
set rs = objDBConn.Execute(SQL)%>

<%rs.MoveNext
Loop%>

<%rs.Close
Set objDBConn = Nothing %>

First, you opened the connection but you did not open recordset. In your code, MyConn.("Empoyer_ID") should be like Rst("Emplyer_ID") after opening recordset.

Second, when you open the connection,
just use like
MyConn.open "DSN=....."

I usually don't use this approach to the DSN in the connection string. As an ODBC connection, all that is required is the name of the Data Source Name. The ODBC manager maintains other relevant information that is needed. If the DSN were "PO8conn", then the connection string would be:

MyConn.Open Connect_String = "DSN=PO8conn; PWD=demo; UID=demo"

The ODBC Manager would facilitate a connection using localhost according to information provided in the system dsn record for PO8conn.

Dear friend,

Try DSN less connection.Otherwise go to the Oracle Installer and reinstall the ODBC driver.

1)When i saw u'r code, I have observed that u r using microsoft ODBC. Microsoft ODBC is giving some problems with Oracle 8i. Instead of using Microsoft ODBC, Use Oracle ODBC and go with provider to connect Oracle from ASP.

2)And the second issue in u'r code is "While Not MyConn.EOF".
Connection object returns a recordset when u r executing like: MyConn.Execute SQLQRY.
else it wouldn't return any recordset. In order to get the recordset, u have to go with recordset object, which is missingin u'r code.

Try this:
Set MyConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
MyConn.Open "Provider=MSDAORA.1;Data Source=PO8;User ID=Demo;Password=Demo"
rs.open "SELECT * FROM emloyee_table",MyConn
while Not rs.EOF
'do the rest

1. You have created the ADO connection object, but have not specified a table or stored procedure. The correct way to specify the Connect string is: MyConn.ConnectString = "DSN=demo;SERVER=po8.world;UID=demo;PWD=demo;"
2. Write code like this:
<%
Response.Buffer = true
dim MyConn
dim oRs
dim i
set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "demo"
set oRs = MyConn.Execute("SELECT * FROM tblEmployee")
If Not oRs.EOF And Not oRs.BOF Then
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
MyConn.MoveNext
Loop
Response.Write ("

")
Else
For each i in MyConn.Errors
Response.Write "
" & i.Description
Next
End IF
MyConn.Close
Set MyConn = Nothing
%>
3. The The first two lines create the connection to the database. It is just a path to the database. You next use the connection objects Execute method to create a recordset object which contains the results of your query. In the example I use "tblEmployee" as thetable name. Check for EOF and BOF both being true. If this test passes, loop until EOF. The line printing the "

" tag. A new table row for each result in the result set. I suggest you try: MyConn.ConnectString= "DSN=demo;SERVER=po8.world;UID=demo;PWD=demo;" .

The problem might be with the driver.
If u would like to use the Oracle driver, U should install the Oracle client software which would update the Oracle ODBC driver.

I haven't done anything on Oracle, but have done connection to SQL and Access.
Looks like your connection open method isn't correct. The following is your code.

***************
dim MyConn
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open Connect_String = "DSN=demo[;SERVER=po8.world][;PWD=demo][;UID=demo]"
*********************

Connect_String is now pointing to database, not the specific table or stored procedure you want to open.
You need another line of code to opena table or a stored procedure.
For example, if you need to open up employee table, you need to write:
"MyConn.Open tblEmployee" to return records.
Good luck

If you want to use DSN-less connection i tought the following might be usefull:

For the current Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _ "Uid=myUsername;" & _
"Pwd=myPassword;"

For the older Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
"ConnectString=OracleServer.world;" & _ "Uid=myUsername;" & _
"Pwd=myPassword;"

For more information, see: http://msdn.microsoft.com/library/psdk/dasdk/orcd1i2b.htm

1. http://www.15seconds.com/issue/000131.htm

Selva Kumar?s article shows how to create practical Oracle database connectivity from ASP using Oracle Objects for OLE (OO4O). OO40, the Oracle middleware, allows native access to Oracle from client applications using the Microsoft Object Linking.
2. Encapsulating Your ASP Page: One Common Method for Database Access to SQL Server and Oracle
http://www.webreference.com/programming/asp/database/

I have seen some good advice, such as
ensuring that your ODBC driver version is
the correct one, etc. However, there is
one obvious piece of advice that is missing.

You can find VB examples (and others) in the
\Lite\Examples directory in
the VB folder. I suggest the show_click()
function in the update.frm file. Here you
will see correct string for Personal Oracle
Lite 8i, and a slightly different method
of access using a VB Database object instead
of a Connection object.

Hope this helps.

Hi Paul,
Check Haiju and Miguels answer concerning the MS Drivers. Ive used MS drivers with Oracle with success. Ive tried to find the code used but havent yet. If I do Ill post it. Also, the comments regarding your code not having a recordset. While it is better coding technique it is not in error at all. For a small piece of code like that theres no reason to create the recordset. Youve got the code fine (even though I would personally have used a "while not" and "loop" statement (have had much better results with it for some reason).

Is this a trick question? Use a recordset.

<%response.buffer = true%>
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.ConnectionString = "DSN=ORAScott;UID=scott;pwd=tiger;TABLE=EMP"
MyConn.Open
strSQL="select * from emp"
set rs=myConn.execute(strSQL)
While Not rs.EOF
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
Response.Write ("

")
rs.MoveNext
Response.Write ("

")
Wend
rs.close
set rs=nothing
MyConn.Close
Set MyConn = Nothing %>

I use a DSN-"less" connection.
<%
Set objConn = Server.CreateObject("ADODB.Connection")
gstrConnection = "PROVIDER=MSDAORA;Data Source=sampleDSName;User Id=sampleID;Password=samplePassword;"

objConn.Open gstrConnection

%>

I save as separate .asp file and "include" on all subsequent .asp pages for connection to the Oracle8i DB. Establish the recordsets you need on your other .asp pages.

Hope this helps.

I use a DSN-"less" connection.
<%
Set objConn = Server.CreateObject("ADODB.Connection")
gstrConnection = "PROVIDER=MSDAORA;Data Source=sampleDSName;User Id=sampleID;Password=samplePassword;"

objConn.Open gstrConnection

%>

I save as separate .asp file and "include" on all subsequent .asp pages for connection to the Oracle8i DB. Establish the recordsets you need on your other .asp pages.

Hope this helps.