Stored Procedure in MySQL

Using MySQL had one setback – lack of stored procedures. But, we had celebrated the farewell of this hindrance after welcoming MySQL 5.0.
Stored procedure is a collection of valid SQL statements residing in the data server. It may or may not return value based on our need.
Stored procedure should be our first choice when we have a choice to play with them – because:

  • Our application would be fast due to decreased use of network
  • Logics can be used more than once
  • Maintenance is simple – just edit at one place… and even more

I will try to use simple example to demonstrate how to use stored procedure in MySQL. I will use MySQL CLI for the illustration.

Syntax: CREATE PROCEDURE procname(parameters)
BEGIN
logic goes here

END
Example:  simple procedure to collect rows of a table:

Lets create a table first:
USE dbTest;
CREATE TABLE member (Id int not null auto_increment primary key, first_name nvarchar(12), last_name nvarchar(12) );

Say we want to collect all rows from the above table using stored procedure:
DELIMITER **
CREATE PROCEDURE selectAll()
BEGIN
SELECT * FROM member;
END
DELIMITER ;
The above snippet would create a stored procedure named selectAll() on the dbTest database.
DELIMITER ** would change the default delimiter of the CLI which is ‘;’ to **. I have to change the delimiter from ‘;’ to ‘**’ or any other character except ”. Otherwise, when the execution gets ‘;’ in the middle of execution, it will halt – and finally we would return the default delimiter at the end.
Using the stored procedure would be:
CALL selectAll(); or CALL selectAll; –> which will list all the rows of the table.
What about when we have parameters?

Listing Databases and Tables in MySQL

To list databases from terminal
1. Go to terminal and type mysql -uroot -proot –> taking user name as root and password as root to log as administrator
2. type show databases; –> this will list all databases in the server.

To list tables in the given database:
1. First move to the database by typing: use databasename where databasename is the name of the database whose tables would be listed
2. type show tables;
To see the make up of the table:
1. First move to the database
2. Type describe tablename or desc tablename

Using Checks

One of the handy tools regarding the constraint in SQL Server is check.
This simple, but powerful, tool allows us to use column based constraints on the data.
Say you have a table with age column. If you want to add constraint on age for values less than or equal to zero and above 100 – thinking we would live 100 years…
ALTER TABLE tbl ADD CONSTRAINT age_constraint CHECK(age < 100);
That is all.
If I try to run a query like this..
INSERT INTO tbl (age,..) VALUES (-12);
Then the engine would tell me that this cannot happen.

Encapsulating Queries in Object

Here is some more pattern for you. I have provided how to implement singleton pattern for database.
The idea is:
Why don’t we encapsulate our queries to the database in an object?
What we need is to execute a query whether it is update query – like delete, update and insert or retrieval query like select. In both cases, the the accomplishment would be one – executing – with different query parameters.
Which means we need only a single interface per application for that would simply execute our queries independent of the query string.
The encapsulating class for queries (acting as command) shall contain the implementation of method executeQuery().

/* 
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

Open for any kind of modification and use.
Please let me know when you use and modify it as I will learn from that 
--Query.php--
*/
	class Query{
		protected $sql;
		protected $db; /*any database implementing IDatabase */
		/*Method executeQuery would accept query string 
and executes it using the database's execute query method */
		public function __construct($database){
			$this->db = $database;
		}		
		protected function executeQuery($sql){
			return $this->db->executeQuery($sql);
		}
	}

