MacLochlainns Weblog

Teaching SQL is interesting because folks try syntax that should be broken. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL.

You can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause. It’s explained in Chapter 11.12.3 of the MySQL 5.1 Reference. Appropriately, the process is called group by hidden columns.

This query actually runs in a generically configured MySQL instance without an error. It returns a meaningless non-grouped by column value. I wonder if there’s code out there that’s returning unintended data sets because of this behavior.

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     grouping
GROUP BY key_one;

You can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable. After setting this system variable, a GROUP BY without all non-aggregated columns should raise the following exception:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

You can add the ONLY_FULL_GROUP_BY mode variable during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you’ve set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

NET STOP mysql
NET START mysql

You can also shut the system down with the following command:

mysqladmin -uroot -p shutdown

-- Conditionally drop sample table.
SELECT 'DROP TABLE IF EXISTS grouping' AS "Statement";
DROP TABLE IF EXISTS grouping;
 
-- Create sample table.
SELECT 'CREATE TABLE grouping' AS "Statement";
CREATE TABLE grouping
( key_one   CHAR(5)
, key_two   CHAR(5)
, counter   INT UNSIGNED );
 
-- Insert nine rows into the sample table.
SELECT 'INSERT INTO grouping' AS "Statement"
INSERT INTO grouping VALUES
('One','Uno-1',1),('Two','Due-1',2),('Three','Tre-1',3)
('One','Uno-2',1),('Two','Due-2',2),('Three','Tre-2',3)
('One','Uno-3',1),('Two','Due-3',2),('Three','Tre-3',3);
 
-- Query with hidden columns, no enforcement on non-aggregate columns.
SELECT 'SELECT non-aggregates FROM grouping with hidden columns' AS "Statement";
SELECT key_one, key_two, SUM(counter)
FROM   grouping
GROUP BY key_one;
 
-- Check current system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;
 
-- Append the mode to the existing system mode variable.
SELECT 'SET SQL_MODE=(SELECT ...)' AS "Statement";
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
 
-- Check modified system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Function or Procedure (or subroutines according to some documentation).

In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.

Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.

You enable output display in Oracle by setting this in SQL*Plus:

SQL> SET SERVEROUTPUT ON SIZE 1000000

You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ).

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

-- Create a procedure in Oracle.
CREATE OR REPLACE PROCEDURE hello_world IS
BEGIN
  -- Print a word without a line return.
  dbms_output.put('Hello ');
  -- Print the rest of the phrase and a line return.
  dbms_output.put_line('World!');
END;
/
 
-- Call the procedure.
EXECUTE hello_world;

It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could likewise do that in Oracle.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE hello_world' AS "Statement";
DROP PROCEDURE IF EXISTS hello_world;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE hello_world()
BEGIN
  -- Print the phrase and a line return.
  SELECT 'Hello World!';
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL hello_world' AS "Statement";
CALL hello_world();

Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.

Guidelines for Development of Procedures

Declaration Guidelines

The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:

1. Variable declarations must go first, you can assign initial values with the DEFAULT keyword. While not required, you should:

2. After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature.
3. Last in your declaration block, you declare your handler events.

Execution Guidelines

1. Variable assignments are made one of two ways:

2. You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
3. You must reset looping variables, like the fetched control variable at the end of the loop to reuse the handler variable in subsequent loops.
4. You must assign values to local variables if you want to use them in the exception handler.
5. If you’ve started a transaction, don’t forget to COMMIT your work.

Exception Guidelines

1. Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
2. When you deploy exception blocks, they’re the last element at the bottom of the exception block.
3. You should consider explicit exception handlers for each error unless the action taken is the same.
4. You should consider grouping all exception handlers when the action taken is the same.
5. You should include a ROLLBACK whenever you’ve performed two or more SQL statements that may modify data.

Below is an example for putting debug code inside a loop.

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- Conditionally drop a sample table.
SELECT 'DROP TABLE IF EXISTS sample' AS "Statement";
DROP TABLE IF EXISTS sample;
 
-- Create a table.
CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_msg   VARCHAR(20));
 
-- Insert into sample.
INSERT INTO sample (sample_msg) VALUES
 ('Message #1')
,('Message #2')
,('Message #3');
 
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE debug_loop' AS "Statement";
DROP PROCEDURE IF EXISTS debug_loop;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE debug_loop()
BEGIN
 
  /* Declare a counter variable. */
  DECLARE lv_counter INT DEFAULT 1;
 
  /* Declare local control loop variables. */
  DECLARE lv_sample_id  INT;
  DECLARE lv_sample_msg VARCHAR(20);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE sample_cursor CURSOR FOR
    SELECT * FROM sample;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a sample cursor. */
  OPEN sample_cursor;
  cursor_sample: LOOP
 
    /* Fetch a row at a time. */  
    FETCH sample_cursor
    INTO  lv_sample_id
    ,     lv_sample_msg;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_sample; END IF;
 
    -- Print the cursor values.
    SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows";
 
    -- Increment counter variable.
    SET lv_counter = lv_counter + 1;
 
  END LOOP cursor_sample;
  CLOSE sample_cursor;
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL debug_loop' AS "Statement";
CALL debug_loop();

