MacLochlainns Weblog

Oracle Press authors met at the bookstore to sign books for an hour this morning and visit with our editors (shown at left). Then, we went off to the OTN Lounge and JavaOne at the Hilton.

It’s interesting to see different Java solution sets, especially on embedded devices. I also got a good look at the Vaadin server-side AJAX framework. It was interesting. The QuickConnect AJAX framework isn’t represented at the conference, but it’s a free open source project that may interest some.

Java’s dominance in the Oracle product stack is clear. Java is the crux of the SOA architecture for Oracle’s middleware solutions. Interestingly, I’ve met a number who are new to Java. A number of the vendors here are looking for skilled Java programmers, which leads me to see a lot of opportunity for developers.

I snagged a copy of Oracle Essbase and Oracle OLAP today. Oracle Essbase is part of Oracle Fusion Middleware 11g. I’ve began reading the book this evening. It appears a good starting place for those exploring Oracle Essbase and Oracle OLAP solution space.

This was a day with a busy schedule because of the publisher’s seminar. It’s where the authors, like me, learn about Oracle products and market focus. Andy Mendolsohn went over the Oracle Database 11g R2, and other VPs presented BI/ERP solutions and the effect of Oracle’s Essbase Plus (previously known as Hyperion), Java’s position and direction, Solaris’ position and direction, and the Fusion Applications. A summary of the highlights I noted follows:

• The Oracle Database 11g R2 presentation explained the idea of quarterly patch set updates (PSUs), advanced compression, the new Oracle Database Firewall, OCFS (Oracle Clustered File System), and the Exadata Server. Three quarterly PSUs, then a point release is a change but a welcome one.
• Advanced compression qualified three subgroups: (a) A 3 times compression for OLTP systems; (b) A 10 times compression for data warehousing, and (c) a 15 to 50 times compression for archive data. If we apply this with the capital cost figure of 40% for storage as valid, compression may substantially reduce costs.
• They shared Gartner statistics that 82% of Fortune 500 companies use Oracle BI/ERP solutions was very interesting. The idea that the largest customer deploys an Oracle BI/ERP solution to 1 million users turns the demarcation between traditional data warehouse explorers and farmers into a historical footnote. They positioned Oracle BI EE Plus as targeted to the development of dashboard and BI Publisher (formally XML Publisher). Oracle Essbase Plus for Model-OLAP (Online Analytical Processing – focused on resolving the discrepancies between R-OLAP (Relational-OLAP) and M-OLAP (Model-OLAP). Oracle Scorecard and Strategy Management tools are reserved to planning Long-Range Objectivs (LROs), Short-Range Objectives (SROs), and Key Indicators (KPIs). The last requires a carefully modeling of the objective needs of the business and data model capability.
• The Exadata server now comes in two types, the X2-2 and X2-8. The first may be a quarter, half, or full Exadata server while the latter is always a full Exadata server. The X2- has 128 cores, 2 TB of memory, a Linux/Solaris 10 GB Ethernet connection, 14 storage servers with 6 core CPU in each storage server.
• Oracle’s commitment to Java is very solid but a question on NetBeans leaves one to believe that it isn’t part of Oracle’s solution space and it may not see much if any evolutionary change. They said that NetBeans would be supported to help external Open Source developers. My guess is that we should plan to migrate NetBeans or bear the cost of owning solutions with a higher integration cost.
• Solaris has come through the merger with new strength and a commitment to four goals that I caught: availability, scalability, and efficiency, and security. They also committed to make Solaris fully virtualizable, which will more effectively support private clouds. You may also note that Oracle has improved efficiency of MySQL 5.5 on the Solaris platform.
• Fusion Middleware Applications focus on: (a) Best Practice – leveraging the best in class of applications from the Oracle eBusiness Suite, PeopleSoft, JD Edwards, and Siebel; (b) Re-inventing User Experience – A role-based user interface, embedded decision support, and pervasive collaboration; (c) Cloud – Support for both private and public clouds; (d) Platform – Standard-based, configurable, adaptive, and secure Fusion middleware. Customers will have three options for the future. They may continue the current path and upgrade eBusiness Suite to eBusiness Suite, et cetera. Alternatively, they can adopt a co-existence strategy that lets them mix and match components form multiple suites. Lastly, they may embrace the complete suite as a whole.

After the Publisher’s Seminar, we went to the Nikko for Oracle Develop. On the way, we had to cross Powell Street. I snapped a photo of John Harper, co-author of Oracle Database 11g Programming Workbook, with the Cable Car. The Nikko’s door is on the right, and the sessions were on the third floor.

After one session, we went back to the Moscone South Exhibition hall to snag a copy of Guy Harrison’s Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. It’s the fresh version of his prior SQL Tuning books. I’d already bought one two months ago, but I couldn’t resist a signed copy for the office when it was free. I recommend the book as worthwhile and have promised Guy to write a review when I get home.

At the conclusion of the day, John and I went to the Oracle Press party to meet other authors. Ric Niemiec, founder of TUSC and author of Oracle Database 10g Performance Tuning: Tips and Techniques, and we took a photo. He’s also mentioned that the next edition covering Oracle Database 11g will include coverage of the Exadata Server. It’s awesome that he gets to write after the terminal release of the database.

I started the day at JavaOne. It’s at the Hilton on Mason Street. I attended a couple sessions on Java. Then, I went to the JavaDEMO Grounds to check things out rather than catch a sandwich.

There were a few interesting venues but I found the JBoss presentation the most useful on Seam 3. The presentation was worth the time, and the demonstration using the technology at the end was effective for a couple reasons. While the presenter’s environment wasn’t ready, he was able to fix it while working through his discussion of the technical stack. Also, the use of Seam 3 was first class. I’d recommend you stop by if you have a chance to see it.

After seeing the JavaDEMO Grounds, I went back to the Marriott to attend the Data Integration hands-on lab with Data Integrator and the Database Performance Diagnostics and Tuning presentations.

Afterward, a friend and I went to Moscone South to the Exhibition Hall. There we found the air hockey table and other game room. Only one here this year. We finished up by hitting the Moscone South Exhibition Hall. Saw some interesting hardware demos and that’s a wrap for Day 1 at Oracle Open World 2010. Actually, my second day because MySQL Sunday counts in my book.

The merry-go-round is running and Oracle Open World 2010 has begun with MySQL Sunday. The merry-go-round is a fixture at the northwest corner of the Moscone South building, at the intersection shared between the Moscone North building.

The keynote was interesting because Oracle confirmed that they have and will continue to invest in MySQL. The MySQL 5.5 Candidate Release is now available for download. Key features that you’ll note are 200% to 300% improved performance, the InnoDB is now the default engine, backup and recovery are dramatically improved, and the enterprise model is integrated to provide more information about internals and performance.

A little research for the comprehensive new feature list for MySQL 5.5 found a well documented page in the MySQL 5.5 Reference. Examples of things left out of the presentation, not an inclusive list of all features, are: improved operation on the Solaris platform (surprise ;-)), support for semisynchronous replication, support for SQL standard SIGNAL and RESIGNAL statements, support for Unicode character sets, a LOAD XML statement, expanded partitioning options – including the ability to truncate only a partition of a table, and may new command options.

