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.


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:
(p_ingame_name IN VARCHAR2,
p_status IN VARCHAR2,
p_device_id OUT VARCHAR2
.....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:
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.



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]