Another point here would be, since we have two major tasks to perform against database – namely update queries and retrieval queries – we need two query commands which would be the derived classes of Query class

	/* This class would perform non resulted queries - 
like update, insert and delete
-- Query.php--
 */
	class UpdateQuery extends Query{
		/* constructor would pass the database for query class */
		public function __construct($db){
			parent::__construct($db);
		}
		/* execution of queries would goes here */
		public function executeQuery($sql){
			return parent::executeQuery($sql);		
		}
	}


	/* This class would perform resulted queries - like select */
	class ResultQuery extends Query{
		/* constructor would pass the database for query class */
		private $result; /* result object that would be created from database */
		public function __construct($db){
			parent::__construct($db);
		}
		/* constructor would pass the database for query class */
		public function executeQuery($sql){
                        /* Assigning the object */
			$this->result =  parent::executeQuery($sql); 
			return $this; //return the result query;
		}
		/* Collect the row from the collected result */
		public function getRow(){
			/*using associative array for the rows*/
		       if ( $row=mysql_fetch_array($this->result,MYSQL_ASSOC) ) {

            			return $row;

        		} else if ( $this->size() > 0 ) {

            			mysql_data_seek($this->query,0);

            			return false;

        		} else {

            			return false;

        		}

    		}
		/* Size of the collected result */
    		public function size () {

        		return mysql_num_rows($this->result);

    		}
	}

The hot-to-go part is simple. Any query should be executed! But the type of query is different – some require result set and other don’t. Which is a kind of topping we want on our query pizza. UpdateQuery and ResultQuery would provide the different type of flavors.
So, the client would encapsulate its request in either of the commands and would call an execute method.
But, this has some problem. We would need to know specifically of which type the query is. But what if we have an interface which would accept any command object and invoke its execute method – this seems neat..

