T-SQL Pseudo-Constants

Stardate: 11047.9

How many times have you come across unfriendly T-SQL such as “IF( @statusID = 1 )” wishing instead for a more meaningful encounter such as “IF( @statusID = ActiveCustomer)”?  And if you think that is bad, try searching for all logic pertaining to active customers!  Right from CS-101, littering code with non-obvious literal constant values is strongly discouraged which is why it is really surprising that even after the release of SQL Server 2008, there is no native support for constants in T-SQL.  While we are on the subject of missing language constructs, another addition that I would really like to see in T-SQL is support for an Oracle-like “CREATE OR REPLACE” to obviate awkward “IF EXISTS…DROP…CREATE” sequences not to mention the associated chain of security processes to reapply access control.

In fairness, a CLR-based mechanism was introduced (which I will cover in a later post) to support constants in T-SQL with SQL Server 2005.  However, that requires deploying .NET assemblies making the implementation disjointed at best.  In any case, for all T-SQL developers out there, here is a work-around for implementing constants entirely in T-SQL!

First, because support for a “CREATE OR REPLACE” syntax is missing, create a helper procedure as follows (this is not entirely necessary, merely for convenience):

PROCEDURE ei_const

Next, create pseudo-constants to your heart’s content  as follows:

Defining Constants

And there you have it!  Never again be faced with the frustration of coming face-to-face with: “IF( @statusID = 1 )”

The Dark Side

Stardate: 11038.0

A few years ago, I – a Microsoft Windows man – would have firmly and unequivocally dismissed the idea of ever using a Mac.  After all, “I’m a PC” – right?  Well, I must confess, I have inched over to the dark side – if ever so slightly.  Due to my crazy travel schedules, I figured it would be prudent to buy a light-weight laptop with a long battery life.  Enter the Mac.  Specifically, the 13″ MacBook Pro MC375LLA with (apparently) a 10 hour battery life!

Ordinarily, the 10 hour (or even if it were 50 hour) battery life would not have pushed me over the PC-Mac divide considering that all my software – both what I use and what I develop – runs on Windows!  But that’s where things have improved considerably on the Parallels/Bootcamp front.  Although, either option would have been fine, I opted for Bootcamp.  And here is where things got interesting.

As part of our corporate standard – rooted in the notion of separation of concerns – the OS partition should be separate from the data partition.  This is easily done on any PC; while setting up Windows, you simply split up the drive into multiple partitions and that’s it.  However, with Bootcamp, it isn’t quite that simple.  After reading various tech blogs including some that prescribed a “simple” almost 15-step process choke full of cryptic partition manipulating commands, I came to the conclusion that there has got to be an easier way.  There was.

  1. Create a Bootcamp partition large enough for the OS and go through the Windows installation process.
  2. After – and only after – Windows is installed, start the “Disk Utility” from the Mac OS.  You should see two partitions – the Mac HD and Bootcamp – under the primary drive.
  3. Now create a new partition from the Mac HD – I named it “elixir” – again part of the standard.
  4. Restart in Windows and format “elixir” to NTFS!

That simple.

Leapfrogging TOAD

Stardate: 11011.2

Sometimes time-saving tools can be fairly time-consuming. TOAD claims this dubious distinction for its auto reformat feature. Our DBAs often use TOAD to apply packages and other PL/SQL code on Oracle databases. Now, TOAD has this feature where it can beautify your code. What that translates into is that PL/SQL on the database cannot be directly matched against (for parity checks) the PL/SQL in the version control system!  So, our challenge was to find some way to quickly but accurately compare these two versions.  Here is how our engineers leapfrogged TOAD:

Run the following 4 regular expression find-and-replace operations on both files (we used Visual Studio 2008 but the same can be accomplished by sed or any other utility)

  • Remove all newlines in the files so that we end up with one very long line of code.  Step 1: Replace ‘\n’ with ‘ ‘
  • Replace all tabs and other whitespace with a simple space.  Step 2: Replace ‘:b’ with ‘ ‘
  • Replace multiple consecutive spaces with a single space.  Step 3: Replace ‘:b+’ with ‘ ‘
  • Replace all semi-colons (PL/SQL statement terminator) with a semi-colon and newline.  Step 4: Replace ‘;’ with ‘;\n’

Voila!  Now we have two files that (although not very easily readable) can be compared for parity!


Stardate: 11005.5  

As promised in https://bkarim.wordpress.com/2010/01/13/stardate/, here is a simplified snippet of C# code to compute stardates!  Enjoy!  😀 

Simple Stardate in C#

Simple Stardate in C#


PS for those who would rather see the code in plain text:

