The K Guy

In the past three posts, we have written metaprograms to create functions for comparing objects of built-in types, user-defined distinct types, ROW types, and ordinary ARRAY types. In today’s post, we will write a single metaprogram that can create comparison functions for all four. Continue reading ‘Metaprogramming in SQL (Part 4)’

In the first post in this series, I introduced the concept of metaprogramming in SQL using dynamic SQL and applied the technique to write a procedure that could generate comparison functions that compare two objects of any basic type. In the second post, I expanded the procedure to produce comparison functions that compare two objects of any ROW type. In today’s post, I will introduce the ARRAY type from DB2 9.7 and show how we can go about comparing two arrays, including arrays of ROW type objects. Continue reading ‘Metaprogramming in SQL (Part 3)’

In the last post on this subject, we used dynamic SQL to generalize an integer comparison function into a stored procedure that builds comparison functions of any basic type. This served as a simple example, but did not really offer much benefit over using the ‘=’ operator. In today’s post, I will extend the same technique to make functions that compare ROW objects just as easily, even though the ‘=’ operator cannot be used on ROW objects. Continue reading ‘Metaprogramming in SQL (Part 2)’

Metaprogramming can feel like magic. You call a function that you neither wrote nor imported from any library and, magically, it comes back with a result. Even more magical is how metaprogramming lets you do otherwise impossible things with your programming language. In “The Art of Metaprogramming”, Jonathan Bartlett’s developerWorks series, he lists three examples that illustrate the benefits of metaprogramming:

• You can use metaprogramming to pre-generate tables of data for use at run-time.
• In applications with large amounts of boilerplate code and limited ability to abstract this code cleanly into functions, you can use metaprogramming to create a mini-language to write the boilerplate for you at run-time, simplifying your own code.
• You can use metaprogramming to transform a programming language that promotes verbosity into one that celebrates terseness. In addition to making up for inadequate language design, this can also ease maintenance.

Examples abound. A famous one is Ruby On Rails’ ActiveRecord class inspired by Martin Fowler’s ActiveRecord design pattern. At least in early versions of Rails, you could simply create an empty class like the following:

class Employee < ActiveRecord::Base
end

and, assuming you had a division column and mgrlastnm column in the employees table in your database, you could write code like Employee.find_by_division_and_mgrlastnm "Sales", "Simpson" and it would just work. The Rails metaprogramming logic would detect that such a function did not exist and would generate the missing function at run-time.

So how does all this relate to SQL? In this post and others, I will answer that question and attempt to illustrate the benefits of metaprogramming in SQL PL. Continue reading ‘Metaprogramming in SQL (Part 1)’