Archive for September, 2011


Roughly a week after installing my 2nd ET9000 I powered on the box to be presented with a blank green screen with a spinning Vix logo in the top left hand corner (the VFD was stuck at “booting…”).

Fortunately I was able to SSH into the box and access the crash log:

IOError: [Errno 28] No space left on device: '/usr/share/enigma2/ViX_HD/skin.xml.tmp'
---- saving lame channel db
FATAL: couldn't save lame channel db!
]]>
</enigma2crashlog>
</crashlogs>
</openvix>

There were hundreds of crash logs with date/time stamps just minutes apart (suggesting the box was stuck looping through the boot routine). Each of the logs had a number of entries suggesting the root cause was a full disk.

df -h confirmed this to be the case:

Filesystem Size Used Available Use% Mounted on
/dev/root 122.0M 121.5M 552.0K 100% /
tmpfs 64.0K 8.0K 56.0K 13% /dev
/dev/sda2 15.4G 67.4M 15.3G 0% /media/usb
tmpfs 155.4M 804.0K 154.6M 1% /var/volatile
tmpfs 155.4M 0 155.4M 0% /dev/shm

But why was /dev/root full when my recordings/crash logs are all stored in /media/hdd which points to /media/net/tv?

I ran a ton of linux commands to try and identify large files/directories but nothing stood out- I was almost ready to re-flash the Vix firmware, then a had a brainwave…

If at some point the box had been unable to mount /media/net/tv (I had suffered a power cut recently) then where would recordings & crash logs be kept? Again- I couldn’t find the location anywhere, so I thought I’d force it…

I took the samba share offline and restarted the box- voila! /media/hdd was mounted but this time I couldn’t see the normal recordings and crash logs- just a single recording…

I ran rm * then df –h

/dev/root 122.0M 60.2M 61.8M 49% /
tmpfs 64.0K 8.0K 56.0K 13% /dev
tmpfs 155.4M 688.0K 154.7M 0% /var/volatile
tmpfs 155.4M 0 155.4M 0% /dev/shm

The ET9000 flash had 51% free space again! I placed the samba share back online, rebooted the box and everything is back to normal.

Thanks to the community @ http://www.world-of-satellite.com/showthread.php?11385-New-ET9000-Disk-Full&p=82840#post82840 for helping me troubleshoot/solve the problem!

L

Advertisements

Updateable Excel Frontend for SQL View

I am currently working on a Web UI: https://tickett.wordpress.com/2014/12/11/excel-like-web-frontend-to-sql-viewtablestored-procedure/

We often have a need to view/manipulate/analyse data from systems in Excel (because, let’s face it- it’s quick & easy). However, it is sometimes the case that we need to add data (it may be commentary, budget figures or any manner of data that for one reason or another cannot be keyed into the underlying system(s)). I hoped Excel’s built in functionality could cater for this… Unfortunately, while it’s very easy to set a datasource to pull data from SQL it doesn’t appear to have any functionality for pushing data back into the underlying database.

So this is what I came up with…

Step 1: The data is retrieved using VBA (I have a master sheet which is pre-formatted). The “template sheet” is then copied and used as a starting point each time the data is retrieved:

Sub RetreiveSheet()
    On Error GoTo Error
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String

    Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _
    "Initial Catalog=EBMS1920;Data Source=sql-01"

    stSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE CRITERIA = 1"
    stSQL = stSQL & vbCrLf & "ORDER BY [Date] ASC"
    Set cnt = New ADODB.Connection

    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With

    Application.EnableEvents = False

    For i = 0 To rst.Fields.Count - 1
        ActiveSheet.Cells(1, i + 1) = rst.Fields(i).Name
    Next i

    Application.ScreenUpdating = False
    For r = 1 To rst.RecordCount
     For i = 0 To rst.Fields.Count - 1
        ActiveSheet.Cells(r + 1, i + 1) = rst.Fields(i).Value
     Next i
     rst.MoveNext
    Next r

    Application.ScreenUpdating = True

    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

    ActiveSheet.PivotTables(1).RefreshTable
    ActiveSheet.Range("B1000").Value = "Last Retreived: " & DateTime.Now()

    Application.EnableEvents = True
    ActiveSheet.Protect UserInterfaceOnly:=True

    Exit Sub

Error:
    MsgBox ("An error occurred")
End Sub

Here is an example with some made up data:

The gray cells indicate cells that are pulling from the system and CANNOT be updated. The pink cells are pulling from my custom table and CAN be updated, and the yellow cells are summary fields (a pivottable). All cells are locked except those in pink and the sheet is protected.

