Tahir Hassan's Blog

My Technical Notes

Wednesday, 8 November 2017

Using SQL Merge Statement (Example)


IF OBJECT_ID('dbo.SystemSetting', 'U') IS NOT NULL 
  DROP TABLE dbo.SystemSetting; 

IF OBJECT_ID('dbo.WebConfig', 'U') IS NOT NULL 
  DROP TABLE dbo.WebConfig; 

CREATE TABLE dbo.SystemSetting([Name] varchar(50), [Value] varchar(250), 
     CONSTRAINT SystemSetting_PK PRIMARY KEY([Name]));

CREATE TABLE dbo.WebConfig([Name] varchar(50), [Value] varchar(250), 
     CONSTRAINT WebConfig_PK PRIMARY KEY([Name]));

GO

INSERT dbo.SystemSetting([Name], [Value]) VALUES('SystemType', 'Live');
INSERT dbo.SystemSetting([Name], [Value]) VALUES('RootUrl', 'http://tahirswebsite.com');

GO
INSERT dbo.WebConfig([Name], [Value]) Values('SystemType', 'Test');
INSERT dbo.WebConfig([Name], [Value]) Values('RootUrl', 'https://realwebsite.com');
GO

-- MERGE statement with the join conditions specified correctly.

BEGIN TRAN;
MERGE dbo.SystemSetting AS dbSetting
USING dbo.WebConfig AS webSetting
ON (dbSetting.[Name] = webSetting.[Name]) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT([Name], [Value]) VALUES(webSetting.[Name], webSetting.[Value])
WHEN MATCHED AND dbSetting.[Value] <> webSetting.[Value]
    THEN UPDATE SET dbSetting.[Value] = webSetting.[Value]
OUTPUT $action, Inserted.*, Deleted.*;
COMMIT TRAN;
GO 

The above example plays out the scenario in which the web server wants to push its configuration data, stored in `WebConfig`, into the database's own `SystemSetting` table.

It will:

  • Creates tables `SystemSetting` and `WebConfig` that have the same two `varchar` columns, `Name` and `Value`.
  • It populates both tables with some sample values.
  • It merges `WebConfig` data into `SystemSetting`.

Monday, 30 October 2017

PowerShell: Command Argument Completion

A parameter's argument can be completed by using the `Register-ArgumentCompleter` function. See Completing Parameter Values With Other Parameter Values, which provides a fully working example, for more information.

For PowerShell V5 and later, there is no need to install TabCompletionPlusPlus module.

Vagrant: Forwarded port not exposed on network

In


Control Panel\System and Security\Windows Firewall\Allowed Programs
Allow `VBoxHeadless` to access the `Domain`, `Home/Work (Private)`, and `Public`.

Tuesday, 17 October 2017

PowerShell: Create Email Message with Attachments

A lot of the times, while in PowerShell, you want to email something as an attachment to a colleague without having to open it in Explorer and right-clicking it, and doing `Send to...` → `Mail recipient`. What is needed is a simple function `Email-File` that does this:


Function Email-File([string[]]$Files) {
    $outlook = New-Object -ComObject Outlook.Application;
    $mail = $outlook.CreateItem(0);
    $mail.Subject = 'Email Files';

    $Files | ForEach-Object {
        $mail.Attachments.Add($_);
    }

    $mail.Save();
    $mail.Display($false);
}

Saturday, 7 October 2017

Installing `adb` (Android Debug Bridge)

To install `adb` you must first download the "Android command line tools" from the Android Studio download page. It is a zip file, not a user friendly installer.

Crate a folder in the root of the `C:\` drive called `android-sdk`, and within it, copy the `tools` folder that is in the zip file.

After that, on the command line, `cd` to the `android-sdk\toosl\bin` folder. Invoke the `sdkmanager.bat` to install the so-called `platform-tools` that contains `adb`:


.\sdkmanager.bat "platform-tools"

After this finishes installing you will have a `platform-tools` folder in the `android-sdk` folder, that contains `adb.exe`. You can simply add this containing folder to the `PATH` environment variable if you wish invoke it from anywhere (or create an "Alias" in PowerShell).

Friday, 6 October 2017

ConEmu redraw problems with Vim

I had the fairly rare (perhaps 2-3 times a day) issue of part of the screen going blank when using Vim inside ConEmu. Apparently, adding this to your `vimrc` file fixes the issue:


set ttyscroll=0

Vim: Loading Fugitive with `Gstatus` opened

I am learning how to use Fugitive, which is a Vim plugin to make it easier to commit to git. I had a use-case where I would be in a directory (while in PowerShell), and I would want to load vim with Fugitive's `Gstatus` run automatically.

In order to do this, I have implemented a simple PowerShell function:


Function vimf {
    vim temp -c 'Gstatus | wincmd j | hide'
}

It works by calling vim with `temp` as a filename. Because this file name does not exist, all that will happen is that vim will create a swap file for it, but otherwise it will be ignored. `-c` is the command that will be executed once temp is loaded into a buffer. `Gstatus` is called as we wanted to show `git status` output. `wincmd j | hide` will hide the temp buffer.