RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Bring Array Mapping Capabilities to SQL

Simplify your SQL tasks by giving your queries and stored procedures the ability to iterate over arrays of table names or values.

QL has been evolving—slowly—for over three decades, (see Wikipedia for a historical summary). While SQL in the hands of an expert is quite powerful, it's sometimes cumbersome compared to other programming languages. It does not scope variables the way modern, structured languages do, and lacks some basic functionality. However, extending the language with stored procedures can help. The general-purpose stored procedure described here enhances the expressive power of SQL by performing some useful array, matrix, and list-processing tasks. For example, with one line of code you can:

  • List the number of rows of each table in an arbitrary list of tables in a single result set.
  • Feed the result of an arbitrary query to a template of an INSERT statement for a different table, and then execute each of those INSERT statements.
  • Re-seed an arbitrary set of tables with the same seed value, or each with a different seed value.
  • For any group of tables, show the number of rows where a given field is above a certain threshold, even if the field name varies from table to table.
  • For a set of users and a set of permissions, enable each permission for each user (i.e. all permutations).
This is a powerful routine to add to any SQL library, letting you accomplish tasks in minutes that would previously have taken hours.

First, you need a little background on the concept of mathematical mapping (as opposed to geographic mapping). Perl and C# work well for this because they have powerful mapping capabilities that help make the subsequent explorations into SQL more fruitful. Then you'll see some SQL examples using the SP_map stored procedure that accompanies this article. These function as more than mere examples, however; they serve as platforms for launching into a discussion of the program's key features and the full stored procedure API. Finally you'll see details on installing the stored procedure in your environment.

Mapping features are commonly found in functional programming languages, which are less commonly used than procedural or imperative languages. A key distinction, as stated on the Wikipedia page, is: "Imperative programs tend to emphasize the series of steps taken by a program in carrying out an action, while functional programs tend to emphasize the composition and arrangement of functions, often without specifying explicit steps."

Perl is typically considered an imperative language but may be used in the style of a functional language as well. In particular, it provides an exceedingly useful mapping capability. A simple example is mapping an array of integers to their ASCII-equivalent characters, as illustrated in Figure 1, and that you can accomplish with any of these three one-liners:

   @chars = map(chr, @nums);
   @chars = map { chr } @nums;
   @chars = map { chr($_) } @nums;
Figure 1. Basic Array Mapping Illustration: Two equivalent views of mapping an array of inputs to an array of outputs using the Perl chr() function.
The map operator is not limited to serving as a function conduit; it can also serve as a processing operator, for example:

   %hash = map { getkey($_) => $_ } @array;
In words, this one-line statement says to feed each element of the input array (@array) to the code block of the map (getkey($_) => $_) and store the results in the hashtable (%hash). That (perhaps arcane looking) map invocation is exactly equivalent to this more traditional procedural form:

   %hash = ();
   foreach $element (@array) {
       $hash{getkey($element)} = $element;
Without knowing Perl, you could probably surmise that the code fragment loops through the given array and builds a hash table.

Author's Note: The preceding examples were taken from Perldoc's map documentation page.

Functional map expressions in Perl can become quite intricate, as in this example of the Schwartz sorting transform popularized in the quintessential Perl Cookbook, section 4.15, which sorts structures by a particular field contained within each record:

   @ordered = map { $_->[1] }
              sort { $a->[0] <=> $b->[0] }
              map { [compute(), $_] }
Even if you're not enthusiastic about Perl, it's useful to show a somewhat canonical example of mapping. Now take a look at the C# equivalent. C# (version 2.0 or greater) provides the ConvertAll generic method, which is effectively the same as Perl's map function. The article entitled Functional List Processing in C# 2.0 presents this handy example:

   List outputList =
       list.ConvertAll(delegate(string s) { return s.ToLower(); });
The preceding code defines an anonymous function as a delegate, passes that to the map method (ConvertAll) of your original List object (list), and returns a new List with all members converted to lowercase. For reference, the Perl equivalent is:

   @outputList = map { tr/A-Z/a-z/;$_} @list;
If you attempt to use the C# version given above, you will likely get the error "The type arguments for method ConvertAll cannot be inferred from the usage. Try specifying the type arguments explicitly." Seems reasonably clear and helpful, but figuring out syntactically how to correct it is far from obvious. Here is the revised version with an explicit type added:

   List outputList =
       list.ConvertAll(delegate(string s) { return s.ToLower(); });
And just as Perl can produce complex transformations by chaining, so can C#. This example from the same article generates a list of all files larger than a given size:

   List paths = new List(
      return paths.ConvertAll( File.OpenRead )
         .FindAll( delegate(FileStream f) { return f.Length >= bigLength; } )
         .ConvertAll( delegate(FileStream f) { return f.Name; } );
That gives you the flavor of mapping in the domain of conventional programming; the next section introduces you to some examples showing how you can use mapping in real-world database programming.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date