MacLochlainns Weblog

A colleague of mine just dropped by his new book on Developing Hybrid Applications for the iPhone. He covers Dashcode, Xcode, JavaScript, and Objective-C. He also covers how to use WebView and native SQLite database access from the iPhone.

It looks interesting. By the way, his blog is here.

I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.

If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!

The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.

I finally got around to creating a blog page that shows you how to install Zend Core for Oracle, verify the installation of PHP, your connection to an Oracle XE database with PHP, and your connection to a MySQL database with PHP.

It’s using the folding concepts I’ve adopted in the blog. One section unfolds to display screen shots for the installation of Zend Core for Oracle. Another section shows you how to confirm your PHP and Apache installation. Two other sections show you how to confirm your connection to an Oracle or MySQL database.

I apologize to those who don’t like to click through to another page, but it was a long page with 20+ images. Hope it helps a few folks, I know it’ll help my students. 😉

My students have to do their group labs in Oracle and then port them individually to MySQL. Most of the conversion resolves around dates because Oracle spoils us with their simplicity in their SQL dialect.

For example, if we wanted to add 9 days to today’s system date (June 27, 2009) we can do this in Oracle:

SQL> SELECT SYSDATE + 9 FROM dual;

It prints

06-JUL-09

If we tried the equivalent in MySQL, we get a null because it treats any day value over 31 as a null. The maximum date in any month is 31, regardless of month. If you add more days than the maximum number minus your current date, you return a null. This is because adding a day never changes the month, and that yields invalid dates. In MySQL, you need to make this calculation with the adddate() or date_add() functions because they’ll increment months and years.

By way of example, if we only added four to today’s date (June 27, 2009) and formatted the output as a date, it works

mysql> SELECT str_to_date(utc_date() + 4,'%Y%m%d');

We’d get this:

+--------------------------------------+
| str_to_date(utc_date() + 4,'%Y%m%d') |
+--------------------------------------+
| 2009-06-31                           |
+--------------------------------------+

I always wanted an extra day in June. 😉

In short, there are three possible non-dates in February that format as dates, and one day in every thirty day month. I’ve a hunch this is a bug (I’ve logged a bug and they’ve verified it and moved it to triage).

The correct way to perform this calculation in MySQL is to use either the adddate() or date_add() functions. They take the same arguments. Here’s a quick example:

mysql> SELECT adddate(utc_date(),INTERVAL 9 DAY);

It yields the correct date:

+------------------------------------+
| adddate(utc_date(),INTERVAL 9 DAY) |
+------------------------------------+
| 2009-07-06                         |
+------------------------------------+

I know this answers a student email but I hope it helps a few others too.

Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.

I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PL/SQL: Item ignored
4/26     PLS-00307: too many declarations OF 'HELLO_THERE' MATCH this CALL
6/5      PL/SQL: Statement ignored
6/13     PLS-00320: the declaration OF the TYPE OF this expression IS incomplete OR malformed

