Stored Procedure Tutorial

is there one to be recommended?

[txbakers]

with mySQL 5 now running, I'd like to learn about these and see if they could be useful in my programs.

[henry0]

Good question! Made me think about it
I found:

This [dev.mysql.com]

That [dev.mysql.com]

And this [forums.mysql.com]

<edit>
The first one looks to be a great one. (Beware it starts only on page 2.)
Got to print the 67 pages
Lots to learn about
</edit>

[txbakers]

I found that first one also - looks very good.

My big question though - is it really needed? I can UNDERSTAND the benefits, but here is for instance:

I have a basic query that I run on many pages. It brings back a list of classes:

var rsChoirs = Server.CreateObject("ADODB.Recordset");
rsChoirs.ActiveConnection = conn;
rsChoirs.Source = "SELECT Choir FROM Choirs WHERE schCode = '" + scode + "'";
rsChoirs.CursorType = 0;
rsChoirs.CursorLocation = 2;
rsChoirs.LockType = 3;
rsChoirs.Open();

conn is defined in an include file.

So I made a stored procedure for this query, but if I were to call it from my ASP program I'd still need most of the same code:

var rsChoirs = Server.CreateObject("ADODB.Recordset");
rsChoirs.ActiveConnection = conn;
rsChoirs.Source = "CALL spCLASSES(scode)";
rsChoirs.CursorType = 0;
rsChoirs.CursorLocation = 2;
rsChoirs.LockType = 3;
rsChoirs.Open();

At least I think I do - I'm not certain yet that this is correct.

So where is the benefit?

[Easy_Coder]

txbakers, the benefit comes in on the db side. It knows about your query and has an execution plan in place and ready to go when you make use of sprocs. The db knows nothing about your query when you throw pass-thru sql at it.

[txbakers]

COOL. now all I need to work out is the proper syntax for calling/retrieving in ASP/Javascript.

[Easy_Coder]

Here's one way to fire your sproc...

set oCMD = CreateObject("adodb.command")
with oCMD
  .CommandType = adCmdStoredProc
  .CommandText = "spCLASSES"
  .Parameters.Append .CreateParameter("@parametername", adVarchar, adParamInput, 25, scode)
  .ActiveConnection = conn  
end with
set oRST = CreateObject("adodb.recordset")
With oRST
  .Open oCMD, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
  If Not .EOF Then
   aResults = .GetRows()
  Else 
   aResults = Null
  End If
  .Close
end with
set oRST = Nothing
set oCMD = Nothing

[coopster]

Easy_Coder has offered one of the benefits, but there are other benefits to using stored procedures. One of the most used examples is the old "suppose you want to update a row but you are not sure if the record exists." One of the most common practices is to SELECT the row first to see if it exists and then UPDATE it if it does otherwise INSERT it if it doesn't. Well, you can see where stored procedures clean things up a bit nicer in this case, as well as move the operation into the database engine as opposed to bouncing back and forth in the code.

Either way will work, of course, SQL or stored procedure. But perhaps this example starts to show what is possible with stored procedures.

[Easy_Coder]

Yeah that's right.... we are barely scratching the surface with the example I gave.

Here's more:

- A sproc is a re-usable object that can be executed from any web page, desktop, dts package etc...
- Reduces network traffic by not passing large pass-thru sql strings
- Lets you isolate database logic from the front end

[aspdaddy]

Much more secure, not as vunerable to SQL injection.

Easy_Coder- I dont know about mySQL, but with MSSQL I use a quick "Refresh" to avoid the paramater type declarations:

set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "spName"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters("@Name")= Value
objCommand.Execute
ReturnValue = objCommand.Parameters("@ReturnValue").Value

[Easy_Coder]

Yeah I'm familier with refresh() but you should avoid using that method because you pay a performance penalty.

That method forces a bunch of round trips to figure out what its got (datatype, size etc...) for each parameter.

[henry0]

When you are speaking about reusable object
are you also ref to an object that in OOP PHP we will instantiate by calling a new whatever object name?
so what is the dif in between creating a new object or sending a query to your DB?

The way to call sproc?

Obviously before shooting in the dark I shall read the tutorial.

[Easy_Coder]

oh and one other thing... set your ActiveConnection Property last to prevent the refresh() method from automatically firing under the hood.

[Easy_Coder]

When you are speaking about reusable object
are you also ref to an object that in OOP PHP we will instantiate by calling a new whatever object name?

It's a reusable object in that you can extend them to just about any other application; not just a single web site. The beauty of that is ease of maintenance. If you need to make an adjustment to the query then it can be done from a single location versus the n number of places where the pass thru sql exists. Now, if you change the interface then you might need to re-wire the command unless you've created a command wrapper.

In my situation I have an accounting system, web based dashboard, website, dts services and web services all firing and sharing the same stored procedures.

[henry0]

YES thanks I see the benefits
If goes many steps above the usual "Write reusable scripts"

But access is pretty much (at the present time) reserved to those managing their own server, I do not think that most ISP are ready to update before the release of a few new versions and lots of proven track records.

I will start by updating my test bed.

[Easy_Coder]

Another benefit is security. You can implement security down to the procedure level so that only certian users have rights to fire certain sprocs. That's an MSSQL statement not sure about mysql.

[coopster]

Yes, true for MySQL as well.

I would dare to strectch far enough to say that any database that has implemented Stored Procedures has implemented that level of security that goes along with it. If not, they really haven't developed a stored procedure engine in their RDBMS.

[aspdaddy]

That method forces a bunch of round trips

Round trips arent necessarily a bad thing if the database and webserver are on the same box.

One big advantage of refresh is a much lower cost of maintaning the code.

[Easy_Coder]

If performance isn't an issue then I suppose it's ok but its really recommended that the refresh method not make it into your production code.