Thursday, January 3, 2008

MySQL Stored Procedures: Part 1

What are Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS's raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here - if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.
A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn't been an option. That has been limiting. Some have claimed that there are two schools of thought - one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?

Why use stored procedures?

  • They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
  • They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.

A simple example

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let's set up a basic stored procedure first. This one will simply say 'Hello' in the Xhosa language - Molo.

mysql> CREATE PROCEDURE molo() SELECT 'Molo';
Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:

mysql> CALL molo()\G
*************************** 1. row ***************************
Molo: Molo
1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.

Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.
There are three types of parameter:

  • IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
  • OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
  • INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.

mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molweni
1 row in set (0.00 sec)

An IN example

Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.

mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL sp_in('Molo');
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molo
1 row in set (0.00 sec)

The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.

An OUT example

mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P='molo';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL sp_out(@x);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: molo
1 row in set (0.00 sec)

We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.

An INOUT example

mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL sp_inout(2);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: 4
1 row in set (0.00 sec)

Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.

Getting information about existing stored procedures

It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.

mysql> SHOW PROCEDURE STATUS\G
************* 1. row ************
           Db: test
         Name: molo
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-07-29 19:20:27
      Created: 2005-07-29 19:20:27
Security_type: DEFINER
      Comment:
************* 2. row ************
           Db: test
         Name: sp_in
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 11:58:34
      Created: 2005-08-02 11:58:34
Security_type: DEFINER
      Comment:
************* 3. row ************
           Db: test
         Name: sp_inout
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 12:16:18
      Created: 2005-08-02 12:16:18
Security_type: DEFINER
      Comment:
************* 4. row ************
           Db: test
         Name: sp_out
         Type: PROCEDURE
      Definer: ian@localhost
     Modified: 2005-08-02 12:01:56
      Created: 2005-08-02 12:01:56
Security_type: DEFINER
      Comment:
4 rows in set (0.00 sec)

It will become clear what all these fields mean as we progress through the rest of this tutorial series.

mysql> SHOW CREATE PROCEDURE molo\G
*************************** 1. row ***************************
       Procedure: molo
        sql_mode:
Create Procedure: CREATE PROCEDURE 'test'.'molo'()
SELECT 'Molo'
1 row in set (0.00 sec)

There is also an ANSI-standard way of doing it, which will be more familiar to other users.

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
     SPECIFIC_NAME: molo
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: molo
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SELECT 'Molo'
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-07-29 19:20:27
      LAST_ALTERED: 2005-07-29 19:20:27
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 2. row ***************************
     SPECIFIC_NAME: sp_in
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_in
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x = P
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 11:58:34
      LAST_ALTERED: 2005-08-02 11:58:34
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 3. row ***************************
     SPECIFIC_NAME: sp_inout
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_inout
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x=P*2
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 12:16:18
      LAST_ALTERED: 2005-08-02 12:16:18
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
*************************** 4. row ***************************
     SPECIFIC_NAME: sp_out
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: sp_out
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER:
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET P='molo'
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE:
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS_SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-08-02 12:01:56
      LAST_ALTERED: 2005-08-02 12:01:56
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: ian@localhost
4 rows in set (0.01 sec)

Let's introduce some more complex examples. First, we will create a sample table.

mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)
 

Delimiters, and multi-statement procedures

Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:

mysql> DELIMITER | 

Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))
    -> BEGIN
    ->  SET @x=CHAR_LENGTH(P);
    ->  SET @y = HEX(P);
    ->  INSERT INTO sp1(id,txt) VALUES(@x,@y);
    -> END|
Query OK, 0 rows affected (0.05 sec)
 
mysql> CALL sp_ins('ABC');
    -> |
Query OK, 1 row affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
1 row in set (0.00 sec)

Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.

Procedure variables

Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the '@' symbol, required for session variables. Here is an example.

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y INT DEFAULT 10;
    ->  SET x = P*y;
    ->  INSERT INTO sp1(id,txt) VALUES(x,HEX('DEF'));
    -> END|
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> CALL sp_declare(4);
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
*************************** 2. row ***************************
 id: 40
txt: 444546
2 rows in set (0.00 sec)

Variables that are declared without a default, such as x above, are set to NULL.

Populating variables from a pre-existing table

Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.

mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
    -> BEGIN
    ->  DECLARE x INT;
    ->  DECLARE y VARCHAR(10);
    ->  SELECT id,txt INTO x,y FROM sp1 LIMIT 1;
    ->  SELECT x,y;
    -> END|
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
mysql> CALL sp_select()\G
*************************** 1. row ***************************
x: 3
y: 414243
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

For now, we just returned the first record, to introduce the syntax. In next month's tutorial, we will look at iterations, which allow us to loop through multiple records.

MySQL Stored Procedures: Part 2

Characteristics

Last month we created skeleton stored procedures, to introduce the concepts. Let's start off this month by looking at all of the characteristics that you can define when creating a stored procedure. Here is a sample CREATE PROCEDURE statement making use of all of the clauses.

CREATE PROCEDURE sp_full() 
LANGUAGE SQL 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY DEFINER 
COMMENT 'Returns a random number' 
SELECT RAND()

