6 April 2018

Using NULLIF to avoiding duplicating long T-SQL sub expressions for safe division, exclusive between

I seem to find myself generating a lot of SQL from other higher-level query structures. And it's bugged me for a while that if I have some long SQL expression, and then need to perform certain operations such as division, or exclusive BETWEEN, then it gets repeated.

So it seems like the ISNULL and NULLIF functions are awesome for solving this kind of problem.

The safe null-if-zero division problem

If we want to divide by some-long-expression and want to return NULL if it is zero, then we might find ourselves writing:

CASE WHEN some-long-expression = 0 THEN NULL ELSE numeratorsome-long-expression END

But we can avoid including some-long-expression twice by using:

numerator / NULLIF(some-long-expression, 0)

The inclusive/exclusive BETWEEN problem

The BETWEEN clause gives us lower <= test-value <= upper, including both lower and upper in the test range. But sometimes we might need lower only, or upper only, or both, to be exclusive tests.

This can lead to duplicating long expressions by having to write:

lower < (some-long-expression) AND (some-long-expression) < upper

But we can use NULLIF to similarly avoid repeating the long expression being tested:

NULLIF( NULLIF( some-long-expression, lower), upper) BETWEEN lower AND upper

There's a few different combinations of scenarios we might need, as follows:

ISNULL(expr, lower) BETWEEN lower AND upper
expr BETWEEN lower AND upper
NULLIF(ISNULL(expr, lower), upper) BETWEEN lower AND upper
NULLIF(expr, upper) BETWEEN lower AND upper
NULLIF(ISNULL(expr, upper), lower) BETWEEN lower AND upper
NULLIF(expr, lower) BETWEEN lower AND upper
NULLIF(NULLIF(ISNULL(expr, some-midpoint) lower), upper) BETWEEN lower AND upper
NULLIF(NULLIF(expr, lower), upper) BETWEEN lower AND upper

13 November 2017

Jaycar Arduino 16 Key Touch keypad XC4602

So my ten year old daughter wanted to build a keypad enabled electric lock for her bedroom "to keep her little brothers out".  TLDR: go here and solder.

We picked up the Jaycar Arduino Compatible 16 Key Touch keypad module cat XC4602, which is based on the TTP229 capacitative touch sensor IC. The board's by duinotech, and the only discernible numbers are 1003010 on the board, and 8229BSF on the IC.

There's a downloadable Arduino demo on the Jaycar page, but we didn't have any luck with it.

To get all 16 keys working, we followed the helpful instructions posted by this guy. Yes, it does actually seem like you have to solder at least jumper 3 (yellow link on his post). That's the only one we did.

Then he's got a link to his code, but it's hidden down the bottom of the page. Direct link. You'll need to create yourself a Dropbox account to be able to download it though. The only other gotcha is that his sample code writes to serial at 115200 baud. Make sure you set your monitor to the same or you'll just get rubbish.

After that, it's happy days. Just follow his wiring, and sample code. I figured I'd put this post out there pointing to his blog because there's a number of other posts out there about this IC that were much less helpful.

Aside, we're using the Jaycar Electric Strike EL-973 (fail safe version), Jaycar cat LA5081, for the lock mechanism. It needs 12V to activate, so I figured we'd drive it like a motor with an IRF520 mosfet. It works a treat.

29 January 2017

Arduino Grove LCD RGB Backlight not working

The Grove Starter Kit is a fun way to start on Arduino for young kids, because components can be easily plugged in, rather than needing to poke components into a board.

The first time I tried to use the Grove LCD RGB Backlight board, I was disappointed that it seemed to not work. I could change the backlight color, however no text would appear.

The simple resolution: The LCD specs state that it requires 5V to work. The Grove Base Shield has a slide switch to select 3.3V or 5V. Sensibly, it came preset to 3.3V. Slide it over to 5V and the LCD text starts working. When all else fails, RTM.

Playing with the Jaycar 2.8" TFT LCD touch screen XC4630

Last year I was playing around with the 2.8" TFT LCD touch screen that's available from Jaycar XC4630 on the Arduino Uno. This is must try for anyone experimenting with Arduino. Easy to use and lots of fun.

If this appears to be not working or broken with a blank screen, then possibly the driver library is not quite right.

Jaycar now have a custom build of libraries on their website, which works fine. Download the file, and follow the instructions in the "XC4630 Notes.txt" file. These drivers and examples work fine for me.

Unfortunately this wasn't available last year when I was trying to first get it working. (I was just sitting down now to write up what you can do, but see that it's all much easier now).

The Jaycar libraries are based on the Adafruit libraries, which are hosted in GitHub. For posterity (or if you want to use a more up to date version of the libraries), I had to do the following to get them to work.

