MySQL 5.0 Stored Procedure Programming Tutorial

May 23, 2007


MySQL Stored Procedure is a new feature release in version 5.0, positioning the database a step closer in features to the leading database products. In this article, you’ll be guided on how stored procedures are created using several tools described below.

Before we start the tutorial, let’s have a quick check on the tools you will need. Listed below are the following you can download from the Web. Also, the code used in this tutorial were tested by these tools, and your results may vary depending on how close or far you are from the tools mentioned below.

The article assume you already have the tools installed and you are now ready to follow the steps. However, you can still follow and learn what makes sense if you are running a different copy of Linux or a different MySQL Administrator version.

What this article will show you are the following steps:

  1. Create a new schema.
  2. Create a new table.
  3. Create a new stored procedure.
  4. Testing the stored procedure.

Create A New Schema

Launch MySQL Administrator from Applications-> Programming-> MySQL Administrator, then select View from the menu bar. The View menu appears, now select Catalogs from the list. You’ll see a list show at the bottom left of the panel. Right click on that list and select Create Schema. A dialog box appears containing a textbox for entering the schema name. Enter a name in the textbox and click the OK button.

Create A New Table

This step will create a new table. Select the newly created schema from the schema list located at the lower left part of the panel. You’ll see five buttons at the lower part, select Create Table. The table editor dialog box shows up, from there you can start naming the table and also add the columns as well. Use the table definition below to populate the dialog box. Click Apply Changes to save and create the table.