/*
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

An invoker class would implement the 'command' pattern over 
the database applicaion
the invoker would be called by any client who needs a service 
from database either update or non-update

--Query.Invoke.php--
*/
	class QueryInvoker{
		private $query_obj; /*any query object inheriting 
from Query class */
		public function __construct($query_obj){
			$this->query_obj=$query_obj;
		}


		public function setQuery($query_obj){
			$this->query_obj=$query_obj;
		}
		/*the invoker would accept object [ which could be 
either update query object or non update query object ] which it 
has no any idea, and would invoke its  execute method */
		public function execute($query_obj,$sql){
			return $query_obj->executeQuery($sql);
		}
	

An example showing the interaction would be:

/*this example assumes you would put all your classes in the 
folder named classes - which is a practice of course.
	require('classes/MySQL.Database.php');
	require('classes/Query.php');
	require('classes/Query.Invoke.php');
	$obj_invoker=new QueryInvoker();
	$objDb=Mysql::getInstance();
	$objDb->connect();
	$objUpdate=new ResultQuery($objDb);
	$sql="select * from tbl";
	$result = $obj_invoker->execute($objUpdate,$sql);
        while($row = $result->getRow()){do stuff}

You can get Mysql class in the singleton pattern part of my article.

How to change password in ubuntu

Here is how you would change password in ubuntu

1. Go to preferences->about me
2. On the right top corner of the form you would see the Change Password..
3. Follow the wizard.

Free FTP tool on Ubuntu

Do you want to upload files to your web server and are you on Ubuntu OS? If yes then you might want to have one cute FTP tool for free. That is named FILEZILLA
Just do this>
1. Go to Applications->Accessories->Terminal
2. sudo apt-get install filezilla
YUP! you are done.
Now go to Applicatoins->internet->Filezilla
Enter your host, username and password then uploading/downloading would be yours.

Singleton in PHP

One of the commonly utilized part in programming is database access. Accessing a database in PHP is by far tied with his beloved one: MySQL.

While connecting and working with database, we have to limit the number of connections to database. Especially in a single database environment, the program shall utilize one object efficiently for all of its actions. Resource management and efficiency would be increased in doing so.

I have tried to apply the singleton pattern on the database class and share it here.
I have an interface with the following signature:


/* An interface for database.  
Author: http://gullele.com
Open for any modification and usage
Date: 18.Apr.2009
*/
	interface IDatabase{
		/* method for connection */
		public function connect();
		/* method for opening the database */
		public function open();
		/* method for closing */
		public function close();
		/* method for query that returns result - common for other kind of databases like SQL Server */
		public function executeQuery($query);
		/* method for query that does not return result */	
		public function executeNonQuery($query);
		public static function getInstance(); /*function to return a static instance of a class*/
	}

This interface would assure all the implementers would have the same functions all over.
If we have this, later, if we change the database – those methods who have been using the database class would remain unaffected.

Here is the mysql database which implements the interface and the logic of singleton pattern is applied.


/*
Author: http://gullele.com
Open for modification and any use - please let me know when you use/modify it as I might learn from your modification. 
Date: 18.Apr.2009
*/
class Mysql implements IDatabase{
		private $host; 
		private $username;
		private $password;
		private $db_name;
                private $connection_error;
		private static $instance = null; /*an instance of the class*/
		/* Connection object to be assigned for the database selected */
		private $connection; 
		/* Make the constructor private or protected to prevent the object from being instantiated directly - this is how to make it singleton pattern being applied*/
		private function __construct(){
			$this->host = 'localhost';		
			$this->username = 'root';
			$this->password = 'root';
			$this->db_name = 'intramicro';
			$this->connect();
		}
		/* Connecting to the database it also opens the database */
		public function connect(){

        		if (!$this->connection = @mysql_connect($this->host,

                            $this->username,$this->password)) {
	    			echo "Connection error";

            			trigger_error('Error Connection to database');

            			$this->connect_error=true;

        		/* Assing to the database for initial use - can be changed later if needed */

        		} else if ( !@mysql_select_db($this->db_name,$this->connection) ) {

            		trigger_error('Database selection error');

            		$this->connect_error=true;

        		}		
		}
		/* Implementation is not needed for this method as mysql would open the database when it connects to it */
		public function open(){
		}
		/* Closing the database - used for non-persistent connection*/
		public function close(){
			mysql_close($this->connection);		
		}
		/* A method to accept query object, execute it and return resource object */
		public function executeQuery($query){
			if (!$query_resource=mysql_query($query,$this->connection))

            			trigger_error ('Query failed: '.mysql_error($this->connection).' SQL: '.$query);	
			return $query_resource;
		}
		/* Non query method is not needed in the MySQL database as it can be done with my_sql()*/
		public function executeNonQuery($query){}
		public static function getInstance(){
			if(is_null($instance))
				self::$instance=new self();
			return self::$instance;
		}
	}

It is necessary to make the constructor of the implementing class private or protected to inhibit the direct instantiation of the object from the clients.
The clients shall know enjoying the object of MySQL class would only through the static getInstance() method of the class.

Here is an example

require(‘…MySQL.Database.php’); //include the file here
$objDb=Mysql::getInstance(); //get instance here
Once it is instantiated, a single object would serve for many clients.
You may want to check the security, error handling and some additional things by your own.
Cool.. Continue reading Singleton in PHP

Table read only – update error in mysql

When you move a database from other server and tried to update it, it might generate an error saying: Table tbl is read only
The very likely case for this is mysql is not able to access the tables due to ownership.
Here is a workaround for it:
1. Go to Terminal – Application->Accessories->Terminal
2. Change the the ownership to mysql using this command
sudo chown mysql:mysql -R /var/lib/mysql/your_db_name
This command will change the owner (chown) of the db to mysql

Changing Permission for Files and Folders

While working on ubuntu, some files and folders might not be accessible due to permission settings.
For example it is not possible to add/delete files or folders to /var/lib/mysql folder. But, this is one of the folders that one might one to visit with changes – like if you want to move the data files..
There is a tool in this regard:
1. Go to Applications->Accessories->Terminal
2. on the CLI, write sudo chmod OGE /folder/or/file/path
The first stands for owner, second for group, and third everyone. Each of these are going to be filled with numbers ranging from 1-7.
The permissions primarily include read, write and execute.
the most common for controlled usage is
sudo chmode 777 /folder/or/file/path
which means Owner can read write and execute and the same for Group and everyone
For recursive permission allocation
sudo chmode 777 -R /folder/or/file/path
For more visit https://help.ubuntu.com/community/FilePermissions