1. Download the libraries
2. Import the libraries into the Arduino software
  • Sketch | Include Library | Add .ZIP Library...  (once for each)
3. Open an example
  • File | Examples | TFTLCD-Library-master | graphicstest
4. Fix the hardware identifier

This is the bit that took a while to figure out. For reference, the serial number on my TFT LCD is: QR4 5265S01 G3/2 TP28017.
  • It seems that the readID() function in the Adafruit library does not work with this particular hardware.
  • In the example graphicstest program, Locate line 60:  uint16_t identifier = tft.readID();
  • Change it to:  uint16_t identifier = 0x9341;
  • Or, equivalently, change line 84 to: tft.begin(0x9341);
The tftpaint program also worked for me with a similar change.

Hope this helps someone.

17 April 2014

Using property getter/setter functions in Angular binding

OK, is most circumstances there's a better way to do this, but sometimes life would be easier if we could have a getter function and a setter function in an Angular binding.

Here's a helper function to make it more convenient to wrap them up. It's a bit rough, but does the job.

 * For use when you really want to intercept two-way Angular bindings.
 * (And usually there's another better way)
 * Example
 * <input
 *     ng-model="asProp(getCheckbox, setCheckbox, someArg1, someArg2).value"
 *     type="checkbox" />
 * ...
 * $scope.asProp = lib.asProp;
 * $scope.getCheckbox = function(someArg1, someArg2) { return ... };
 * $scope.setCheckbox = function(newValue, someArg1, someArg2) { ... };
 * Takes a getter and a setter function.
 * And optionally additional arguments.
 * Returns an object with a single property called 'value'.
 * 'value' uses the provided getter and setter functions.
 * Additional arguments are passed to the getter and setter:
 * getter(additionalArgs) -> currentValue
 * setter(newValue, additionalArgs)
lib.asProp = function (getter, setter /* .. args */) {
 var res = {};
 var args = _.toArray(arguments).slice(2);
 Object.defineProperty(res, 'value', {
  get: function () {
   return getter.apply(null, args);
  set: function (val) {
   var args2 = [val].concat(args);
   setter.apply(null, args2);
  enumerable: true,
  configurable: true
 return res;

4 January 2014

Aurora inverter USB connection

We had solar electric installed (yay!). We are using the Aurora PVI 4.2kW inverter, which appears to be a very well recommended unit, but there were a couple of challenges with trying to plug into the USB connection to download live data. Just thought I'd note them down here to hopefully save someone else a bit of time.

There are two pieces of software you need:
1. USB driver - makes the Aurora appear under Windows as a COM port. (Texas Instruments 3410 USB driver). We used this one instead of the Aurora one. "TI WDF USBUART Single Driver" from the software section of this page.
2. Aurora communicator software - Communicates with the Inverter to download and chart data.
(Unfortunately the inverter doesn't actually collect historical data, you need to essentially leave the program running and poll for it). Communicator can be downloaded from here. (select 'UK' as the country, then select Software - because not all countries have any 'software' listed against them).

Issue 1: My laptop is running Windows 7 (x64). For better or worse, the 64 bit version of Windows doesn't run unsigned device drivers. The driver may look like it has installed - but it won't run. The work-around for now is to boot Windows in the special mode to allow unsigned drivers. (Press F8 while the computer is booting, and select "Disable Driver Signature Enforcement."). Or see here for other options.

Issue 2: The Inverter itself needed to be assigned an "address" before it would talk to anything. Go to the inverter itself and use the menu options to go into the settings and set an address. Any number will do. We used "2". Seems to be necessary and in our case wasn't already preconfigured.

Next task: ditch the 70W laptop and start using a Rasberry Pi instead.

28 October 2013

Bank Security Madness

This has probably been said by countless people before, but banking security is just mind bogglingly dumb.

Yesterday I phoned the NAB bank. Along with the usual questions I was asked to name a recent transaction. I couldn't recall one so I asked my wife who was standing next to me. The phone operator proceeded to tell me that I wasn't allowed to ask her because I was the person who needed to know the answer! NAB, if you make me communicate with my wife using a pen and paper instead, then you're not going to gain any security, you're just going to annoy customers.

The next thing that struck me was that the transaction used for authentication could be a debit. Now, generally speaking you don't need to do any authentication to put money into an account. Anyone can deposit into your account as long as they have the account number; thereby rendering this authentication method (as it stands) useless at best.

Today I phoned the ANZ bank. Their system is straight forward enough. I just need to know my Customer Reference Number, my telecode, my security code, and my web password. And I'd better well be able to remember which is which before I run out of attempts.

Authentication is a hard problem to solve, but surely we can do a bit better than this.