Voice of the DBA

Tag: error handling

  • Do You Have ALL the YAML?

    YAML is a file format, and as with many formats, some of us love them and some of us hate them.  It seems to be better than XML in many ways, and perhaps easier to deal with the JSON. It might not be better than csv/tsv/delimited formats for large transfers but for many of us, it’s a nice format for configuration items.

    While the format felt fairly intuitive to me, and it’s not hard to write, it is quite persnickety about whitespace. This makes using some plugin, like the Red Hat YAML extension for VS Code, important to help you prevent mistakes. Even as easy as the format can be to read and understand, it’s also easy to make mistakes with the whitespace as you indent and try to add subkeys.

    I was watching an AWS talk, and there was an interesting note about using YAML for control planes and being sure that you have some sort of checksum if you do. Why? Because you aren’t sure if you have the entire file. A YAML file could be truncated in any file transfer, and it would still appear to be valid. Hearing that made me realize that those annoying closure tags in XML and JSON might have some value.

    Those of you that work with YAML, how are you sure you got the entire file? Is there something you’d program in? Do you checksum the file and pass that along? Do you include a required, closing key:value tag of some sort? I don’t, but I might think about doing so in any place where an invalid or incomplete file might cause me problems. This certainly seems like something you’d want in a control file, like one used for Kubernetes.

    In most cases, we assume if we can read a file, then we have the complete file. I don’t know of many customers that require some sort of checksum or validation for a file. Certainly, if a CSV or TSV was missing rows, the file might still appear valid to an import process. XML and JSON should have a closing tag or character, so we’d hope we could catch this, but maybe not.

    Moving around data through files, especially data used to drive processes, should include some error handling. That would mean that we have some way to detect if part of our file is missing. There are ways, but it seems that in many cases we’ve gotten lazy about implementing them in file transfers. Certainly, I don’t see people adding a checksum to their YAML files, which seems like something that we’d want to require.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Basic TRY..CATCH

    Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result. 

    However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.

    The Syntax

    The basic syntax is this:

    BEGIN TRY

    — do some work here.

    END TRY

    BEGIN CATCH

    — error handling code here.

    END CATCH

    This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.

    Using TRY . . CATCH

    The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:

    BEGIN TRY
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
    END TRY

    BEGIN CATCH

    — CATCH BLOCK

    END CATCH

    SELECT @@rowcount

    The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.

    However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.

    Example

    Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.

    ALTER PROCEDURE spGetCommission
    @userid INT
    AS
    PRINT ‘Before TRY’;

        BEGIN TRY
            PRINT ‘Start TRY’;
            SELECT TOP 10
                    cs.CustomerID
                ,   cs.LastSale
                ,   cs.Salesman
                ,   CAST(cs.SaleValue AS NUMERIC)
                FROM
                    dbo.CustomerSales AS cs;
            PRINT ‘End TRY’;
        END TRY
        BEGIN CATCH
            PRINT ‘Start CATCH’;
            THROW 51000, ‘A calculation error occurred’, 1;
            PRINT ‘End CATCH’;
        END CATCH;

    PRINT ‘End of proc’;

    GO

    If I not execute this, with a parameter, I get this:

    Before TRY
    Start TRY

    (0 row(s) affected)
    Start CATCH
    Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
    A calculation error occurred

    That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.

    However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.

    ALTER PROCEDURE spGetCommission
    …

        BEGIN CATCH
            PRINT ‘Start CATCH’;
            PRINT    ‘A calculation error occurred’
            PRINT ‘End CATCH’;
        END CATCH;

    PRINT ‘End of proc’;

    GO

    In this case, I’ll get all my print statements.

    Before TRY
    Start TRY

    (0 row(s) affected)
    Start CATCH
    A calculation error occurred
    End CATCH
    End of proc

    A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.