LANGUAGE SQL simply means that the language used to write the procedure is SQL. MySQL cannot yet use procedures in any other languages, but it probably will in the future. It is, unsurprisingly, the default.
NOT DETERMINISTIC implies that the procedure may produce different results, given the same inputs. The alternative is DETERMINISTIC, if the procedure always gives the same results given the same input. The default is NOT DETERMINISTIC. Procedures that are NOT DETERMINISTIC have implications for binary logging and replication (if it performs updates, it means that slave data could be different to master data, and for this reason you can't define a NOT DETERMINISTIC procedure if binary logging is enabled). Besides binary logging, MySQL as yet makes no use of this information (potentially the optimizer could use it to improve performance).
MODIFIES SQL DATA indicates that data could be modified by the procedure. Alternatives are CONTAINS SQL (which would be SQL that does not read or write data), NO SQL or READS SQL DATA. The default is CONTAINS SQL.
SQL SECURITY DEFINER indicates that MySQL should check the privileges of the user that defined the procedure when executing the procedure. The alternative is SQL SECURITY INVOKER, which causes MySQL to use the privileges of the user calling the procedure. The default is SQL SECURITY DEFINER.
COMMENT is self-explanatory, and can be used to describe the procedure. It is a non-standard MySQL extension.

SQL statements unique to Stored Procedures

A procedure may need to use conditions or loops, and standard SQL is not sufficient for these purposes. For that reason, the SQL standard includes a number of statements that only occur inside stored procedures. We already looked at the DECLARE statement last month. Let's take a look now at conditions and loops. The concepts should all be familiar to anyone with programming experience.

Conditions: IF THEN ELSE

MySQL implements conditions in the stock standard, tried and tested way.

IF condition 
THEN statement/s 
ELSE statement/s
END IF

The logic is simple. If the condition is met, then a set of statements are carried out. Else, another set of statements are carried out.
Here is an example:

mysql> CREATE PROCEDURE sp_condition(IN var1 INT) 
BEGIN 
 IF (var1 > 10)  
  THEN SELECT 'greater'; 
  ELSE SELECT 'less than or equal'; 
 END IF; 
END|
Query OK, 0 rows affected (0.06 sec)
mysql> CALL sp_condition(5)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL sp_condition(15)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

I presume you can follow the logic easily enough. NULL values throw a curveball into the mix - an argument against their usage is that they undermine standard boolean logic. Nevertheless, you may come across instances where they are used. Let's see what happens if we pass a NULL to the procedure we have just created:

mysql> CALL sp_condition(NULL)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

As you probably expected, NULL > 10 evaluates to false, so the ELSE statement is run. Let's switch things around:

mysql> CREATE PROCEDURE sp_condition2(IN var1 INT) 
BEGIN 
 IF (var1 <= 10) 
  THEN SELECT 'less than or equal'; 
  ELSE SELECT 'greater';
 END IF; 
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_condition2(NULL)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Using what should in boolean logic be an identical test, we get a differing result. NULL <= 10 also evaluates to false. NULL's are beyond such dualistic thinking. This should be enough of a warning to be cautious of NULL's!

Conditions: CASE

The other construct used for condition is CASE.

CASE variable 
WHEN condition1 statement/s 
WHEN condition2 statement/s
ELSE statement/s
END CASE

This construct is used when the same variable is being tested against multiple conditions. Instead of long nested IF statements, using a CASE statement allows the procedure code to be more compact and easily readable. Here is an example:

mysql> CREATE PROCEDURE sp_case(IN var1 INT) 
BEGIN 
 CASE var1 
  WHEN 1 THEN SELECT 'One'; 
  WHEN 2 THEN SELECT 'Two'; 
  ELSE SELECT 'Something else'; 
 END CASE; 
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(1)\G
*************************** 1. row ***************************
One: One
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(2)\G
*************************** 1. row ***************************
Two: Two
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(3)\G
*************************** 1. row ***************************
Something else: Something else
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Loops: WHILE

Loops are a vital component of procedures - they allow the same portion of code to be repeated a number of time. WHILE loops, the first type of loop we are going to look at, continuously repeat a block while a particular condition is true.

WHILE condition DO
statement/s
END WHILE

Here is an example. Be careful when entering it though, and with all loops! Always create your procedures on a test server first. All novice (and some not so novice) programmers at one time or another create an infinite loop. If you had forgotten to increment var1 in the procedure, it would always stay as whatever you had passed to the procedure. If this were anything less than 20, the loop would continue indefinitely.

mysql> CREATE PROCEDURE sp_while(IN var1 INT)
BEGIN 
 WHILE (var1 < 20) DO 
  SELECT var1; 
  SET var1=var1+1; 
 END WHILE;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while(18)\G
*************************** 1. row ***************************
var1: 18
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 19
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while(22)\G
Query OK, 0 rows affected (0.00 sec)

Note that when we called the procedure, passing 22, no statements were executed, as the condition failed immediately.

REPEAT UNTIL

The other commonly used loop is the REPEAT UNTIL construct.

REPEAT 
statement/s
UNTIL condition
END REPEAT

The statements are repeatedly performed until the condition is met. A difference to the WHILE loop is that the condition is only tested after the statements have been performed, so there is always at least one instance of the statements being run. Here is an example:

mysql> CREATE PROCEDURE sp_repeat(IN VAR1 INT)
BEGIN 
 REPEAT 
  SELECT var1; 
  SET var1=var1+1; 
  UNTIL var1>5 
 END REPEAT; 
END|
Query OK, 0 rows affected (0.09 sec)
mysql> CALL sp_repeat(3)\G
*************************** 1. row ***************************
var1: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 5
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_repeat(8)\G
*************************** 1. row ***************************
var1: 8
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Note, that even when we call the procedure with 8, which means that the condition evaluates to true (and exits the loop) the first time it is encountered, the SELECT statement is still executed once, as the condition is only tested at the end.

LABELS, LEAVES and LOOPS

Labels are simply text strings that are used to mark part of the procedure. They can simply play the role of comments, or be part of the logic, as we will see below in the LOOP construct. Here is an example of LABELS used to comment the start of a procedure. The label is begin1. This may not seem useful here, but complex statements can be deeply nested, and in that case, clear labels will make the logic easier to follow. They also play a vital logical role, as we will see later.

mysql> CREATE PROCEDURE sp_label() 
 begin1: BEGIN 
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_label2() 
 begin1: BEGIN 
 END begin1|
Query OK, 0 rows affected (0.00 sec)

In the second example, the end is also labeled. If an end label exists, it must match a start label of the same name. You can use LABELS before BEGIN, WHILE, REPEAT and LOOP (introduced below) statements, as well as the accompanying END statements, and also as targets for ITERATE statements (also introduced below).
A third kind of loop is the LOOP construct. This one does not test against a condition at either the start or the end of the loop. It continues looping until explicitly exited with a LEAVE statement, making it easy to get stuck in an infinite loop (as I did when creating this example). The LEAVE statement exits a block (which can include the procedure itself). Since there can be many nested constructs, it must also be accompanied by a label name to determine which block to leave.

label LOOP
 statement/s
 LEAVE label
 statement/s
END LOOP

Here is an example:

mysql> CREATE PROCEDURE sp_loop(IN var1 INT)
BEGIN
 loop1: LOOP
  IF (var1 > 5) THEN
   LEAVE loop1;
  END IF;
  SET var1=var1+1;
  SELECT var1;
 END LOOP;
END |
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_loop(2)\G
*************************** 1. row ***************************
var1: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 5
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 6
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

The LABEL name we used above is loop1, and the LEAVE statement explicitly left loop1. We did not make use of an end label.

ITERATE statements

Iterates can only appear within LOOP, REPEAT, and WHILE statements. They are followed by a LABEL name, and effectively direct control back to that LABEL. So, if they appear half-way through a loop, and they direct control back to the top of that loop, the rest of the loop will not be carried out (bear this logic in mind when testing, as it again opens the possibilities for infinite loops).
In this next example, we use a WHILE loop, and ITERATE back to the beginning of the loop if var1 is still less than 3.

mysql> CREATE PROCEDURE sp_while_iterate(IN var1 INT)
    -> BEGIN
    ->  while_label: WHILE (var1 < 5) DO
    ->   SELECT CONCAT('starting the loop: var1 is: ',var1);
    ->   SET var1=var1+1;
    ->   IF (var1<3) THEN
    ->    ITERATE while_label;
    ->   END IF;
    ->   SELECT CONCAT('ending the loop: var1 is: ',var1);
    ->  END WHILE;
    -> END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while_iterate(1)\G
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 1
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 2
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 5
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

The first time through the loop, only starting the loop is displayed, as var1 is at 2 when the time comes to evaluate the IF condition, and the condition evaluates to true. The ITERATE sends control back to the start of the loop again. The second time through, var1 starts at 2, but is then incremented to 3, and the IF condition is false. The full loop is executed.

MySQL Stored Procedures: Part 3

Handlers and error handling

With stored procedures allowing the DBMS to grapple with concepts that beforehand were only dealt with in the murkier programming world, there is a clear need for a more elegant way of handling errors and exceptions. Enter the handler. There are two types of handler supported by MySQL - EXIT handlers that immediately exit the current BEGIN/END block, and CONTINUE handlers that allow processing to continue after the handler actions have been performed (the UNDO handler that may be familiar to users of other DBMS' is not yet supported). Below is an example. Remember that we are still using the | character as a delimiter, as outlined in part 1 of the series.

mysql>
CREATE procedure sp3()
 BEGIN
  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
  DECLARE EXIT HANDLER FOR 'unknown column'
   SELECT 'error error whoop whoop';
  SELECT aha;
  SELECT 'continuing';
 END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp3()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

So, what happened here? We declared a condition, called 'unknown column'. It is a condition that occurs when SQLSTATE 42S22 is reached, which is when there is an unknown column. You can find a full list of error codes and messages on the MySQL site. Next, we declare an exit handler for the 'unknown column' condition, declared above. The handler simply displays the message error error whoop whoop. The actual body of the procedure consists of two statements, SELECT aha, which is designed to trigger SQLSTATE 42S22, and SELECT 'continuing', which is never actually executed as, being an exit handler, the procedure is immediately exited when the condition is met. So, when we call sp3(), the SELECT statement triggers the condition, and the message is displayed. Let's change this to use a CONTINUE handler, and see the difference.

mysql>
CREATE procedure sp4()
 BEGIN
  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
  DECLARE CONTINUE HANDLER FOR 'unknown column'
   SELECT 'error error whoop whoop';
  SELECT aha;
  SELECT 'continuing';
 END;
 
mysql> CALL sp4()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.06 sec)

As expected, the procedure continues executing after the error, and this time the SELECT 'continuing' statement is run.
Here is another procedure. What do you think it will do? If we want to display the error error and still handling messages as part of the handler, after reaching the aha statement, and then continue with the continuing statement, will this achieve that?

CREATE procedure sp5()
 BEGIN
  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
  DECLARE CONTINUE HANDLER FOR 'unknown column'
   SELECT 'error error whoop whoop';
   SELECT 'still handling the error';
  SELECT aha;
  SELECT 'continuing';
 END;
 
mysql> CALL sp5()\G
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

The answer is clearly no. I hope that you were eagle-eyed enough to spot the misleading indentation. The SELECT 'still handling the error'; is actually part of the main procedure body, and not part of the error handler. Since we have no BEGIN or END statements as part of the handler, it consists of the one statement only. Here is what will achieve what we actually intended.

mysql>
CREATE procedure sp6()
 BEGIN
  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
  DECLARE CONTINUE HANDLER FOR 'unknown column'
   BEGIN
    SELECT 'error error whoop whoop';
    SELECT 'still handling the error';
   END;
  SELECT aha;
  SELECT 'continuing';
 END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp6()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)

