Blog » childs.be

How to enable and change the Remote IR GPIO pin in OSMC

10/30/2017 08:00:00 pm

So you want to rig up your Raspberry Pi OSMC to be controlled by your favourite remote control, easy peasy - it is just a matter of plugging the IR receiver diode in and swith GPIO on in the settings.

Dont like the standard pins 17 or 18, no problem - you can change them at teh same time, just follow these instructions.

Go to 'My OSMC,

Select 'Pi Config'

Scroll down to 'Hardware support' and enable LIRC GPIO:

Then select the alternative pins as required

I prefer GPIO 4 for the receiver due to it's proximity to the 3.3v and the GND pins.

How to set up your Logitech Harmony remote on your RaspberryPi

10/30/2017 07:47:26 pm

So you've got your RaspberryPi hooked up to the TV and are loving the set-up but one thing is bugging you, iyou've not hooked it up to your Harmony remote.

What you'd think would be an easy google quickly turns into a dead end, it's strange because wiring up an IR receiver is a doddle and Logitech's Harmony devices can literally imitate any remote control going. What should be as simple as picking any old remote, loading the lirc script to your Pi and adding the device to your Harmony.

simple XBOX360

LG AKB73715601

https://sourceforge.net/p/lirc-remotes/mailman/message/34073175/

from a LG-TV 32LN5758

http://lirc.sourceforge.net/remotes/

DIY Infrared Remote control IR receiver for Raspberry PI / OSMC

10/30/2017 07:45:54 pm

intro

Remove Real VNC from RaspberryPi Pixel and install TightVNC

03/14/2017 08:00:00 pm

I came across my old shortcut on my windows machine to vnc onto the Pi which prompted me to have a go connecting to my new RaspberryPi3.

I duly fired it up only to receive an error message along the lines of 'No configured security type is supported'. After a bit of research it turns out that RealVNC has upped their security protocols and TightVNC is no longer cross-compatible.

No probs I thought - juust install the RealVNC viewer on my windows pc and give it a crack that way. I've never really been a fan of RealVNC, but you know it's been over a decade now and I'm not that set in my ways that I can't change so I installed it to see whats new.

Turns out there a lot new! Some crappy licensing scheme whereby you have to sign into an account through their website before you vnc onto anything. This is not a grteat user experience and it's not in the RaspberryPi's way of doing things eother. So it has to go.

Remove RealVNC

If you want to do away with it also, just follow these instructions, in terminal or over SSH:

dpkg --get-selections | grep vnc

this will show you what the name of the package is you need to install, then un-install it:

sudo apt-get remove realvnc-vnc-server

Install TightVNC

Then we can move on to getting tightvnc installed

sudo apt-get install tightvncserver

then run it to configure it

tightvncserver

once you've set up some options/passwords it will then start an instance for you, we'll go ahead and kill it then start up a new one with a better/your own resolution

vncserver -kill :1
vncserver :1 -geometry 1024x1024 -depth 24

Connect

you can now connect using the hostname/ip of your pi from your viewer

raspberrypi3:1

Autostart

to start the server everytime you boot your pi, you can do the following:

sudo nano /etc/rc.local

then type in

echo "Starting VNC server on port 5901"
sudo -u pi tightvncserver :1

ctrl+o, enter and ctrl+x to save and exit.

Running a SSL certificated/authenticated HTTPS website over dynamic DNS

03/06/2017 08:00:00 am

One of reasons why I originally held back on buying a SSL certificate for my website was that I wasn't sure that it'd work using a dynamic IP routing service - cos I didn't want to waste $15!

Well you don't need to fret about it because I can confirm it works after I eventually got round to setting up a free LetsEncrypt.org one on my RaspberryPi.

Now you've got the answer, why don't you head over to my tutorial of how it's done.

Free LetsEncrypt secure SSL server certificate install tutorial for Raspberry Pi

Yes, SSL certificates work with dynamically routed domains on dynamic IP adresses.

Integrate an OLED screen into your Raspberry Pi 3 project or case

02/21/2017 10:12:25 pm

Logitech K375s Wireless Keyboard review

02/21/2017 10:04:40 pm

I've had my Surface Pro 3 now for a while but have not fully commissioned it as my everyday computer as I've been struggluing to integrate it into my setup.

As my ageing gaming rig is nearing the end of it's life I've been reluctant to let it go and have been

Verdict - Great

Love the key feel
nice and light
wakes my Microsoft Surface Pro 3 from hibernate
very quickly switches between devices
can add additional unifying receivers to pair aditional computers via USB

what I don't like

missing the right win and context menu button
would have like some more customisable options

Free LetsEncrypt secure SSL server certificate install tutorial for Raspberry Pi

02/18/2017 08:00:00 am

For me being SSL secured has always been the benchmark as having a bonafide website. That little green padlock next to your websites URL doesn't really mean much for a blog, but I always wanted one. At the start of last year I started thing about trying to get one.

Ever being a skinflint I wanted to pay nothing for it, but logic overcame this thinking as all the free ones either had massive catches that you had to manually renew them every thirty days or they were so free that browsers just didn't rattify them as being legit.

I'd decided at this point to drop the princely sum of $15 on a three year Comodo certificate from these guys. There was a possible stitch up of evorbadent fees come renewal time but my limited research showed up that like car insurance you just go somewhere else to pick up a bargain price again.

Roll on one year and I still hadn't got round to doing it but in my quest to re-find where I'd intended to buy when I came across LetsEncrypt.org, free, auto-renewing and recognised by all the major browers - cool!

So I'll crack on with how to get it on your machine in not time at all....

Instructions

So before we start, check the version of the operating system you are running as the install instructions do vary:

cat /etc/os-release

Certbot

My RaspberryPi Model B is running wheezy so the install instructions from the Certbot wesite gives this this (check the rest of the page in the link for instructions for your OS if different):

wget https://dl.eff.org/certbot-auto
chmod a+x certbot-auto

Authentification

There are two ways for you to prove ownership of the website:

let Certbot automatically authenticate it by launching a standalone server temporarily
or setting up a directory for it to place some files temporarily

I did try the former byrunning the command

./certbot-auto

But for some reason I got an error back that there was already a server running on port 443 - fairly obvious really, and I would have thought that it could automatically stop the apache server for a moment to enable it to complete seeing as it'd have to in the future to renew your licences. Having read back the instruction in writing this post perhaps I should have added a --apache switch, but too late now...

Webroot

As I said, the other method requires use of an available folder to serve up some files, as my website runs some pretty comprehensive Rewrites chances of it working were slim so after a quick google showed up this set-up in Apache's generic.conf - should be handy for me to be able to quickly get certificates to my other hosted sites later if all goes well. Here is the code you'll need to add for a multisite webroot. To add it, fire up the config with:

sudo nano /etc/apache2/conf.d/generic.conf

and add this code:

Alias /.well-known/acme-challenge/ 
/var/www/html/.well-known/acme-challenge/ 

     Options None
     AllowOverride None
     ForceType text/plain
     RedirectMatch 404 "^(?!/\\\\.well-known/acme-challenge/[\\\\w-]{43}$)"

To save and exit ctrl+o, enter then ctrl+x. Make sure to change the path to wherever your website is served from in then above then you'll have to reload to apache config at this point to go ahead and and run:

sudo service apache reload

then we can get on with running Certbot and following the instructions:

./certbot-auto certonly

all was reported back as having successfully completed after a short time so good to go.

Certificate install

