How to Create Stored Procedures and Functions in a MySQL Database

Over the past few years one major advantage that some databases, such as Oracle and Microsoft SQL Server, have had over MySQL is their ability to use stored functions and stored procedures. Well, that was before MySQL 5; with MySQL 5 a database developer can start adding in their own bespoke functionality.
What are Stored Procedures and Stored Functions?

Any programmers reading this should already be comfortable with the concept of subroutines and functions: encapsulated pieces of code that can be called by programs - often used to carry out repetative or complicated tasks.

Subroutines and functions can be made available to a single program or many; and that, of course, is what stored procedures and stored functions are - they are procedures (or subroutines) and functions stored in the database.
What's the Difference Between a Stored Procedure and a Stored Function?

The difference between a stored procedures and stored functions is the same as the difference between a subroutine and a function:

* a stored procedure runs some code
* a stored function runs some code and then returns a result

Why Use Stored Procedures and Stored Functions?

The real advantage to using stored procedures and stored functions is that they provide functionality which is platform and application independant. For example, a team of developers may provide:

* a Visual Basic application on Windows
* a Gambas application on Linux
* a PHP application on a web server

Without stored procedures and stored functions then the functionality would have to be developed independently for each application, but with stored procedures and stored functions the functionality only has to be developed once.

1. Creating MySQL Stored Procedures

A stored procedure is the same as a subroutine in that it cannot directly return a result, however it can receive variables that can be modified by the procedure; these variables are defined as:

* in - the variable can only be used as an input to the procedure
* out - the variable can only be used as an output from the procedure
* inout - this is both an input to, and an output from, the procedure

Procedures are always declared in the same way:

* define the procedure name
* define the procedure inputs and outputs
* define the the body of the procedure (enclosed within a BEGIN ... END statement)

One other (rather imporant) thing to bear in mind is that semicolons are used as part of the definition of the procedure. For this reason the end of line delimiter must be redefined to something that won't be used in the definition. For example:

delimiter //
create procedure circle_area (in r double, out a double)
begin
set a = r * r * pi();
end
//
delimiter ;

Running MySQL Stored Procedures

A MySQL stored procedure is run by using the call method:

call circle_area(22, @a);
select @a;


In this example 1520.5308443375 would be displayed on the screen.

2. Creating MySQL Stored Functions

Unlike stored procedures stored functions always return a result, they will also be one of two types:

* not deterministic - may produce different results for the same inputs (for instance random numbers or dates)
* deterministic - will always produce the same result for any given inputs

Like procedures, all functions are created the same way:

* define the function name
* declare any inputs
* define the data type to be returned by the function
* state whether or not the function is deterministic
* define the body of the function (again within a BEGIN ... END statement)

For example:

delimiter //
create function circumference (r double) returns double
deterministic
begin
declare c double;
set c = 2 * r * pi();
return c;
end
//
delimiter ;



Running MySQL Stored Functions

Unlike stored procedures stored functions are used as part of a select statement:

select circumference(22);

