Solution: BI Publisher QR Codes

December 12, 2019 Leave a comment

I saw a post on StackOverflow asking How to use variable in chart.apis.google link for QR Code and I was quite intrigued. I didn’t think it could be done at first. But then I figured it out.

I do know that BI Publisher has built in Chart support, so my first thought was to just use that. But BI Publisher charting cant generate QR Codes. None the less, I linked to BI Publisher Report Designers guide to explain those details if anyone stumbled upon this post looking for actual charts.

My second thought was to follow a blog entry that an Oracle developer posted about using QR Codes in BI Publisher. It mentioned making a java class, and using an IDAutomation QR Font. It was quite complex. This is probably the method to go after if you want all the data to remain internal to the Oracle server in the enterprises data-center. I posted the details to that too.

Finally, I remember using dynamic images at Emerson for insertion of Casting images into Work-orders/Shop-packets. I figured why not give it a shot. I created a quick XML test file, and a simple RTF file. I use the concat (concatenate) function in BI Publisher to combined the Google Charts API for QR codes and the variable I wanted encoded.

Test XML

<test_header>
<test_row>
<row_num>1</row_num>
<value>TESTA</value>
</test_row>
<test_row>
<row_num>2</row_num>
<value>TESTB</value>
</test_row>
</test_header>

Test RTF Template

RTF Template

RTF Template

Image ALT TEXT