All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( pv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (pv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := pv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( iv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (iv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := iv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.

Somebody asked me why you must provide the IDENTIFIED BY password clause on a grant after you’ve already created the user. This is a very good question, I wondered it myself a while back. This blog provides the what, why, and how of it.

The reason you typically must provide the IDENTIFIED BY password clause is that typically you’ve enabled the NO_AUTO_CREATE_USER value in your sql_mode. That parameter is there by default from MySQL 5.0.2 forward. It’s what requires you to use the IDENTIFIED BY password clause.

The parameter enforces a match against user, host, and password columns in the user table of the mysql database. If you disable it, the match is on user and host columns in the user table. If you make a grant to a user without specifying a host and a wildcard (%) host doesn’t exist for that user, the grant will insert a duplicate user. The same user name with a wildcard host trumps the one with a localhost host value for access privileges, which means that user no longer needs a password to gain entry to the database.

The following steps will show and explain what happens in MySQL’s security model.

1. Sign on as the root user, then create a new myuser user:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser'@'localhost';

If you attempt to grant permissions to the myuser without a password value, like this

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

You should see an outcome like the following. At least, you will when the NO_AUTO_CREATE_USER value is set in your sql_mode variable.

ERROR 1133 (42000): Can't find any matching row in the user table

If it let you set it, then the NO_AUTO_CREATE_USER value isn’t set. You can check what’s set with this query:

mysql> SELECT @@sql_mode;

You should see this:

+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

2. Reset the sql_mode to disable the NO_AUTO_CREATE_USER value, like this (presuming you’ve got the generic MySQL 5.1 settings):

mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. After disabling the restriction, you can grant privileges on a sampledb to the myuser user without the IDENTIFIED BY password clause. You’d use the following syntax:

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

Before you sign off and sign on to test your new user, you should now query the USER table. Here’s the query to focus on the three important columns:

mysql> SELECT USER, host, password
    -> FROM USER
    -> WHERE USER = 'myuser';

If you return more than one row, you made an error and most likely left off the @'localhost' or used @'%'. If you made that mistake, you’ll find that disabling the NO_AUTO_CREATE_USER value, can yield multiple entries in the user table for the same user. Worse yet, the user no longer can sign in with their password.

+--------+-----------+-------------------------------------------+
| USER   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| myuser | %         |                                           |
| myuser | localhost | *CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188 |
+--------+-----------+-------------------------------------------+

If you were to sign off and attempt to sign on with the myuser account password, you’d get the following error message:

ERROR 1045 (28000): Access denied FOR USER 'myuser'@'localhost' (USING password: YES)

You can now sign on as the myuser user only without a password. Fixing it is simple, just drop the user for any host:

mysql> DROP USER 'myuser'@'%';

While disabling the sql_mode parameters makes your job of granting privileges easier, it risks compromising your database. Therefore, avoid it or be careful. If I missed something or got it wrong, let me know.

I put together a post on how to upload to MySQL from a CSV file with dates. It was more or less for my students but one of them was curious how the mega formula worked. As commented, the easier solution is to create a custom format. Oddly, Open Office does support the MySQL default format natively.

Excel doesn’t support the native MySQL date format as a default format mask, which is YYYY-MM-DD, or 2009-06-02 for June 2, 2009. That means you have to convert it from a scalar date to a string or create a custom format mask (see Dmitri’s comment below). If you just shook your head at the term scalar date, maybe a custom format mask is best. However, if you want a programming solution let me explain that Excel supports only three data types. They’re a string literal, a numeric literal, and a formula. Dates in Excel are merely formatted numbers. When the numbers are integers, the date is a date, but when the number has a fractional component, the date is really a timestamp.

Here’s a brief description of the process required to convert a date in Excel into a MySQL date format string literal in a CSV file. You need the following Excel functions:

Date Functions

• The DAY(date) function returns a 1 or 2 digit numeric value for the day of the month, with ranges of 1 to 28, 1 to 29, 1 to 30, or 1 to 31 dependent on the month and year.
• The MONTH(date) function returns a 1 or 2 digit numeric value for the month of the year.
• The YEAR(date) function returns a 4 digit numeric value for the year.

Logical Functions

• The IF(logical_expression,truth_action,false_action) function returns the truth action when the expression is true, and the false action when the expression isn’t true.

MySQL Server

• CONCATENATE(string_1, string_2, ...) glues strings together.
• LEN(numeric_value) function returns the length of a string or number.

MySQL requires that you return an eight character string of numbers. The first four numbers must be a valid year, the fifth and sixth numbers a valid month, and the seventh and eigth numbers a valid day in the context of the year and month provided. Unfortunately, the DAY() and MONTH() functions may return a 1 or 2 digit value. That can’t happen in the CSV file’s string for a date, so you use the IF() and LEN() functions to guarantee a 2 digit return value.

Here are the examples that guarantee 2 digit day and month values, assuming that the base date is in the A1 cell. The concatenation of a "0" (zero between two double quotes) or the "" (two double quotes or a string null) ensures the number data types become strings.

=IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1))
=IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1))

A zero is placed before the day or month when the logical condition is met, which means the day or month value is a single digit string. A null is place before the day or month when the logical condition isn’t met, which means the day or month value is a two digit string. There’s only one problem with these mega functions. They return a number.

The year calculation doesn’t require the explicit casting when you concatenate it with the other strings because it is implicitly cast as a string. However, it’s a better practice to include it for clarity (most folks don’t know about the implicit casting behaviors in Excel).

=CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),DAY(A1)))

As Goodwin reported in a comment, there’s an easier way that I missed. You can simply use the TEXT function when the source column is a valid serialized date value.

=TEXT(A1,"YYYYMMDD")

You can see the full MySQL import from CSV in the previous post. Naturally, you may want to copy and paste special the value before creating the CSV file. Also, don’t forget to delete any unused columns to the right or rows beneath because if you don’t your file won’t map to your table definition.

You have one approach to uploading CSV file data into a MySQL database. You use the LOAD statement. There two caveats about using the LOAD command, and they are:

• You must convert dates to the default MySQL format – YYYYMMDD, or the four digit year, two digit month, and two digit day.
• You can’t use AUTO_INCREMENT from the source to the loading table.

It’s untrue that you must input dates as strings and convert them in the database. However, you can do that if you like. I’d suggest you open the file in Microsoft Excel, then create a column with the correct data format. You can do that by creating a custom format, or with a formula. I figure the custom format is easiest but sometimes a formula is necessary for applications.

=CONCATENATE(CONCATENATE("",YEAR(A1)),IF(LEN(MONTH(A1)),CONCATENATE("0",MONTH(A1))),IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),CONCATENATE("",DAY(A1))))

You should note that loading a date at the end of a line has problems in MySQL. The easiest fix that I’ve found is to place a comma at the end of each line. In a Microsoft world, that eliminates the need for the \r from the LINES TERMINATED BY clause.

While this shows a trivial amount of data, here’s a CSV file to use in the test case. Create a directory like Data off your base logical drive or mount point. Then create a file named transaction_upload.csv and put the following data set in it.

20090102,5
20091231,5
20091128,5
20090616,5

Create an importing.sql file in the C:\Data folder with the contents below. It’s only a recommendation but generally import tables aren’t useful in between uploads, and that’s why I create the table as an in-memory table. In-memory tables are dropped when you recycle (boot) the MySQL instance.

-- Conditionally drop the table.
DROP TABLE IF EXISTS transaction_upload;
 
-- Create the new upload target table.
CREATE TABLE transaction_upload
( transaction_date   DATE
, transaction_amount FLOAT ) ENGINE=MEMORY;
 
-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM transaction_upload;

Before you connect as anything other than the root account, you’ll need to grant the global FILE privilege to the targeted user. This is true because you’ve not qualified a LOCAL file in the import statement. Users that have all privileges on a given database also have read-write access to LOCAL tables, which is read write to client-side files. When the LOCAL key word is omitted, you’re instructing a read from the server by the client tool. You must therefore grant a global permission to enable a connected user can call a server-side file.

If you want to import without granting any additional global permissions, you can rewrite the statement like this:

LOAD DATA LOCAL INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

If you don’t want to rewrite the statement, you’ll get the following error:

ERROR 1045 (28000): Access denied FOR USER 'student'@'localhost' (USING password: YES)
Empty SET (0.01 sec)

C:\Data> mysql -uroot -pcangetin -P3306

mysql> GRANT FILE ON *.* TO 'student'@'localhost' IDENTIFIED BY 'student';

C:\Data> mysql -ustudent -pstudent -P3306

Run the script:

mysql> \. importing.sql

Then, you should see the following:

+------------------+--------------------+
| transaction_date | transaction_amount |
+------------------+--------------------+
| 2009-01-02       |                  5 |
| 2009-12-31       |                  5 |
| 2009-11-28       |                  5 |
| 2009-06-16       |                  5 |
+------------------+--------------------+
4 rows in set (0.00 sec)

Another quick tidbit, dates can be tricky when they’re the last column in the file. The best solution is to put a comma at the end of each string. If you put the comma at the end of each line, you can also dispense with the \r on the Windows platform.

Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.

The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.

Here are the detailed steps, even though there are other blog entries with information on related subjects.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12)
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create a stored procedure with IN-only (default) mode parameters.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  SAVEPOINT starting_point;
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.sql

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.

