Re: Example ADO function

From: Tim <df_at_sd.net>
Date: Wed, 25 Jun 2003 05:08:24 GMT
Message-ID: <jdbifvgphkbta0sdrjcf4fd32l6ljn67c7@4ax.com>

I've never done this with a function, but I know it works with a procedure. Use the refcursor as an OUT or IN OUT parameter of the procedure. Your ASP code should work as is.

On Tue, 24 Jun 2003 15:59:58 +0200, Holger Baer <holger.baer_at_science-computing.de> wrote:

>Pile wrote:
>> Hi,
>> I have searched high and low but can not find the answer to this.
>> Without going down the 004O route please could someone comment on the
>> following.
>>
>> All it consists of is a very simple package, and a very simple ASP
>> page. I just want to be able to call a function via ASP but so far
>> have not been successful.
>>
>> Error I get is:Error Type:
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
>> ODBC driver does not support the requested properties.
>> /fastcoder/query.asp, line 30
>>
>> Many thanks in advance.......
>>
>> CODE (Oracle 1st & then the asp Page)
>> =====================================
>>
>> CREATE OR REPLACE PACKAGE test_pkg
>> AS
>>
>>
>> TYPE cursor_type IS REF CURSOR;
>>
>> FUNCTION fn_fastsearch (
>> p_query IN VARCHAR2
>> ,p_querytype IN PLS_INTEGER
>> ) return cursor_type;
>>
>> END test_pkg;
>> /
>> CREATE OR REPLACE PACKAGE BODY test_pkg
>> AS
>>
>> FUNCTION fn_fastsearch
>> (
>> p_query IN VARCHAR2
>> ,p_querytype IN PLS_INTEGER
>> )
>> return cursor_type IS
>>
>>
>> l_cursor fastcode_pkg.cursor_Type;
>>
>> BEGIN
>>
>> open l_cursor for select object_name from all_objects where
>> rownum<100;
>> return l_cursor;
>>
>> END;
>>
>>
>> END test_pkg;
>> /
>>
>>
>> -- Cut here, NOW THE ASP PAGE...
>>
>>
>> <html>
>> <head>
>> <title>Result of Database Query</title>
>> </head>
>> 
>> 
>> <%
>>
>>
>> Private Function GetData()
>> SET rs = Server.CreateObject("ADODB.Recordset")
>> set Con = Server.CreateObject("ADODB.Connection")
>> set cmd =Server.CreateObject("ADODB.Command")
>> set InputParam1 = Server.CreateObject("ADODB.Parameter")
>> set InputParam2 = Server.CreateObject("ADODB.Parameter")
>>
>> con.ConnectionString = "driver={Microsoft ODBC for Oracle};
>> server=pcom; uid=test;pwd=test"
>> con.Open
>>
>> cmd.ActiveConnection = con
>> cmd.CommandText = "test_pkg.fn_fastsearch"
>> cmd.CommandType = adCmdStoredProc
>> Set InputParam1 =
>> cmd.CreateParameter("Prm1",adVarChar,adParamInput,44,"ahdakjdh")
>> Set InputParam2 =
>> cmd.CreateParameter("Prm2",adInteger,adParamInput,,25)
>>
>>
>> cmd.Parameters.Append InputParam1
>> cmd.Parameters.Append InputParam2
>>
>> set rs= cmd.Execute
>>
>> ' If this works, add in a loop to write result into table.
>>
>> End Function
>>
>> %>
>> <body>
>>
>> <h1>Result of Database Query - ASP/VBScript</h1>
>> <%=GetData()%>
>>
>> </body>
>> </html>
>
>You might want to check the following site (I'm not an VB/ASP Programmer
>myself, so I'm not sure if this is of any relevance with your problem, so
>please bear with me if I'm missing your point):
>
>http://asktom.oracle.com/~tkyte/ResultSets/index.html
>
>Holger
Received on Wed Jun 25 2003 - 00:08:24 CDT