Ronald Bradford gave a good presentation on MySQL idiosyncrasies. I thought spelling out behaviors common to Windows and Mac platforms that differ from Linux and Unix platforms was great. He’s got a new book that he co-authored on MySQL and PHP. I’ve look into the book yet.

I also enjoyed Giuseppe Maxia’s presentation on partitioning. His regular web site is here, but beware if you’ve a slow web connection because the photos delay page loading. 😉

Update on MySQL Sunday

Here are some links provided by Oracle to presentations:

• Edward Screven’s presentation
• Live Webinar: “Delivering Scalability and High Availability with MySQL 5.5 Replication Enhancements

Summer’s almost over and it time to start blogging again. It’s strange to think that Oracle OpenWorld is only a month away, and MySQL and JavaOne are all in the same event. I’m looking forward to it! As I did two years ago on my MacBook Pro, I’ll blog during the event on my iPad.

Anyway, I want to provide an observation about the HP LaserJet 1606dn and HP support. As a late reminder to me, always test purchased equipment within the return window. I failed in this case and Murphy’s law struck. I bought a DOA HP LaserJet 1606dn from Staples. I must go through the RMA process because I didn’t take it out of the box until after the 14 day return policy.

I thought explaining it to a store manager might allow me to return it, but I was wrong. Their policy is ironclad. It’s actually a wonderful example of why Staples’ Easy Button ISN’T EASY for consumers when you fail to test the product within 14 days of purchase. Make that 13 days, so it can be returned on the 14th day.

Anyway, HP’s Support Chat Queue works well. It was responsive but the RMA policy leaves much to be desired in a world of identity theft. Their shipping group wants to call you at their convenience to secure a credit card for the RMA. They don’t provide you with a number that you can confirm, or a process that supports your schedule. Clearly, the cost of support is more important to HP than customer satisfaction.

I’ll update what happens as it all sorts out. The problem with the HP LaserJet 1606dn is that it doesn’t recognize ink in the LaserJet toner cartridge. It appears to be a sensor defect but the support staff person maintained excellent transparency, which means I’ll never know for sure if they shipped a bunch of bad units. The quickness in agreeing with the sensor failure gave me the impression that they may know something they’re not saying. Alas, we may never know for sure …

