homepage Welcome to WebmasterWorld Guest from 54.166.122.86
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Stored Procedure Tutorial
is there one to be recommended?
txbakers

WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 11:29 pm on Oct 24, 2005 (gmt 0)

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

 

henry0

WebmasterWorld Senior Member henry0 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 12:24 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 1:03 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 1:36 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 1:41 pm on Oct 25, 2005 (gmt 0)

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

Easy_Coder

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 1:52 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 3:46 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 3:58 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 6:41 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 9:03 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member henry0 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 9:05 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 9:16 pm on Oct 25, 2005 (gmt 0)

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

Easy_Coder

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 9:23 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member henry0 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 9:35 pm on Oct 25, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 12:39 am on Oct 26, 2005 (gmt 0)

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

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 106 posted 2:59 pm on Oct 26, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 3:15 pm on Oct 26, 2005 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 106 posted 1:25 am on Oct 27, 2005 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved