All the Perl that's Practical to Extract and Report

Cold ConFusion and SQL Butcher

avik on 2005-01-28T14:49:43

My company uses only ColdFusion and SQL Server, so I'm learning new things, for example:

  • In Access I used to write IIf(if_condition,true_result,false_result), but now in SQL Server I have to write CASE source_value WHEN target_value THEN true_result ELSE false_result END

  • Never thought that select str(3.1415927) as foo would return 3

  • How awful that Format(Now(),'yyyy-mm-dd') must now be written as left(convert(varchar(12),getdate(),120),10) to get me the same output.

  • String & ' ' & Number & ' ' & String works fine in Access, but in SQL Server it must be written as string + ' ' + Cast(number as varchar) + ' ' + string but why the pluses if we don't even add anything, we're concatenating, right?

  • Cold Fusion's cfquery causes all single quoted characters to be auto-escaped, by doubling them. Good in 90% of cases, but what if you are doing dynamic SQL think with interpolation of single-quoted values? Then that becomes a mess. You must now use tag in order to to be used in PreserveSingleQuotes(sql) manner. This is not intuitive. I spent about an hour to find an answer.

  • Documentation sucks big time for SQL server. Documentation is little better for ColdFusion, but not clear enough in many cases.

  • I deeply miss relative simplicity and friendly uglinness of Perl + DBI + DBD::ODBC + CGI.pm

    • SQL Server docs

    runrig on 2005-01-28T17:15:00

    I find most of what I need in the 'Help' for the MS Query Analyzer. I can't stand all the proprietary built-in functions of different databases though. If I had a choice, I'd use only generic (as generic as possible) SQL, and munge the results later in the code. But that isn't always possible, and some languages don't make it as easy to munge as perl does, so in our (non-perl) code we have a home-grown 'sql_parse()' function which tries to convert these things and handles most of it, but there's still alot of "if it's SQL Server do this, if it's Oracle do that, ..." throughout the code.

    Re:SQL Server docs

    avik on 2005-01-28T19:11:54

    Exactly! "Cool, yet proprietary" things are one programmer's bread and butter, but another programmer's headache and tears.