MacLochlainns Weblog

Escape from New York, a cult classic, has Snake (Kurt Russell) rescue the President from Manhattan, which in this dystopian film’s theme is a prison. Windows 8 seems like a new dystopian version of Windows where only Microsoft’s browser works to perform real browser activities.

Gone is the European Union’s antitrust suit. Why? Oh, yes, Microsoft promised to provide options to users of its platform. Gone that! Will the European Union retake the mantle of the Avengers and free us from the mantle of Microsoft’s attempt to play Loki in compelling users back to their browser?

An excerpt from Greg Keizer’s ComputerWorld piece:

“Windows on ARM [the former name for Windows RT] prohibits any browser except for Internet Explorer from running in the privileged ‘Windows Classic’ environment,” said Anderson. “In practice, this means that only Internet Explorer will be able to perform many of the advanced computing functions vital to modern browsers in terms of speed, stability, and security to which users have grown accustomed.”

While I’ve used Microsoft Excel since version 3 – sent to me for review by Microsoft along with my first non-Mac mouse in 1990 – their browser is something to avoid. It’s the first thing that I disable when installing a new Windows’ instance, by substituting Mozilla’s Firefox.

My question is: Will all those accounts and finance folks who drive Microsoft Office sales because of Excel’s VBA features really drink the Kool-Aid and buy into this new generation of browser tyranny? Let’s hope they don’t! Maybe they’ll take a closer look at OpenOffice 3.

However, I strongly doubt they’ll surrender their VBA driven models unless OpenOffice provides something similar. Let’s face it. Microsoft Excel’s VBA feature is probably one of the smartest thing Microsoft ever marketed because it’s seals the deal for the Microsoft cash cow – the Microsoft Office Suite.

Disclaimer: I’m primarily a Mac OS X, Linux, and Unix user, but like it or not, the reach of the Windows’ desktop is omnipresent in our lives; and let’s face it: Windows 8 will continue to be the most frequent choice for corporate desktops. It’s also the platform for Microsoft SQL Server. Therefore, it’s critical for us to rise up and shout for browser freedom – Firefox, Chrome, Safari, and the little guys too!

