Connecting to Oracle using OLE and a DSNLess Connection - Using Libraries

Source Code

Typically if you are connecting to a database on a regular basis, it makes more sense to make commonly used code into a function, such as connecting to the database, which can be reused by many scripts. The following example uses a libary file called ASPLib.asp to include a function to connect to the database -getdBConnection, plus the procedure query2table found in the Access examples, that can take the results from any query and convert it to table output.

The example below uses the OLE connection used before. It includes the library file: ASPLib.asp and the ASP file that uses both these functions.

Library File - ASPLib.asp

<%

Function getdBConnection()
  Dim dbConn
  SET dbConn=SERVER.createobject("adodb.connection")
  WITH
    dbConn .ConnectionString="Provider=MSDAORA; Data Source=ora12c;" & _
      "User ID=myOracleUsername;" & "Password=myOraclePassword"
    .Open
  END WITH

  SET getdBConnection=dbConn
End Function

%>

<%

SUB query2table(inputquery, conntemp)

DIM rstemp
SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border="2" bgcolor="#E1E2B4"><tr>
<% 'Put headings On The table of Field Names
for i=0 to howmanyfields %>
  <th><%=rstemp(i).name%></th>
<% NEXT %>
</tr>

<% ' Now lets print the records
DO WHILE NOT rstemp.EOF %>

<tr>
<% FOR i=0 TO howmanyfields
  thisvalue=rstemp(i)
  ' hardcode a space if value blank
  IF ISNULL(thisvalue) THEN
    thisvalue="&nbsp;"
  END IF%>
  <td VALINE=TOP><%=thisvalue %></td>
<% NEXT %>
</tr>
<%rstemp.MoveNext
LOOP %>

</table>

<% ' close the connections
rstemp.Close
SET rstemp=nothing
END SUB

%>

ASP File

The ASP file must include the library file at the start. Note the use of the ActiveConnection property when passing the results to query2table.

<html>
<head>
<title>Connecting to Oracleusing OLE and DSNLess connection and a Library</title>
</head>
<body>

<H2>Connecting to an Oracle database using OLE and DSNLess connection</H2>
<!--#include file="_lib\ASPLib.asp"-->
<%

DIM objCommand
DIM SQLStr
SET objCommand=Server.CreateObject("ADODB.Command")
SET objCommand.ActiveConnection=getdBConnection()
response.write("<H3>Department table</H3>")
SQLStr="SELECT * from Dept"
CALL query2table(SQLStr, objCommand.ActiveConnection)

response.write("<H3>Employee table</H3>")
SQLStr="SELECT * from Emp"
CALL query2table(SQLStr, objCommand.ActiveConnection)

' close the database connection
objCommand.ActiveConnection.Close

%>
</body>
</html>

Click here to run the program