This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.

Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”

I promised to post an answer tonight, and morning counts too. You can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.

Here’s an example in MySQL:

mysql> CREATE TABLE data_type (type bool);
 
mysql> DESCRIBE data_type;
+-------+------------+------+-----+---------+-------+
| FIELD | Type       | NULL | KEY | DEFAULT | Extra |
+-------+------------+------+-----+---------+-------+
| type  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
 
mysql> INSERT INTO data_type VALUES (true),(false);
 
mysql> SELECT * FROM data_type;
+------+
| type |
+------+
|    1 |
|    0 |
+------+

Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%".$title."%'";

However, they tried to bind the variable following the same pattern, and found that it failed. They used the following type of syntax:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%?%'";

It raised the following error:

Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in program_name on line #

The reason is the parser, it expects variables to be independent tokens in the SQL string. You can’t bind a string into the shell of an apostrophe delimited string. You could naturally, make an assignment to the local PHP variable before binding it to the variable, like this:

$title = '%'.$title.'%';

It’s actually a better idea to concatenate the multiple character wildcard operator inside the SQL statement. The correct syntax requires that you use the CONCAT() function. You could reset to use piped concatenation but generally you should avoid that on the MySQL platform (see this post for an explanation of SQL concatenation on Oracle, MySQL, and SQL Server).

This is the required statement when using a MySQL database:

$sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the OCI error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $title = (isset($_GET['title'])) ? $_GET['title'] : $title = "RoboCop";
 
    query_insert($title);
  }
 
  // Query results afret an insert.
  function query_insert($title)
  {
    // Return successful attempt to connect to the database.
    if (!$c  = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";
 
      // Prepare statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"s",$title);
 
        // Execute it and print success or failure message.
        if (mysqli_stmt_execute($stmt)) {
 
          // Store result.
          mysqli_stmt_store_result($stmt);
 
          // Bind result to local variable.
          mysqli_stmt_bind_result($stmt,$item_title);
 
          // Open HTML table and print header.
          $out = '<table border="1" cellpadding="3" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<th align="center" style="font-weight:bold;padding:2px;margin:1px;background:#8DB3E2;width:300px">Item Title</th>';
          $out .= '</tr>';
 
          // Read result.
          while (mysqli_stmt_fetch($stmt)) {
            $out .= '<tr>';
            $out .= '<td style="padding:2px;margin:1px;background:#DBE5F1;">'.$item_title.'</td>';
            $out .= '</tr>';
          }
 
          // Close the HTML table.
          $out .= '</table>';
 
 
          // Print the HTML table.
          print $out;
        }
      }
 
      // Free system resources.
      mysqli_stmt_free_result($stmt);
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
?>

Alas, poor ereg() I abused you well. PHP 5.3 has deprecated ereg() and now we must move forward with preg_match(). Along with that change, somebody asked me to show how to upload images to the file system as opposed to the database. Personally, I think they should be stored in the database.

With my bias toward databases, I threw in a virtual directory mapping in a MySQL database because it doesn’t natively support an Oracle equivalent BFILE data type. You can see this older post how to use the DBA_DIRECTORIES view in Oracle to mimic this behavior.

Naturally, MySQL is the preferred database of the person asking the question. You could also implement this exactly the same in Oracle but you really don’t want to do so. Using Oracle’s virtual directories has it’s own pre-built set of security features. They provide a more robust solution.

The code is presented as follows (setup for MySQL instructions):

1. Create and seed the DIRECTORY table in MySQL:

-- Create a directory table.
CREATE TABLE directory
( directory_id   int PRIMARY KEY AUTO_INCREMENT
, virtual_name   varchar(30)
, directory_name varchar(60));
 
-- Seed the table with a virtual directory mapping.
INSERT INTO directory VALUES ( NULL,'CMS_IMAGES','C:\\Data' );

2. Create a MySQLCredentials.inc credentails file for inclusion in the PHP program:

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

3. Create the PHP uploading program, named MySQLFileUpload.php:

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Declare input variables.
  $id = (isset($_POST['id'])) ? $_POST['id'] : 1021;
 
  // Upload a file to server in a mapped physical drive location.
  if (process_uploaded_file(map_virtual_directory($id)))
    print "Successfully Uploaded the file.<br />";
 
  // Map a virtual directory to a physical directory.
  function map_virtual_directory($virtual) {
 
    // Return successful attempt to connect to the database.
    if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
 
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Return error message.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a case insensitive dynamic SQL statement.
      $sql = "SELECT directory_name FROM directory WHERE virtual_name = UCASE(?)";
 
      // Prepare the statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
 
        // Bind the input parameter to the prepared statement.
        mysqli_stmt_bind_param($stmt,'s',$virtual);
 
        // Execute the prepared statement.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind the result to a local variable.
          mysqli_stmt_bind_result($stmt,$directory);
 
          // FetchPrepare statement and link it to a connection.
          while (mysqli_stmt_fetch($stmt))
            return $directory;
        }
        else
          // Return error message.
          print mysqli_error();
      }
      else
        // Return error message.
        print mysqli_error();
 
          // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload.
  function process_uploaded_file($directory) {
 
    /* Assume the application may allow a virtual directory with a trailing backslash or forward
       slash to be stored in the database, and manage both scenarios across Windows and Linux. */
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      if (preg_match("/\b\\\/",$directory));
      else if (preg_match("/\b\//",$directory)) {
        $directory = substr($directory,0,strlen($directory)-1);
        $directory = $directory."\\";
      }
      else $directory = $directory."\\";
    else
      if (preg_match("/\b\//",$directory))
        $directory = substr($directory,0,strlen($directory)-1);
      else
        $directory = $directory."/";  
 
    // Check for, move uploaded file, and confirm processing.
    if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
 
      // Move temporary cache into a file directory with the uploaded file name.
      move_uploaded_file($_FILES['userfile']['tmp_name'],$directory.$_FILES['userfile']['name']);
 
      // Remove this from real code, it's here for example only. ;-)
      print "Uploaded [".$_FILES['userfile']['name']."] to".$directory."<br />";
 
      // Return true to encapsulate the functional logic on success.
	    return true;
    }
    else
      // Return false to encapsulate the functional logic on failure.
	    return false;
  }