Just to round off our examples, here is an example of a procedure where the error handler is not called, and none of the handler statements are executed.

mysql>
CREATE procedure sp7()
 BEGIN
  DECLARE 'unknown column' CONDITION FOR SQLSTATE '42S22';
  DECLARE CONTINUE HANDLER FOR 'unknown column'
   BEGIN
    SELECT 'error error whoop whoop';
    SELECT 'still handling the error';
   END;
  SELECT 'continuing';
 END;|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp7()\G
*************************** 1. row ***************************
continuing: continuing
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Variations and uses

For the examples above, we declared a condition to detect an SQLSTATE error. There are other ways. Firstly, handlers can be declared for specific error codes directly - you don't always need to go via an intermediate condition, although doing so is more useful in that the condition name can (and should) be descriptive, so there is no need to refer to a list of error codes at a later stage. The error code can also either be the SQLSTATE error number, as above, or the MySQL error code, as well as one of the more generic SQLWARNING, for all errors with an SQLSTATE beginning with 01, NOT FOUND for all errors with an SQLSTATE beginning with 02, or SQLEXCEPTION for all others. Below is a procedure that acts in a similar manner to our earlier examples. This time we use the MySQL Error code 1054, which is almost equivalent to SQLSTATE 42S22, and we also skip the condition:

mysql> 
CREATE procedure sp8()
 BEGIN
  DECLARE EXIT HANDLER FOR 1054
   BEGIN
    SELECT 'error error whoop whoop';
    SELECT 'still handling the error';
   END;
  SELECT aha;
 END;|
 
mysql> CALL sp8()\G
*************************** 1. row ***************************
error error whoop whoop: error error whoop whoop
1 row in set (0.00 sec)
*************************** 1. row ***************************
still handling the error: still handling the error
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

That's enough examples for now - hopefully you are starting to consider some practical uses for this. The handlers could ROLLBACK statements, or log to an error table. Moreover, the statements could be as complex as required, incorporating all the loops and conditions we looked at in the previous article.

Cursors

Cursors are another of the frequently demanded features now supported by MySQL 5. For those familiar with other DBMS implementations, MySQL 5 does not yet support them completely. In fact, it has some quite severe restrictions. MySQL 5's cursors are asensitive (so you should not update the table while using a cursor, otherwise you will get unpredictable results), read-only (you cannot update using the cursor position) and non-scrolling (you can only move forward to the next record, not back and forth).

In general, cursors are used to access a resultset that can be retrieved one or more rows at a time. They are also used for positioning a pointer at a specific row, and can allow updates to rows based upon this current position; although as mentioned, MySQL does not support this. The term cursor is short for CUrrent Set Of Records. In our examples, we will use the same table, sp1, we used in part 1 of this series. When we left off, the table contained the following records:

mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
 id: 3
txt: 414243
*************************** 2. row ***************************
 id: 40
txt: 444546
2 rows in set (0.00 sec)

Here is a sample procedure containing a cursor.

mysql>
CREATE PROCEDURE sp9 (OUT rx INT, OUT ry INT)
 BEGIN
  DECLARE x,y INT;
  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
  OPEN sp1_cursor;
   FETCH sp1_cursor INTO x,y;
  CLOSE sp1_cursor;
  SET rx = x;
  SET ry = y;
 END |
 
mysql> CALL sp9(@x,@y)\G
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 3
@y: 414243
1 row in set (0.01 sec)

Here we declared variables (x and y and a cursor (sp1_cursor). The order of declarations is important - they must be in a particular order - variables and conditions, then cursors, then handlers.
Next is a new statement - OPEN sp1_cursor, which activates the cursor we declared earlier. The FETCH statement is the one that does all the magic, returning the next row of the actual resultset. Results must be placed somewhere, and the two variables x and y are the recipients of the two columns returned by the SELECT id,txt FROM sp1 query that makes up the cursor. The cursor is then closed (although MySQL will free the resources when it reaches the end of the compound statement block for you if you do not explicitly do so), and the results assigned to the two OUT variables. By calling the procedure, and then querying the two session variables that receive the results, we can see that they have been populated with the first row from the sp1 table, as expected.
However, returning just one row is not particularly useful. We need to loop through the entire resultset and return the results. We can achieve this using a simple REPEAT UNTIL loop, which we were introduced to in part 2.

mysql>
CREATE PROCEDURE sp10 (OUT rx INT, OUT ry INT)
 BEGIN
  DECLARE x,y,z INT;
  DECLARE sp1_cursor CURSOR FOR SELECT id,txt FROM sp1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;
  OPEN sp1_cursor;
  REPEAT
   FETCH sp1_cursor INTO x,y;
   UNTIL (z=1)
  END REPEAT;
  CLOSE sp1_cursor;
  SET rx = x;
  SET ry = y;
 END |
 
mysql> CALL sp10(@x,@y)|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x,@y\G
*************************** 1. row ***************************
@x: 40
@y: 444546
1 row in set (0.00 sec)

The handler is declared for the NOT FOUND condition. The handler sets the variable z to 1, and z=1 is the condition the REPEAT UNTIL loop tests for. As mentioned earlier, the NOT FOUND condition includes all errors with an SQLSTATE beginning with 02, one of which is the No data to FETCH error. This will apply when we have retrieved all the rows in the table. As expected, upon calling the procedure and querying the session variables, this time we see the last row of the sp1table, as in the procedure each row's data is assigned to rx and ry, overwriting the previous contents.

 

MySQL Stored Functions

What's a Stored Function

If procedural programming is new to you, you may be wondering what the difference is between a Stored Procedure and a Stored Function. Not too much really. A function always returns a result, and can be called inside an SQL statement just like ordinary SQL functions. A function parameter is the equivalent of the IN procedure parameter, as functions use the RETURN keyword to determine what is passed back. Stored functions also have slightly more limitations in what SQL statements they can run than stored procedures.

A Stored Function example

Here is an example of a stored function:

mysql> DELIMITER |
mysql>
 CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT)
  RETURNS INT
   DETERMINISTIC
    BEGIN
     DECLARE avg INT;
     SET avg = (n1+n2+n3*2+n4*4)/8;
     RETURN avg;
    END|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT WEIGHTED_AVERAGE(70,65,65,60)\G
*************************** 1. row ***************************
WEIGHTED_AVERAGE(70,65,65,60): 63
1 row in set (0.00 sec)

As mentioned in the first stored procedures tutorial, we declare the "|" symbol as a delimiter, so that our function body can use ordinary ";" characters. This function returns a weighted average, as could be used to determine an overall result for a subject. The third test score is weighted twice as heavily as the first and second scores, while the fourth score counts four times as much. We also make use of the DECLARE (declaring a variable) and DETERMINISTIC (telling MySQL that, given the same input, the function will always return the same result) statements, as discussed in earlier tutorials.

Accessing tables in stored functions

Stored functions in early versions of MySQL 5.0 (< 5.0.10) could not reference tables except in a very limited capacity. That limited their usefulness to a large degree. Newer versions can now do so, but still cannot make use of statements that return a result set. So, no SELECT queries returning result sets from a table. However, you can get around this by using SELECT INTO. For the next example, we create a table allowing us to store 4 marks, and a name. Then we will define a new WEIGHTED_AVERAGE function to make use of the dynamic data from the table.

mysql> CREATE TABLE sfdata(mark1 INT,mark2 INT,mark3 INT,mark4 INT,name VARCHAR(50))
mysql> INSERT INTO sfdata VALUES(70,65,65,60,'Mark')|
mysql> INSERT INTO sfdata VALUES(95,94,75,50,'Pavlov')|
mysql>
 CREATE FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50))
  RETURNS INT
  DETERMINISTIC
   BEGIN
    DECLARE i1,i2,i3,i4,avg INT;
    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sfdata WHERE name=v1;
    SET avg = (i1+i2+i3*2+i4*4)/8; 
    RETURN avg;
   END|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT WEIGHTED_AVERAGE2('Pavlov') AS Pavlov, WEIGHTED_AVERAGE2('Mark') AS Mark\G
*************************** 1. row ***************************
Pavlov: 67
  Mark: 63
1 row in set (0.00 sec)

By SELECTING the contents of the mark1 to mark4 rows INTO the variables we have just declared, there is no need to return a result set, and we can happily use the results inside of the function.
All the usual behaviors and conditions apply inside the function. Here is what happens if one of the records is missing a field.

mysql> INSERT INTO sfdata VALUES(90,NULL,70,60,'Isabelle')|
Query OK, 1 row affected (0.18 sec)
mysql> SELECT WEIGHTED_AVERAGE2('Isabelle') AS Isabelle\G
*************************** 1. row ***************************
Isabelle: NULL
1 row in set (0.16 sec)

As expected, the NULL (and NULLs are always a bad idea to use) contaminates the entire result, and MySQL, not knowing what else to do, can do nothing other than return a NULL.
Here is the full syntax for stored functions:

CREATE FUNCTION sf_name ([parameter1 [...]])
    RETURNS type
    [
     LANGUAGE SQL
     | [NOT] DETERMINISTIC
     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
     | SQL SECURITY { DEFINER | INVOKER }
     | COMMENT 'string'
    ] 
    SQL statements

Manipulating tables

With the early restrictions on accessing tables inside a function lifted, you can use a function to make changes to a table as well. The next two examples are not ideal use of functions (in their current format they would more ideally be stored procedures), as we are not interested in the result being returned, and only want to manipulate the data, but they show you some of the potential power of functions. A function is best used when you want to return a result. Building upon these examples, you can create your own where complex INSERTs, SELECTs and UPDATEs are performed, with a single result being returned at the end of it all. First, we INSERT a record into the sfdata table.

mysql>
 CREATE FUNCTION WEIGHTED_AVERAGE3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))
  RETURNS INT
  DETERMINISTIC
   BEGIN
    DECLARE i1,i2,i3,i4,avg INT;
    INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
    RETURN 1;
   END|
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT WEIGHTED_AVERAGE3(50,60,60,50,'Thoko')\G
*************************** 1. row ***************************
WEIGHTED_AVERAGE3(50,60,60,50,'Thoko'): 1
1 row in set (0.00 sec)
mysql> SELECT * FROM sfdata\G
*************************** 1. row ***************************
mark1: 70
mark2: 65
mark3: 65
mark4: 60
 name: Mark
*************************** 2. row ***************************
mark1: 95
mark2: 94
mark3: 75
mark4: 50
 name: Pavlov
*************************** 3. row ***************************
mark1: 90
mark2: NULL
mark3: 70
mark4: 60
 name: Isabelle
*************************** 4. row ***************************
mark1: 50
mark2: 60
mark3: 60
mark4: 50
 name: Thoko
4 rows in set (0.01 sec)

Similarly, the next example UPDATEs a record based upon the parameters passed to it:

mysql>  
 CREATE FUNCTION WEIGHTED_AVERAGE_UPDATE (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))
  RETURNS INT
  DETERMINISTIC
   BEGIN
    DECLARE i1,i2,i3,i4,avg INT;
    UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;
    RETURN 1;
   END|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko')\G
*************************** 1. row ***************************
WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko'): 1
1 row in set (0.00 sec)
mysql> SELECT * FROM sfdata\G
*************************** 1. row ***************************
mark1: 70
mark2: 65
mark3: 65
mark4: 60
 name: Mark
*************************** 2. row ***************************
mark1: 95
mark2: 94
mark3: 75
mark4: 50
 name: Pavlov
*************************** 3. row ***************************
mark1: 90
mark2: NULL
mark3: 70
mark4: 60
 name: Isabelle
*************************** 4. row ***************************
mark1: 60
mark2: 60
mark3: 60
mark4: 50
 name: Thoko
5 rows in set (0.01 sec)

Information about existing stored functions

As with stored procedures, there are various ways to get to the metadata about existing stored functions. There is SHOW CREATE FUNCTION, and SHOW FUNCTION STATUS. The former returns the CREATE statement applied to the supplied function, while the latter returns metadata about all existing functions, as follows.

mysql> SHOW CREATE FUNCTION WEIGHTED_AVERAGE\G
*************************** 1. row ***************************
       Function: WEIGHTED_AVERAGE
       sql_mode:
Create Function: CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT) 
RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
1 row in set (0.01 sec)
mysql> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 13:21:37
      Created: 2005-12-07 13:21:37
Security_type: DEFINER
      Comment:
*************************** 2. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE2
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 13:41:07
      Created: 2005-12-07 13:41:07
Security_type: DEFINER
      Comment:
*************************** 3. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE3
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 15:51:16
      Created: 2005-12-07 15:51:16
Security_type: DEFINER
      Comment:
*************************** 4. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE_UPDATE
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 16:03:26
      Created: 2005-12-07 16:03:26
Security_type: DEFINER
      Comment:
4 rows in set (0.00 sec)

Another way to get the same information is to query the mysql.proc table. As you may know, the mysql database stores all sorts of data about permissions, and you can UPDATE the user or db table to change the MySQL privileges. Since MySQL 5, the mysql.proc table also contains metadata about stored procedures and functions.

mysql> SELECT * FROM mysql.proc\G
*************************** 1. row ***************************
              db: test
            name: WEIGHTED_AVERAGE
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT, n2 INT, n3 INT, n4 INT
         returns: int(11)
            body: BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
         definer: root@localhost
         created: 2005-12-07 13:21:37
        modified: 2005-12-07 13:21:37
        sql_mode:
         comment:
*************************** 2. row ***************************
              db: test
            name: WEIGHTED_AVERAGE2
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE2
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: v1 VARCHAR(50)
         returns: int(11)
            body: BEGIN     
                    DECLARE i1,i2,i3,i4,avg INT;    
                    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    
                    SET avg = (i1+i2+i3*2+i4*4)/8;    
                    RETURN avg;   
                  END
         definer: root@localhost
         created: 2005-12-07 13:41:07
        modified: 2005-12-07 13:41:07
        sql_mode:
         comment:
*************************** 3. row ***************************
              db: test
            name: WEIGHTED_AVERAGE3
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE3
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)
         returns: int(11)
            body: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
   RETURN 1;
  END
         definer: root@localhost
         created: 2005-12-07 15:51:16
        modified: 2005-12-07 15:51:16
        sql_mode:
         comment:
*************************** 4. row ***************************
              db: test
            name: WEIGHTED_AVERAGE_UPDATE
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE_UPDATE
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)
         returns: int(11)
            body: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;
   RETURN 1;
  END
         definer: root@localhost
         created: 2005-12-07 16:03:26
        modified: 2005-12-07 16:03:26
        sql_mode:
         comment:
4 rows in set (0.00 sec)

Note that querying the mysql.proc table returns more complete data than either of the first two methods, effectively returning the sum of both of those methods.
However, people coming from other DBMS', familiar with the ANSI standard, may be uncomfortable with these MySQL-specific methods. The standard way is to query the INFORMATION_SCHEMA. It is a highly flexible way of getting what you want, but can be a bit of an overkill, hence MySQL's provision of the more simple SHOW methods. I will leave a more complete explanation of INFORMATION_SCHEMA for another day, as it extends well beyond stored procedures and functions. For now, suffice to say that you can query INFORMATION_SCHEMA.ROUTINES to get similar metadata as the above, as follows:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 13:21:37
      LAST_ALTERED: 2005-12-07 13:21:37
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 2. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE2
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE2
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
                      DECLARE i1,i2,i3,i4,avg INT;    
                      SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    
                      SET avg = (i1+i2+i3*2+i4*4)/8;    
                      RETURN avg;   
                    END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 13:41:07
      LAST_ALTERED: 2005-12-07 13:41:07
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 3. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE3
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE3
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
   RETURN 1;
  END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 15:51:16
      LAST_ALTERED: 2005-12-07 15:51:16
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 4. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE_UPDATE
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE_UPDATE
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1;
   RETURN 1;
  END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 16:03:26
      LAST_ALTERED: 2005-12-07 16:03:26
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
4 rows in set (0.00 sec)

The MySQL documentation gives a complete overview about the INFORMATION_SCHEMA structure, and some MySQL oddities, if you want to pursue that further.

Conclusion

Stored procedures and stored functions open a whole new world to MySQL developers, and mean that MySQL is starting to attract attention from developers of entirely new types of applications. While the implementation in MySQL 5.0 is still raw (MySQL 5.1 is out in alpha now, and develops things further, as will MySQL 6.0), most of what is needed is already there. Not all of the my applications are running on MySQL 5 yet, but the itch to move everything is starting to get stronger every time I do further development, and have to make do with more unwanted logic in the application. I came across a query recently from one poor developer bemoaning the lack of features in MySQL 3.23. Upgrading legacy systems is not fun, but for those of you lucky enough to start with a clean slate, enjoy the new world of MySQL 5, stored procedures and stored functions!

Wednesday, January 2, 2008

Introduction to SOAP