CREATE TABLE `mmorpg_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) default NULL,
  `user_url` varchar(100) default NULL,
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) default NULL,
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) default NULL,
  `latest_ping` datetime default NULL,
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Create A New Stored Procedure

There are four tabs at the top part of the form. Select the Stored Procedures tab to open the list. Click the Add Procedure button and a routine name dialog box will show up, enter the name of the procedure and select OK. At this point, you can simply copy-and-paste the stored procedure code shown below. Click Execute button to commit the code.

CREATE DEFINER=`root`@`localhost`
    PROCEDURE `setUser`(OUT _id int, _uid varchar(60),
                                         _pwd varchar(64), _nm varchar(50),
                                          OUT _retcode int)
BEGIN
	declare _cnt int;
	declare _errno int;
	set _retcode = 0;
	case _id
		when 0 then
			select count(*) into _cnt from mmorpg_users
				where user_login = _uid;
			if _cnt = 0 then
					insert into mmorpg_users (user_login, user_pass, user_nicename)
						values (_uid, _pwd, _nm);
					select last_insert_id() into _id;
			else
					set _retcode = 1;
					select ID into _id from mmorpg_users
						where user_login = _uid;
			end if;
		else
			update mmorpg_users
				set 	user_login = _uid,
					user_pass = _pwd ,
					user_nicename = _nm
			where ID = _id;
	end case;
	select * from mmorpg_users where ID = _id;
END

Testing The Stored Procedure

At this point, you are now going to test the stored procedure. Open the Terminal program from Applications->Accessories->Terminal. Run mysql with the following:

desktop:~$ mysql -u root

Then enter the following text inside mysql.

mysql> call setUser(@id, 'John', 'btd19x', 'redball', @retcode);

Conclusion

This is just one sample implementation of a MySQL stored procedure, walking you through the steps. With these tools, you can now expand to even create more procedures for your own applications. Perhaps even write sophisticated ones as well.

Advertisements

Adobe Flex Development On Ubuntu Linux

May 13, 2007

Adobe Flex is an API that works in tandem with Flash 9 player, is available as an SDK. There is a free version of the SDK that allows you to develop Flex applications. You can get the SDK from the download section and install it in your development directory. The SDK contain two sets of development tools that target two platforms: Linux and Windows. You can code and test in Windows or choose a flavor of Linux, remember to simply run the appropriate compiler to get the output file generated.

If you’re interested to know more about setting-up a Flex development environment in Linux, please read-on as I’ll document the steps needed to get you up-and-running.

From here on, you’ll be seeing concrete examples that might confuse you if you’re not familiar with what I’m describing. For example, Ubuntu will be used as a reference OS and that might confuse non-Ubuntu developers. If that’s the case, you can still follow-through and try to relate them to your familiar environment.

Let’s start with what tools are needed to setup the environment.

  1. Ubuntu 7
  2. Adobe Flex SDK (free version)
  3. Java JRE
  4. Firefox

We’re now ready to assemble the parts. Here we go.

Ubuntu

I chose Ubuntu as a reference for this article. The reason behind for choosing Ubuntu is a subjective one and I’m not going to go there explaining why. This is one of the benefits of having to participate in Freedom Culture.

I’ve set up the latest version known as Feisty Fawn and you can get a free copy from www.Ubuntu.com , download a copy and install, you should be OK to follow the next step.

Flex SDK

The free version of Flex SDKis available from Adobe Flex Download. Download the package and install it in your development directory.

  1. Click download to start download.
  2. Save package in ~/Packages.
  3. Expand downloaded package in ~/Packages/flex
  4. For every build.sh file add the following text shown in the code box.
  5. That’s it.

Setting the path to Java JRE instead of the default Java that came with Ubuntu.

#code box
export JAVA_HOME=~/Packages/jre
export PATH=$JAVA_HOME/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

If this approach doesn’t appeal to you, there is another method you can do and that is simply add the setting to ~/.bashrc instead.
Java JRE

Java developers may find this one optional as they already have Java JRE installed. In my case, I don’t code Java so I had to download the JRE from http://www.Sun.com, that’s definitely an extra step. One thing I found out when I was testing the sample files, the Java that came installed in Ubuntu didn’t work, that’s why you need to get a working version for Flex SDK.

  1. Download Java JRE from http://www.sun.com
  2. Install in ~/Packages/jre/

At this point, you can now compile the sample files located in ~/Packages/flex/samples/ directory. Simply navigate to the directory and run the shell script build.sh and you’ll see the output files with the extension .swf, you can drop the swf file onto a browser and it will render once it gets loaded.

Firefox

This is the last step to get you going. Flex is browser dependent and mostly all testing will be done using a browser. In Ubuntu’s case, the Firefox browser is already installed and accessible from the desktop toolbar.

Conclusion

At this point, your Flex development environment is already set up. And if you count how much they cost to get these tools installed you will be surprised they are free as in beer.

Thanks for reading if you’ve reach this far.

Addendum: by the way, check out the Flex SDK website for more information on developing applications using the Flex SDK


One Terabyte Load-And-Query Perf Test

May 7, 2007

Let me start with how much space is one terabyte. According to this webpage, one terabyte is about 1024 gigabytes; so let’s say that would be about four 256GB disks if you buy them from BestBuy, or ones they sell now you can get two 512GB disks currently selling at Fry’s. For a home enthusiast/hobbyist, buying two 512GB disks would be the right choice for a homebrew PC box. Stick it in the two bays, plug the cables and let Ubuntu take care of it. That’s pretty much it, got it squared-away.

512GB Disk

I’m going to the next point now and that is about data sourcing. The web is basically a network of data, a vast collection of whatever-you-wanna-call-it is right there sitting on the web. It’s just there, wow! Imagine the wealth of information you can get from that vast sea, ocean or even celestial data space. Man! that is simply awesome, mind-blowing just thinking about that great number of data. So yeah, data is up there waiting to be mined. That is the source, pure unadulterated wide-open wealth of knowledge right at your doorstep.

Here’s my third point, I’m going to relate the first paragraph to the second paragraph and it will go something like a data processing system on your home machine. All I got is one terabyte of empty space, waiting to be populated with collected data from the Great Web. I’m guessing one terabyte is enough to perform a simple experiment required to generate a very interesting report which may or may not have any value to anyone, except me. The resulting output definitely has a huge potential because I believe in this truism that “the perfect data is the one you have never seen yet.” Casting a big wide net to the web and hauling it over to a one terabyte space for processing will definitely capture that hidden gem. The most important part of the process is performing thing this called synthesis, which would even refine it a cleaner version.

This is my closing for this entry. Some of the tools are already in place, I just got it working yesterday, enough to proceed and carry-on to the next level of test. Although, I may have to cough-up some dough for the 1024GB disk as they are not cheap. 500GB disk is still pretty expensive compared to 160GB, though. It is definitely quite an investment for that small experiment I’d like to perform. Drive and Redland will be the ones doing the heavy lifting.


Ubuntu Desktop, Webservices, DOM, RDF

May 6, 2007

MyDesktop

Setting-up the webservice wasn’t easy. It took me a good number of hours figuring out how this thing will land on a user home directory. Simply following a concept of having a separate HTTP server specifically for the desktop can provide a good separation, a layer between other HTTP programs was in order.

A new directory was created to house the webservice server, located at ~/webservice. The test webservice program finally ran in the late afternoon. One problem I did encounter was correctly setting-up an Ubuntu launcher icon capable of launching the server. I tried many times without success, though the server went up without any problems when started from inside a shell.

That’s only one piece of puzzle already in-place. The remaining pieces are still out there in the wild. But they will be added later, time permitting.

Design

Basically, here is what I’m after.

  1. A desktop webservice – provide a set of services covering the desktop.
  2. An Object Model – an object model exposed to javascript and the CLR.
  3. An Entity-like client – code resides behind a URL, delivered via HTTP, then instantiated by a browser-like program, similar to Entity.
  4. RDF enabled – an agent residing behind the server. Connected to an Object Model and is reachable via Javascript and the CLR.