In this case (for anyone that's interested) the result would be 138.23007675795.
Conclusion

Stored procedures and stored functions are important tools for any database developer, and thankfully, those tools are now available to MySQL users - provided, of course, that they're using MySQL 5.

Read more: "MySQL Stored Procedures and Functions: How to Create Stored Procedures and Functions in a MySQL Database" -
http://database-programming.suite101.com/article.cfm/mysql_stored_procedures_and_functions#ixzz0DVzBNS5i&A

How to Access MySQL Stored Functions from a C++ Program

http://www.atlasindia.com/sql.htm

One of the most powerful combinations that any programmer can use is the combination of C++ and MySQL - a flexible programming language with a multi-platform and stable database; but this may seem an intimidating task to the new software developer.

It's not. This article will show just how easy it is for a programmer to use C++ to:

* set up a connection to a MySQL database
* use the C++ code to access an MySQL stored function
* display the results returned by the MySQL stored function
* and (perhaps most importantly) handle any errors

Setting up Test Data in a MySQL Database

Before a programmer can use a database that database must, of course, exist; or, at very least, a test database must exist. Fortunately creating a database in MySQL is very simple and consists of three steps:

1. log on to MySQL
2. use SQL to create the MySQL database and any tables
3. populate the tables with appropriate data

The first step (logging on to MySQL) can be done from the command line:

mysql -p -u user mysql
&nbsp or create new user from root login
mysql -u root -p
GRANT ALL ON cpp_data TO 'user1'@'localhost' IDENTIFIED BY 'password';
exit
mysql -p -u user cpp_data
Next, simple SQL can be used to the database and tables for the database:

Read more: "Using a MySQL Database with C++: How to Access MySQL Stored Functions from a C++ Program"

create database cpp_data;
use cpp_data;
create table users(id int, fname varchar(25), sname varchar(25), active bool);
insert into users values (1, 'Fred', 'Smith', True);
insert into users values (2, 'Jane', 'Jones', True);
With this done, it's time to start thinking about doing some actual programming.
Creating a Stored Procedure in a MySQL Database

One of the new additions to MySQL is one that Oracle users will already know - the stored function. The great advantage to using stored functions is that programming code can be built into the database rather than into an application - meaning that multiple applications can use the same piece of code:

delimiter //
create function user_count () returns int
deterministic
begin
declare c int;
select count(*) into c from users where active = True;
return c;
end
//
delimiter ;
select user_count ();
This code simply returns the number of active users (from the table users).
Loading the MySQL Header File into C++

When using MySQL with C++ the programmer needs to know absolutely nothing about the actual mechanics of the process - all the programmer has to do is to load the MySQL header file:

#include &lt iostream &gt
#include &lt mysql.h &gt
using namespace std;
MYSQL *connection, mysql;
MYSQL_RES *result;
MYSQL_ROW row;
int query_state;
int main() {
return 0;
}

C++ Code for Connecting to a Database

This example code above will compile and run, but doesn't actually do anything - first the C++ code must make a connection to the MySQL database:

mysql_init(&mysql);
//mysql_real_connect(&mysql,"localhost","User","Password","databaseName",0,0,0);
connection = mysql_real_connect(&mysql,"localhost","user1","password","cpp_data",0,0,0);
if (connection == NULL) {
cout << mysql_error(&mysql) << endl;
return 1;
}
The above code:

* initialises the MySQL connection
* makes the connection to the MySQL database (for which the programmer needs to define the host, user name, password and database)
* displays an error message if the connection is rejected for any reason

C++ Code for Running a Query on a MySQL Database

Having made a successful connection to the MySQL database the C++ code may be used to send s SQL query - in this case to run the stored procedure created earlier:

query_state = mysql_query(connection, "select user_count()");
if (query_state !=0) {
cout << mysql_error(connection) << endl;
return 1;
}
This time the C++ code sends the SQL and then displays another error message if any problem is encountered.
C++ Code for Processing the Results of a MySQL Query

If the connection is successful and the query returns a result (otherwise known as a recordset) then the next step is to display those results:

result = mysql_store_result(connection);
while ( ( row = mysql_fetch_row(result)) != NULL ) {
cout << row[0] << endl;
}
C++ Code for Disconnecting from a MySQL Database

The final step is to free up any memory used by the recordset and to close the connection:

mysql_free_result(result);
mysql_close(connection);
Compiling and Running the C++ Code

How the code is compiled will depend on the operating system being used and the local set up - in the case of Debian Linux the code would be compiled by using the command:

g++ -o db db.cc -L/usr/include/mysql -lmysqlclient -I/usr/include/mysql
Assuming, of course, that the code is stored in a file named db.cc.
Conclusion

Both the MySQL database and the C++ programming language are powerful tools in their own right; and combined they are an incredibly important tool for the software developer - an important tool and one which is very easy to use, and very, very effective.
Further Reading

MySQL Stored Procedures and Functions

Read more: "Using a MySQL Database with C++: How to Access MySQL Stored Functions from a C++ Program" - http://c-programming.suite101.com/article.cfm/using_a_mysql_databases_with_c#ixzz0DVt7ShLb&A