In class today, we reviewed pass-by-value (IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.

Here’s the analogy and below is the code to support it:

“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she can’t shrink, grow, or learn throughout the story; whereas, a pass-by-reference parameter in a procedure is like sending Alice into the rabbit hole where she can shrink, grow, fight the Jabberwocky, and learn things that make her life better when she exits the rabbit hole – consistent with the storyline of Alice’s revisit to Wonderland.”

The example code creates a stored procedure that accepts two parameters – one pass-by-value and one pass-by-reference. Inside the procedure there’s a local variable and a reassignment of value to the pass-by-reference parameter. It’s in this wonderland procedure (by the way don’t forget to manage the DELIMITER value when you 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

CREATE PROCEDURE wonderland
( IN     pv_value_param  VARCHAR(20)
, INOUT  pv_ref_param    VARCHAR(20))
BEGIN
 
  /* Declare a variable. */
  DECLARE lv_value_param  VARCHAR(20);
 
  /* Query the local variable and reference parameter before changing values. */ 
  SELECT   'On Entry' AS "Where"
  ,        pv_value_param AS "Value Parameter"
  ,        pv_ref_param AS "Reference Parameter"
  ,        IFNULL(lv_value_param,'     ') AS "Local Variable";
 
  /* Assign a lowercase value parameter to a local variable. */
  SET lv_value_param := LOWER(pv_value_param);  
 
  /* Assign a uppercase reference parameter value to the reference parameter. */
  SET pv_ref_param := UPPER(pv_ref_param);
 
  /* Query the local variable and reference parameter after changing values. */ 
  SELECT   'On Exit ' AS "Where"
  ,        pv_value_param AS "Value Parameter"
  ,        pv_ref_param AS "Reference Parameter"
  ,        IFNULL(lv_value_param,'     ') AS "Local Variable";
 
END;
$$

A tester procedure than tests how the pass-by-value and pass-by-reference modes of operation differ. It’s here:

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

CREATE PROCEDURE tester
( IN  pv_value_param  VARCHAR(20)
, IN  pv_ref_param    VARCHAR(20))
BEGIN
 
  /* Query the local and reference parameters. */ 
  SELECT   'Before  ' AS "Where"
  ,        pv_value_param AS "Value Parameter"
  ,        pv_ref_param AS "Reference Parameter";
 
  /* Call the wonderland procedure that changes the pass-by-reference parameter. */
  CALL wonderland(pv_value_param, pv_ref_param);
 
  /* Query the local and reference parameters. */ 
  SELECT   'After   ' AS "Where"
  ,        pv_value_param AS "Value Parameter"
  ,        pv_ref_param AS "Reference Parameter";
 
END;
$$

You call the tester program with this syntax:

CALL tester('Alice','Alice');

The test case returns the following values:

+----------+-----------------+---------------------+
| Where    | Value Parameter | Reference Parameter |
+----------+-----------------+---------------------+
| Before   | Alice           | Alice               |
+----------+-----------------+---------------------+
1 row in set (0.00 sec)
 
+----------+-----------------+---------------------+----------------+
| Where    | Value Parameter | Reference Parameter | Local Variable |
+----------+-----------------+---------------------+----------------+
| On Entry | Alice           | Alice               |                |
+----------+-----------------+---------------------+----------------+
1 row in set (0.02 sec)
 
+----------+-----------------+---------------------+----------------+
| Where    | Value Parameter | Reference Parameter | Local Variable |
+----------+-----------------+---------------------+----------------+
| On Exit  | Alice           | ALICE               | alice          |
+----------+-----------------+---------------------+----------------+
1 row in set (0.02 sec)
 
+----------+-----------------+---------------------+
| Where    | Value Parameter | Reference Parameter |
+----------+-----------------+---------------------+
| After    | Alice           | ALICE               |
+----------+-----------------+---------------------+
1 row in set (0.03 sec)

Basically, Alice inside the pv_ref_param parameter grows to uppercase during the trip through the wonderland procedure, while Alice inside the pv_value_param remains unchanged. If it didn’t help you learn a principle, maybe it gave you a laugh on how to view the travels of IN-only and INOUT parameters.

NOTE: Line 12 in the alice procedure is impossible with an immutable variable because the value of a call parameter to an immutable IN-only formal parameter shouldn’t allow the call parameter value to change during the execution of the program. This means that MySQL IN-only mode parameter values actually hold a mutable copy of the call parameter and the call parameter can be either a variable or literal value. The parameter value is discarded at the completion of procedure. This is more easily demonstrated with this assignment procedure:

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

CREATE PROCEDURE assignment
( IN  pv_value_param  VARCHAR(20))
BEGIN
 
  /* Query the local and reference parameters. */ 
  SELECT   'Before  ' AS "Where"
  ,        pv_value_param AS "Value Parameter";
 
  /* Call the wonderland procedure that changes the pass-by-reference parameter. */
  SET pv_value_param := UPPER(pv_value_param);
 
  /* Query the local and reference parameters. */ 
  SELECT   'After   ' AS "Where"
  ,        pv_value_param AS "Value Parameter";
 
END;
$$

Therefore a call like this prints an uppercase INBOUND string inside the program but can return nothing to the calling scope since the call parameter is a string literal.

SET @sv_session = 'inbound';
CALL assignment(@sv_session);
SELECT @sv_test AS "Current Value";

The query displays:

+---------------+
| Current Value |
+---------------+
| inbound       |
+---------------+

I hope this helps.

Someone asked how to display errors in the HTML output when they test PHP scripts with the Zend Server CE. Here are the two screen shots that help you enable the display of error and warning messages in your HTML display.

1. Click the Server Setup tab and then click the Error Handling and Logging choice.

2. Click the On radio button for Display errors and then restart PHP at the bottom right corner.

Hope this helps people looking for the navigation.

After updating the AVGFree virus definitions, I was surprised to find that Zend CE (Community Edition) 4.0.6 had a reported worm in the JavaServer.exe file. There was greater surprise when Zend CE 5.3.9 (5.6.0-SP1) also had the same reported worm.

This is the message identifying the worm (click on it to see a full size image), and you can read about this particular worm on the Mcafee site or the AVG threat labs site:

If you check AVGFree’s page, the actual infection isn’t a stated variant, but it appears the heuristics are a bit aggressive.

File Name: C:\Program Files (x86)\Zend\ZendServer\bin\JavaServer.exe
Infection: Win32/DH.FF860061{00000000-00080000-00000000}

Unless you have the full version of AVGFree or another security program to try and fix the file, you can only quarantine the file. Quarantine or removal disables Zend CE from working. It begs the question: “How does Zend release a core file with a worm?” or “Is AVGFree reporting a false positive?”

Update: AVGFree was providing a false positive. In addition to the checks by Zeev at Zend, I created a new test instance with Norton 360 and it likewise found no virus/worm in Zend’s JavaServer.exe file. Hopefully the post will prevent others from spending more than a Google search to sort it out.

Since I use AVGFree on all my Windows 7 VM test instances, it seemed logical to illustrate how to work around this current false positive and annoying quarantining of the core JavaServer.exe file from the Zend Server. There are two sets of tasks, the first requires removing the file from quarantine and the second eliminates future scans from quarantining the file again.

Remove the file from the Virus Vault

1. Launch AVGFree and navigate to the History menu option and choose the Virus Vault option, as shown below.

2. Click the Virus Vault option in the list of the History, which displays the following screen. Click the Infection row and then click the Restore button to remove the file from the virus vault.

3. A confirmation dialog opens and you click the Yes button to proceed.

4. The Infection row is gone When you’re returned to the History dialog. Click the Close button to complete this task.

Exclude the file from future scans

1. Select the Tools menu option and choose the Advanced settings … option, as shown below.

2. Click the Excluded files option in the list of the History, which displays the following screen. Click the Add button to select the file for exclusion. Click the Apply button to effect the change and the OK button to complete the change.

All I can say, one the AVGFree false positive was annoying and it’s dark at 3 a.m. and light the next day.

Thanks to those who knew or surmised it was AVGFree’s heuristics and took the time to add a comment.

Last day of Collaborate 2012 and Scott Spendolini, Sumneva, gave a great presentation on APEX. Only caught the beginning Jan Visser’s Perl presentation because of the distance to the Luxor from the Mandalay South Conference Center and anticipated queuing time for checkout.

We can now look forward to Collaborate 2013 in Denver, Colorado.

Back to observing and working with code, here’s a nice article from MacWorld on how you set up a WebDAV on the Mac. While I’m mentioning Mac OS X and development, there’s still no firm upgrade window for the missing text editing tool – TextMate, and WWDC 2012 tickets sold out in two hours.

Virtualization is important and Dave Welch from the House of Brick gave a great presentation of experiences with VMWare and Tier 1 databases. It was a comprehensive presentation, but the white paper was easier to follow. The slides were complete but the volume of information was a lot for an hour presentation. Well worth the time though.

Utah Oracle User Group (UTOUG) announced a call for Fall Symposium papers today. The Fall Symposium will be in Salt Lake City on 9/6/2012. If you’re interested in presenting on Oracle or MySQL, the call for presentations will be open until 6/15/2012.

The conference party was tonight, and it provided some nice orderves and pizza. The theme was a return to 1980s music, and some folks really dressed their parts. You can listen to a short snapshot of the band by clicking the image to launch a small video segment.

I’m looking forward to the APEX Behind the Scenes presentation at 8:30 a.m. tomorrow. When the conference is over, I won’t miss the smoke filled air that we walk through from the Luxor to the Mandalay. It’s really amazing that the complex is more than a mile in length. It runs from the Luxor to the Mandalay South Conference Center.

It seems the Titanic is everywhere, even inside the pyramid of the Luxor hotel. While the Luxor is within the Mandalay Bay complex, it’s about a half mile walk to the conference and a half mile back. We go by the Mandalay Conference Center’s aquarium. We thought it might be interesting but at $18 an admission, we opted to pass on it. It’s amazing to have an aquarium in the desert, but it’s probably not as nice as the Monterey Bay aquarium.

It was interesting to start the day listening to Rich Niemiec on partitioning tables and using Exadata in Oracle. The NoSQL (Not Only SQL) presentations were interesting, as was the upgrading of Oracle 11gR2 in an E-Business Suite environment presentation. Then, I finished the day with what’s new with the Oracle VM Server.

Checking out the exhibit hall I managed to get a signed copy of Rich Niemiec’s Oracle Database 11g Release 2 Performance Tuning Tips & Techniques and a copy of MongoDB The Definitive Guide.

Collaborate 2012 started on Sunday but for me I began on Monday. I enjoyed Bob Burgess, SalesForce, presentation on shell scripting for MySQL Administration today. It preceded my presentation in the same room, which I thought was an interesting coincidence since we got our conference credentials together.

I presented on portable SQL between Oracle and MySQL. The presentation went well. Before I took questions, I got to ask them because I had three copies of my new Oracle Press book to give away: Oracle Database 11g and MySQL 5.6 Developer Handbook. Handing out the books served as a nice ice breaker for the audience to ask questions about the presentation.

My favorite question was, “Will Oracle continue to improve MySQL?” My answer to that is always simple because Oracle’s support for MySQL has and continues to be great, “Oracle only spends money on winners and that means MySQL wins.” Oracle product management was in attendance and they re-enforced Oracle’s commitment to MySQL.

At 6 p.m., the Exhibit Hall opened and I checked it out. Cisco hired Kathy Bailey to draw caricatures, and she drew mine as you can see at the left. I’m looking forward to more presentations tomorrow.

Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.

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

COLUMN list      FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names     FORMAT A42
COLUMN members   FORMAT 9,990
 
 
SELECT   m.account_number AS account
,        c.last_name AS last_name
,        LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
           WITHIN GROUP (ORDER BY 2) AS names
,        COUNT(*) AS members
FROM     contact c INNER JOIN member m USING (member_id)
GROUP BY m.account_number
,        c.last_name
ORDER BY c.last_name;

It produces the following output:

ACCOUNT    LAST_NAME  NAMES                                      MEMBERS
---------- ---------- ------------------------------------------ -------
SLC-000021 Jonah      Gretelz S.                                       1
SLC-000020 Moss       Jane W.                                          1
SLC-000023 Nathan     Smith B.                                         1
SLC-000024 Potter     Albus S., Ginny, Harry, James S., Lily L.        5
SLC-000022 Royal      Jennifer E.                                      1
SJC-000003 Sweeney    Ian M., Matthew, Meaghan                         3
SJC-000002 Vizquel    Doreen, Oscar                                    2
SLC-000018 Ward       Clinton G.                                       1
SLC-000019 Ward       Brandt H.                                        1
SJC-000001 Winn       Brian, Randi                                     2
 
10 rows selected.

I also found some existing examples you might like, at Oracle-Base, and there they’re showing you how to make it work in prior releases of the database without the new LISTAGG function.

Let’s say you just wanted a concatenated list of users, you could use the following in-line view approach:

1
2
3
4
5

SELECT   list.names
FROM    (SELECT   'List' AS list
         ,        LISTAGG(last_name ||', '||first_name||DECODE(middle_name,NULL,NULL,' '||SUBSTR(middle_name,1,1)||'.'),'; ')
                  WITHIN GROUP (ORDER BY 2) AS names
         FROM     contact) list;

As always, I hope this helps somebody.

While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an error had he replaced the metasequence with .* instead of the solitary *.

The original call to the procedure passes the following well formed regular expression:

CALL prepared_dml('(^|^.+)war(.+$|$)');

Or, they could eliminate the metasequences and use:

CALL prepared_dml('^.*war.*$');

Either returns the following entries from a column with movie titles from the sample code:

Charlie's War
Star Wars I
Star Wars II
Star Wars III

The dot (.) means any possible character, and the plus (+) means one-to-many possible repeating characters of a preceding character. When the dot precedes the plus, it means one-to-many wildcard characters. The student replaced the metasequence with an asterisk by itself and generated the badly formed regular expression error.

The misunderstanding occurs because the asterisk (*) by itself doesn’t mean zero-to-many wildcard. The combination of the dot and asterisk creates a zero-to-many wildcard, which works when there is or isn’t a character before the first character of a string or after the last character of a string. It also eliminates the need for a metasequence.

Here’s a small test case outside of the book’s stored procedure:

-- Conditionally drop the table.
DROP TABLE IF EXISTS list;
 
-- Create the table.
CREATE TABLE list ( list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, list_item VARCHAR(20));
 
-- Insert for rows.
INSERT INTO list (list_item) VALUES ('Star'),(' Star'),(' Star '),('Star ');
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many leading characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many leading characters  "
,        LENGTH(list_item) AS "Length"
,        '^.+Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.+Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many trailing characters"
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many trailing characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.+$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.+$';

The output from the scripts is:

+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many leading characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many leading characters   | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  2 |  Star                            |      5 | ^.+Star.*$         |
|  3 |  Star                            |      6 | ^.+Star.*$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.00 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many trailing characters | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many trailing characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  3 |  Star                            |      6 | ^.*Star.+$         |
|  4 | Star                             |      5 | ^.*Star.+$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.02 sec)

Hope this helps.