MacLochlainns Weblog

The topic of WebSockets came up today, and it seems a good idea to qualify WebSockets in the blog beyond simply pointing folks to Kaazing. HTML5’s WebSockets are a great fix for the half-duplex world of AJAX. They certainly have the potential to be more scalable than current Comet solutions, and present a real-time communication alternative. The latest draft of The WebSocket API was published earlier this month.

If you’re new to WebSockets, you may want to review Peter Lubbers’ PowerPoint presentation from the International Conference on Java Technology, 2010 (or the updated version he provided via a comment to this post). That is probably shorter than watching the multiple parts posted in 10-minute segments on YouTube.

More or less the PDF file of the presentation covers how HTTP is half-duplex not full duplex, and why it doesn’t support real-time data in a browser. It points to AJAX (Asynchronous JavaScript and XML) as a programming nightmare – or more precisely a workaround to a limitation of HTTP/HTTPS. It also covers the idea of COMET programming or frameworks as complex and incomplete attempts to simulate full duplex or bi-directional communication on the web.

As Peter Lubbers qualifies, AJAX and COMET solutions don’t scale against high transaction volumes or concurrency because their header traffic overwhelms the actual data transfers. This reality occurs more or less because browsers only implement unidirectional communication through a request and acknowledgement model and send large header sequences compared to small data footprints.

Peter Lubbers presents three types of HTTP solutions in the presentation:

• Polling involves periodic requests to the server for updated information, and it is the backbone of many Ajax applications that simulate real-time communication. The HTTP message headers involved in polling are frequently larger than the transmitted data; and while polling works well in low-message rate situations it doesn’t scale well. It also involves opening and closing many connections to the server and hence database needlessly in some cases.
• Long Polling or asynchronous-polling requests the server to keep the connection open for a set period of time. It doesn’t solve problems with high-message rates situations of polling in general because it creates a continuous loop of immediate polls and each poll, like ordinary polling messages, sends mostly HTTP headers not data.
• Streaming architecture opens a socket but can cause problems with proxy and firewall servers, create cross-domain issues due to browser connection limits, and periodically pose overhead to flush streams that have built up in the communication channel. Streaming solutions reduce HTTP header traffic but at a cost in overhead to the server.

Websockets are designed to fix these issues. The most interesting thing about polling, long polling, streaming, or Websockets is they require the same careful attention to how databases validate user access and serve up content. When the HTML5 standard nears completion, they’ll be a great need for database connection solutions, like Oracle’s Data Resident Connection Pooling.

By the way, here are some great video links for learning HTML5.

I finally got around to cleaning up old contact me messages. One of the messages raises a question about RESULT_CACHE functions. The writer wanted an example implementing both a standalone schema and package RESULT_CACHE function.

The question references a note from the Oracle Database 11g PL/SQL Programming book (on page 322). More or less, that note points out that at the time of writing a RESULT_CACHE function worked as a standalone function but failed inside a package. When you tried it, you raised the following error message:

PLS-00999: Implementation Restriction (may be temporary)

It’s no longer true in Oracle 11gR2, but it was true in Oracle 11gR1. I actually mentioned in a blog entry 4 years ago.

You can implement a schema RESULT_CACHE function like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION full_name
( pv_first_name   VARCHAR2
, pv_last_name    VARCHAR2 )
RETURN VARCHAR2 RESULT_CACHE IS
BEGIN  
  RETURN pv_first_name || ' ' || pv_last_name;
END full_name;
/

You would call it like this from a query:

SELECT   full_name(c.first_name, c.last_name)
FROM     contact c;

You can declare a published package RESULT_CACHE function like this:

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE;
END cached_function;
/

You would implement the function in a package body like this:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE PACKAGE BODY cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE IS
  BEGIN  
    RETURN pv_first_name || ' ' || pv_last_name;
  END full_name; 
END cached_function;
/

You would call the package function like this from a query:

SELECT   cached_function.full_name(c.first_name, c.last_name)
FROM     contact c;

I hope this answers the question.

Life’s interesting, and as they say, “Things always change.” While there’s a great debate about the future of books and their usefulness in certain formats, there’s also a discussion about whether they should be free, cheap or pricey? This debate is especially interesting in the arena of technology because the life-cycle of books becomes ever shorter as software release cycles narrow.

For example, you can purchase the new Introducing Microsoft SQL Server 2012 printed and bound book (shown in the illustration at the left) at Amazon.com, or you can download it for free from here as a PDF, EPUB, or Mobi file. While I only glanced through it, it looks like a well written and complete book. It’s also free electronically, which begs the questions how the authors and publishers recovered the cost of producing the work.

Is this the future of technical books? After all, technical books exist to smooth the uptake of new software and to humanize the information that is often too verbose (trying to be exhaustive of software uses) or too short (trying to be accessible in the smallest amount of time). It seems there will always be books for these reasons about technology. I would characterize three problems with technology books, and they’re readability, content, and affordability.

