Tales from Technological Backwaters

Some folk with sufficient years under their belts (or over their belts as the case may be) will notice the hat tip to Yes's Tales of Topographic Oceans. Beyond the name, there's little to connect that prog rock masterpiece to this post.

As far as I can tell, I (and the database guru) work in a technological backwater. I'm part of the dev.to community and that's cool, but I'm physically far away from pretty much everything (suburban Perth, WA Australia, UTC+08) and I have no idea whether what I'm doing is cutting edge or spine-of-the-blade.

Having said that, I do overhear the odd IT conversation on public transport (we're a one car family and I don't drive it much) and the vast majority are talking about older technologies like Excel formulas, SAS scripting and AutoLISP.

So to add to the mundane mix, here's a little JavaScript class that I've been using for years now. I use it to build SQL requests.

class SQLStoredProcedureCall {
    _sproc: string;
    _tail: string[];

    constructor(sprocName: string) {
        this._sproc = sprocName;
        this._tail = [];
    }
    boolean(key: string, val: boolean) {
        this._tail.push(key + " = " + (val ? "1" : "0"));
        return this;
    }
    number(key: string, val: number) {
        this._tail.push(key + " = " + val.toString());
        return this;
    }
    date(key: string, val: Date | string) {
        this._tail.push(key + " = '" + val.toString() + "'");
        return this;
    }
    string(key: string, val: string) {
        this._tail.push(key + " = '" + val.toString().replace(/'/g, "''") + "'");
        return this;
    }
    toString() {
        const result = this._sproc + " " + this._tail.join(", ");
        return result;
    }
}

This is the typescript version. The original javascript version gets executed thousands of times per day as part of larger projects that interface with our SQLServer databases on two separate Azure servers. I've even got a C# implementation kicking around that also sees a lot of activity. There are probably better ways of doing it, but this is meeting the need of the moment.

For those not wanting to convert this to simpler dialects of JavaScript, here's what ts2gas makes of it.

var SQLStoredProcedureCall = (function () {
    function SQLStoredProcedureCall(sprocName) {
        this._sproc = sprocName;
        this._tail = [];
    }
    SQLStoredProcedureCall.prototype.boolean = function (key, val) {
        this._tail.push(key + " = " + (val ? "1" : "0"));
        return this;
    };
    SQLStoredProcedureCall.prototype.number = function (key, val) {
        this._tail.push(key + " = " + val.toString());
        return this;
    };
    SQLStoredProcedureCall.prototype.date = function (key, val) {
        this._tail.push(key + " = '" + val.toString() + "'");
        return this;
    };
    SQLStoredProcedureCall.prototype.string = function (key, val) {
        this._tail.push(key + " = '" + val.toString().replace(/'/g, "''") + "'");
        return this;
    };
    SQLStoredProcedureCall.prototype.toString = function () {
        var result = this._sproc + " " + this._tail.join(", ");
        return result;
    };
    return SQLStoredProcedureCall;
}());

Calling that runs like this

var age = 58;
var sql = new SQLStoredProcedureCall("Database.dbo.SomeStoredProcedure")
    .string("@P1", "Bruce's string")
    .number("@P2", age)
    .boolean("@P3", age < 60)
    .date("@P4", new Date())
    .toString();

with the resulting SQL looking something like this

Database.dbo.SomeStoredProcedure @P1 = 'Bruce''s string', @P2 = 58, @P3 = 1, @P4 = 'Thu Sep 19 16:30:05 UTC+0800 2019'

Actually handing that off to SQLServer for evaluation, and then handling what comes back is left as an exercise for the reader.