SOAP is a simple XML based protocol to let applications exchange information over HTTP.
Or more simply: SOAP is a protocol for accessing a Web Service.

What You Should Already Know

Before you study SOAP you should have a basic understanding of XML and XML Namespaces.

If you want to study these subjects first, please read our XML Tutorial.

What is SOAP?

  • SOAP stands for Simple Object Access Protocol
  • SOAP is a communication protocol
  • SOAP is for communication between applications
  • SOAP is a format for sending messages
  • SOAP is designed to communicate via Internet
  • SOAP is platform independent
  • SOAP is language independent
  • SOAP is based on XML
  • SOAP is simple and extensible
  • SOAP allows you to get around firewalls
  • SOAP will be developed as a W3C standard

Why SOAP?

It is important for application development to allow Internet communication between programs.
Today's applications communicate using Remote Procedure Calls (RPC) between objects like DCOM and CORBA, but HTTP was not designed for this. RPC represents a compatibility and security problem; firewalls and proxy servers will normally block this kind of traffic.
A better way to communicate between applications is over HTTP, because HTTP is supported by all Internet browsers and servers. SOAP was created to accomplish this.
SOAP provides a way to communicate between applications running on different operating systems, with different technologies and programming languages.

Microsoft and SOAP

SOAP is a key element of Microsoft's .NET architecture for future Internet application development.

SOAP 1.1 was Proposed to W3C

UserLand, Ariba, Commerce One, Compaq, Developmentor, HP, IBM, IONA, Lotus, Microsoft, and SAP proposed to W3C, in May 2000, the SOAP Internet protocol that they hope will revolutionize application development by connecting graphic user interface desktop applications to powerful Internet servers using the standards of the Internet: HTTP and XML.

W3C is Working on SOAP 1.2

The first public Working Draft on SOAP was published from W3C in December 2001. To read more about the SOAP activities at W3C please visit our W3C tutorial.

File downloads in PHP

This is a simple code snippet. It will allow you to force the web-browser that is currently viewing your script to come up with a file-download box (and the a file on your system can be downloaded).

Here is the code:

//so only the data from the headers is sent
ob_start();
//dispable caching
header (”Cache-Control: must-revalidate, pre-check=0, post-check=0″);
header (”Content-Type: application/binary”);
header (”Content-Length: ” . filesize($export_long_name));
header (”Content-Disposition: attachment; filename=yourfile.ext”);
readfile($export_long_name);

A list of of mime types can be found Here

Apache mod_rewrite tips and tricks

What is mod_rewrite?

Mod_rewrite is a rewriting engine (based on regular-expressions) built into the apache webserver and it is used to rewrite urls dynamically. The URL manipulations can depend on various tests, of server variables, environment variables, HTTP headers, or time stamps. Even external database lookups in various formats can be used to achieve highly granular URL matching.

How to install it


Apache by default comes with the mod_rewrite module installed but it is not enabled. So if you have Apache installed on your own server, you will need to enable it.

If you need to install apache on your system, there are many free, easy install packages available:

Xamp
- http://www.apachefriends.org/en/xampp.html
apache2triad - http://apache2triad.net/
apachePHPMysql - http://apachephpmysql.narhoz.ru/
EasyWebServer - http://e.w.s.free.fr/index_fr.php
FoxServ - http://sourceforge.net/projects/foxserv/

Setting it up


Once installed, mod_rewrite basically relies on one file for all it’s functionality. It’s called .htaccess. This file should be placed in the root directory of your website.

A simple Redirect


Place the following in a .htaccess file:

RewriteEngine
on
RewriteRule ^test\.html$ test2.html

RewriteEngine on should always be placed at the beginning of all your .htaccess files.

Note
: If you are using a hosting provider, you may have to place the following line in your file (under rewrite_engine on): RewriteBase /
Script details:

  • ^ is used before a URL. If a relative URL is used, it starts in the same directory as the .htaccess file
  • $ is used for the end of a string that will be matched.
  • \ is used to escape the period, periods need the \ before them if they are not going to be part of the actual rule (in this case, it is part of the filename).

This script will redirect all access from test.html to test2.html. IE: if a user goes to http://www.yoursite.com/test.html, they will be automatically forwarded to http://www.yoursite.com/test2.html

Other interesting uses

A) Blocking a specific Ip addressing from accessing your website.

