Stored Procedures

[sd0t1]

I'm just now getting into using Stored Procedures. Mainly cause a developer on my new project is suggesting it. As I've been doing my research and going through tutorials I keep running across older articles that claim they are bad and to keep my scripts in PHP.

I need some more opinions on this. Can anyone share their thoughts on the use of Stored Procedures in MySql?

 

[DonQuichote]

There are a lot of reasons stored procedures are good or bad. I use them sparingly. However, I worked for a company that had a policy to communicate with the database through procedures exclusively. The main reason was security: When you only have execute rights on procedures, you can never have access to the underlying tables. As an example, you may have the right to a CheckLogin routine, but not to the Accounts table. Off course, all direct table rights should be denied in such a case.

I use procedures mainly in my (repeatable) re-create scripts, because I sometimes have to check if something exists before either creating or dropping it.

Occasionally, I use them for difficult queries or difficult updates as well.

However, procedures get "behind the back" of the programs. But then, all database connections do. That's what multi-user programming is all about.

That said, I am more afraid of triggers than of procedures. Triggers are some "black magic" that can run without anyone realising it, but procedures just have to be called. So you can find procedure calls in the query log, for instance.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[jpadie]

horses for courses. depends on the spec of your mysql box compared to the spec of your php box, and also depends what you're doing with stored procedures of course. and whether your database is accessed by more than one front end (asp/ php etc).

non-dynamic selects that are performed regularly would of course benefit by stored views.

there are also somethings (date/time manips) that may be better done in the database too.

go with what your comfortable with: your time is nearly always worth more than minor optimisation. the scale tips on very big/busy sites, however: as tsuji has pointed out in another thread.

 

[sd0t1]

Hey thanks alot guys. I do have one more question.

What is this syntax? Is it shorthand php or does it mean somethinig else?

I'm talking about the "->" dash and arrow bracket.

$result->fetch_row()

$mysqli = new mysqli($this->host, $this->user, $this->pwd, ACTIDE_DB);
if($result=$mysqli->query($sql)) {
while ($row = $result->fetch_row()) {
print_r($row);
echo "<br><br>";
}
}

 

[feherke]

Hi

What is this syntax? Is it shorthand php or does it mean somethinig else?

I'm talking about the "->" dash and arrow bracket.

See thread434-1555122, jpadie and vacunita already explained the basics of OOP there.

Feherke.

http://rootshell.be/~feherke/