url:{concat(‘https://chart.apis.google.com/chart?cht=qr&chs=500×500&choe=UTF-8&chld=H&chl=&#8217;,value)}

My first attempt failed because of a space in my test data. If you have spaces or special characters in the data you want encoded in the QR code, make sure you URL encode the data that you want to pass into the Google API URL. Oracle allows you to do this with the UTL_URL function.

Example use of UTL_URL.ESCAPE

select rownum row_num, utl_url.escape(record_value) value from test_table

Once I removed the space from my test data, it worked! I was shocked how easy it could be.

Test Output

PDF Output

PDF Output

Other Considerations

One last thing to note, if you expect this to work on your enterprise server/network/data-center, you’ll probably have to open up the firewall.

Updating Realtek Drivers for PFSense on a ZOTAC ZBox

January 25, 2019 5 comments

A long while back I built a ZOTAC Zbox (ZBOX-CI323NANO-U) to run PFSense and act as my router. For the most part, it has worked pretty well.  The features are great, without any of the gimmicky stuff you’d find on a consumer router. It’s also a lot more upgradable than a consumer router.

One problem though, on occasion I’d have to restart the ZBOX when the internet would stop working. The display I had connected to the PFSense machine said this during a failure:

re1: watchdog timeout

Each time the error occurred, I would hunt around the internet for a few minutes to find a solution, but I was never able to find one. I gave up for a while. But recently I took a deeper dive.

I found some pages blaming the default network adapter drivers and how they don’t work well with realtek adapters. I also found this page announcing new realtek drivers. I’ve dabbled with Linux a bit, but I’d still consider myself pretty novice. It was pretty difficult for me to get the drivers copied over to the machine, and then installed.

Here’s the process I followed to get them installed:

Read more…

Whole Home Humidifier

January 23, 2018 2 comments

1320352173735008201water-droplet-hi.pngDry air is very unpleasant to be in for extended periods of time. Last winter I tried to rectify the problem by using a single ultrasonic humidifier. It seemed totally inadequate, so I purchased a second higher capacity unit. I quickly became annoyed at how tedious it was to fill the reservoirs. And neither really solved my problem well. This year, I decided to install a whole home humidifier system.

Installing a Humidifier is not an easy task, and required quite a bit of knowledge of different building systems. See below for all of the work that I did, what I learned, and a list of parts.

System Selection

The most common type of whole home humidifier is a bypass humidifier, where water trickles down a special mesh pad that heated air passes over. The humidifier unit itself is quite large and must be attached to a fairly large surface area of unobstructed flat ductwork, as well as have a return/supply bypass. Unfortunately I did not have the space on either side of my furnace (cold air return, or heated air supply) to place the bypass unit.

I did quite a bit of research to find some alternatives. I selected a steam humidifier; an AprilAire 800. The AprilAire 800 is a steam generating machine which placed on a wall/support close to the hot air supply of the furnace. A steam pipe is connected from the steam generator to the ductwork. The AprilAire 800 has a replaceable canister with electrodes in it.  The unit fills the canister with water and generates steam. Periodically, the system will flush the mineral rich water out to reduce mineral buildup in the canister.

A significant benefit to a steam system is that it doesn’t need the furnace to run heat at the same time it is humidifying, it only requires air circulation.

The unit has a capacity of 34.6 gallons a day, but my configuration will be at 23.3 gallons a day. Seems the maximum capacity of a bypass unit would be about 12 gallons a day. Ultrasonic units have a capacity closer to 3 gallons a day.

Read more…

Fixing my Zyxel NWA1123-AC

October 6, 2017 1 comment

My home network is pretty advanced. I have a PFSense home-built router (based on Zotac ZBOX CI323) that connects to my modem, several smart switches (Netgear JGS524E), and 2 wireless Access Points.  The two access points are both made by Zyxel; an NWA1123-AC and an NWA1123-ACv2.  I thought it would be prudent to upgrade the firmware of both devices this week but ran into a problem.

Since both need different firmware, I first downloaded the firmware for the v2 device, and then did the v1 device.  Somehow I managed to download the NWA1121-NI firmware for the v1 device, and then applied it. As I was doing some configuration on the device, I saw I was missing the 5 Ghz settings, which lead me to figuring out I had flashed it with the wrong firmware. I thought it would be easy to revert back since I was able to go one way, but that proved wrong! The device wouldn’t accept the correct firmware.

I did some hunting around the internet, gave up quick, and submitted a technical support request.  Zyxel got back to me the following day saying I would have to RMA the device, sending it back to them, assuming it was in warranty. After some following correspondents,  it was determined that I was out of warranty/support. I turned back to the internet.

I found, on Zyxel’s website (PDF), they had directions on how to have the device pull firmware from a TFTP server before the router fully booted. However, there were some critical steps missing.

  • There’s no serial port on the AP; WHAT?
  • How do I unzip a bin file? And how do I unzip what comes out of that?
  • What software do I use to TFTP?
  • What software do I use for a terminal?

Zyxel support insisted that this KB article on their site did not apply to my AP problem, and there was no way I could recover the device without sending it in to them.

I did some more hunting and found someone over at the OpenWRT.org site posted the internal parts of the NWA1123-AC AP, as well as some serial port details. I also found a page for serial port specifics saying I needed a USB TTL adapter.  The same page had details on the pins to connect the adapter to on the AP (see: Router with serial port / header / pins). Turns out Amazon and my local MicroCenter both carried a USB to TTL adapter, for use with RaspberryPi devices. I biked over to MicroCenter, and picked one up.

Once I got home, opened up my AP, connected everything, downloaded and set up Tera Term and TFTPD64, and starting going through the KB article. I used 7zip to extract the contents of the bin file, and the contents of the subsequent file. I ended up with the files shown in the KB article, so even though 7zip said there was extra contents that wasn’t extracted, I figured I was on the right track.

Everything seemed to work great, and got my device flashed with the correct firmware! Sadly, Zyxel’s poor design required me to clear my browser cache to get the AP’s config page working correctly, but if that was the only hiccup, I wasn’t too angry, and I had been stupid enough to flash the wrong firmware on the AP in the first place.

Access Point saved from certain death!

Amazon Alexa Skill – Electric Price

July 26, 2017 3 comments

amazonecho

I was sick of opening up my phone to check the ComEd hourly price of electric supply, so I decided to make an Alexa app for my Amazon Echo!

It took about 2 hours. It was strange I had to set up both an Alexa skill and the AWS Lamda function, then link the two. I ended up copying most of the code from someones reference code on checking stock prices. Changed the regular expression function which found the stock price to a substring of the ComEd API returned text. Works pretty well! Only problem I’ve had is when the ComEd API is down. If it happens too much, I guess I’ll have to update it with some error handling.

Alexa Skill: Electric Price

Ask: “Alexa, ask Electric Price for Current Price.”

The logo is crap, I know.  And I couldn’t use the ComEd company name in any of the program details/functions, so its a bit generic.  But pretty good for my first try if I do say so myself!

Raspberry Pi CTA Tracker Kiosk

February 9, 2017 Leave a comment

 

img_6610-1

I purchased a Raspberry Pi 3 about a year ago and finally got around to creating a fully functional CTA bus/train tracker out of it.

Parts Needed

Raspberry Pi
http://amzn.to/2lp5YOf 

Micro SDHC Cards
http://amzn.to/2kYn04N

Screen
http://amzn.to/2kTArTK

Cable (Cable that came with screen was defective)
http://amzn.to/2ltoJvX

Keyboard and Mouse (any will do, but I like this)
http://amzn.to/2kNfUO5

CTA Tracker URL:

Create your specific CTA Tracker URL and save it for later
http://www.transitchicago.com/developers/diybtform.aspx

Read more…