Tuesday, December 30, 2014

Coder Tidbit: SQL - Last index of specific character

The Problem

Today I had the opportunity to work with data pertaining to files on a network share and the feature I was working on was to include this information in a site search. To match the needs of the search I needed to include the extension of the file in the results. However, the only information that I had was the full path to the file, where it resided on the network share.

In C# I could have done something like .LastIndexOf(x) but I did not know of any SQL equivalent so I sought after a solution and came up with the following, after some Googling.

The Pattern Used

What's Going On

SUBSTRING(param1, param2, param3)
param1 is field in which we're substringing
param2 is the starting position to begin the getting the substring
param3 is the distance across the char array the substring function should travel before returning

To get the starting position we can get the last '.' and substring to the end of the string. To get the 'last index' of the '.' we can reverse the string with REVERSE(...) and then use that in conjunction with CHARINDEX(...). If my path is \\testpath\TestFile.txt the result from using this is 4 since the string is reversed it's still counting up and since '.' is at the fourth position of the character array. With this information we know that we can then subtract this number from the length of string with LEN(...) to get the position of the '.' we're after. To get the return format of .txt we need to add 1 to the start position of the SUBSTRING call.

Now, we have our beginning position for our substring and also the length needed for our extension, otherwise everything from the '.' to the end of the string and we now have our extension.



Wednesday, December 17, 2014

So, I Built a Weather Station

So, I Built a Weather Station

The Back Story

Growing up on the Outer Banks of North Carolina I am no stranger to amazingly strong weather. Having weathered many a storm, many a hurricane, I grew to truly appreciate and respect the weather.

All the strong weather experiences and seeing the destruction it can cause always made me want to study meteorology and collect weather data. Though I didn't become a meteorologist I still have a passion for weather so I started a project of building a custom weather station. This blog is to chronicle what I've learned through this project.

Since I followed information and tutorials from the great people at Spark Fun Electronics this blog will mostly represent my cataloging the process that I took to finishing this project as well as the steps I took in troubleshooting when I ran into a few issues.

First Off; The Equipment

Below is the list of equipment that I made use of in this project: 

Weather Meters (Anemometer, Wind Vane, Rain Tip Bucket)
SparkFun Red Board (Similar to the Arduino Uno micro-controller, programmed with Arduino)
SparkFun Weather Shield (Temperature, Humidity, Barometric Pressure, Ambient Light,
Electric Imp (Think SD card shaped wireless micro-controller)
Electric Imp Shield
Solar Radiation Shield (To keep the electronics out of direct contact with the elements but keeping the sensors open to open air)
9 Volt Wall Wart
Barrel Jack Extension Cord

The Guidance

I enjoy Spark Fun Electronics a lot and they also have a lot of great tutorials for many of the items they sell and as you can tell from The Equipment that I purchased most of the equipment for this project through them. Nate Seidle at Spark Fun has a great tutorial on using much of the equipment listed above and this is the tutorial that I followed.

The Differences

Nate, however, used battery power and solar charging where I used 9 volt wall wort. I assume he chose battery power and solar due to not having easy access to power for his station. I however, was able to run electrical to where I needed it and then affixed the station to the roof of my garage.

He built an elaborate rig to keep his station on his, flat top, roof, I however purchased an articulating flag mount and bolted it to my roof with a ton of tar and silicone sealant. No seriously, I don't want leaks.

Copy Paste Woes

Since Nate Seidle laid out such a great tutorial that it was easy to emulate what he had done and then tweak as needed. I will say that, for the most, part this tactic worked well but instantly upon setting up the station I found that my wind bearing readings were not working.

I spent hours of debugging this, no seriously, I'm a bit embarrassed it took so long, but I had a constant reading of 113 degrees. At first I thought "bad instrument" but then I read more about the vane and found out that the reading was based on the voltage reading coming from the instrument itself. After finding this out I then assumed it must be a broken reed switch, which is a little glass tube with tiny metal reeds that, when a magnetic field crosses over the tube, make contact and complete the circuit. The vane makes use of 8 reed switches so this seemed like a chance so I checked it out and they all looked fine.

My next move? To check the micro-controller passed voltage itself. I tried a few things, I redirect the value to be parsed on another pin. Nope. I tried redirecting more voltage up to the weather shield in hopes that maybe the Electric Imp was hogging too much power. Nope. And then, at this point, I was really at a lose so I read more about the Electric Imp because at this point I found that unplugging the imp allowed me to read the values over USB through the Red Board.

The Little Things

On the Electric Imp there's an ability to do what's called "Card Detect." Since the shield is just a "holder" for the Electric Imp the Card Detect allows the micro-controller to, as it sounds detect if the Imp is in place. It also happens to use the analog pin 0 which just so happens to be the same pin that the weather vane is trying to use and this is where the issue with the weather vane value came in. On the bottom of the card there is a small area denoted with CD - A0. This, this is it. This is what was causing all my issues. I hadn't cut the trace on the Card Detect. I took my knife and carefully broke the line between the two metal pads you see in the picture, hooked it back up and viola I was getting the correct readings.

The; Well, I, uh, Hadn't Thought of That's

With any project comes the things you never thought about. For me there were a few funny ones but I'm only going to highlight one in particular. I placed the weather station on the side of my house, this isn't optimal in many ways but it's enough for what I want. Once I hung the solar shield and placed the instruments I was proud and looked over at it often when outside. It just so happens I was out getting the mail well past sunset one night and looked over at it and saw something that I hadn't planned on at all in this project. My solar shield, with the gleam of the LEDs on the micro-controller turned into a lightning lamp. This is something I'm going to make "go away" because I don't necessarily want it to be an obvious thing for someone to want to take. None-the-less I hadn't planned on the light show.

This video is very dark but watch for flashes of light. I recorded this on my phone and it did not justice to how bright it actually looks but, then again, that could be the paranoia speaking.


Thanks for your interest. If you decide to build your own station I'd love to hear about it and I'd love to help.

Also a big thanks to my friend and colleague Rusty for the encouragement and for being a sounding board for bouncing ideas off of.

Thanks to David for his tweeting about the project and his support.

Another big thanks to +SparkFun Electronics for great tutorials and great products.

Finally, The Data


Are you a programmer and want to play with the data? It's cool, I know you're curious just play with the data anyways. Here's an ODATA feed for the last 24 hours worth of readings: http://weather.costrcode.com/odata/Readings

If you'd want to play with this data in the browser try the following links:
FOR JSON: http://weather.costrcode.com/odata/Readings?type=json
FOR XML: http://weather.costrcode.com/odata/Readings?type=xml

NOTE that some browsers will attempt to render the XML as HTML so it will look, well it'll look all messed up. Try using the JSON link instead.

Let me know your thoughts, questions, funny jokes. Hit me up with a few comments.

NOTE: I'M SORRY! I just found out I had broken links for the test data. How embarrassing. They should be working now.

NOTE: I took down access to my weather data for the time being. If you would like access please let me a comment. Thanks!

Wednesday, August 20, 2014

Chasing the bug? Let Windows help.

I feel that one of the hardest things to do really will is communication. Everyone communicates differently and interprets communication to them differently and for that very reason it can make something like debugging a bug, that you are unable to reproduce, extremely challenging.

Most of the clients that I work with are running at least Windows Vista. With this, built in to Vista, and up, is a program called "Record Steps to Reproduce a Problem." Lengthy name but I refer to it as "Steps Recorder."

Steps recorder isn't anything terribly robust but that's one of the things I really like about it. The quick run down is this:
  • Windows key + type "Steps recorder" (Windows understands what you're looking for)
  • Hit enter, or click "Record Steps to Reproduce a Problem"
  • Click "Start Record"
  • Perform the actions needed to reproduce what you need to illustrate
  • Once done, click "Stop Record"
  • File Dialog will show
    • Select your save destination
    • Give it an appropriate name
    • Click Save
  • Done and Done
The file will be saved to where you specified as a .zip. From here you can send the steps that you recorded to whomever might need them and get the resolution process moving.

  1. If you're using multiple monitors the steps recorder will screenshot all of the monitors displayed. I often temporarily revert to one monitor, record what I need, and then revert back to all monitors. Also all monitors will dramatically increase the file size of the saved recording.
  2. I use Steps Recorder for archiving steps to do a task as well. This way I can easily send a file to others instead of having to repeat the same training over and over.
  3. .mht files, the format the recorder saves in, (inside the .zip folder) is also mhtml file and natively opens in Internet Explorer but, with plug-ins, can be opened in most other browsers.
  4. You can also add notes to steps, and this also allows you to highlight the area on the screen and then type in your note and continue with your steps.

Saturday, February 8, 2014

Coder Tidbit: CSS Arithmetic

I was recently work on a project where I was attempting to center text in a div that had a left property of 50px. The issue with this was that the text centering was going to be off by 50px, a negligible amount for average browsing, but on a mobile phone with a double, or more, pixel density the amount was substantial.

To help accommodate this CSS provides a function called calc( ).

calc( ) takes the math that you'd like to have performed, runs, and dumps out the calculated value for you.

Here's the snippet of the code that worked for my needs: