Stored procedures

[Anon]

Can we write Stored procedures in MYSQL? or is there an alternative to it. I want to do a bunch of SQL statements with tmp tables and return a record set

Thanks
Nirmal

[[deleted]]

"Can we write Stored procedures in MYSQL?"

No.

"or is there an alternative to it."
Kind of, you can write custom functions in C and 'install' them into MySQL.

"I want to do a bunch of SQL statements with tmp tables and return a record set"

Why don't you just do them from PHP?

[Anon]

: Why don't you just do them from PHP?

Stored Procs are much more powerful (multiple sql querys, temp tables, etc) and much quicker. Im waiting for the day mySql supports them.

• James

[[deleted]]

"Stored Procs are much more powerful (multiple sql querys, temp tables, etc) and much quicker. Im waiting for the day mySql supports them."

Stored procedures are nothing more than a predefined set of queries that you can execute a bit like a batch file. From an SQL point of view, there's nothing they can do that 'normal' queries can't. (that's becuase they are built up of normal SQL queries)

Normal queries can also use temp-tables, and you can start as many queries as you like (just not all at once, which you really don't want anyway)

[Anon]

Doing what Stored Procedures do in PHP is a lot of work, you need to process the record set record by record using a loop.. it is a lot of work.. and it eats a lot of SQL compling time

[[deleted]]

"Doing what Stored Procedures do in PHP is a lot of work, you need to process the record set record by record using a loop.. it is a lot of work.."

If you are unwilling to use temporary tables perhaps....

"and it eats a lot of SQL compling time"

If anything it eats PHP cycles, the database just gets less and less work to do.

[Anon]

Every time a SQL is passed to MYSQL it is parsed and complied , if you have a stored procedure, it is a complied verion of the all the SQLs, so you save lots of time there.. and then there is a lot of network traffic generated when you send SQLs one by one...

[[deleted]]

Actuallty the stored procedures are compiled everytime you execute them, they are not precompiled.

But hey, I'm not saying stored procedures are evil or anything, they are great! super! I use them all the time.

I'm just saying that you're not in trouble just because MySQL doesn't support them. The overall functionality is the same, it's just slower, that is all.

[Anon]

Stored Procedures
So what happens when your queries actually get to the database server? The answer is quite astonishing (or at least it was to me when I first learned about it): they are compiled. Most developers use SQL as if it were an interpreted language; they send the whole query text to the server every single time, where it's compiled and then executed. If you think about it, this is not the ideal approach. It's a bit like shipping the source code for a C++ application, then recompiling it every time it has to run. (This is an extreme example; the kind of compilation performed by the server is not the same as compiling a C++ program.) A much better solution would be to have the database server compile a query once, store it in a compiled format, and run it on request without having to recompile it every single time. This is what stored procedures do.
http://www.microsoft.com/mind/defaulttop.asp?page=/mind/0399/sql/sql.htm&nav=/mind/0399/inthisissuecolumns0399.htm

[[deleted]]

...and?

[Anon]

he is just saying that they aren't compiled every time like you said.

this whole thread is flame bait...

[[deleted]]

Very flamebait indeed.

[Anon]

You may have to use SQL Server I have used it with PHP, or Oracle but you could use PostgreSQL.

[gerardg]

There was an article on slashdot a while ago about stored procs and mysql using perl.