The moral of the story is always: buy, immediately test, and return failures to avoid RMAs. Especially, do so with brick-and-mortar stores like Staples, so you can walk it right back for a full refund and avoid the RMA.

After I dig into their Registry management, I’ll have more to say about working around HP’s approach to drivers. Their approach requires uninstalling the printer software and then re-installing it when you use DNS licenses for the printer IP address. It appears their logic is in a combination of configurations files and the Windows Registry. When I sort it out, I’ll post it and how to work around it. Oops, that’s hack it, isn’t it. 🙂

This week I attended the first ACM conference on Bioinformatics and Computational Biology in Niagara Falls, NY. The next conference is in Rome next January. It was interesting to note who’s using what technology in their research.

Here’s a breakdown:

• Databases: MySQL is the de facto winner for research. Oracle for clinical systems, mostly Oracle 10g implementations. That means moving data between the two is a critical skill. Specifically, exporting data from Oracle and importing it into MySQL. Oracle was criticized for being a DBA-preserve and unfriendly to development. When I probed this trend, it seemed to point to DBAs over managing sandbox instances at companies with site licenses. Microsoft SQL Server didn’t find a lot of popularity in the research community.
• Programming Skills: C#, C++, Objective-C and PHP were high on the list. C# to import data into Microsoft SharePoint and develop Windows SmartPhones. C++ to extend MySQL. Objective-C to develop iPhone and iPad applications. PHP to build applications to manage studies and facilitate input, but there were a couple using Perl (not many).
• Collaboration Tools: Microsoft SharePoint won handily. It’s made a home in the clinical and research communities.

Overall, they want programmers who understand biology and chemistry. They’d like knowledge through Medical Microbiology and Introductory Biochemistry, and they want strong math and statistical knowledge in their programming staff. They like Scrum development frameworks. They seem to emphasize a chief engineering team, which means the developers get maximum face-time with the domain experts. The developers also have to speak and walk the talk of science to be very successful.

As to Niagara Falls, I’m glad that I took my passport. The Canadian side is where I spent most of my extra time and money. It has the best views of the falls, the best food, and ambiance. Goat Island and the Cave of the Winds are the only two features I really liked on the U.S. side of Niagara Falls. The U.S. side is dreary unless you like gambling in the Seneca Niagara Casino & Hotel. Since I’m originally from Nevada, I never entered it to check it out. Technically, when you step on the casino property you enter the Seneca Nation of New York. The New York state government in Albany really needs to address the imbalance or they’ll continue to see Canada score the preponderance of tourist dollars.

I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.

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

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:sql="org.apache.xalan.lib.sql.XConnection"
  extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
  name="movies"
  select="sql:new('com.mysql.jdbc.Driver','jdbc:mysql:///storedb','student','student')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
  name="queryResults"
  select="sql:query($movies,'SELECT i.item_title, i.item_asin, i.item_release_date FROM storedb.item i')" />
<html>
<head><title>MySQL Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
  <tr>
    <xsl:for-each select="$queryResults/sql/metadata/column-header">
      <th><xsl:value-of select="@column-label" /></th>
    </xsl:for-each>
  </tr>
  <xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
  <tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
  <td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>

Hope this helps those looking for a solution.

Somebody posted a comment inquiring about the release of Oracle 11g XE. They felt it had been delayed. As far as I know, the last word on that came from Andy Mendelsohn last October, as published in this InfoWorld article.

The Oracle Database 11g XE is released and the download link is here.

When Andy Mendelsohn originally announced Oracle 11g XE in an interview with the NY Times, he clearly said it would be based on the terminal release of Oracle 11g. I also suspect it’ll include APEX 4.0, which was recently released. Maybe we’ll see the release at or near Oracle Open World 2010.

I don’t endorse the negative remarks in the InfoWorld article about why Oracle 11g XE isn’t released. While I have no direct knowledge of the forthcoming release, I think there’s an alternative explanation for any delay. Based on my involvement with the Oracle 10g XE release, I believe Oracle wants a rock solid starter version. At least, that was a major concern when they released Oracle 10g XE.

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

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

UPDATE member m
SET    member_type =
 (SELECT common_lookup_id
  FROM   common_lookup
  WHERE  common_lookup_table = 'MEMBER'
  AND    common_lookup_column = 'MEMBER_TYPE'           
  AND    common_lookup_type =
          (SELECT  dt.member_type
           FROM   (SELECT   c.member_id
                   ,        CASE
                              WHEN COUNT(c.member_id) > 1 THEN 'GROUP'
                              ELSE 'INDIVIDUAL'
                            END AS member_type
                   FROM     contact c
                   GROUP BY c.member_id) dt
           WHERE   dt.member_id = m.member_id));

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

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