?>

4. Create a web page to test it:

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

<html>
<head>
<title>
  UploadFileFormMySQL.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="MySQLFileUpload.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

Hope this helps a few folks. I imagine that the prepared statement with bound variables may help a few folks because it’s not found (at writing) on the php.net web site.

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.

Sometimes you chalk something up as straightforward because you’ve done it a while. I did that in a lab assignment recently. It asked my students to upload a large text file and image to the MySQL database, store them in a TEXT and MEDIUMBLOB column, and read them back out of the database, like this:

The trick was that I wanted them to read the file into a string and then load the string. There wasn’t a single code example to do this out there, except some that might exist behind an account and credit card payment. I put together a complete example like the Oracle LOB processing page. You can find it here in the MySQL LOB processing blog page. More or less, it shows you how to stream an image into a MySQL database in chunks (I chose 8 K chunks).

The general tricks to upload a string require you enclose to enclose them with the addslashes() function before assigning a binary stream to a variable, then stripslashes() function by segment before you load it to the database. You really don’t need to do that. It’s a myth. The binary stream doesn’t require that extra handling. In fact, you can corrupt certain images when you use the addslashes() and stripslashes() functions; they should be avoided in this context.

You should do it in streams (at least when they’re larger than 1 MB), I chose the procedural mysqli to demonstrate it because there wasn’t an example that I or my students could find on the web. Just for information, some laptops don’t have the resources to accommodate LARGEBLOB datatypes on the Windows OS. The BLOB or MEDIUMBLOB should work fine, especially for this little example.

46
47
48
49
50
51
52
53
54
55
56
57
58

    // Declare a PL/SQL execution command.
    $sql = "UPDATE item SET item_blob = ? WHERE item_id =  ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id);
 
      $start = 0;
      $chunk = 8192;
      while ($start < strlen($item_blob)) {
        mysqli_send_long_data($stmt,0,substr($item_blob,$start,$chunk));
        $start += $chunk;
      }

You can find the code in that blog page referenced. Hope it helps some folks.

I just wanted to see how Zend Server Community Edition might be broken. Performing a full feature install on Windows x64, I confirmed that Zend Server’s Java Bridge depends on the 32-bit JRE (common sense prevails). Installing it against the JRE 64-bit jvm.dll did raised an exception but none of the instructions address the problem.

It’s one of those simplifying assumptions – everybody knows 32-bit software works with 32-bit software. Anybody running on Windows XP x64 should know that they may need a JDK 64-bit and both a JRE 64-bit and JRE 32-bit for some applications. For those who don’t know this, like my students and other newbies, when you run Windows XP the 64-bit stuff goes in the C:\Program Files directory and the 32-bit stuff goes in the C:\Program Files (x86) directory. This lets you develop 32-bit or 64-bit Java applications on the same 64-bit machine.

Another tidbit of interest, don’t choose a full install if you’ve already installed MySQL. The Zend Community Server isn’t smart enough to alter the configuration to another port, and their my.ini points to a 3306 listener port. This causes the MySQL_ZendServer51 service to fail. It also doesn’t uninstall well. If you don’t want to clean the Windows Registry, don’t choose to install a second MySQL.

As an FYI, the Zend installation of MySQL doesn’t put a password on the root account. Don’t forget to add one after the install if you go down the full product road. This has the Zend Server Community Edition installation instructions.

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.