Re: ASP and Oracle

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Thu, 17 Jul 2003 15:46:05 -0500
Message-ID: <e92ehvkur2r00epfnllcp0t2jm030jascr@4ax.com>

Michael Rothwell <marothwell_at_yahoo.com> wrote:

>Daniel Morgan wrote:
>
>> Michael Rothwell wrote:
>>
>>
>>>I have been asked to look into porting an ASP system to an Oracle DB.
>>>Currently, they are using SQL Server and retrieving data via stored procs. I
>>>know how to migrate the DB, but what work will have to be done to the ASP code
>>>to be able to access the Oracle DB as well as the SQL Server DB?
>>>--
>>>Michael Rothwell
>>
>>
>> You do understand you will need to rewrite every proc to have a REF CURSOR as an
>> OUT parameter?
>>
>> If connecting via ODBC and all SQL is in the procs ... essentially nothing need be
>> changed. If the SQL is being
>> passed through ... everything needs to be rewritten.
>
>Yes, I am aware that I would have to re-write all the procs, and I had counted
>on that.
>
>All SQL is in the procs, so what I dont know is how ASP is written to make these
>calls. After I change the procs and the ODBC connection, what needs to be done
>to the ASP code?
>

You can build a Sql statement
SqlStr = "exec myproc(any parameters here please )" The with your open ADO connection ( use, if possible a DSN-less connection) you can 'send' it to the database and, if it returns a ref-cursor you can treat it like a record set: Something like this is one way to use that method: <%
' Create and establish data connection
Set objDC = Server.CreateObject("ADODB.Connection") objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30

'Code to connect to Oracle
 objDC.Open "Provider=MSDAORA.1;Password=secret;User ID=yours;Data Source=tnsaliasname"

SqlStrco = "exec myproc(testparam)"
Set rs1 = objDC.Execute(SqlStrco)
arrCoData = rs1.GetRows()

 rs1.Close
   Set rs1 = Nothing

iRecFirstCo   = LBound(arrCoData, 2)
iRecLastCo    = UBound(arrCoData, 2)
iFieldFirstCo = LBound(arrCoData, 1)
iFieldLastCo  = UBound(arrCoData, 1)  

objDC.Close

   Set objDC = Nothing

%>

If all goes well you will have an array with the data returned by the proc... It is the 'Set rs1 = objDC.Execute(SqlStrco)' that actually creates the recordset, then any use that asp/html/javascript supports can then be used with it.
Hope it helps,

<snipped> Received on Thu Jul 17 2003 - 15:46:05 CDT