CREATE OR REPLACE FUNCTION get_member_type
(pv_member_id NUMBER) RETURN NUMBER IS
 
  -- Define a collection of strings.
  TYPE type_options IS TABLE OF VARCHAR2(10);
 
  -- Declare local variables.
  lv_dependent NUMBER := 1;
  lv_return_id NUMBER;
 
  -- Declare a local variable of the collection.
  lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP');
 
  -- Define a dynamic cursor to count the number of foreign key values. 
  CURSOR count_contact (cv_member_id NUMBER) IS
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = cv_member_id;
 
  -- Define a dynamic cursor to find a key for an individual or group member type.    
  CURSOR get_lookup_id (cv_type VARCHAR2) IS
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_context = 'MEMBER'
    AND    common_lookup_type = cv_type;
 
BEGIN
 
  -- Get the number of foreign key values for a contact.
  OPEN  count_contact(pv_member_id);
  FETCH count_contact
  INTO  lv_dependent;
  CLOSE count_contact;
 
  -- Open the dynamic cursor with the required value.  
  IF lv_dependent = 1 THEN
    OPEN get_lookup_id(lv_member_type(1));
  ELSE
    OPEN get_lookup_id(lv_member_type(2));
  END IF;
 
  -- Get the correct surrogate primary key value.
  FETCH get_lookup_id
  INTO  lv_return_id;
  CLOSE get_lookup_id;
 
  -- Return the correct primary key for use as a foreign key.
  RETURN lv_return_id;
END;
/

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

SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement";
DROP FUNCTION IF EXISTS get_member_type;
 
SELECT 'DELIMITER $$' AS "Statement";
DELIMITER $$
 
SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$
CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT 
BEGIN
 
  /* Define three local variables. */
  DECLARE lv_contact_number  INT;
  DECLARE lv_member_type     CHAR(30);
  DECLARE lv_return_value    INT;
 
  /* Define a dynamic cursor to count the number of foreign key values. */  
  DECLARE contact_cursor CURSOR FOR
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = pv_member_id;
 
  /* Define a dynamic cursor to find a key for an individual or group member type. */
  DECLARE common_lookup_cursor CURSOR FOR
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_table = 'MEMBER'
    AND    common_lookup_column = 'MEMBER_TYPE'           
    AND    common_lookup_type = lv_member_type;
 
  /* Get the number of foreign key values for a contact. */    
  OPEN  contact_cursor;
  FETCH contact_cursor
  INTO  lv_contact_number;
  CLOSE contact_cursor;
 
  /* Assign group membership when more than one foreign key value is found; and
     assign individual membership when only one foreign key value is found. */  
  IF lv_contact_number > 1 THEN
    SET lv_member_type = 'GROUP';
  ELSE
    SET lv_member_type = 'INDIVIDUAL';
  END IF;
 
  /* Get the correct surrogate primary key value. */  
  OPEN  common_lookup_cursor;
  FETCH common_lookup_cursor
  INTO  lv_return_value;
  CLOSE common_lookup_cursor;
 
  /* Return the correct primary key for use as a foreign key. */  
  RETURN lv_return_value;
END;
$$
 
SELECT 'DELIMITER $$' AS "Statement"$$
DELIMITER ;

You can query the results of the update statement with the following.

1
2
3
4
5
6
7
8
9
10
11

SELECT   m.member_id
,        dt.quantity
,        m.member_type
,        cl.common_lookup_type
FROM     member m JOIN (SELECT   member_id
                        ,        COUNT(c.member_id) AS quantity
                        FROM     contact c
                        GROUP BY c.member_id) dt
ON       m.member_id = dt.member_id JOIN common_lookup cl
ON       m.member_type = cl.common_lookup_id
ORDER BY m.member_id;

As always, I look forward to helping and gaining insight.

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

SET @counter := 0;
SET @msg := '';

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$

SELECT debugger();
SELECT @msg;

+------------+
| @msg       |
+------------+
| [Debug #1] |
+------------+

SET @counter := 0;

CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;

1
2
3
4
5
6
7

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$

SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

+---------------+
| debug_comment |
+---------------+
| [Debug #1]    |
| [Debug #2]    |
| [Debug #3]    |
+---------------+

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

-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that returns 1.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Declare session level variables.
SET @counter := 0;
SET @msg := '';
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to local session variables.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT @msg;
 
-- Declare a session level variable.
SET @counter := 0;
 
-- Conditionally drop the function when it exists.
DROP TABLE IF EXISTS debugger;
 
-- Create a temporary (in-memory) table to record debugging information.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to a debugging table.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;