MacLochlainns Weblog

The Zend Core Server replaces the deprecated Zend Core for Oracle. I’ve put a quick installation guide here. It’s much nicer, and the licensed server is now the recommended direction from Oracle.

The community edition also installs MySQL, phpMySQLAdmin, and a brand new console. You should try it out.

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.

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.

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.

There’s so much energy that goes into blogging with big blog pages, I figured it was time to setup WordPress on my MacBook Pro MAMP (Mac, Apache, MySQL, and PHP) installation. That way, the world doesn’t need to know when I mess up a <div> tag.

The biggest benefit of creating a local blog becomes obvious when you start building your own plugins, templates, and themes. You also have the benefit of a built in backup if you post your work locally before moving it to your blog. Likewise, you can work on your blog when not connected to the network. By the way, thanks to Joel for pointing out that I forgot to say why this was important.

Here are the setup instructions for those interested in doing it too.

1. Download the software from WordPress.org by clicking their button or this one. If you need the tar.gz, click on the web site link.

2. When you download it, you’ll be prompted to open it. Choose to save it.

3. You’ll find it in your downloads folder, where you can double click it to launch the unzip process into a file folder. Mine happens on the desktop because it’s convenient.

4. After you’ve unzipped it into a folder. You drag it into your MAMP htdocs folder. You can check where the htdocs folder is by launching the MAMP application, and choosing Preferences.

In the preferences dialog, click the Apache tab, you’ll see where the htdocs folder location is. Mine is set to the default location.

5. After you drag the wordpress folder into the htdocs folder, you return to the MAMP application and click the Open start page button.

6. Click the phpMyAdmin link and create a database, like the screen shot.

After a moment, you should see that you’ve created a new database named WordPress.

7. Go to your /Applications/MAMP/htdocs/wordpress folder, and open up wp-config-sample.php file in a text editor. You should see the same lines 19, 22, and 25. The editor screen capture is from the TextMate editor.

Edit those lines to reflect your database name, and the root password. Naturally, if you’re concerned that your local host isn’t secure, you’ll need to purchase MAMP Pro to change your root password. You can’t change it in the free version.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'WordPress');
 
/** MySQL database username */
define('DB_USER', 'root');
 
/** MySQL database password */
define('DB_PASSWORD', 'root');

After these changes, save the wp-config-sample.php file as wp-config.php in the same folder.

8. Enter the following in URL in your browser, and press the enter key.

http://localhost:8888/wordpress/wp-admin/install.php

9. On the Welcome web page, enter your blog name and email (mine is omitted for obvious reasons). Click the Install WordPress button to proceed.

10. You’ll then be prompted with the admin user and a password. You should print it or copy it to your buffer because you’ll need it when you first login. Then, click the Log In button to proceed. You should probably change your password to something that you’ll remember before you do anything else.

11. You now have a complete localhost WordPress installation. Now, you can go to your real web accessible WordPress install and export your blog from the Tools menu. Then, on your localhost blog, you can import from the export file of your blog. If you’re using plugins (like anybody doesn’t), then copy the contents from the wp-content/plugins folder from your online blog to your local one; and then, activate them. You can snag your Akismet key from your online site too, but it is unlikely anybody will see your localhost blog.

Violà, c’est fine.

You can now access WordPress on your local machine by typing in the following URL:

http://localhost:8888/wordpress/

LAMP the ubiquitous acronym for Linux, Apache, MySQL, and PHP, Python, or Perl has a couple cousins. They are OPAL (Oracle’s stack on Linux, and MAMP (Mac OS X, Apache, MySQL, and PHP et cetera). Perhaps another acronym on the horizon is: OPAM (Oracle, PHP, Apache, and Mac OS X). OPAM is a guess on my part. Nobody knows what Oracle’s marketing department may choose. Regardless of the acronym for it, Oracle has published instructions for configuring an Oracle/PHP stack on Mac OS X.

I generally configure the OPAL stack with Zend Core for Oracle and the Oracle database on a virtual machine running Windows XP, Windows Vista, Ubuntu, or Red Hat Linux. If you follow my posts I prefer VMWare Fusion over Parallels. The MAMP stack I use is open source and provided by Living E. It follows the pattern of Mac OS X installations, which differs from the recent posting from Oracle. It’s easy to install, as you tell from the documentation. MAMP installs PHP 5.2.6 as the current release.

It’s a great choice when you incorporate the open source Sequel Pro tool. Isn’t it ashame that Sequel Pro doesn’t work natively with Oracle. If I find some time this summer, it might make a great project to extend it to Oracle. The interface to Sequel Pro looks like this:

When you create a connection, you should know the typical values. The database value can be left blank when connecting as the superuser root:

Host:     localhost
User:     root
Password: root
Database: 
Socket:   /Applications/MAMP/tmp/mysql/mysql.sock
Port:     3306

Here’s the connection dialog where you’ll enter the values:

Have fun playing with the MAMP stack.