|
 |
asp_databases thread: Re: Returning a recordset _and_ ouput parameters from a stored procedure
Message #1 by "Ines Llambias" <illambias@s...> on Mon, 23 Sep 2002 17:26:57
|
|
Hi everyone! I'm new in this list, and my english is very bad (I'm from
Argentina). My problem is the same Joel' problem. I read all replace but I
don't see how combine the two examples. I want to return a recordset as
well as output parameters from a stored procedure (only one). I resolve
the single problems, but not both in one stored procedure.
Any ideas?
Thanks for listening.
Ines
> To return a recordset and parameters, just combine the two
examples. First
> create and append the parameters and then set a recordset =
Command.Execute.
> I've read a few times in this list that the returned recordset must
first be
> closed before you can actually get the returned parameters. This makes
no
> sense to me, but I've never tried it either.
>
> ? Chuck
>
Message #2 by "Ken Schaefer" <ken@a...> on Tue, 24 Sep 2002 11:21:54 +1000
|
|
http://www.adopenstatic.com/faq/SprocsAndParameters.asp
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ines Llambias" <illambias@s...>
Subject: [asp_databases] Re: Returning a recordset _and_ ouput parameters
from a stored procedure
: Hi everyone! I'm new in this list, and my english is very bad (I'm from
: Argentina). My problem is the same Joel' problem. I read all replace but I
: don't see how combine the two examples. I want to return a recordset as
: well as output parameters from a stored procedure (only one). I resolve
: the single problems, but not both in one stored procedure.
:
: Any ideas?
:
: Thanks for listening.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "Ines Llambias" <illambias@s...> on Tue, 24 Sep 2002 11:03:49 -0300
|
|
Hello! thank you Ken for help. I read the link (it's excelent for beginners
like me!). I tried to use your tecnique, but it doesnt work.
I use a bit of code of one example, provided by John Kilgo in the web. I
modify the example, because I neet return a indivual output parameters, and
a recordSet.
I get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
/SaludSite/SP/getData2.asp, line 69
If you have few minutes to help me, thak you again...
The code is like that:
*** ASP Page:
Dim cnnTest
Dim strConnectString
Dim strSQL
'GET ALL PERSON
'Setup connection object
'strConnectString = "UID=alf_info;PWD=<password>;driver={Microsoft
ODBC for Oracle};SERVER=ora_p;"
strConnectString = Application("ORAConn_ConnectionString")
Set cnnTest = Server.CreateObject("ADODB.Connection")
With cnnTest
.ConnectionString = strConnectString
.CursorLocation = adUseClient
.Open
End With
'Define call to stored procedure to return all data
'strSQL = "{call packperson.allperson({resultset 10000, o_ssn,
o_fname, o_lname, o_Error})}"
'strSQL = "{call packperson.allperson({resultset 10000, o_ssn,
o_fname, o_lname}, o_Error)}"
'Set the command Object
Dim objCommand, objRS
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection = cnnTest
.CommandType = adCmdStoredProc 'adCmdText
'.CommandText = strSQL
.CommandText = "packperson.allperson"
.Parameters.Append .CreateParameter("o_ssn", adInteger,
adParamOutput, 9)
.Parameters.Append .CreateParameter("o_fname", adVarChar,
adParamOutput, 15)
.Parameters.Append .CreateParameter("o_lname", adVarChar,
adParamOutput, 20)
.Parameters.Append .CreateParameter("o_Error", adVarChar,
adParamOutput, 200)
End With
Set objRS = objCommand.Execute()
' Move recordset data (if any) to VBScript array
Dim arrResults
If not objRS.EOF then
arrResults = objRS.GetRows
End If
' We now close the recordset
' see www.adopenstatic.com/resources/code/objDispose.asp
' this function is included in utiles.asp
Call objDispose(objRS, True, True)
Dim strSSN, strFirstName, strLastName
' We can now get our output parameters
strSSN = objCommand.Parameters("o_ssn").Value
strFirstName = objCommand.Parameters("o_fname").Value
strLastName = objCommand.Parameters("o_lname").Value
' We now dispose of objCommand
Call objDispose(objCommand, False, True)
Response.Write("<center>")
Response.Write("<h2><font color=blue>Oracle Stored Procedure
Output</font></h2>")
Response.Write("<p>")
'Begin recordset results HTML table
Response.Write("<table border=1 cellspacing=2 cellpadding=2>")
Response.Write("<tr>")
Response.Write("<td>SSN</td><td>First Name</td><td>Last Name</td>")
Response.Write("</tr>")
'Loop through recordset and produce table output
'While Not rsTest.EOF
Response.Write("<tr>")
Response.Write ("<td>" & strSSN & "</td><td>" & strFirstName &
"</td><td>" & strLastName & "</td>")
Response.Write("</tr>")
'rsTest.MoveNext
'Wend
Response.Write("</table>")
Response.Write("</center>")
' We now dispose of cnnTest
Call objDispose(cnnTest, True, True)
%>
</BODY>
</HTML>
*** And the Stored Procedure is... (it' s in a Package called "PACKPERSON")
PROCEDURE AllPerson (o_ssn OUT tbl_ssn,
o_fname OUT tbl_fname,
o_lname OUT tbl_lname,
o_Error OUT Varchar2)
IS
CURSOR c1 IS
SELECT ssn, fname, lname
FROM person
ORDER BY ssn;
percount NUMBER DEFAULT 1;
BEGIN
o_Error := '';
FOR c IN c1 LOOP
o_ssn(percount) := c.ssn;
o_fname(percount) := c.fname;
o_lname(percount) := c.lname;
percount := percount + 1;
END LOOP;
-- Atrapa Error
Exception
When OTHERS Then
o_Error := SQLCODE || ' - ' || SUBSTR(SQLERRM,1,200);
END;
"Ken
Schaefer" To: "ASP Databases"
<ken@a... <asp_databases@p...>
atic.com> cc:
Subject: [asp_databases] Re: Returning a
23/09/02 recordset _and_ ouput parameters from a stored
22.21 procedure
Please
respond to
"ASP
Databases"
http://www.adopenstatic.com/faq/SprocsAndParameters.asp
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ines Llambias" <illambias@s...>
Subject: [asp_databases] Re: Returning a recordset _and_ ouput parameters
from a stored procedure
: Hi everyone! I'm new in this list, and my english is very bad (I'm from
: Argentina). My problem is the same Joel' problem. I read all replace but
I
: don't see how combine the two examples. I want to return a recordset as
: well as output parameters from a stored procedure (only one). I resolve
: the single problems, but not both in one stored procedure.
:
: Any ideas?
:
: Thanks for listening.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 25 Sep 2002 11:19:03 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ines Llambias" <illambias@s...>
Subject: [asp_databases] Re: Returning a recordset _and_ ouput parameters
from a stored procedure
:
: Hello! thank you Ken for help. I read the link (it's excelent for
beginners
: like me!). I tried to use your tecnique, but it doesnt work.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I don't think there's anything wrong with the technique. The 80040e14 error
indicates that there is a syntax error in your SQL statement.
Can you run your procedure in whatever developer tools you have?
Cheers
Ken
Message #5 by Kruschev Balcora <balcora@y...> on Tue, 24 Sep 2002 19:26:57 -0700 (PDT)
|
|
hi! does anyone here know where can i get a free chat
server or a free bulletinboard. preferably based on
ASP 3, but other technologies are also welcome. thanks
for the help!
Chev Balcora
=====
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
Message #6 by "Ines Llambias" <illambias@s...> on Fri, 27 Sep 2002 16:39:11 -0300
|
|
I have review my sql statment, and retry.
Thank you very much!
Ines
"Ken
Schaefer" To: "ASP Databases"
<ken@a... <asp_databases@p...>
atic.com> cc:
Subject: [asp_databases] Re: Returning a
24/09/02 recordset _and_ ouput parameters from a stored
22.19 procedure
Please
respond to
"ASP
Databases"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ines Llambias" <illambias@s...>
Subject: [asp_databases] Re: Returning a recordset _and_ ouput parameters
from a stored procedure
:
: Hello! thank you Ken for help. I read the link (it's excelent for
beginners
: like me!). I tried to use your tecnique, but it doesnt work.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I don't think there's anything wrong with the technique. The 80040e14 error
indicates that there is a syntax error in your SQL statement.
Can you run your procedure in whatever developer tools you have?
Cheers
Ken
|
|
|
View Cart
