Inserting Data with MySQL Stored Procedures

A common operation of many applications is the simple task of inserting data into a database. If data needs to be persisted across multiple sessions or users then a central...

a year ago

Latest Post Automatic Offline Backup With a Raspberry Pi by Tyler Moon

A common operation of many applications is the simple task of inserting data into a database. If data needs to be persisted across multiple sessions or users then a central database server is the way to go. MySQL is a common choice and has a free version which makes it great for small projects and startups. In this article we are going to go through how to install MySQL Server, setup a simple database with a couple of tables, and then how to write stored procedures to insert data into the database in a controlled and secure fashion.

Prerequisites

Setup

For this article I am going to focus on how to install MySQL server on MacOS but it also runs on the major Linux distros and Windows 10

Assuming that you have Homebrew installed. The following command will install the MySQL server to a local environment.

brew install mysql@5.7 

# Note: the @5.7 part is optional as it selects and older version. Without this it defaults to the latest

Now to start the server run:

mysql.server start

And there you go! Your MySQL server should be up and running now

Creating A Demo Database

While you could create the database through the CLI tool it is much easier for this kind of work to be done through a GUI. This is where Sequel Pro comes in. While its not the fanciest or most robust option out there it is free, lightweight and for most small projects gets the job done.

Go ahead and open up Sequel Pro and you should be greeted with the connection screen

Enter the following information (substituting as needed for any differences in local setup)

Host: localhost
Username: root
Password:
Database:
Port: 3306

Next click on  Choose Database and then Add Database...

In the next dialog enter demo_db and click Add

Now that we have a database setup lets create a table for our stored procedure to insert data into. If its not open already open the Query tab which allows us to run SQL queries against the MySQL database. Enter the following query and then click Run Previous to execute.

CREATE TABLE demo_table (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  value varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This query will create a table called demo_table with an id column and a value column.

Creating the Stored Procedure

Now that we have all that setup out of the way we can focus on creating our stored procedure that inserts data into the demo_table table. Delete the contents of the Query window as we have already created our table so we don't need them anymore and replace it with the following query and once again click the Run Previous button to execute.

use demo_db;
DELIMITER $$

DROP PROCEDURE IF EXISTS insertDemoData$$
CREATE PROCEDURE insertDemoData (
	IN p_id INT(11),
	IN p_value VARCHAR(50)
)
BEGIN

INSERT INTO demo_table(id, `value`)
VALUES (p_id, p_value);

END$$
DELIMITER ;

You should now have a stored procedure (aka a "sproc") which will insert data into the table with the entered data. Try the following two queries to call the newly created sproc and then view the data inserted.

CALL insertDemoData(1, "Hello World!");

SELECT * FROM demo_table;

And there you go the stored procedure can insert data into your MySQL database.

Summary

In this article we went over the basics of:

  1. Installing MySQL using Homebrew on MacOS
  2. Using Sequel Pro to create a new database
  3. Using Sequel Pro to create a table in that database
  4. Writing a stored procedure to insert data
  5. Executing that procedure with data parameters

For more information on how SQL queries work check out this awesome set of tutorials over at W3Schools.

Tyler Moon

Published a year ago

Comments?

Leave us your opinion.