SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

20 December 2007

 

ADODB execute a stored procedure

I have noticed a lot of activity on my posts to do with ADODB SELECT statements in ASP VBScript code. So here is some more information, this time about executing stored procedures.

ORACLE STORED PROCEDURES

Let's run an Oracle stored procedure and extract the data from the OUT variable. This post does not teach you about Oracle PL/SQL programming, but the head of the stored procedure we are going to run is:
CREATE OR REPLACE PROCEDURE sp_create_device
(p_ingame_name IN VARCHAR2,
p_status IN VARCHAR2,
p_device_id OUT VARCHAR2
)
IS
.....bulk of stored procedure here....

The remainder of this post is about the ASP code you need to execute the above stored procedure. First just a bit of setup to create the ADODB objects (these can be put in an include file at the top of all your ASP scripts):
set objConn = server.CreateObject("ADODB.Connection")
objConn.Open (application("gc_db_conn"))
set objCommand = Server.CreateObject("ADODB.Command")

objCommand.ActiveConnection = objConn

In the above, replace application("gc_db_conn")which I have defined in my global.asa with the connection string for your database. More information setting up a connection string here. Now identify the stored procedure to execute:
objCommand.CommandText = "sp_create_device"
objCommand.CommandType = adCmdStoredProc

Next, for each of the stored procedure's variables as defined in the stored procedure itself (our procedure sp_create_device here has three variables, two of which are IN variables and one of which is an OUT variable), define the variables:
objCommand.parameters.append objCommand.createparameter("p_ingame_name",adVarChar,adParamInput, 20)
objCommand.parameters.append objCommand.createparameter("p_status", adVarChar,adParamInput, 25)
objCommand.parameters.append objCommand.createparameter("p_device_id", adVarChar,adParamOutput,24)

For the avoidance of doubt, I know the above will probably wrap on your screen, but there are THREE lines in the above bit of code, one for each parameter. Notice how each parameter is identified by name (the names are in the code taken from the stored procedure), the data type is defined, the direction (in or out) is defined and the length of the variable is specified.

Now let's assign a value to each of the input variables (there are only two IN variables, so only two of the parameters need to be given a value):
objCommand("p_ingame_name") = "ghast"
objCommand("p_status") = "forced offline"

Finally now execute the stored procedure and this allows us to return (extract) the result:
objCommand.Execute
v_device = objCommand("p_device_id")

The VBScript variable v_device now contains the value that the stored procedure put into its p_device_id variable.

Please note that no error processing / handling has been included in the above. But I know you know that!

Good luck. Feel free to drop me a line if you need any help.

Comments:
Hi,
I am trying to run a Oracle DB procedure from ASP page. Can you help me with the Syntax part - how to create the connection and have the procedure executed?
I also want to fire COMMIT command,after executing the stored procedure.How to do that also?
 
Hi Vyom
By default the commit is implicit and is therefore automatically done as part of executing the stored procedure.
The way to create the connection before running the statements here is something like this:

in your global.asa put this defintion:

application("gc_db_conn") = "Provider=OraOLEDB.Oracle;User ID=youruserid;Password=xxxxxxxx;Data Source=xe"




then in an include file attached to top of each ASP page, put this:



Dim objConn, objRS, objCommand, objParam, sqlString

set objConn = server.CreateObject("ADODB.Connection")

on error resume next

objConn.Open (application("gc_db_conn"))
If Err.Number <> 0 then
response.redirect("unavailable.asp")
End If
On Error GoTo 0


set objRS = Server.CreateObject("ADODB.Recordset")
set objCommand = Server.CreateObject("ADODB.Command")
set objCommand.ActiveConnection = objConn
set objParam = objCommand.Parameters


then you are ready to go with the stored procedure stuff in your asp page
 
Post a Comment

Subscribe to Post Comments [Atom]





<< Home

Links

Archives

September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]