RewriteCond %{REMOTE_ADDR} ^(W\.X\.Y\.Z)$
RewriteRule ^/* http://www.yoursite.com/sorry.htm [L]

Replace w.x.y.z with the IP you would like to block and http://www.yoursite.com/sorry.htm with the redirected URL.

B) Block/redirect a site that is linking to you

RewriteCond %{HTTP_REFERER} ^http://www\.blockedsite\.com [NC]
RewriteRule ^/* http://www.yoursite.com/sorry.htm [L]

Replace http://www.blockedsite.com/ with site you do not want linking to you, and http://www.yoursite.com/sorry.htm with the redirected URL.

C) preventing people from linking to your images

RewriteCond %{HTTP_REFERER} !^http://(www\.)?yoursite\.com [NC]
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} ^http://.*$
RewriteRule \.(png gif bmp jpe?g)$ /images/stopstealing.png [L]

Replace http://www.blockedsite.com/ your site, and /images/stopstealing.png with an image path of choice.

Full Apache Docs: http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html

Top 5 free content management systems

1) CMS made simple

Features

  • Easy User and group management
  • Group-based permission system
  • Admin panel with multiple language support
  • RSS module
  • Module API for easy extendability

Front-end Demo here
Admin Demo here
username: admin
password: demo

2) DragonFly CMS

Features

  • Built-in photo gallery
  • BBcode support
  • MMCache and eAccelerator support
  • Site-wide cache-based template system
  • Debugging system and page query list

Front page demo here
Admin Demo here
Username: admin
Password: Demo123 (Case Sensitive)

3) Bitweaver

Features

  • SEO friendly urls
  • Content spam protection (automatic no-follows)
  • Many formats supported (TikiWiki, HTML, BBCode, Wikipedia (aka MediaWik))
  • Hotwords - allow you to specify particular words that can be associated with a URL
  • Generate PDF documents from all Content

Front-page demo here
Admin demo here
Username: admin
Password: demo

4) Drupal

Features

  • SEO Friendly URLs
  • Many community modules
  • Role based permission system
  • External authentication source support with Jabber, Blogger, and LiveJournal
  • Blogger API support

Front-end demo here
Admin demo here
Username: admin
Password: demo

5) Joomla

This is one of the most mature, free (did I mention open source), content management systems available. Also a fork of a CMS called Mambo. How are they different?

Joomla version 1.0 is derived from Mambo 4.5.2.3 but includes many additional bug fixes and security patches. Joomla version 1.5 is an extensive refactoring of the API as is Mambo version 4.6 to its codebase. Both applications continue maintain a similar user inferface (look and feel), similar default component and module sets. Both Joomla 1.5 and Mambo 4.6 will include internationisation support. Joomla will use an easy-to-use ‘ini’ format for their translation files while Mambo uses the ‘gettext’ format. Joomla 1.5 will correctly support the UTF-8 character set. Joomla 1.5 also includes many new features such as additional authentication models (LDAP, Gmail, etc), xml-rpc client-server support. It also natively supports database drivers for MySQL 4.1+ (on PHP 5) and has improved support for MySQL 5 as well as the groundings to support other database engines.

Demo here

Tuesday, January 1, 2008

Country wise Currency Codes

CountryCapitalCurrency NameCode
AfghanistanKabulAfghanistan AfghaniAFA
AlbaniaTiranAlbanian LekALL
AlgeriaAlgiersAlgerian DinarDZD
American SamoaPago PagoUS DollarUSD
AndorraAndorraEuroEUR
AngolaLuandaAngolan New KwanzaAON
AnguillaThe ValleyEast Caribbean DollarXCD
AntarcticaNoneEast Caribbean DollarXCD
Antigua and BarbudaSt. JohnsEast Caribbean DollarXCD
ArgentinaBuenos AiresArgentine PesoARS
ArmeniaYerevanArmenian DramAMD
ArubaOranjestadAruban GuilderAWG
AustraliaCanberraAustralian DollarAUD
AustriaViennaEuroEUR
AzerbaijanBakuAzerbaijanian ManatAZM
BahamasNassauBahamian DollarBSD
BahrainAl-ManamahBahraini DinarBHD
BangladeshDhakaBangladeshi TakaBDT
BarbadosBridgetownBarbados DollarBBD
BelarusMinskBelarussian RubleBYB
BelgiumBrusselsEuroEUR
BelizeBelmopanBelize DollarBZD
BeninPorto-NovoCFA Franc BCEAOXOF
BermudaHamiltonBermudian DollarBMD
BhutanThimphuBhutan NgultrumBTN
BoliviaLa PazBolivianoBOB
Bosnia-HerzegovinaSarajevoMarkaBAM
BotswanaGaboroneBotswana PulaBWP
Bouvet IslandNoneNorwegian KroneNOK
BrazilBrasiliaBrazilian RealBRL
British Indian Ocean TerritoryNoneUS DollarUSD
Brunei DarussalamBandar Seri BegawanBrunei DollarBND
BulgariaSofiaBulgarian LevBGL
Burkina FasoOuagadougouCFA Franc BCEAOXOF
BurundiBujumburaBurundi FrancBIF
CambodiaPhnom PenhKampuchean RielKHR
CameroonYaoundeCFA Franc BEACXAF
CanadaOttawaCanadian DollarCAD
Cape VerdePraiaCape Verde EscudoCVE
Cayman IslandsGeorgetownCayman Islands DollarKYD
Central African RepublicBanguiCFA Franc BEACXAF
ChadN'DjamenaCFA Franc BEACXAF
ChileSantiagoChilean PesoCLP
ChinaBeijingYuan RenminbiCNY
Christmas IslandThe SettlementAustralian DollarAUD
Cocos (Keeling) IslandsWest IslandAustralian DollarAUD
ColombiaBogotaColombian PesoCOP
ComorosMoroniComoros FrancKMF
CongoBrazzavilleCFA Franc BEACXAF
Congo, Dem. RepublicKinshasaFrancsCDF
Cook IslandsAvaruaNew Zealand DollarNZD
Costa RicaSan JoseCosta Rican ColonCRC
CroatiaZagrebCroatian KunaHRK
CubaHavanaCuban PesoCUP
CyprusNicosiaCyprus PoundCYP
Czech Rep.PragueCzech KorunaCZK
DenmarkCopenhagenDanish KroneDKK
DjiboutiDjiboutiDjibouti FrancDJF
DominicaRoseauEast Caribbean DollarXCD
Dominican RepublicSanto DomingoDominican PesoDOP
EcuadorQuitoEcuador SucreECS
EgyptCairoEgyptian PoundEGP
El SalvadorSan SalvadorEl Salvador ColonSVC
Equatorial GuineaMalaboCFA Franc BEACXAF
EritreaAsmaraEritrean NakfaERN
EstoniaTallinnEstonian KroonEEK
EthiopiaAddis AbabaEthiopian BirrETB
European UnionBrusselsEuroEUR
Falkland IslandsStanleyFalkland Islands PoundFKP
Faroe IslandsTorshavnDanish KroneDKK
FijiSuvaFiji DollarFJD
FinlandHelsinkiEuroEUR
FranceParisEuroEUR
French GuianaCayenneEuroEUR
French Southern TerritoriesNoneEuroEUR
GabonLibrevilleCFA Franc BEACXAF
GambiaBanjulGambian DalasiGMD
GeorgiaTbilisiGeorgian LariGEL
GermanyBerlinEuroEUR
GhanaAccraGhanaian CediGHC
GibraltarGibraltarGibraltar PoundGIP
Great BritainLondonPound SterlingGBP
GreeceAthensEuroEUR
GreenlandGodthabDanish KroneDKK
GrenadaSt. George'sEast Carribean DollarXCD
Guadeloupe (French)Basse-TerreEuroEUR
Guam (USA)AganaUS DollarUSD
GuatemalaGuatemala CityGuatemalan QuetzalQTQ
GuernseySt. Peter PortPound SterlingGBP
GuineaConakryGuinea FrancGNF
Guinea BissauBissauGuinea-Bissau PesoGWP
GuyanaGeorgetownGuyana DollarGYD
HaitiPort-au-PrinceHaitian GourdeHTG
Heard Island and McDonald IslandsNoneAustralian DollarAUD
HondurasTegucigalpaHonduran LempiraHNL
Hong KongVictoriaHong Kong DollarHKD
HungaryBudapestHungarian ForintHUF
IcelandReykjavikIceland KronaISK
IndiaNew DelhiIndian RupeeINR
IndonesiaJakartaIndonesian RupiahIDR
IranTehranIranian RialIRR
IraqBaghdadIraqi DinarIQD
IrelandDublinEuroEUR
Isle of ManDouglasPound SterlingGBP
IsraelJerusalemIsraeli New ShekelILS
ItalyRomeEuroEUR
Ivory CoastAbidjanCFA Franc BCEAOXOF
JamaicaKingstonJamaican DollarJMD
JapanTokyoJapanese YenJPY
JerseySaint HelierPound SterlingGBP
JordanAmmanJordanian DinarJOD
KazakhstanAstanaKazakhstan TengeKZT
KenyaNairobiKenyan ShillingKES
KiribatiTarawaAustralian DollarAUD
Korea-NorthPyongyangNorth Korean WonKPW
Korea-SouthSeoulKorean WonKRW
KuwaitKuwait CityKuwaiti DinarKWD
KyrgyzstanBishkekSomKGS
LaosVientianeLao KipLAK
LatviaRigaLatvian LatsLVL
LebanonBeirutLebanese PoundLBP
LesothoMaseruLesotho LotiLSL
LiberiaMonroviaLiberian DollarLRD
LibyaTripoliLibyan DinarLYD
LiechtensteinVaduzSwiss FrancCHF
LithuaniaVilniusLithuanian LitasLTL
LuxembourgLuxembourgEuroEUR
MacauMacauMacau PatacaMOP
MacedoniaSkopjeDenarMKD
MadagascarAntananarivoMalagasy FrancMGF
MalawiLilongweMalawi KwachaMWK
MalaysiaKuala LumpurMalaysian RinggitMYR
MaldivesMaleMaldive RufiyaaMVR
MaliBamakoCFA Franc BCEAOXOF
MaltaVallettaMaltese LiraMTL
Marshall IslandsMajuroUS DollarUSD
Martinique (French)Fort-de-FranceEuroEUR
MauritaniaNouakchottMauritanian OuguiyaMRO
MauritiusPort LouisMauritius RupeeMUR
MayotteDzaoudziEuroEUR
MexicoMexico CityMexican Nuevo PesoMXN
MicronesiaPalikirUS DollarUSD
MoldovaKishinevMoldovan LeuMDL
MonacoMonacoEuroEUR
MongoliaUlan BatorMongolian TugrikMNT
MontenegroPodgoricaEuroEUR
MontserratPlymouthEast Caribbean DollarXCD
MoroccoRabatMoroccan DirhamMAD
MozambiqueMaputoMozambique MeticalMZM
MyanmarRangoonMyanmar KyatMMK
NamibiaWindhoekNamibian DollarNAD
NauruYarenAustralian DollarAUD
NepalKathmanduNepalese RupeeNPR
NetherlandsAmsterdamEuroEUR
Netherlands AntillesWillemstadNetherlands Antillean GuilderANG
New Caledonia (French)NoumeaCFP FrancXPF
New ZealandWellingtonNew Zealand DollarNZD
NicaraguaManaguaNicaraguan Cordoba OroNIC
NigerNiameyCFA Franc BCEAOXOF
NigeriaLagosNigerian NairaNGN
NiueAlofiNew Zealand DollarNZD
Norfolk IslandKingstonAustralian DollarAUD
Northern Mariana IslandsSaipanUS DollarUSD
NorwayOsloNorwegian KroneNOK
OmanMuscatOmani RialOMR
PakistanIslamabadPakistan RupeePKR
PalauKororUS DollarUSD
PanamaPanama CityPanamanian BalboaPAB
Papua New GuineaPort MoresbyPapua New Guinea KinaPGK
ParaguayAsuncionParaguay GuaraniPYG
PeruLimaPeruvian Nuevo SolPEN
PhilippinesManilaPhilippine PesoPHP
Pitcairn IslandAdamstownNew Zealand DollarNZD
PolandWarsawPolish ZlotyPLZ
Polynesia (French)PapeeteCFP FrancXPF
PortugalLisbonEuroEUR
Puerto RicoSan JuanUS DollarUSD
QatarDohaQatari RialQAR
Reunion (French)Saint-DenisEuroEUR
RomaniaBucharestRomanian LeuROL
RussiaMoscowRussian RubleRUR
RwandaKigaliRwanda FrancRWF
Saint HelenaJamestownSt. Helena PoundSHP
Saint Kitts & Nevis AnguillaBasseterreEast Caribbean DollarXCD
Saint LuciaCastriesEast Caribbean DollarXCD
Saint Pierre and MiquelonSt. PierreEuroEUR
Saint Vincent & GrenadinesKingstownEast Caribbean DollarXCD
SamoaApiaSamoan TalaWST
San MarinoSan MarinoItalian LiraITL
Sao Tome and PrincipeSao TomeDobraSTD
Saudi ArabiaRiyadhSaudi RiyalSAR
SenegalDakarCFA Franc BCEAOXOF
SerbiaBelgradeDinarRSD
SeychellesVictoriaSeychelles RupeeSCR
Sierra LeoneFreetownSierra Leone LeoneSLL
SingaporeSingaporeSingapore DollarSGD
SlovakiaBratislavaSlovak KorunaSKK
SloveniaLjubljanaSlovenian TolarSIT
Solomon IslandsHoniaraSolomon Islands DollarSBD
SomaliaMogadishuSomali ShillingSOD
South AfricaPretoriaSouth African RandZAR
South Georgia & South Sandwich IslandsNonePound SterlingGBP
SpainMadridEuroEUR
Sri LankaColomboSri Lanka RupeeLKR
SudanKhartoumSudanese DinarSDD
SurinameParamariboSurinam GuilderSRG
Svalbard and Jan Mayen IslandsLongyearbyenNorwegian KroneNOK
SwazilandMbabaneSwaziland LilangeniSZL
SwedenStockholmSwedish KronaSEK
SwitzerlandBernSwiss FrancCHF
SyriaDamascusSyrian PoundSYP
TaiwanTaipeiTaiwan DollarTWD
TajikistanDushanbeTajik RubleTJR
TanzaniaDodomaTanzanian ShillingTZS
ThailandBangkokThai BahtTHB
TogoLomeCFA Franc BCEAOXOF
TokelauNoneNew Zealand DollarNZD
TongaNuku'alofaTongan Pa'angaTOP
Trinidad and TobagoPort of SpainTrinidad and Tobago DollarTTD
TunisiaTunisTunisian DollarTND
TurkeyAnkaraTurkish LiraTRL
TurkmenistanAshgabatManatTMM
Turks and Caicos IslandsGrand TurkUS DollarUSD
TuvaluFunafutiAustralian DollarAUD
U.K.LondonPound SterlingGBP
UgandaKampalaUganda ShillingUGS
UkraineKievUkraine HryvniaUAG
United Arab EmiratesAbu DhabiArab Emirates DirhamAED
UruguayMontevideoUruguayan PesoUYP
USAWashingtonUS DollarUSD
USA Minor Outlying IslandsNoneUS DollarUSD
UzbekistanTashkentUzbekistan SumUZS
VanuatuPort VilaVanuatu VatuVUV
VaticanVatican CityEuroEUR
VenezuelaCaracasVenezuelan BolivarVUB
VietnamHanoiVietnamese DongVND
Virgin Islands (British)Road TownUS DollarUSD
Virgin Islands (USA)Charlotte AmalieUS DollarUSD
Wallis and Futuna IslandsMata-UtuCFP FrancXPF
Western SaharaEl AaiunMoroccan DirhamMAD
YemenSan'aYemeni RialYER
ZambiaLusakaZambian KwachaZMK
ZimbabweHarareZimbabwe DollarZWD