• Readability is an interesting trick with a technology book because of how we seems to measure it. Unlike a novel, The Da Vinci Code, technology books seem to be measured on different criteria. The criteria seem to be how well the books expose features, instruct audiences, and provide complete or comprehensive reference; and sometimes, seemingly rarer, they’re read cover-to-cover with an expectation of thematic story telling along with the features, instructions, or reference materials.
• Content should be accurate, concise, and accessible. This is always a challenge as the length of books get larger because the time allotted to the write, shrinks during editing and review cycles. Often the author doesn’t get to review the index or final proof galleys and typos invariable creep in to any book. Typos in code are annoying but incomplete code fragments drive reader’s nuts. Content should include complete programs or modules that enable the reader to test concepts explained in the text.
• Affordability is the largest hurdle because access to information drives success for technicians working with cutting edge technology. Paraphrasing what Alvin Toffler wrote in Powershift: Knowledge, Wealth, and Violence at the Edge of the 21st Century, knowledge or access to knowledge is power, more precisely power that captures wealth.

If vendors, like Microsoft, underwrite books by paying the author and publisher upfront, they remove the risk and vouchsafe financial return associated with producing the book. The likelihood is that the freely distributed copies may no longer yield revenue to the author or publisher, which means the author’s and publisher’s compensation is upfront and limited to a contracted amount. This would operate like the current advance amount, which is only exceeded when the book sells well. This type of arrangement guarantees books at or near production plus supply chain and inventory costs.

If the incentive to write, produce, and maintain (correct problems with) the book are reasonable and the book provides readability, accurate content, and affordability, this may be the future of technical publishing. It certainly begins to lower the barrier to entry cost of their technology. What do you think?

A question came up today about how to stripe a MySQL view, and this post shows you how. Along with the question, there was a complaint about why you can’t use session variables in a view definition. It’s important to note two things: there’s a workaround and there’s an outstanding request to add lift the feature limitation in Bug 18433.

A striped view lets authorized users see only part of a table, and is how Oracle Database 11g sets up Virtual Private Databases. Oracle provides both schema (or database) level access and fine-grained control access. Fine grained control involves setting a special session variable during a user’s login. This is typically done by checking the rights in an Access Control List (ACL) and using an Oracle built-in package.

You can do more or less the same thing in MySQL by using stored functions. One function would set the session variable and the other would fetch the value for comparison in a view.

Most developers who try this initially meet failure because they try to embed the session variable inside the view, like this trivial example with Hobbits (can’t resist the example with the first installment from Peter Jackson out later this year):

1
2

CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = @sv_login_name;

The syntax is disallowed, as explained in the MySQL Reference 13.1.20 CREATE VIEW Syntax documentation. The attempt raises the following error message:

ERROR 1351 (HY000): VIEW's SELECT contains a variable or parameter

The fix is quite simple, you write a function that sets the ACL value for the session and another that queries the ACL session value. For the example, I’ve written the SET_LOGIN_NAME and a GET_LOGIN_NAME functions. (If you’re new to stored programs, you can find a 58 page chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.)

You would call the SET_LOGIN_NAME when you connect to the MySQL database as the first thing to implement this type of architecture. You would define the function like the following. (Please note that the example includes all setup statements from the command line and should enable you cutting and pasting 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

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS set_login_name$$
 
-- Create the function.
CREATE FUNCTION set_login_name(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task. */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT FALSE;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    /* Set the session variable and enable the success flag. */
    SET @sv_login_name := pv_login_name;
    SET lv_success_flag := TRUE;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

You can use a query to set and confirm action like this:

SELECT IF(set_login_name('Frodo')=TRUE,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Or, you can use the actual number 1 in lieu of the TRUE, like this:

SELECT IF(set_login_name('Frodo')=1,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Please check this older post on how MySQL manages logical constants and the realities of TRUE and FALSE constants. A more practical example in an API would be this, which returns zero when unset and one when set:

SELECT set_login_name('Frodo') AS "Login Name Status";

The getter function for this example, simply reads the current value of the MySQL session variable. Like the prior example, it’s ready to run too.

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

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS get_login_name$$
 
-- Create the function.
CREATE FUNCTION get_login_name() RETURNS VARCHAR(20)
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_name;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

Before you test it, lets create a HOBBIT table, seed it with data, and create a HOBBIT_V view. They’re bundled together in the following microscript:

-- Conditionally drop the table.
DROP TABLE IF EXISTS hobbit;
 
-- Create the table.
CREATE TABLE hobbit
( hobbit_id    INT UNSIGNED
, hobbit_name  VARCHAR(20));
 
-- Seed two rows.
INSERT INTO hobbit VALUES ( 1,'Bilbo'),( 1,'Frodo');
 
-- Conditionally drop the view.
DROP VIEW IF EXISTS hobbit_v;
 
-- Create the function-enabled view.
CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = get_login_name();

A query to the table after setting the session variable will only return one row, the row with Frodo in the HOBBIT_NAME column. It also guarantees an unfiltered UPDATE statement against the view only updates the single row returned, like this:

UPDATE hobbit_v SET hobbit_id = 2;

In a real solution, there are more steps. For example, you’d want your tables in one database, views in another, and functions and procedures in a library database. However, I hope this helps seed some ideas for those interested in creating fine-grained virtual private databases in MySQL with user-authenticated application controls.

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.