Home » RDBMS Server » Server Administration » Calling Stored Procedures from ASP page.
Calling Stored Procedures from ASP page. [message #369639] Tue, 25 January 2000 14:23 Go to next message
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 Go to previous message
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!
Previous Topic: inserting a date
Next Topic: How can I find Nth minimum number
Goto Forum:
  


Current Time: Thu Mar 28 12:11:40 CDT 2024