The SQL query is pulling from FORECAST_SPREADSHEET which is a view joining tables from the underlying system with my custom table FORECAST_DATA.

Now the clever bit… allowing updates to write back to the database!

Step 2: We create a trigger on our view:

ALTER TRIGGER [dbo].[tr_UPDATE_FORECAST]
   ON [dbo].[FORECAST_SPREADSHEET]
   INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;
      DELETE A FROM FORECAST_DATA A INNER JOIN INSERTED B ON B.ORG_CODE = A.ORG_CODE AND B.ID = A.EVENT_ID
      INSERT INTO FORECAST_DATA SELECT
       ORG_CODE
      ,ID
      ,ADD_SERVICE_INCOME
      ,TOTAL_CATERING_EXPENSES
      FROM INSERTED
END

Step 3: We add a routine to the “worksheet_change” event:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.ScreenUpdating = False Then Exit Sub
If Target.Value <> "" And IsNumeric(Target.Value) = False Then
Target.Value = ""
MsgBox ("Value must be numeric!")
Exit Sub
End If

Dim updateSQL As String
Dim orgCode As String
Dim eventID As String
Dim columnName As String
Dim newValue As String

orgCode = Range("A" & Target.Row).Value
If orgCode = "" Then Exit Sub
eventID = Range("D" & Target.Row).Value
columnName = Cells("1", Target.Column).Value
newValue = IIf(Target.Value = "", "NULL", Target.Value)

updateSQL = "UPDATE FORECAST_SPREADSHEET SET " & columnName & " = " & newValue & " WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset

Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _
"Initial Catalog=EBMS1920;Data Source=sql-01"

Set cnt = New ADODB.Connection

With cnt
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 0
    Set rst = .Execute(updateSQL)
End With

selectSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID

Set rst = cnt.Execute(selectSQL)
Application.EnableEvents = False
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(Target.Row, i + 1) = rst.Fields(i).Value
Next i
Application.EnableEvents = True

cnt.Close
Set rst = Nothing
Set cnt = Nothing

ActiveSheet.PivotTables(1).RefreshTable

End Sub

You will notice in the code used to retrieve the data earlier that I set Application.EnableEvents = False to prevent this event being fired when my code is executing (I only want it to fire when the user is keying in data).

We’re done! RetreiveSheet() can be executed to retrieve the data and keying into the pink cells will update the custom SQL table in real-time! I chose to retrieve the latest copy of an entire row everytime an update is committed to the database.

Sidenote: One trick I learnt whilst building this solution was how to avoid cell formula references being automatically updated (broken) when the sheet to which they refer is no longer valid (deleted). We do this using the INDIRECT() function. So =SUM(’26_201109′!covers) becomes =SUM(INDIRECT(“’26_201109′!covers”))

L

I mentioned in an earlier post that I wasn’t entirely happy with the AB IPBox 9900HD and intended to order one (or several) alternatives. Well, I’ve been using the Clarke-Tech ET9000 for quite some time but haven’t gotten around to blogging about it. I ordered another Xtrend ET9000 and thought I’d take the opportunity to blog about the steps necessary to get the box up and running with Sky HD UK.

The first step is to download the latest vix firmware. I found the world-of-satellite a great resource so downloaded from there http://www.world-of-satellite.com/forumdisplay.php?209-Clarke-Tech-Xtrend-ET9000-ViX-Team-Images.
Once downloaded you need to unzip the archive and place the et9000 folder on the root of a USB stick.

Ensure the ET9000 is powered off either at the mains or using the switch on the back, plug the USB stick in then power it on.
Open the flap concealing the buttons/card readers etc and when the box prompts "flash image" press OK (the box should now display "flashing…")
Once flashed and booted the setup wizard should automatically start

Configure the basic options as you see fit
When prompted to configure tuner(s) use the configuration shown below

When prompted to run a scan select: No scan later manually
Determine your ET9000’s ip address: Menu, Setup, System, Network, Device, Adapter

Download the latest Sky settings from http://www.world-of-satellite.com/showthread.php?8060-ViX-Team-Enigma-2-HD-settings-for-28.2E-amp-UK-DTT
Connect via FTP using the client of your choice (the default username is root and password is blank)
Upload all of the files from the archive to /etc/enigma2

And upload satellites.xml to /etc/tuxbox

Then download the picons from http://www.world-of-satellite.com/showthread.php?8056-ViX-Team-ALICE-Picons-For-UK-Channels-28.2E-and-DTT-By-Silverfox0786&p=60088#post60088
Extract the archive and upload the picon folder to /media/usb