5. Write the following into a call_oracle_procedure.php:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN double_insert(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement.
    oci_bind_by_name($stmt, ":a", $val1);
    oci_bind_by_name($stmt, ":b", $val2);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print "Congrats! You've executed a Oracle stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

6. Run the call_oracle_procedure.php from the command line, like this:

php call_oracle_procedure.php

7. Now you can requery the database to see the inserts made by the PHP program.

SQL> SELECT * FROM a;
 
      A_ID A_TEXT
---------- ------------
         1 This IS one.
         2 Hello Hal!
 
SQL> SELECT * FROM b;
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         2          2 Hello Dave!

There’s now a sample file for both approaches. As always, I hope this helps some folks.

A friend, who’s a MySQL-only developer, asked me a question that I couldn’t resist sharing. He wanted to know how to simplify his PHP code by making a single call to the database for a set of inserts to multiple tables.

You can imagine my delight when I got to say a stored procedure. He said, “It’s not Oracle; that won’t work.” I said, “MySQL supports stored procedures too.” Then, he asked how do you do that. Since the reference manual is a bit sparse on that, here’s a quick example.

Here are the detailed steps, even though there are some other blog entries with more information on these topics.

1. Sign on as the root user, if you need more on that go here. Create a database – storeddb:

CREATE DATABASE storeddb;

2. Create a user and grant them permissions:

mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student';

3. Create a create_mysql_procedure.sql file with the following contents:

-- Select the database.
USE storeddb;
 
-- Conditionally drop the objects to make this rerunnable.
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP PROCEDURE IF EXISTS double_insert;
 
-- Create the tables.
CREATE TABLE a
( a_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_text CHAR(12));
 
CREATE TABLE b
( b_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_id   INT UNSIGNED 
, b_text CHAR(12));
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- Create a stored procedure.
CREATE PROCEDURE double_insert
( input_a CHAR(12), input_b CHAR(12))
BEGIN
  START TRANSACTION;
  INSERT INTO a VALUES (NULL, input_a);
  INSERT INTO b VALUES (NULL, last_insert_id(), input_b);
  COMMIT;
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;
 
-- Declare a couple local session variables.
SET @text1 = 'This is one.';
SET @text2 = 'This is two.';
 
-- Call the local procedure.
CALL double_insert(@text1,@text2);
 
SELECT * FROM a;
SELECT * FROM b;

4. Quit the session as the root user.

mysql> QUIT;

5. Sign on as the student user.

C:\> mysql -ustudent -pstudent

6. As the student user, source the file. You have two ways to do that. One leverage csh/Tcsh shell syntax and the other uses Bourne, Korn, or BASH shell syntax.

6(a). The csh/Tcsh syntax:

mysql> SOURCE create_mysql_procedure.sql

6(b). The Bourne, Korn, or BASH syntax:

mysql> \. create_mysql_procedure.sql

When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.

DATABASE changed
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected (0.14 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.08 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.09 sec)
 
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
+------+--------------+
1 ROW IN SET (0.00 sec)
 
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
+------+------+--------------+
1 ROW IN SET (0.00 sec)

A quick note to those new to automatic numbering in MySQL. You use a null when you don’t want to write an override signature for the INSERT statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server in this blog post.

7. Write the following PHP program, and name it call_mysql_procedure.php. We’re going to call it from the command line, but you shouldn’t have to modify it when you call it from a browser.

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "student", "student", "storeddb");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "CALL double_insert(?,?)";
 
    // Prepare the statement and bind the two strings.
    if (mysqli_stmt_prepare($stmt, $sql)) {
      mysqli_stmt_bind_param($stmt, "ss", $val1, $val2);
 
      // Execute it and print success or failure message.
      $success = mysqli_stmt_execute($stmt);
      if ($success) {
        print "Congrats! You've executed a MySQL stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
  }
}
?>

8. Run the call_mysql_procedure.php from the command line, like this:

php call_mysql_procedure.php

You should see the following message:

Congrats! You've executed a MySQL stored procedure from PHP!

9. You can now connect to the MySQL database storeddb and re-query the tables a and b. You should see the following, which tells you that your PHP code worked.

mysql> SELECT * FROM a;
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
|    2 | Hello Hal!   |
+------+--------------+
2 ROWS IN SET (0.00 sec)
 
mysql> SELECT * FROM b;
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
|    2 |    2 | Hello Dave!  |
+------+------+--------------+
2 ROWS IN SET (0.00 sec)

I guess Alice that writing PL/SQL may actually be portable to other databases? Actually, there are some severe limits on loops in MySQL’s implementation but you can move basic stored procedures as of MySQL 5.1. Officially, MySQL 5.0 supports them.

As always, I hope this helps some folks.