This is the bit of the process that took the longest for me - I'll admit most of it was user error! I have been runnig a server on port 443 with a snakeoil certificate for my admin login portal for a while and it has to be said the the default apache host script for it is rather lengthly so it took a bit of head scratching before I realised that I was still getting 'get me out of here' messages from my browser was that I hadn't removed all of the code from my previous config.

To add it to apache's default config, :

sudo nano /etc/apache2/sites-available/default-ssl

update with the locations of your certificates:

SSLEngine on
SSLCertificateFile    /etc/letsencrypt/live/yourdomain.com/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/yourdomain.com/privkey.pem
SSLCertificateChainFile /etc/letsencrypt/live/yourdomain.com/fullchain.pem

reload apache's config:

sudo service apache reload

And head over to your site to see if it working. If it is not working at this point then check your site at this handy website for diagnostics on your auth status:

https://www.digicert.com/help/

Renewal

Add cron job to auto renew

crontab -e

then add this

# Renew letsencrypt certificate 31 3 * * * /home/pi/bin/certbot/certbot-auto renew --quiet --no-self-upgrade --post-hook "sudo service apache2 reload"

then we are all done, I've still got a couple of months to wait to see of it actually workks, but stay posted and I'll let you know.

Additional domains

I was actually so pleased with the results that I went straight ahead and added my other domain philfish.co.uk to the certificate as well, I can report that the multiple webroot worked a charm and the whole process was finished in a couple of minutes just with the --expand switch with this bit of code.

How to make an clock for a raspberry pi with an OLED display

02/10/2017 08:00:00 am

My dream RaspberryPi project that I am slowly chipping away at is to create the ultimate alarm clock. It is a slow burner though as at it's inception I had none of the skills I needed to pull it off.

I've now amassed all of the technical skills to pull it off and I'm just left with finalising the design and finding the time and the parts (not a lot then!).

An interim project along the way is whipping together the first prototype of the time display re-using some of the parts from an old Arduino project.

What you need

Pretty easy, you don't not a lot is required, obviously you'll need a:

OLED display

There is a range of available units in different colours and with different pin-outs. This tutorial is based on these are the instructions for the four pin variety - vin, sda, scl & gnd.

Some have five or more including rst but they'll require different libraries to the ones we'll be using here so I wouldn't go for one of those. Just head over to ebay and search for 'oled i2c' and for between £2 and £7 you'll be in business. I'd go for one with the header pins already soldiered for you as if you haven't got a soldering iron it will be a massive inconvenience.

You will also need either:

a bread board and 4 male/female breadboard wires;
or 4 female/female breadboard wires.

Install the software

First up we'll update you RaspberryPi with the required packages, hopefully you are familiar with either the terminal window or SSH, I won't go through them here we will just crack straight on with it

we'll be downloading packages to download projects from Github, Python and I2C:

sudo apt-get install git-core
sudo apt-get install python python-dev python-setuptools
sudo easy_install Pillow
sudo apt-get install python3 python3-dev python3-setuptools
sudo easy_install3 Pillow
sudo apt-get install python-imaging python-smbus i2c-tools

I've included both Python 2 & 3 here but you only really need one of them if you are short on space.

Libraries

Here we are going to create a folder in you'r home directory and download the python libraries we'll need to operate the display, these are the main display calls, a funky font which looks just like an LCD display and some code so we can run the scripts in the background as a system service (as I describe in this tutorial)

cd ~
mkdir oled
cd oled
git clone https://github.com/BLavery/lib_oled96.git
git clone https://github.com/keshikan/DSEG.git
git clone https://github.com/metachris/python-posix-daemon.git
git clone https://github.com/mc3k/OLED-Clock.git
cp

Wire up the pi

Probably the most daunting bit, but in the big scheme of GPIO this is about as simple as it gets - just refer to the

Obviously the usual disclaimer applies - if you electrocute yourself, blow up you RPI or burn your house down - don't come to me with your law suit; you need consult a someone who is fully trained up in electronics to guide you through it if you are unsure about any aspect of what you are doing regardless of what I've written!

Legalities out of the way, you just need to connect the four pins of your display to their corresponding pins on the pi - consult the header labels on your OLED with the pinout diagram. It is pretty straightforward in this case as they are all grouped together at the end of the header. The VCC pin can usually be connected to either 3.3v of 5v in the case of this component but check your specs to make sure.

Wiring diagram

Pinout diagram

For the RPI2 & 3, if you have a different version consult the relevant diagram for your model.

After you have checked and

Finalise the Pi setup

sudo raspi-config

and enable i2c in the advanced menu, then reboot and see if we can detect the screen:

sudo reboot
sudo i2cdetect -y 1

after the last command you will get a readout telling you that your pi has picked up your display:

     0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
00:          -- -- -- -- -- -- -- -- -- -- -- -- --
10: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
20: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
30: -- -- -- -- -- -- -- -- -- -- -- -- 3c -- -- --
40: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
50: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
60: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
70: -- -- -- -- -- -- -- --

You can see mine here popping up a 3c

Lets start the coding

So now we've got all the pre-requisites out of the way we can get on with firing up the display and getting coding. The way we'll go about it is to grab the various files we need from the libraries and copy them into the working directory.

I'm doing it this way to keep it simple as there's an overwelming amount of files knocking about in the subfolders.

First up is the OLED library and some bare bones code to get us started

cd /home/pi/oled
cp lib_oled96/lib_oled96.py .
cp OLED-Clock/python/clock-min.py .

then we can fire the script up to see if it works

python clock-min.py

hopefully you should be seeing the display pumping out the time and date along with a the time scrolling up on the command prompt. Jobs a good-un.

To cancel just hit ctrl+c.

If you want to have a look at the code just use the following command:

nano clock-min.py

Next up is a more fancy looking script:

cp lib_oled96/FreeSans.ttf .
cp OLED_Clock/python/clock.py .
python clock.py

The third is has a more traditional look of an LCD clock, this one also only updates once a minute instead of once per second - I've not test the performance overhead of these script but it may have less overhead:

cp DSEG/fonts/DSEG7-Modern/DSEG7Modern-Bold.ttf .
cp OLED-Clock/python/clock-dseg.py .
python clock-dseg.py .

Running as a background service

As good as these scripts are they are pretty useless in their current state as they tie up the terminal/SSH session stopping you working and quitting the python job if you exit them. So what we need is to run them as a background service, fortunately this is pretty easy when you know how, first off copy the files:

cp python-posix-daemon/src/daemon2x.py .
cp OLED-Clock/python/clock-dseg-d.py .
cp OLED-Clock/python/clock-d.py .

We can start and stop the clock with these commands:

python clock-d.py start
python clock-d.py stop

python clock-dseg-d.py start
python clock-dseg-d.py stop

As you see you it doesn't hog the bash prompt and you can even quit your terminal / SSH session.

Starting at boot

if you want to run the script at boot you'll need to

sudo nano /etc/rc.local

then type in

# Start the OLED Clock
if [ 'i2cdetect -y 1 | grep 3c' ];  then
        echo "Starting python logging daemon\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n"
        sudo python /home/pi/oled/clock-d.py start;
else
        echo "no";
fi;

How to run a python script as a service / in background / as a daemon

02/02/2017 08:00:00 am

So you've written a killer python script and you want to run it in the background as a service because when you close the terminal window/SSH session it kills it. I originally worked it out when I wrote my Twitterbot app, and was going to write it up seperately then but it took me so long to get it running in the background and I tried so may things that I couldn't actually tell you how I eventually managed it!

It was only as I was working on my new project - an OLED clock for my new mediacentre pi that I finally worked out how easy it actually was; here are the instructions.

First we'll get the code using a terminal window or SSH:

cd ~
git clone https://github.com/metachris/python-posix-daemon.git
cd python-posix-daemon/scr
ls

so you'll see the three files which do the business:

daemon2x.py
daemon3x.py
daemon-example.py

you'll obviously need python at this point tp do anything so I'll assume you have this, we can try the provided example file to see if everything is working:

python daemon-example.py start

and

python daemon-example.py stop

to stop it.

At this point you'll notice that nothing really happened - thats because the sample dosen't actually do anything, we can have a look by openeing the file

nano daemon-example.py

it just sleeps for 1 second and then repeats.

If you want to see an example of a script that does something you can use my example:

It creates a log file ant writes the time to it every two seconds. Download the script:

wget https://gist.githubusercontent.com/mc3k/0d0f19d6a92387d2b564cbaec3cdcc54/raw/80a8a462de179dc7a0f359ac1a860a4d2e39b2d0/daemon-example2.py

and start it:

sudo python daemon-exampl2.py start

leave it for and few seconds then stop it:

sudo python daemon-example2.py stop

we can see the results using:

cat /var/log/daemon-example.log

and there you have it, everything is easy when you know how. If you want to put it in you library then you just need to put it in a python sys path.

if you want to run the script at boot you'll need to

sudo nano /etc/rc.local

then type in

echo "Starting python logging daemon"
sudo python /home/pi/python-posix-daemon/scr/daemon-example2.py start

but I wouldn't recommend it for this script.

Desk stand and holder for Surface Pro 3 & 4

01/31/2017 08:29:18 pm

How to pivot data by month in SQL and output to a temporary table

12/28/2016 08:00:00 am

There are some things that aren't easy to do in SQL as you'd like. I don't know why it has to make the seemingly simple things to complete in Excel so annoyingly tricky.

Dynamic pivots is one of those things that is overcomplicated, sure there's plenty of tutorials on the t'internet , but what they all seem to lack is instructions of how to write them to a temporary table, I'm a big fan of temp tables in my workflows and on this occasion google has been drawing blanks.

So if you are struggling to work it out you have to use a global temporary table as EXEC command you run actually uses a different process so your process won't see it after it has run. I also had a couple of attemps to get it in the right place - SQL isn't really helpful in this respect either - the code highlighting doesn't extend that far.

Anyway, I'll get to the example so you can see for yourself:

Print 'Pivot transactions'

begin try drop table ##mcTemp1 end try begin catch end catch

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @Qry AS NVARCHAR(MAX)
DECLARE @FromDate AS NVARCHAR(MAX)
DECLARE @ToDate AS NVARCHAR(MAX)

Set @ToDate='2015-10-31'
Set @FromDate=DateAdd(MONTH,-24,@ToDate)+1

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Month)
FROM (SELECT DISTINCT Convert(Date,[PAY_DATE]-DAY([PAY_DATE])+1) AS 'Month' FROM [WHS_PAYMENT_HISTORY] 
WHERE PAY_DATE between @FromDate and @ToDate) AS Months
ORDER BY MONTH


Set @Qry = N'SELECT ACCOUNT_NUMBER,'+@ColumnName +'
INTO ##mcTemp1
from
(
SELECT ACCOUNT_NUMBER,
	[PAY_DATE]-DAY([PAY_DATE])+1 AS ''Month''
    ,[PAY_VAL] As Payment
  FROM [WHS_PAYMENT_HISTORY]
  WITH(NOLOCK)
  WHERE PAY_DATE between ''' + @FromDate + ''' and ''' + @ToDate + '''
) pd
pivot
(
SUM([Payment])
FOR [Month] in (' + @ColumnName+')
) As Pvt'

EXEC sp_executesql @Qry

SELECT * FROM ##mcTemp1

How to set up your Dell E310dw printer the really easy, bloat free way on windows

12/02/2016 08:00:00 am

One of the common themes of the computer industry is to try to install a load of bloat on your computer under the guise of making it easier for you, well when I first got my Dell E310dw and had a flick through the setup guide to head off any such shinangans I noticed they had some sort of 'universal' installer.

Trying not to fall for that I steamed ahead to wing it myself, turned out to be pretty easy really, just follow these steps:

Unbox
plug in ethernet cable from printer to router
turn on

That's it, seriously! I went to the 'Devices and Pirnters' screen in Windows 10 to go and try to install it as a network printer and it was the already waiting to go.

I didn't actually believe it so I fired of a test page and it printed - magic!

One of the option I was missing though was the booklet print option, so if you want to get to all the advanced settings you do need to install the dell drivers. You can do that easily

Go to the Dell download page for the printer
Download the latest Windows driver and unzip it
Search for device 'Device Manager' in the start menu
Expand the printer option and right click on 'Dell Printer E310dw'
Click update Driver Software
then 'Browse my computer for driver software'
then 'Let me pick from a list of drivers on my computer'
then the 'Have Disk' button
Then navigate to where you unpacked the divers and follow it through Drivers/PCL/32_64 and select DLOHL13A.INF

Then you are good to go, fire up the printer properties pages and you get:

For extra enjoyment, fire up you web browser and make your way over to the IP address of your printer and you'll find yourself up to you neck every option and bit of info you could think of including Google's Cloud print gubbings.

How to install Dell Printer E310dw on your Raspberry Pi

11/24/2016 08:00:00 am

I'm always one for saving a bit of money and many years ago I came up with the plan that I could save a lot of money in printing costs if I didn't own a printer. It is a great idea, you'd be suprised at how much unnecessary printing you get up to and there's always the kick in the teeth of inkjet printer ink being more expensive than gold!

However, ever since I learned how you could refill some laser toner cartridges for next to nothing, I've always wanted one of Brother's epic little mono laser printers. Over the years I've been watching them add features such as the fully enclosed paper cartridge, ethernet conectivity and even WiFi.

So when it came to our trip to Cambodia I had to bite the bullet as we were travelling around a bit we had far too many hotel, bus, tours, boat rides and flights that it was going to be a logistical nightmare to beg, steal and borrow printing capbility.

I was initially going to get on of the aforementioned Brother HL-L2XXX models, but a quick perusal on ebuyer.com sees that the ethernet version is the thick end of £80. They did however have a seemingly better, wireless enabled Dell E310dw for a measly £30. The reviews looked good and a new Dell high yield toner cartrtidge was only the same price as the extra the Brother would cost to buy + the cost of a refill kit. So if I couldn't refill the cartrigde I would be no worse off.

So I took the plunge and couldn't be happier, I can print my favoured booklet print and the printer was super easy to set up without installing any bloatware.

When it came to installing the printer on the raspberry pi it got a little trickier, Dell does list it as being linux compatible but only actually supplies it with drivers for Ubuntu. I tried a few of the tutorials of installing generic PCL drivers, i386 drivers and a bunch of other stuff but to no avail.

It was during my scratching around the internet that I actually discovered that my Dell E310dw was actually a rebranded Brother HL-L2360, magic. Unfortunatley there ain't no Brother Raspberry Pi either driver either but a bit of uncovering later led me to another option:

Install CUPS on your Pi using these instructions

http://www.howtogeek.com/169679/how-to-add-a-printer-to-your-raspberry-pi-or-other-linux-computer/

Add printer by clicking the button on the administration webpage and when you get to the diver selection list, go for:

Brother 'HL-7050N-CUPS+Gutenprint'

Et voila.

To test it out, set it as your default printer in the administration tab and then try this bit of code:

echo "test" | lp

The easy way to find and delete duplicates in SQL

06/03/2016 08:00:00 am

