Chapter 23. Arrays

for RuBoard

If you would master the craft of programming do this: never stop learning. Never be satisfied with what you know, always purpose to improve and add to it somehow, in some way. Craft first your mind, then spend the rest of your life letting it craft you.

H. W. Kenton

My previous book, The Guru's Guide to Transact-SQL , included a chapter on array processing in Transact-SQL that presented a number of ways of working around the fact that Transact-SQL doesn't have built-in array support. These techniques originated from two methods of approximating array support in the language: using tables (with columns simulating array dimensions) and using long varchar columns (with an entire dimension stored in a single column). Although mimicking arrays is better than not having them at all, I took a different approach in this book. I decided to add array support to Transact-SQL myself .

You'll recall from the Preface that I said that the secret to mastering Transact-SQL stored procedure programming was to be found in mastering programming itself, and that one key way of improving one's overall programming skill was to learn multiple programming languages. You'll recall that I mentioned the concept of cross-pollination and the perspective one gains from learning languages with completely different paradigms for creating software.

This chapter is evidence that that philosophy works. That learning multiple programming languageseven those seemingly unrelated to Transact-SQLwill make you a better stored procedure programmer and will open your eyes to techniques and possibilities you might not have otherwise thought of. In this chapter I bring together concepts I've learned from several different languages, including C/C++, Clipper, and, of course, Transact-SQL.

"Clipper?" you ask. C and C++ might seem at least relevant because you know by now that we build extended procedures using them, but Clipper? Yes, Clipper. For those of you who aren't yet sporting your first (or second) gray hairs, Clipper is a compiler for a dBase-style database programming language that had its heyday before Visual Basic came on the scene (and, in case you're not familiar with dBase, it was a programmable database program similar to Microsoft Access or Borland Paradox that was most popular before Access came into its own). Clipper still exists, but it gave up its place as the premier PC database programming language long ago. One of Clipper's niftier features was that it added arrays to the dBase programming language. It did this through functions (dBase UDFs were also a Clipper innovation). It provided functions for creating arrays, searching them, destroying them, and so forth. Behind the scenes, these functions called C code that actually manipulated the arrays. Because C code actually did the slicing and dicing of the arrays, Clipper's implementation was relatively fast, and because it was set up through simple functions, it was easy to use. As you'll see in just a moment, I've taken this same approach in adding array support to Transact-SQL. I've created UDFs that wrap calls to extended stored procedures written in C/C++. These extended procedures handle all the real work of manipulating the arrays. The array processing is fast because it's natively compiled, and it's easy to use because it's accessible through UDF calls.

WARNING

Use these routines with care and at your own risk. Failing to deallocate an array will make its memory unavailable to SQL server.


for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net