Tales from the messagebox: Not just the happy path!

When you’re designing and implementing a solution don’t forget there’s more to it than the “happy path.”

It’s going to break.

It’s a certainty that at some point something will break. You’ll get a call from users asking why they’re not getting output, or not getting the output they expect. How will you resolve this?

Leave yourself a trail of breadcrumbs.

Have you saved logs and archives of inputs and outputs? These can be invaluable in investigation and recovery, but don’t forget to secure any sensitive data! Ideally they should be on a separate write only system. Should the main system be compromised you can trust the logs to help determine the impact.

Since BizTalk uses XML and not JSON don’t forget you can embed comments in your messages. Comments are ignored by automated processes, but a comment in an archived output explaining the choices it made can be a life saver. Don’t forget to include the inputs. If data is changed after a process runs it may be nearly impossible to understand what happened after the fact.

What you didn’t check can kill you

At one employer a dev lead I worked for never considered this. He was updating a query that added a year to a customer’s subscription ending date if they met the right qualifications during the month end processing.

He commented out the SQL WHERE clause during his testing and forgot to restore it when producing the production version. Net result was tens of thousands of subscribers received an additional year of service they weren’t qualified for.

He caught it immediately when the monthly process ran and tried to restore to the latest backup. He found out there were no backups. The database team that handled backups informed us we had the level of service that included backups, but we did NOT have the level of service that ensure the backups actually succeeded. The SQL server agent running the backup code wasn’t running for weeks so the backups we did have were significantly out of date.

How did this happen? We had processes in place.

He tested his code to ensure those who deserved another year got another year. He did NOT test the inverse. That those who did NOT deserve another year did NOT get it. He could have saved his career with that one test.

The QA department checking his work failed in the same way. They created a database of deserving clients and all of them got their addition year of service. There were no records for clients that didn’t meet the criteria. QA PASS.

Test for failures too!

Create tests for more code paths. Ideally keep these tests so other developers can run them and have better assurance they didn’t accidentally break code they didn’t change.

Hopefully now you’re forewarned you’ll not fall prey to this kind of mistake.

Happy coding!

Jay

Tales from the messagebox: Database horror tales

In one sense it’s not the worst code I’ve ever seen. In another, it is.

The worst I recall was when a Visual Basic (6) coder created an application that implemented the real estate licensing test. After completing the test for one state they were asked to implement a second. So they opened the visual layout editor and proceeded to draw the second quiz form on top of the first one.

Imagine trying to maintain two forms jumbled on top of one another. In the form open event he made all the controls for the selected state visible, and all the others invisible. It was awful, but understandable. If you weren’t aware you could have two forms and just load the one you needed…

I think I want to give the title of worst to the guy (or girl) who created a database with primary keys that are the datetime type. It worked when it was tested with an empty database. Now I’ve got to support the thing with millions of rows and I’m not allowed to fix it. “It will cost too much.” It’s okay to let it randomly fail when you get collisions, perform badly, and pay for tech support to handle tickets though.

Small things do matter, and bad choices do come back to haunt you.

esp8266 clock using Tasmota

I was looking for something else, and stumbled across an interesting post:

Tasmota internet clock

Tasmota is general purpose open source firmware for cheap Wifi capable microcomputer boards. A few bucks and a little software magic later you have something useful… like a clock.

Features:

• Has NTP built in so it will automatically get the correct time
• Changes when daylight savings time does
• Adjustable brightness

The author used a D1 mini board, which I don’t have. I do have a hand full of extra esp8266 boards though. I looked up the documentation for the tasmota module:

https://github.com/tasmota/docs/blob/development/docs/TM163x.md

It documents the setup, wiring, and the commands needed to send the time to the board. It worked pretty much on the first try. I did have to hunt around for a USB cable that had the data wiring. A lot of the cheapo cables just carry power and dispensed with the other two wires.

You’ll also need the timezone table so you can set it up correctly:

https://tasmota.github.io/docs/Timezone-Table/

I need to print a case for it too, but there are quite a few free on the 3D models sites.

———— Update! —————

Finished it up with a case: 

———— Update! —————

One of the important tenets of Edge Computing is to push as much behavior off the server as possible. If the connection to the server is down the system should continue to work in a reasonable manner. I.E. if the internet is down you should still be able to turn the lights on and off.

I’d like to change the brightness down at night to save power and make it easier to sleep. So I’ll push that feature to the clock itself using Tasmota rules and timers.