Next in my series of helpful SQL code is duplicates, I seem to always be having issues with duplicates. This annoys me immensely as all of the issues comes from other peoples data I have to work with.

No problem I hear you say, google is awash with ways of how to delete them. Trouble is these are all based on blindly deleting superfluous rows without care of how they got there and any specific criteria on the order of how to delete them.

Identifying duplicates

First up is you have to identify the duplicates in order to understand why they are duplicated in the first place, using this nufty bit of code we can pull up a table only containing the duplicates:

select * 
from [#Table1]
where [Person] in (
    SELECT [Person]
    FROM [#Table1]
    group by [Person]
    HAVING (COUNT(*) > 1)
)

Deleting rows

Having checked the duplicates we can proceed to getting rid of them. If the entries are pure duplicates and we just want to delete the extra we have two ways of proceeding. We can either

delete the duplicates in situe or
or filter out the duplicates in a query

Delete duplicates from a table

Firstly we add an automatically incrementing number field to the table, then delete the record that is not the first occurrence of item in the selected field:

ALTER TABLE [#Table1] ADD AUTOID INT IDENTITY(1,1)
DELETE FROM [#Table1] WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM [#Table1] GROUP BY [Person])

Nice and straight forward.

Filter out duplicates in a query

The trick with this one is that you just have to group by all the fields you want to un-duplicate, though this is invariably all of them!

select AccNo, Person, Bikes from #table1 group by AccNo, Person, Bikes

What you have to remember is that you need to know which fields are duplicated in the first place.

Smart deletion of duplicate records

This is the scenario where we've looked at the duplicates and decided the course of action needed to weed out the duplicates. This is usually to route out the oldest date or the lowest values, this is the solution you need - sort the dataset into a new table then go ahead and delete the superfluous rows:

select * into #Table2 from #Table1 order by [Date1] desc, [Field1] asc
ALTER TABLE #Table2 ADD AUTOID INT IDENTITY(1,1)
DELETE FROM #Table2 WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM #Table2 GROUP BY [Person])
ALTER TABLE #Table1 drop column AUTOID

Now all we need to do is get people to check their data for duplicates before they send it to save us hours of work!

How to add an auto incrementing ID field in SQL server

05/12/2016 08:00:00 am

So it turns out that SQL is really quick to pick up, once you have the (very) basics down everything you need to do fall intuitively into place as it is all based on the first principals of a select statement. And even if you do get stuck all answers are a very rapid google search.

The biggest trouble I have with starting off programming any new languages is that I expect to hit the ground running and just jump into the complex stuff without going through the basic tutorials. THe problem with this is it can them be tricky to actually google the right term to get the answer to your problem. So in light of this I've decided to log all the little bits and bobs which fall into this category as I come across them.

So stay tuned for further posts in the series under tag SQL.

One of the first things which fell into this category were row numbers. The normal way people tell you to deal with this it is to append a table with a new column with the row numbers in. Nice and easy:

ALTER TABLE #Table ADD AUTOID INT IDENTITY(1,1)

This is all well and good but what I need most regularly is to add row numbers into outputs of queries as I run them, I don't want to have to write the results to a table then append the row number on. Fortunately the code is simple and it will also let you order the results at the same time by using the OVER function:

Print'Add row numbers to an output'
select 
     ROW_NUMBER() over (order by [FirstColumn]) as AUTOID
    , *
from #Table

How to add better comments into SQL server

04/26/2016 08:00:00 pm

Whilst I'm firmly in the 'Tried and Test' camp for most things in life and I may only be new to SQL programming but there are just sometimes when I have to buck the mainstream trend and do it my way.

One of these occasions is comments in SQL, the widely adopted way would be to use the prescribed double dash -- or the widely adopted across multiple languages slash-star:

-- comment here
/* another type of comment */

However in this instance I'm going to declare my way is best! I put my comments into a print statement (not in the middle of the code obviously)

Print'Comment here'
Then code

Not only does it stand out better in your code editor's syntax highlighter but it makes the messages log more verbose when you are debugging or reconciling record numbers throughout your scripts.

How to connect a photoresistor or light dependant resistor to an ESP8266-12E

01/01/2016 08:00:00 pm

Having purchased my first Raspberry Pi a couple of years ago now I've still not got round to implementing some Internet of Things (IoT). The trouble was that I wasn't really convinced it was suitable for the job as I wanted something able to be battery operated to work in remote situations as the power draw was just too great.

I liked the idea of the Arduino stuff but it was as expensive as RaspberryPis once you've hooked it up with wifi and the like.

A couple of weeks ago I stumbled across the awesome ESP8266 boards - just what I was looking for. Small, inexpensive and had wifi and an antenna built in. When I say inexpensive I really do mean it you can get going for just a couple of quid.

For my first foray into the scene I opted for a NodeMCU v1.0 development board as it has a USB connection for uploading firmware and power and if you buy a slim one you can connect it to a breadboard for prototyping.

I already own the CamJam EduKit 2 - Sensors lying around as I hadn't yet got round to hooking it up the Pi but I wanted to try it out on the ESP8266-12E. Trouble is there doesn't seem to be any decent instructions on the internet of how to hook up the light dependant resistor. After a bit of scratching around I found out the required resistor (10k ohm) and a circuit diagram from here and I was away.

What you need

List of parts required:

ESP8266
LDR / photoresistor
10k ohm resistor
breadboard
computer with Arduino IDE with ESP8266 library installed
USB cable
Male-Male Dupont/breadboard wires

Circuit Diagram

The LDR actually has a an analogue output so it gets connected to the A0 pin. Quite simple really, just wire your prototype up like this:

The code

Simple really, we will just use the example code in the Arduino IDE - go to

File/Examples/1.0Basics/ReadAnalogueVoltage

// the setup routine runs once when you press reset:
void setup() {
  // initialize serial communication at 9600 bits per second:
  Serial.begin(9600);
}

// the loop routine runs over and over again forever:
void loop() {
  // read the input on analog pin 0:
  int sensorValue = analogRead(A0);
  // Convert the analog reading (which goes from 0 - 1023) to a voltage (0 - 5V):
  float voltage = sensorValue * (5.0 / 1023.0);
  // print out the value you read:
  Serial.println(voltage);
}

The output

All we need to do next is to fire up the serial monitor, select 9600 baud and watch away:

2.16
2.13
2.09
2.07
2.06
2.04
2.03
2.02
2.01
2.00
2.03
2.09
2.15
2.18

You can also use the serial plotter to see a graph of your results:

Next...

You are then ready to integrate it into your own project.

Best way to optimise images and pictures to display on the web

11/19/2015 08:00:00 am

Conscious that hosting a website from a home internet connection from the Raspberry Pi may lead to slower load time than you'd want I've been working on speeding up its response.

I've always been focused on server speed throughout its whole development in a bid to speed up load times. I think I've got it cracked – but the only thing I'm lacking is a decent workflow for optimising the file size of the images I'm serving up.

I played around with a few web and windows tools but the process all seemed to be time consuming and cumbersome.

IrfanVIew

After a while I settled upon an add in from a tried and tested bit of software that I've been using since I can remember - IrfanView using the 'Save for web' plugin. It is easy, quick and also tune-able whilst also providing a live view of what the output would look like.

It is actually really quick if you've associated it to automatically open image files, my workflow was to drop the required image into the folder on the web-server, hit enter, select the save for web - the GUI pops up and you just hit save to overwrite the file with the optimsed settings.

The results were good, I was getting some good file size reductions. Although it doesn't take too long what is really needed is some automated way to transform your image in a single click.

ImageMagik

I've been a user of imagemagick for a while now so this was my first port of call in testing. After a bit of playing around I soon realized that all I needed was to to just change the quality to 70. Easy peasy, just need to run the following code and you are done:

convert sample.jpg -quality 70 output.jpg

Note for windows users, if you try and run convert.exe without specifying a path the the executable it won't work, this is because there's another convert.exe system file which it uses in preference. To fix this I simply rename it to img_convert.exe and dump it into my windows directory. The code would now be

convert sample.jpg -quality 70 output.jpg

Exiftool

The next trick is to remove all of the EXIF metadata attached to the image, you wouldn't probably think it isn't many bytes worth but a) they all add up and b) you'd be suprised how much info the modern digital camera now spits out.

So go ahead and install a Exiftool on you system and run the following code:

exiftool -all= output.jpg -overwrite_original -P

Bringing it all together

Now all we have to do is clobber the code together and we are away.

Linux

go ahead and create a new file:

nano optimiser.sh

and paste the following code in:

convert $1 -quality 70 $2 && exiftool -all= $2 -overwrite_original -q

we then have to make it executable with:

chmod +x optimiser.sh

and now we can run it using:

optimser.sh input.jpg output.jpg

Windows

It is a similar story for windows, just create a file called

optimser.bat

open in notepad and paste the following:

img_convert %1 -quality 70 %2
exiftool -all= %1 -overwrite_original -P

Drag & Drop

With windows we can add an extra flourish - you can set up the batch file so that you can optimise your jpg by just dragging the image on the .bat and it will convert it automatically in the GUI without going near a command prompt:

Create a new file:

optimiser_DD.bat

and enter this code:

@echo off
copy %1 %~n1"_"%~x1
img_convert %1 -quality 70 %1
exiftool -all= %1 -overwrite_original -P

What the code does here is to copy the input.jpg to input_.jpg then optimise the original file.

The results

I was quite impressed by the results, I tried it on a plethera of files. The most notable reductions come from digicam images, here's a typical example

Original digicam jpg: 2843 kb
Reduced to quality 70: 650kb
then exif info removed: 595kb

As we can see, the reduction is an impressive 79% reduction.

Extra credit

Head over to GitHub for the extended versions of the scripts with multiple command line options which gives you control of the quality parameters and lets you also resize the image:

Web image optimiser on GitHub

Add a mailing list subscription to your website

11/11/2015 11:09:54 am

How to create real time graphs of CPU temperature on a Raspberry Pi

11/03/2015 08:00:00 am

Probably heavily linked with my inner analyst, for a while now I've wanted to create some real-time graphing of some of my raspberry pi stats. I've had a couple of stabs at it but getting it to actually work has eluded me until now.

The below example guides you through how to plot your CPU temp as a graph to an image file, the code is in my github repository where I'll hopefully add further functionality in the future.

How to guide

First off you need to install Python3

sudo apt-get install python3
sudo apt-get install python-setuptools
sudo apt-get install python3-pip

Next install GNUPlot

sudo apt-get install gnuplot-x11

Then we need to setup a working directory:

cd ~
mkdir plottemp
cd plottemp

create the necessary files:

nano datagen.py

Paste in the following code:

#!/usr/bin/env python

import sys, os, logging, urllib, datetime

def fetchtemp():
	cmd = '/opt/vc/bin/vcgencmd measure_temp'
	line = os.popen(cmd).readline().strip()
	output = line.split('=')[1].split("'")[0]#+' C'
	return output 

format = "%Y-%m-%d,%H:%M:%S" 
today = datetime.datetime.today()
s = today.strftime(format)
output = s+' '+fetchtemp()+'\\n'

print(output)

with open('/home/pi/plottemp/tempdata.dat', 'a') as f:
	f.write(output)

Create this also

nano plottemp.sh

and paste the following:

#!/bin/bash
echo "set terminal png size 900, 300
set xdata time
set xrange [ time(0) - 86400 : time(0) ]    # 86400 sec = 1 day
set timefmt '%Y-%m-%d,%H:%M:%S'
set format x '%H:%M'
set output '/home/pi/plottemp/tempplot.png'
plot '/home/pi/plottemp/tempdata.dat' using 1:2 with lines" | gnuplot

make it executable

chmod +x plottemp.sh

next all we have to do is to run the script every 5 minutes (or whatever interval you choose) with cron:

crontab -e

and add this line:

*/5 * * * * python3 /home/pi/plottemp/datagen.py && /home/pi/plottemp/plottemp.sh &> /dev/null

and there we have it, it will generate a file called:

plottemp.png

The result

If you have successfully implemented the instructions you should get something like this:

How it works

I'll very briefly walk you through how it works - there's a cron job running every five minutes which firstly runs a command line function which reports the CPU temp and saves it to a log file. Upon completion it then runs a GNUPlot command to generate a graph of the last 24 hours logging.

For extra credit

There will be a few formatting tweaks to the graph to come and I hope to include more stats so stay tuned folks!

You can head over to my graph-stats Git repository for latest updates with extra embellishments

Its thanks to this post at raspberrypi-spy which eventually got me on my way. Thanks Matt

How to log and plot network activity graph on a Raspberry Pi

10/29/2015 02:41:21 pm

When I first got the pi up and running as a webserver and implemented some WAN logging I was freaked out by the amount of probing going on eminating from remote computers.

There's always the fear that some unscrupulious hacker has taken over your pied-and-joy (see what I did there!) and using it to route some more than illegal traffic through your network connection to cover their tracks. Trouble is with all this logging you can't see the wood from the trees.

So I got to thinking, whilst in the early days there wasn't a lot of traffic through my website I'd easily be able to tell something was up

sudo apt get install gnuplot

. In fact https://www.raspberrypi.org/forums/viewtopic.php?f=36&t=7390

http://serverfault.com/questions/448768/cat-proc-net-dev-and-ip-s-link-show-different-statistics-which-one-is-lyi

How to make and set up a twitter bot on your own server / Raspberry Pi

07/12/2015 08:00:00 am

As a follow up from my ealier post introducing my twitter bot I thought I'd tell you how I did it and how to set one up on you own RaspberryPi or other Linux server.

How it works

It's not that tricky when you think it through, it is just hooking a few existing processes together to create your working bot. The only real tricky part for me was that I hadn't used Python before and had to learn it from scatch!

Here's a quick run through of what I used:

Twitter API - stating the obvious really but you need to use the api to do anything on twitter!

Twython - a Python wrapper for the the twitter API, it takes all the hard work out of posting and reading tweets

Python3 - you'll need to do all your programming in python to make use of the Twython

Python daemon - for running you script as in the background as a service

Wolfram|Alpha API - to answer questions

Wikipedia API - to get more answers

What you need to do

There is quite a lot of things to cover here so I'll not go into much detail about those tasks which are easy to complete with a little googling and I'll concentrate on the things which I struggled with.

Twitter

First off you'll need a twitter account, I started a new one just for my RaspberryPi to tweet from. Then you'll need to go ahead and create an app within your twitter account and generate some keys for use with the API.

Wolfram|Alpha

Same here, open an account and set up an app to get an API key

Set up your RPI

Folder

You'll need a folder to put all your code in, I set mine up in a directory in my home drive, something like:

/home/usr/pi/bin/tweeter

Use these commands to get you going:

cd ~
md bin
cd bin
md tweeter
cd tweeter

Daemon service

Next up you need to download the Daemon class suitable for Python 3.x - daemon3x.py script and save it in the directory. You can do this in command prompt:

wget http://www.jejik.com/files/examples/daemon3x.py

Install Python3

run the following in the command prompt:

sudo apt-get update
sudo apt-get install python3

Install Twython

run this from the command prompt:

sudo apt-get update
sudo apt-get install python-setuptools
sudo easy_install pip
sudo pip install twython

Install my tweetbot script

Download tweetbot-d.py and save it to your directory.

wget https://www.childs.be/data/uploads/tweetbot-d.py

Or you can get hold of the code over at GitHub:

tweetbot code GitHub

Open it up in your favourite text editor and add in your API keys for Twitter and Wolfram|Alpha, then change the twitter ID of the stream you want to follow and additionally the superuser id which will enable you to turn the tweetbot off remotely should you need to:

nano tweetbod-d.py

Save and exit:

Ctrl+O
Ctrl+X

Fire up your tweetbot

We should now be ready to start it up, you can use the following commands to start, stop and restart the bot

python3 tweetbot-d.py start
python3 tweetbot-d.py stop
python3 tweetbot-d.py restart

Logging

By default the script will log all activity from the bot to a file named

tweetbot.log

you can check on everything your tweetbot has been up to in this file:

nano tweetbot.log

Start the tweetbot when your Pi boots up

If you want to start the bot up automatically when the server boots up follow these commands:

sudo nano /etc/rc.local

then add these lines (obviously change the directory to the one you have saved your script in)

# Start tweetbot service
echo "Starting tweetbot script"
python3 /home/pi/bin/tweeter/tweetbot-d.py start

Using the tweetbot

The script runs in the background monitoring any tweet containing your @twitter_handle, it then kicks in to analyse the content of the tweet, on discovery of the following hashtags:

#cputemp
#uptime

it will run a run some command line code to reply back to the user with the appropriate answer. On detection of:

#question

it will run a search through Wolfram|Alpha of the rest of the text in the tweet and reply with the answer. If Wolfram doesn't come back with anything it will try wikipedia as well.

Should you need to turn the twitterbot off for some reason when you can't access the Pi by usual means there is a kill switch built in. Simply tweet:

#stoptweeting

from your specified superuser account and the tweetbot will shut itself down.

The blog has now reached 50 posts!

06/02/2015 08:00:00 am

Well, I didn't think I had it in me to write so much, I'm generally not a person with much to say (this is a lot for me!).

My general interest in all things tech had always since the early days compelled me to want to make my own website, trouble is you actually need to have some content to put on it.

After a while toiling I'd decided that I'd like to use Blogger and write posts about excel. Great, I thought I was onto something here. I knocked my first two posts out on the same day in 2008 (I've re-published them on this site), but my third took another 6 months to appear then nothing after that.

It was then another five years before I blogged again, this is when I had the idea that I could blog about my favorite bike rides. Great as this also gave me the green light to get a RaspberryPi - I had always wanted one but couldn't really justify it as I had really got a use for it.

Now I was on, hosting my own web server and knocking up my own site. Ali weren't so happy in the early days - apart from cycling, eating and working I did little else (not much sleep either) as I had a lot on my plate. I'd previously only lightly tinkered with Linux and I'd yet to write a line of php so there was a lot to learn.

It actually turns out that I had much more than bike rides to share (its all relative - much for me isn't that much), and hopefully I've another 50 more posts in me. I don't think I've a novel or even a short story in me, and I think there's zero hope of an autobiography!

The childs.be twitterbot is alive

05/17/2015 08:00:00 am

My latest project is now live, the @childs_be twitterbot is a go.

The original idea was to have the pi tweet pictures from the barrelcam, at regular intervals. I had that running quite quickly and my attentions turned to adding functionality.

I came up with being able to tweet the server's uptime and cpu temperature, but it seemed a bit lame really. So some more thinking later I decided that it would have to be interactive.

So the idea of the twitterbot was born (OK, it's not the most original idea but hey). A few late nights and head scrathing later I had it up and running.

In the initial version I'd integrated all the functionality to automatically reply to a mentioned tweet, you can try it yourself by tweeting the Pi with these:

@childs_be #cputemp
@childs_be #uptime
@childs_be #barrelcam

or you can even string them altogether:

@childs_be #cputemp #uptime #barrelcam

Heres an example output.

Ask a question

Wanting to build on the functionality my thoughts turned to being able to respond to anything, so by hooking into to both Wolfram|Alpha & Wikipedia's APIs I'm now able to answer questions about a broad range of topics, you can try it youyself:

@childs_be #question what is a bicycle

Take a look at my post of how I did it and how you can run your own tweetbot.

My ultimate RaspberryPi project - an alarm clock internet radio

05/10/2015 06:29:02 pm

Display your server uptime on your website

04/23/2015 03:00:00 pm

If you host your own server and website like myself, you'll probably want to distplay your current uptime somewhere on your site. You are in luck, it is easy....

It uses the Linux command uptime and strips the unecessary gubbins from the output string:

$ uptime

This results contains some useful info including number of users logged on and loads as well as the uptime:

18:10:45 up 39 days, 22:04, 1 user, load average: 0.07, 0.06, 0.08

All you have to do is to insert the following php code into your page and you are away.

You can see the results from my site at the footer of the page.

Powered by Raspberry Pi, 39 days uptime

How to log your own IP

04/04/2015 08:00:00 am

Call me paranoid but ever since the antics of ACS:Law in the noughties I've been logging my IP address, it is not because I'm am an illegal downloader, my intrigue came about from the thought of how you would prove that you didn't if I was wrongly accused.

Stay with me here, but think of the things that can go wrong in the chain of collecting torrentors IPs, requesting the info from the ISP and collating and issuing all the thousands of law suits - there are any number of times a transposition of an IP address or a time and date could happen. Hell, even the music industry didn't like the way some people were doing it!

So my solution was to log my IP address, I've been doing it for five years now and even though my RaspberryPi is also logging my IP in a couple of different ways: through Syslog-ng by logging all of my router activity and through running a cron job to dynamically update its IP address to the DNS server.

It is trickier than you think to get your external ip address, ipconfig will only tell you your internal ip:

This is because of NAT Loopback so we'll need to find another way. Google will tell you your IP address if you google it, but it will be much simpler to get it from another source that returns just the IP address in plain text so we don't have to clean up the html. For this we are going to use ip.changeip.com, I'm not abusing their service here as I already use them as my DNS.

Instructions

These instructions are for windows users only, this is on the basis that if you are a linux user you'll either a) already know how to do this or b) know how to modify this for your own requirements.

Set up your folder

Create a new directory somewhere safe on your computer, I called mine logmyip, if you are using Windows 7 you may come into a few security issues when trying to change the contents of some of the files after you've created so you may as well go ahead and get it all working in another directory within your documents for example.

wget

Wget is a software package for retrieving files using HTTP, HTTPS and FTP, it is run in the command line and comes as standard with any Linux distro. For windows its a little harder to get hold of because you are expected to compile it yourself from the source code but you can download the windows binary from gnuwin32 at sourceforge, direct link (currently 1.11.4). Extract wget.exe from the zip to your newly created folder.

logmyip.bat

Next all we need is to set up the logging script, we'll be using a batch command. Open Notepad (or similar) then paste the following text in then save as logmyip.bat. Make sure you do not save it as a text document as it won't work, choose 'All files' in the save dialogue.

echo off
CHOICE /T 10 /C ync /CS /D y
wget http://ip.changeip.com -q -O %temp%\\\\ip.txt
set /p var1=<%temp%\\\\ip.txt
echo %date% %time% %var1%>>iplog.txt
del %temp%\\\\ip.txt
pause

logmyip.vbs

This next step is not compulsory but I prefer it as it will run the script 'silently', i.e. won't flash up any annoying boxes when it runs - we'll be using a bit of vbs for this. Same as above, fire up Notepad and get the code saved down:

dim fso
dim curDir
dim WinScriptHost
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing
Set WinScriptHost = CreateObject("WScript.Shell")
WinScriptHost.Run chr(34) & curDir & "\\\\logmyip.bat" & chr(34), 0
set WinScriptHost = nothing

Scheduling

There are a couple of choices here, depends how often you want to log you IP address. My Sky router is pretty stable and doesn't reset itself very often resulting in a change of IP - I only run it once a day. I've scheduled this but just getting it to run at startup. This is easy to setup - right-click the vbs file you created above and copy, then right-click somewhere in your folder and paste a shortcut. Then all you do is drag this shortcut over to the start menu, then keeping the left click on navigate through 'All programs' then into the startup folder. Job done - it will then run every time you boot up.

If you need to log more often than this you can schedule it in Task Scheduler.

Running a my ip script on your own server

If you want to host your own IP revealer, just use this php code below:

It is also coded that if you run the query string ?td=y then it will also spit out the server time and date, you can check it out here.

How to display Flickr images on your own website

03/28/2015 08:00:00 am

I've struggled for a long time trying to find a way to elegantly and simply display my flickr photos on my site. Up 'til now I've had to stick with slideshow apps from other websites like flickriver.

This is until I found this blueimp Gallery when I was looking for some lightbox coding for my website. I didn't end up using the blueimp lightbox because I went with fancybox as it was already built into getsimple I just had to enable it with a tiny plugin. I did however utilise the code to gather a list of photos from Flickr.

The code is great; it calls the Flikr API people.getPhotos from ajax and neatly writes the thumbnail html into the blank div of your choosing.

It took me a while to tweak it to show my own photostream from its default interingness list, but now I have it I'm pretty pleased how simple, easy and effective it is.

Instructions

Add the page code

In the place you want the thumbnails to appear insert the following div & also place the link to the script in your html code:

Install the JS file

Then save the javascript file down in the corresponding place (you can use the download link at the bottom), then change the user_id in the script to that of the one you want to display the images of.

Download JS

You can also tweak the thumbnail sizes by changing the underscored suffix in line 35, see the different settings here. _q denotes 150x150px image. You can also change the size of picture the photo links to by changing the _b on line 36.

Additionally you can set the number of photos to get, the default is 100 but you can have up to 500 as required.

jQuery

Next you need to ensure you have a jQuery installed on your page, you can either do this via a link to a CDN or host the script file yourself.

Enjoy

If all went well, when you fire up your webpage you should see the thumbnails of your Flikr phhotostream.

Raspberry Pi - record uptime

02/17/2015 08:14:06 pm

Well, i had pre-written a post celebrating a whole year of uptime for my RaspberryPi - guess I shouldn't have jinxed it! On Friday I came home to find the Pi unresponsive to web access, putty & vnc.

There was noting for it and I had to suck up the loss and kill the power, oh well I shouldn't be to disappointed, 345 days is a respectable acheivement. Looking on the brightside, it gave me a window to swap to a larger SD card to allow me to do some software updates.

Well done RaspberryPi!

philfish.co.uk is launched - Phil Cam is live!

05/11/2014 08:00:00 am

The momentous day for Phil has been reached, his tank hosted server has launched!

His Samsung Galaxy S2 server is live and pumping out his very own website.

Phil is said to be delighted with his web 2.0 upgrade and is looking forward share the images of the tank to the whole world. He says he's not finished yet and is working in the background to bring you some enhancements but in the meantime you can enjoy the wonders of webcam and Flappy Phil game.

He also notes that he has a number of vacanies available to become a friend of Phil and is currently accepting applications.

Set up a Wireless IP Webcam for free

05/03/2014 08:00:00 am

This a hack for repurposing an old android handset as an ip webcam - all you need is your old unused android smartphone and the free app IP Webcam.

Heres a rundown own the steps you need to go through, I'll take you through more of the detail below.

Steps

Find the old android device you'll be using and connect it to your wireless network;
Download the free IP Webcam app from the Play store;
Fire up the app, you'll see a few configuration options but you can skip these for now and scroll to the bottom and hit start server;
You phone's camera should start up and on the screen you'll see the URL that you can view from your browser;
Go to the browser on a computer on the same newtork and type in the address (probably something like 192.168.XX.XX:8080);
Enjoy!

Further steps

If you want to be able to view your webcam from the internet outside your home network, there are a couple of other steps you'll need to take:

Configure a static IP address for your phone (either through the router config or on the phone itself);
Configure your router to route (by default port 8080) traffic from the internet to your phone;
Set up static access to your home network if your broadband link has a dynamically assigned IP address.

Port 80 android limitation

Theres a limitation with the android OS we have to get around round first though. All ports below 100 are restricted so by default you'll have to serve your site through port 8080. To get around this you'll either have to

root your device and install iptables (I've not found a way to do this yet);
use some router configuration to pass port 80 requests onto port 8080;
or you can use another server as a proxy.

Router configuration

Most routers don't have much in the way of configuration options so unless you are using dd-wrt or similar, its unlikely that you'll be able to redirect traffic to port 8080. I have a guide here on how to set-up a domain name with your router, but unfortunately you'll probably be stuck with an address to access you webcam like:

yoursite.com:8080

unless you can find a service to do this for you or you have access to another server to do this.

Apache Proxy

If you have access to an apache server you can easily set up a ProxyPass to forward traffic to your webcam. You can capture the requests via a subdomain and then forward them on to your webcams network IP address (e.g. 192.168.0.23):


    ProxyPass / http://Internal-IP:8080/ 
    ProxyPassReverse / http://Internal-IP:8080/ 

    ServerName webcam.yoursite.com

Once this has all been setup you will be seamlessly served your webcam images through your sub-domain URL.

Setting up your own domain name with a dynamic IP address on Sky Broadband

04/17/2014 08:00:00 am

Most broadband connections will nowdays dynamically assign you with an IP address. This means that every few days or every time you restart your router you will be assigned a new IP address. Consequently if you want to host a website using your domain name then you are going to encounter a problem.

What you need to do is get a static IP address (typically an ISP will charge for this if they even allow it), or find some way of updating your name server with every change of IP address. Your typical domain name seller doesn't want to offer this service as they want to sell you a hosting package so you will have to find another way around.

Dynamic IP woes

Most routers will have some sort of option to update a dynamic DNS service with your ip address and assign you a CNAME/URL that you can use, its not very pretty but it is one step closer closer to the goal.

For Sky Broadband customers the only configuration option here is to use DynDNS, unfortunately this service is no longer free and at $30 per year is too much for this tight-wad. [Note: they do offer a free option but the hostname expires every 30days so its of no real use]

Get a static hostname

One free dynamic DNS service is changeip.com, they try to hide the free option but it is there and it does work. So after you have signed up, select your sub-domain name and head over to the DNS Manager page select you new domain and manually update it with your IP address you are away. Just bang in your new URL into a web browser and you are one step closer:

http://yournewsubdomain.dynamic-dns.net

they even helpfully provide a shell script you can set as a cron job to automate the updating of your IP address, it works a treat and helpfully keeps a log of your IP address as it changes also.

Pointing your domain name

Now the final step is to change to nameserver entry on your own domain name, your service provider should have the option in your account settings somewhere to change your DNS entries. You have to take care here as you could screw things up. I started out by testing the waters by setting up a subdomain of childs.be first to test that out.

What you need to do is change your DNS record for your selected [sub]domain to CNAME and then set the value as your new yournewsubdomain.dynamic-dns.net. Thats it, you are away, your domain name URL will now always be pointed at your dynamic ip.

I've been using this method for several months now and I rate the service as excellent.

Another sneaky way

If you've googled the subject enough you may have discovered that while DynDNS isn't free, D-Link DNS is. It is only for D-Link router users only and once you have created an account you can log into both D-Link DNS and DynDNS with the same details.

This makes it very easy as setting up your router to automatically update your dynamic IP in this way and is a doddle!

I must stress though, using this service without a D-Link router is against its TOS, but it is reported that does work in this way.

Edit 24-July-2014: Looks like Dyn.com are making D-Link users verfiy their ownership to continue use of their service, see here, still you could always buy a used on from ebay to keep you going but that might only get you another 6 months before you have to pay.

Edit 22-Oct-2014: Dyn.com have now deleted unverified accounts.

Hosting your own webserver on port 80 with Sky broadband

04/05/2014 08:00:00 am

One of the first things I needed to check before ordering my Raspberry Pi was if it was technically feasable to host my own webserver from my Sky Broadband. In the past its been known by ISPs to block certain ports which makes it difficult and usually adds extra cost.

One of the ways round the issue of your desired ports are blocked would be to use some sort of service which relays incoming port 80 traffic to another port to your server. Its messy and is probably going to cost for this service and given one of the reasons why I've got a RPi was to have practically free hosting.

It seems there is no information on the internet as to whether Sky blocks port 80 for their broadband users. I'll cut to the chase if you can be bothered to read the rest of the article to say that:

Ports 80 and 8080 are not blocked on Sky ADSL Broadband.

Now we have established this, its just a case of setting up your router to forward internet traffic to your Pi webserver. There's a helpful page on Sky's support website explaining how to do this:

Sky Hub : How to Configure Port Forwarding

Well thats the easy part, next you've got to get around that whole dynamic DNS thing, but luckily for you I've got a tutorial for that:

Setting up your own domain name with a dynamic IP address on Sky Broadband

New childs.be website launched

03/22/2014 07:53:00 pm

The new website has now gone live. With the acquisition of my Raspberry Pi comes a whole host of possibilities.

My Pi is currently running as the server for this website. Unfortunately the Pi is not powerful engough to run Wordpress or Joomla (who'd of thought they were processor intensive) - it does run, but a four to six second delay in spitting out a page is way too long.

What I've gone for on my first crack is a flat-file CMS with blogging functionality as a plugin running stock Bootstrap and Bootstrap-theme CSS. I'm pretty pleased with the first real attempt. It needed some mods out of the box but I'm happy with it for the moment.

Here's a rundown of the software specs it's rocking:

Software rundown

Raspberry Pi	Model B
OS	Linux: wheezy-raspbian
Webserver	Apache / PHP5 / MySQL
CMS	Get Simple CMS
CSS	Bootstrap for GetSimple CMS with Bootstrap-theme and some other modifications
Blogging	GS Blog with OS Openspace API for mapping
FTP	vsftpd
Filesharing/SMB	Samba
Logging	syslog-ng
Other	Uptimed

I'm hoping to post some more articles on the trickier things I've come across when setting up the RPi.

As a bonus in the mean time, I've reposted some posts from Blogger from a long while ago when I first though I'd start a blog but quickly ran out of stuff to blog about!

You can check out the Raspberry Pi's specs here

the Raspberry Pi is alive

03/04/2014 01:14:00 am

The new server has now gone live, the Rasberry Pi is sitting quietly in the corner of my living room serving up my website to the internet.

I know I've backdated this post, but it is to mark the moment in history - I'll give the Pi one final good luck reboot and it is ready to go with the aim of surviving a whole year of uptime.

For many years I've been wanting more functionality from my paid hosting company but being too tight to fork out the hundreds of pounds a year it would cost as I wasn't quite sure if I'd actually get my monies worth out of it.

I've been keeping an eye on the Raspberry Pi project for a few years now, I even had an invite to buy one from the very early batches however I really wasn't sure I'd ever put it to good use. As time has gone on and my skills have improved as I dabble ever more with WAMP, I decided to take the plunge.

At only £30 with a bundled case from ebay it couldn't have been cheaper. A couple of days and a few internet tutorials later and I'm in business.

You can check out the Raspberry Pi's technical specs here

It is said that the Pi is great for kids and newbs, however you've got to take that with a pich of salt. Complete beginners might have a tricky time with the various processes and while it is true you can have its running as a webserver in no time, actually implementing it running your own site is a fair bit trickier.

What you get with the Pi initially is very minimal - just a circuit board! So you will have to invest or scrounge a couple of things to get you started:

My kit list

Raspberry Pi Model B

Spare micro USB mains charger (700mA recommended)

4Gb Class 6 SD card (borrowed from my ASUS EeePC)

Required for initial setup only

TV / Monitor with appropriate VGA / HDMI cable

USB Keyboard & mouse

4Gb Class 6 SD card (borrowed from my ASUS EeePC)

Blog » childs.be

How to enable and change the Remote IR GPIO pin in OSMC

How to set up your Logitech Harmony remote on your RaspberryPi

DIY Infrared Remote control IR receiver for Raspberry PI / OSMC

Remove Real VNC from RaspberryPi Pixel and install TightVNC

Remove RealVNC

Install TightVNC

Connect

Autostart

Running a SSL certificated/authenticated HTTPS website over dynamic DNS

Integrate an OLED screen into your Raspberry Pi 3 project or case

Logitech K375s Wireless Keyboard review

Free LetsEncrypt secure SSL server certificate install tutorial for Raspberry Pi

Instructions

Certbot

Authentification

Webroot

Certificate install

Renewal

Additional domains

How to make an clock for a raspberry pi with an OLED display

What you need

Install the software

Libraries

Wire up the pi

Finalise the Pi setup

Lets start the coding

Running as a background service

Starting at boot

Further reading

How to run a python script as a service / in background / as a daemon

Desk stand and holder for Surface Pro 3 & 4

How to pivot data by month in SQL and output to a temporary table

How to set up your Dell E310dw printer the really easy, bloat free way on windows

How to install Dell Printer E310dw on your Raspberry Pi

The easy way to find and delete duplicates in SQL

Identifying duplicates

Deleting rows

Delete duplicates from a table

Filter out duplicates in a query

Smart deletion of duplicate records

How to add an auto incrementing ID field in SQL server

How to add better comments into SQL server

How to connect a photoresistor or light dependant resistor to an ESP8266-12E

What you need

Circuit Diagram

The code

The output

Next...

Best way to optimise images and pictures to display on the web

IrfanVIew

ImageMagik

Exiftool

Bringing it all together

Linux

Windows

The results

Extra credit

Add a mailing list subscription to your website

How to create real time graphs of CPU temperature on a Raspberry Pi

How to guide

The result

How it works

For extra credit

How to log and plot network activity graph on a Raspberry Pi

How to make and set up a twitter bot on your own server / Raspberry Pi

How it works

What you need to do

Twitter

Wolfram|Alpha

Set up your RPI

Fire up your tweetbot

Using the tweetbot

The blog has now reached 50 posts!

The childs.be twitterbot is alive

Ask a question

My ultimate RaspberryPi project - an alarm clock internet radio

Display your server uptime on your website

How to log your own IP

Call me paranoid but ever since the antics of ACS:Law in the noughties I've been logging my IP address, it is not because I'm am an illegal downloader, my intrigue came about from the thought of how you would prove that you didn't if I was wrongly accused.