OurSQL Episode 119: I Do DECLARE

This week we talk more about stored routines, including the several different DECLARE statements. Ear Candy is about ssh hacks to make logging into multiple servers easy, and At the Movies is "Everything You Ever Wanted to Know About Deployment But Were Afraid to Ask".

Events
FOSDEM 2013 is taking place in Brussels, Belgium Sat Feb 2 - Sun Feb 3rd. Call for papers for the MySQL & Friends room closes December 21st.

Northeast LinuxFest is happening at Harvard University, Cambridge MA Saturday March 16th and Sunday March 17th, 2013. Open Database Camp will be held there. Sponsors are needed, make sure to specify you are helping out because of Open Database Camp. Registration is free, register today!

Percona Live: MySQL Conference and Expo 2013 is being held at the Santa Clara Convention Center Monday April 22nd - Thursday April 25th. The tutorial schedule is up. Register today, early bird pricing goes until Friday, December 28th.

Training
MySQL Marinate, a FREE basic MySQL training course led by Sheeri.

SkySQL Trainings

Oracle's MySQL Trainings

Percona Trainings

Advanced Stored Routines
Episode 118 about the basics of stored routines

Connecter/Net Visual Studio Debugger for Stored Routines

Meet the MySQL Experts podcast
about Debugging Stored Routines in MySQL with Fernando Gonzalez.

Dr. Dobbs article about how to debug stored routines the clever/hacky way

Why SQLyog does not yet have a stored routine debugger

Commercial tools that have a debugger:
MySQL Debugger
Toad
DB Forge studio
Illatis StepIn (note: this link was broken at the time the show aired, but we leave it here for completeness' sake.

Full list of MySQL error codes, SQL states, and messages in the manual

DECLARE ... HANDLER syntax

MySQL Stored Procedure Programming by Guy Harrison and Steven Feuerstein.

Flow Control Statements in MySQL stored procedures, explaining CASE, IF, ITERATE, LEAVE, LOOP, REPEAT, RETURN and WHILE.

max_sp_recursion_depth system variable

CURSORs in MySQL and cursor syntax.

Sample stored procedure:
The full stored procedure definition is:
DELIMITER |
CREATE PROCEDURE check_actors()
BEGIN
DECLARE cur_actor SMALLINT UNSIGNED;
DECLARE film_count INT UNSIGNED;
DECLARE done,actor_count INT UNSIGNED DEFAULT 0;
DECLARE c_all_actors CURSOR FOR SELECT actor_id FROM actor;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN c_all_actors;
WHILE done=0 DO
FETCH c_all_actors into cur_actor;
SET actor_count=actor_count+1;
SELECT COUNT(*) INTO film_count FROM film_actor WHERE actor_id=cur_actor;
IF film_count=0 THEN
SELECT * FROM actor WHERE actor_id=cur_actor;
END IF;
END WHILE;
CLOSE c_all_actors;
SELECT actor_count;
END
|

Nesting MySQL cursor loops by Roland Bouman

mysqldump --routines

Stored routine limitations

Ear Candy
about /etc/hosts
about ~.ssh/config

Sample ssh_config:
host hostname
user gerry
Port 2222
IdentityFile ~/.ssh/ident
ForwardX11 yes

At the Movies
This week, in at the movies, we present Laura Thomson of Mozilla talking about Everything You Ever Wanted to Know About Deployment but Were Afraid to Ask. This presentation was given at CodeConnexx last month in Indianapolis.

Where you can see us
Gerry:
Gerry will be giving a webinar Best Practices for a Successful TokuDB® Evaluation on Tuesday Dec 11th starting at 2pm ET / 11AM PT.

Sheeri:
MySQL Marinate starts January 1st, 2013. It is a virtual self-study course learning MySQL which both Gerry and Sheeri will be helping lead.

Starting in January, the Seattle MySQL meetup will talk about MySQL Marinate for those who will be following it. The main topic for January will be the first 'MySQL show and tell' session where Lisa Phillips will be talking about MySQL @ Twitter.

Sheeri will be at the January Boston MySQL meetup group on Monday, January 14th from 7-9 pm. The topic is NuoDB, an emergent database that is 100% SQL, 100% ACID, and scales out elastically on decentralized resources in the cloud, on-premises, or both.

Sheeri will be speaking at the miniconfs at Linux Conf Australia, about "Getting Started With a Podcast", "mysql security" and "The Art of Cat Herding: How to Manage Geeks." Linux Conf Australia will be held Monday January 28th through Saturday February 2nd, 2013 in Canberra.

Sheeri will be speaking at Rocky Mountain Oracle User Group Training Days in denver, colorodo from Monday Feb 11 - Thursday February 14th on "Backing up MySQL" and "Are You Getting the Most Out of Your Indexes?"

Sheeri will be speaking at Confoo in Montreal, Canada on Monday February 25 - Friday March 1, 2013, giving talks in English about "Are You Getting the Most Out of Your Indexes?" and "Different (MySQL) Forks for Different Folks".

Gerry will present a tutorial at Percona Live:MySQL Conference and Expo on Using TokuDB: A Guided Walk Through TokuDB Implementation. Many of the concepts apply to custom MySQL / MariaDB installations as well. Percona Live: MySQL Conference and Expo 2013 will be held April 22-25, 2013 in Santa Clara, California.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1193