DateTime utcNow = DateTime.UtcNow;
double century = Math.Floor( utcNow.Year / 100.0 );
double starCentury = century – 19.0; //Hack: In order to keep it consistent with Star Trek
double starYear = utcNow.Year – ( century * 100.00 );
double starDay = ( utcNow.DayOfYear * 24.0 ) + utcNow.Hour;
double totalHoursInYear = ( DateTime.IsLeapYear( utcNow.Year ) ? 366 : 365 ) * 24;
double starDate = ( starCentury * 10000.0 ) + ( starYear * 100 ) + ( starDay * 100.0 / totalHoursInYear );

Mad hatter

Stardate: 11001.4

Is it just me or does the must-learn space expand exponentially and almost immediately with respect to one’s current knowledge base?  It seems as though the more I learn, the more I discover is out there to master.  Reminds me of the limitations to approaching light-speed captured by the famous E = mc2 relationship; yet another naturally occurring asymptote perhaps programmed in existence to avoid the God-complex!

Why this train of thought?  I guess it began with a call earlier today discussing various upgrades to our software platforms and the new technologies on the horizon.  Specifically, we were discussing strategic initiatives that must be incorporated in our technology roadmap.  Now, any such discussion that is void of resulting implications to the end-users and their line of business is dangerously myopic.  So, that lead us down the path of trying to divine the evolution of various industries and segments including healthcare, utilities, field-service, distribution, and so on.  And then to anticipate their technology needs and the optimal means of addressing those needs – see where I’m going with this?  Well, neither do I!  ;-D  But, I’m sure that as we continue these meetings over the course of the next few weeks, a lot of this will begin to crystallize – at least that is the hope 😀

And this is perhaps the basis (at least partially) for those natural asymptotes; So that we would continue to learn,  continue to find challenges and experience the exhilaration on surmounting them!  (Only to find more challenges, …)

MapPoint 2010 and .Net

I have noticed that even the simplest of tasks – if performed infrequently – become challenging.  One example, upgrading the MapPoint control.  Since new releases only happen every year or every other year, it becomes increasingly difficult to assert with a reasonable degree of confidence that nothing was missed in the upgrade process!  Of course, upgrades are *much* easier than the initial implementation.

So, for all those who have so badly wanted to do add MapPoint functionality in their .Net applications but didn’t have a cheatsheet, here is a peek at our process for integrating MapPoint in your Visual Studio applications.  Enjoy!

  1. Copy the MapPointControl.ocx and MPNA83.tlb (from the MapPoint installation folder to your working folder)
  2. From your working folder, run AxImp
    • For instance: Aximp /keycontainer:YourKeyContainer /source MapPointControl.ocx /out:AxMappoint.dll
    • Where YourKeyContainer is the name of your certificate container for code signing.  Quick note: code signing is optional but I would strongly recommend it
  3. This will generate 3 files:
    • MapPoint.dll
    • AxMappoint.dll
    • AxMappoint.cs
  4. Next, you will need to run the type library importer as follows:
    • tlbimp MPNA83.tlb /namespace:MapPointApp /out:MapPointApp.dll /asmversion:M.m.B.R /keycontainer:YourKeyContainer
    • Where M.m.B.R is simply the Major, minor, build, and revision numbers that you wish to assign to the generated MapPointApp.dll
    • Once again, code-signing is optional but recommended
  5. Almost there! Here is the final step (needed only when code-signing)
    • “C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\signtool” sign /i Issuer /n CertificateSubjectName /t TimeStampServerURL /v MapPoint.dll
    • “C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\signtool” sign /i Issuer /n CertificateSubjectName /t TimeStampServerURL /v AxMapPoint.dll
    • “C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\signtool” sign /i Issuer /n CertificateSubjectName /t TimeStampServerURL /v MapPointApp.dll
    • “C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\signtool” sign /i Issuer /n CertificateSubjectName /t TimeStampServerURL /v MappointControl.ocx
    • Where Issuer is the name of the certificate issuing authority, CertificateSubjectName is well the subject name on the certificate and the TimeStampServerURL is also jsut that
    • The /v option is to enable verbose mode

Software upgrades

Tis the season I guess. I have at least 5 different applications/toolsets that need to be upgraded. Somehow, even for a techno geek such as myself, upgrades are always accompanied by a sense of apprehension. A fear of rejection of the upgrade that sets the entire system into a spiral of degeneration leading to multiple untraceable BSDs or an apparent slowdown of the overall computer system.

Having said that, it is time to overcome my fears for as the old adage goes, no guts no glory. Will report later on the overall success/frustration ratio.