Here’s the script to setup timers to change the brightness based on time of day:

(The ‘#’ denotes a comment. Don’t paste that into the tasmota console.)

# become a clock on boot up
Rule1 ON System#Boot DO DisplayClock 1 ENDON
Rule1 1

# setup timers. Normal sunrise is fine, enable timers
Sunrise 1
Timers 1

# set Timer2 to 10 minutes after sunset every day of the week, run a rule
Timer2 {"Enable":1,"Mode":2,"Time":"0:10","Window":0,"Days":"SMTWTFS","Repeat":1,"Output":1,"Action":3}

# set Timer3 to 10 minutes after sunrise every day of the week, run a rule
Timer3 {"Enable":1,"Mode":1,"Time":"0:10","Window":0,"Days":"SMTWTFS","Repeat":1,"Output":1,"Action":3}

# concatenate all the events into one rule
# turn brightness up 10 minutes after sunrise
# down 10 minutes after sunset
# down even more after 11pm ( 23 hours after midnight, 23 * 60 = 660 minutes after midnight)
Rule2
 ON Clock#Timer=2 DO DisplayDimmer 26 ENDON
 ON Clock#Timer=3 DO DisplayDimmer 44 ENDON
 ON Time#Minute=660 DO DisplayDimmer 13 ENDON

Rule2 1

Shelly doesn’t impress so far…

Bought some Shelly duo bulbs since they claim MQTT works without a cloud connection. So far the bulbs won’t stay connected even just a few feet from the router. I confirmed the signal strength was 10db higher than the perfectly reliable Tasmota devices I’m using.

My suspicion is the latest revision of software is buggy. They’re probably going back unless their support can fix it.

Won’t buy from Wyze…

I drank the kool-aid and bought a couple of power plugs and color changing bulbs. They seemed so nice… 😉

They won’t share any information about using the devices to developers. That’s not great, but I see the author of esp2ino spent a lot of time creating software to do an OTA flash of Tasmota! Now we’re cooking!

Wyze found out and spent considerable time and effort to nerf the project. Sorry guys, if I’m not allowed to do what I like with my purchase I’m not using it. I’m sorry I gave them any money now.

Extending my Wifi with stuff from the junk box

After having issues with Wifi dropping out I did a speed test at the farthest point from my utility access point. The speed was quite low, and the signal dropped in and out regularly.

I had an extra Raspberry Pi 3b+ in the junk box that I just refreshed for a new project. I wondered if it could also be used as an access point.

The answer is definitely yes, and it works well. I chose the routed access point configuration instead of the bridged to give more security and control. A few hours later it was working fine. The speed test results were ten times higher bandwidth than using the marginal wifi from my provider.

All the details can be found in their excellent documentation!

Find all BizTalk send ports not in use

If you didn’t know you can query the configuration of your BizTalk install from the BizTalk management database.

Here’s a query to find all send ports not connected to anything, and therefore are no longer used.

USE BizTalkMgmtDb

GO

— find all send ports not in use

SELECT

    SP.nvcName AS SendPortName , APP.nvcName AS ApplicationName

   FROM bts_sendport AS SP

     INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID

   WHERE NOT EXISTS( SELECT 1 FROM [bts_spg_sendport] WHERE nSendPortID = SP.[nID] )     — not in a send port group

       AND CONVERT(VARCHAR(MAX), nvcFilter) = ”                                        — has no filter

       AND  NOT EXISTS( SELECT 1 FROM [bts_orchestration_port_binding] WHERE nSendPortID = SP.[nID] )  — not connected to an orchestration

BizTalk two way send port breaks XML returned from SqlServer

I recently wrote an application that calls a stored procedure from BizTalk. I used the stock “Add Generated Items” wizard to create the port bindings and the schemas. The response was returned split into arbitrary pieces! I’m not sure but I believe by the WCF layer.

Here’s what the returned message looks like:

<bsp_OutputResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
  <StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_Output">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;Root xmlns=" …SNIP… </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_Output">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
        alue&gt;&lt;interestRate&gt;3.00000&lt;/ …SNIP…"&gt;&lt;tran
     </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_Output">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>sactionDate&gt; …SNIP…/Header&gt;&lt;/Root&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
  </StoredProcedureResultSet0>
  <ReturnValue>0</ReturnValue>
</bsp_OutputResponse>

The nodes prefixed with “XML” are statically generated by SQL server or WCF.

As is the result is unusable since it’s broken at arbitrary places in the xml result. I needed to extract the strings, unescape them, to convert the result to a message. A custom pipeline will work but I believe the following solution is simpler.

To reassemble the result I created a map using hand written XSLT. The input is the generated result set schema. The output is a schema you create matching the XML you receive if you run the query manually in SQL Server management studio. The map extracts the result set nodes sequentially, unescapes them, and emits the resulting reconstructed XML. Here’s the XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s1 s0" version="1.0"
  xmlns:ns0=" Statements"
  xmlns:s0="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_Output"
  xmlns:s1="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"
  >

  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

  <!--discard unwanted nodes-->
  <xsl:template match="s1:ReturnValue">
  </xsl:template>

  <!--copy content of result set nodes-->
  <xsl:template match="s0:XML_F52E2B61-18A1-11d1-B105-00805F49916B">
    <xsl:value-of select="./text()" disable-output-escaping="yes" />
  </xsl:template>

  <xsl:template match="/s1:bsp_FA_StatementOutputResponse/s1:StoredProcedureResultSet0/s0:StoredProcedureResultSet0">
    <xsl:apply-templates select="s0:XML_F52E2B61-18A1-11d1-B105-00805F49916B" />
  </xsl:template>

</xsl:stylesheet>

The root template (the last one) applies a template to match each fragment of the result (the middle one). The useful magic here is the option:

disable-output-escaping="yes" 

This removes the escape characters from the content text. The first template removes the ReturnValue node from the result.

Easy peeazy, lemon squeezy.

BizTalk XML output files have invalid encoding

I recently had an issue where output files from BizTalk were being rejected. The error message indicates the file encoding and the encoding declaration in the XML do not match. The software that receives the file checks for this issue and rejects the file.

BizTalk has a hard coded default that adds a “UTF-16” declaration to everything regardless of how it is actually encoded. The XMLTransmit pipeline in the send port has an option to remove the declaration.

It defaults to True, set it to False.

Exporting Movies from the Kodi database backup

One of the options in Kodi is the ability to export your media as an XML file. I’ve gotten requests for a way to create a csv version of the data. Here’s my attempt at doing that.

I say attempt because it’s really not as trivial as it sounds and is a pain to do well. The csv format has a host of issues and it’s the wrong tool for what users are doing. I know right away if any of the movies contain commas in the title the file will not import correctly.

A LOT of people want to use Excel as a database and general purpose tool to write reports. You *can* use a hammer to put in screws but you’re really not doing yourself any favors.

Here’s an xslt script to export movie data from the kodi videodb.xml file.

<!--
This xsl produces a csv format list of kodi tv show content.
It is used from the command line so it's use can be automated

Example for Windows (Assumes msxsl from microsoft is installed. http://www.microsoft.com/en-us/download/details.aspx?id=21714):
msxsl videodb.xml VideodbToTvShowsCsv.xslt -o tvshows.csv

Example for linux (Assumes "xalan" is installed. Should be available from your software repository):
cat videodb.xml | xalan -xsl VideodbToTvShowsCsv.xslt -out tvshows.csv

written by Jay Sprenkle
-->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="text" encoding="us-ascii"/>

<!-- television shows-->
<xsl:template match="movie">
    <!--title-->
    <xsl:text>"</xsl:text>
    <xsl:value-of select='../title/text()'/>
    <xsl:text>"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:value-of select='year/text()'/>
    <xsl:text>,</xsl:text>

    <!-- may be more than one rating so just take the first one -->
    <xsl:value-of select='ratings/rating[1]/value/text()'/>
    <xsl:text>,</xsl:text>

    <!--tagline-->
    <xsl:text>"</xsl:text>
    <xsl:value-of select='tagline/text()'/>
    <xsl:text>"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:value-of select='runtime/text()'/>
    <xsl:text>,</xsl:text>

    <xsl:value-of select='id/text()'/>

    <xsl:text>
</xsl:text>
</xsl:template>

<xsl:template match="/videodb">
    <xsl:text>"Title"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:text>"year"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:text>"rating"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:text>"tagline"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:text>"runtime"</xsl:text>
    <xsl:text>,</xsl:text>

    <xsl:text>"id"</xsl:text>

    <xsl:text>
</xsl:text>

    <xsl:apply-templates select="movie"/>
</xsl:template>

</xsl:stylesheet>