| Calling Stored Procedures from ASP page. [message #369639] |
Tue, 25 January 2000 14:23  |
Luiz Gustavo Milfont Pere
Messages: 2
Registered: January 2000
|
Junior Member |
|
|
Hello everybody,
I am trying to call stored procedures from an
asp page. I have experience doing it in VB6,
but it does not seem to be the same process
under ASP.
I am using Personal Oracle 7.3 and I usually
call a store procedure in VB6 like this:
sqlq = "Begin procedure1(" & Chr(13)
sqlq = sqlq & parameter1 & "," & Chr(13)
sqlq = sqlq & ");END;" & Chr(13)
Set PLORACLE = New ADODB.Command
Set PLORACLE.ActiveConnection = conn
PLORACLE.CommandText = sqlq
conn.BeginTrans
PLORACLE.Execute
conn.CommitTrans
Set PLORACLE = Nothing
----
If anyone can help, please contact me,
Best regards,
Luiz.
|
|
|
|
| Re: Calling Stored Procedures from ASP page. [message #369644 is a reply to message #369639] |
Sat, 29 January 2000 12:27  |
Luiz Gustavo Milfont Pere
Messages: 2
Registered: January 2000
|
Junior Member |
|
|
Hello guys...
I've found in the Internet an article about this
and I decided to share it with you.
Here it is:
----
FAQ #3: How do I call an Oracle Stored Procedure
by Surya Rao
HOW TO CALL A STORED PROCEDURE FROM AN ASP PAGE:
=================================================
Folks, contrary to popular belief there are many ways to call stored procedures from an ASP page. I've tried it with Oracle (the only REAL RDBMS ;-) and it works.
If this bit below, is useful, it can be archived for future use by the LISTMASTER, with any changes.
Assume you have a procedure like this one below,
and that it has been already created on the
Oracle database. This procedure doesn't return
anything, but that doesn't change anything!
STEP #1:
+++++++++
/******STORED PROCEDURE ON ORACLE DATABASE************/
/*====================================================*/
create or replace procedure test_me
is
w_count integer;
begin
insert into TEST values ('Surya was here');
--commit it
commit;
end;
/*****END OF STORED PROCEDURE****/
STEP # 2:
+++++++++
I assume you have tested it from sql*plus by running the
following statements:
/************TEST THE STORED PROCEDURE FROM SQL*PLUS******/
SQL> execute test_me
PL/SQL procedure successfully completed.
SQL>
/***************END OF TESTING THE STORED PROC************/
STEP# 3:
++++++++
/*****CALLING A STORED PROCEDURE FROM ASP******************/
1. USING THE CONNECTION OBJECT
You can execute stored procedures which perform Oracle Server side
tasks and return you a recordset. You can only use this method if
your stored procedure doesn't return any OUTPUT values.
<% SET CONN='Server.CreateObject("ADODB.Connection")<br'>
Conn.execute "test_me",-1,4
%>
Note that -1 means no count of total number of records is
required. If you want to get the count, substitute count
with some integer variable
Note that 4 means it is a stored procedure. By using the
actual number -1 and 4, you don't need the server side
include ADOVBS.INC ;-)
The above would do the job on the database and return
back to you without returning any recordsets.
Alternatively, you could:
<% RS='conn.execute("test_me",w_count,4)' % SET>
W_count is the number of records affected. If your stored
procedure were to return a query result, it is returned
within your recordset (rs). This method is useful with Stored procs
which return results of an SQL query
2. USING THE COMMAND OBJECT
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set Comm = Server.CreateObject("ADODB.Command")
Set comm.ActiveConnection = conn
comm.commandtype=4
'(or use adCmdStoredProc instead of 4, but then you would have to
'include the ADOVBS.INC. Its upto you
comm.commandtext = "test_me"
comm.execute
'or
Set rs = comm.execute()
%>
STEP# 4
+++++++++
/************PASSING INPUT/OUTPUT PARAMETERS**************************/
<%
'If your stored procedure accepts IN parameters and returns OUT parameters
'here's how to go about it
set param = comm.Parameters
param.append comm.createparameter("Input",3,1)
param.append comm.createparameter("Output",3,2)
'Note that 3 = adInteger for the datatype
'Note that 1=adParamInput and 2=adParamOutput for parameter direction
'Pass the input value
comm("Input") = "...."
OR
set param = comm.createparameter("InPut",3,1)
set param = comm.createparameter("OutPut",3,2)
comm.parameters.append param
'Pass the input value
comm("Input") = "...."
'Execute after setting the parameters
comm.execute()
'If your stored procedure returns OUT parameters, here's how to get it
Out_1 = comm("Output")
'and so on...
%>
Thats it!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