Now we need to configure the guide to show the picons. Press Guide, Menu and configure as below

Press green to save then restart the box (Menu, Standby / Restart, Restart)
Now run a Service Scan (Menu, Setup, Service Searching, Automatic Scan)

Once complete you should be able to view free to air channels

The following instructions are from http://www.world-of-satellite.com/showthread.php?9735-How-to-Install-CCcam-MGCamd-Others-on-Duo-Solo-Uno-ET9000-ET5000-ViX
Download enigma2-plugin-cams-cccam.2.2.1_1.0-r0_mipsel.ipk from http://www.4shared.com/dir/wnyHV50A/VIX_CAMS.html
And ftp to /tmp

Now install the package (Menu, Setup, Vix, Install Local Extension)
Once installed you need to start Cccam (Blue, Softcam Manager, Green- Start Cccam.2.2.1)
Then enable autostart (Blue, Softcam Manager, Blue- Enable Startup)
You should now be able to view all channels you entitled to by your Sky subscription
If you press Guide you’ll notice the listings are mostly missing

We need to configure CrossEPG (Menu, Setup, System, EPG, CrossEPG, OpenTV providers, Sky UK OpenTV (Astra2 on 28.2)- Green enable

And configure the box to download regular updates (Menu, Setup, System, EPG, CrossEPG, Configure)

We can now download EPG data (Blue, CrossEPG Downloader)
And the guide should now have a complete set of listings

I decided I didn’t want to put a hard disk in my ET9000 but instead wanted recordings to be stored on the fileserver. I won’t go into that now.

L

Mac OS X Lion on ESXi 5

After countless failed attempts I’ve finally managed to get Mac OS X Lion running in ESXi 5.

I used Donk’s ESXi 5 Mac OS X Unlocker: http://www.insanelymac.com/forum/index.php?s=&showtopic=267296&view=findpost&p=1745191

Unfortunately when you try and boot from the Lion installation DVD the Virtual Machine hangs on the apple logo. Pressing F8 or configuring the VM to force entry into the BIOS on next boot allows you to select EFI Internal Shell

And boot verbosely by issuing the command: boot –v

We now see the boot is hanging at the PCI configuration stage:

Issuing the command: boot –v npci=0x2000

Allows us to get past the PCI configuration step but now hangs looking for the installation media (still waiting for root device):

This appears to be because the IDE controller is unsupported. Attaching an external USB DVD ROM, enabling pass-through:

Then attaching the USB controller to the VM:

And voila- the Lion installation begins!

The next obstacle came when trying to select the target disk for the installation. The virtual disk wasn’t listed and attempting to partition/format the disk resulted in an error: Unable to write to the last block of the device

I found a few suggestions: https://discussions.apple.com/thread/3226425?start=0&tstart=0

Launching Terminal and issuing: diskutil list
Allowed me to identify the disk: /dev/disk0
And issue: diskutil eraseVolume jhfs+ "OSX" /dev/disk0
Which also failed but back in the disk utility I was now able to partition & format the disk ready for the installation!

Everything went smoothly from here. Installing VMWare tools v5.0.0: http://www.insanelymac.com/forum/index.php?showtopic=267339 went smoothly but after rebooting I couldn’t login to (the password seemed to be being rejected). I rebooted the VM in safe mode (hold the shift key whilst during boot) and was able to login.

Performance is pretty poor (I think this is due to the lack of graphics acceleration) so I went straight in and enabled remote management so I could now use Apple Remote Desktop to administer the server.

Good luck!

L

*EDIT* One important thing to note the VM cannot be powered on from vCenter (error: Failed to find a host for powering on the virtual machine. The following faults explain why the registered host is not compatible. The guest operating system ‘darwin10_64guest’ is not supported). Simply logging directly into the host allows you to power on the VM.

Fresh OSX Lion Installation

I’ve just got a new SSD for my MacBook Pro (official apple- toshiba 256GB) and decided to install OS X Lion and all my apps from scratch. This is basically a list of said apps for me to reflect on at a later date :)

OS X Lion
Picasa
Chrome
Virtualbox
Citrix Receiver
VLC
Filezilla
UnRarX
Photoshop CS5
Skype
Office 2011

Not noticed many differences in Lion yet… other than the scrolling works in the opposite direction to previous version (two-finger down scrolls up and two-finger up scrolls down… seemed odd at first but got used to it before i even bothered finding the setting to change it!).

L

%d bloggers like this: