Connecting to Windows/MSSQL 2008 from Linux/CodeIgniter/PHP

Update: I’ve written a new article, covering CodeIgniter 3 and Ubuntu 14/16. Read it here.

Microsoft SQL Connecting to Microsoft SQL Express 2008 from Linux/PHP is a lot trickier than I expected. These notes are really for my own benefit so I can reproduce the setup, but maybe they’ll help you too. One of the problems is that many existing PHP drivers for MS SQL have difficulty talking to SQL 2008. Here’s a workaround using FreeTDS and ODBC.

My web application is built using CodeIgniter, the PHP application framework. It resides on an Ubuntu Server box, running Apache. Prerequisites on that Ubuntu Server (which I installed using Aptitude, but your favourite package manager will do):

  • unixODBC
  • FreeTDS
  • FreeTDS development package/(header files and libraries)

To my freetds.conf file (in /etc/freetds on my server) I added a section that looks something like this:

[my-server]
host = my.server.local
port = 1433
tds version = 9.0

Note: TDS version 9.0 should work with SQL 2008.

In /etc/odbcinst.ini, add the following ODBC driver (32-bit):

[TDS]
Driver = /usr/lib/odbc/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib/odbc/libtdsS.so

or 64-bit:

[TDS]
Driver = /usr/lib64/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib64/libtdsS.so

(You may need to check the precise location of the driver and setup files.)

And to /etc/odbc.ini, I inserted a DSN similar to the following:

[my-server]
Driver = TDS
Description = My Server
ServerName = my-server
Database = my-db

Generally within CodeIgniter, I am connecting to MySQL databases and that’s how my default connection is configured. I therefore added a second configuration to my database.php configuration file, like this:

$db['my_server']['hostname'] = "dsn=my-server;uid=myusername;pwd=mypassword";
$db['my_server']['username'] = '';
$db['my_server']['password'] = '';
$db['my_server']['database'] = '';
$db['my_server']['dbdriver'] = 'odbc';
$db['my_server']['dbprefix'] = '';
$db['my_server']['pconnect'] = TRUE;
$db['my_server']['db_debug'] = TRUE;
$db['my_server']['cache_on'] = FALSE;
$db['my_server']['cachedir'] = '';
$db['my_server']['char_set'] = 'utf8';
$db['my_server']['dbcollat'] = 'utf8_general_ci';

Now the ODBC driver within CodeIgniter can produce queries that MS SQL doesn’t like. We can fix this with a hack. You really REALLY shouldn’t do it this way (!) but to get things working and as described >here<, I edited the CodeIgniter core file system/database/drivers/odbc_driver.php. The function _from_tables() has a line reading:

return '('.implode(', ', $tables).')';

I changed it to this:

return implode(', ', $tables);

(In other words, we’re removing the spurious parentheses.)

I created a database method m_my_server.php like this:

/**
 * NOTE: We're using a feature-incomplete driver here.  Don't attempt to use
 * CodeIgniter's ActiveRecord Class or num_rows().  Use bare queries instead.
 */
class M_my_server extends Model {

  var $my_server;

  function M_my_server() {
      parent::Model();
      $this->my_server = $this->load->database('my_server', TRUE);
  }

  function get() {
    $query = $this->my_server->query('SELECT TOP(100) * FROM dbo.tblUserSummary');
    $result = $query->result_array();  // note ->num_rows() doesn't work with this driver
    if(count($result) > 0) {
      return $result;
    } else {
      return false;
    }

  }
}

/* End of file m_my_server.php */
/* Location: ./application/models/m_my_server.php */

At the SQL Server end, I set up a new standard SQL user (myusername/mypassword) rather than attempting to get Windows authentication to work (I bet it wouldn’t).

My SQL instance wasn’t listening to TCP/IP connections by default. I fired up SQL Server Configuration Manager and browsed to SQL Server Network Configuration –> Protocols for [my db instance]. Then you have to right-click TCP/IP and click Enable.

With all that in place, the following controller produces successful results:

  function SQLtest() {
    $this->load->model('m_my_server');
    $result = $this->m_my_server->get();
    if($result) {
      print_r($result);
    } else {
      echo 'nada';
    }
    exit;
  }

It’s not ideal; for one thing, bare SQL queries involve learning Microsoft’s particular dialect of SQL (whereas I’m used to MySQL). The tables I’m querying are generated by Microsoft Forefront TMG though, so I’m basically stuck with MSSQL. At least now I can query those tables from my favourite PHP application framework.

Image copyright © Microsoft. Used with permission from Microsoft.

Job Done

Steve Jobs by acaben: http://www.flickr.com/photos/acaben/541420967/sizes/l/in/photostream/Business legend Steve Jobs died late last night. I heard about it through that most modern of news outlets: Facebook. I read the story on my company-supplied iPhone (which, by the way, I didn’t want; I’d rather use the Android that I keep in my drawer – long story).

The death of Steve Jobs follows the release of the much-anticipated iPhone 4S, the successor to the iPhone 4. Perhaps that ‘S’ should stand for ‘swan song’?

This morning, I mentioned Jobs’ death to a friend, who said, “Who’s Steve Jobs?”

Who’s Steve Jobs? I guess if you’re indifferent to technology and design, you could be forgiven for not knowing his name. But whether you’ve heard of him or not, his influence has almost certainly had an impact on you. The number of modern innovations associated to his name is impressive:

  • First successful personal computer with a graphical user interface (the Macintosh)
  • First WWW (World Wide Web) server
  • Pioneering in rich content email
  • Through Pixar, the first entirely CGI film, Toy Story
  • iTunes: simple access to large online catalogue of music, incorporating digital rights management
  • iPod: user-friendly range of media players
  • iPhone: user-friendly smart phone. When asked to recommend a phone to colleagues, I suggest Android for the technophiles and iPhones for the technophobes. The success of this approach tells me everything!
  • iPad: a device that “experts” claimed was superfluous but which has shipped in phenomenal numbers, battering all manufacturers’ competition in the process
  • Obsessively well-designed low voltage power supplies (built in cable management, magnetic quick-release plugs)
  • Multi-touch mouse

Given the number of patents that bear his name, I have inevitably missed some…

I am not an Apple fanboy. In fact one of my favourite sports is baiting Apple fanboys. But I simply can’t help admiring this charismatic man who for so long helmed one of the most successful companies of all time simultaneously bringing design genius and technical excellence to the masses. Steve Jobs, one time living legend, your legacy will live on.

Meanwhile, someone somewhere just patented the iHarp.

Steve Jobs image copyright © Ben Stanfield, licensed under Creative Commons. Used with permission.

Kindle Fire: Pretender to the iPad’s Crown?

Kindle Fire showing InsensateWhen last week I read about the new $199 Kindle Fire released in the States, my first thought was, “This is a loss leader for a content delivery system.” Amazon has recognised it can make so much money out of selling ebooks, it doesn’t need to make a profit on the reading units.

In the Fire, Amazon has produced a unit that can in all important respects compete with most of the other tablets/pads out there, apparently (but not actually) for a fraction of the cost to the consumer. Obviously, Amazon wants to be positioned ready for when that 50:50 ebooks-to-print-books ratio tips to 75%+. And of course it’s not just ebooks, because Amazon can deliver audio, video and apps to the Fire, positioning it squarely in Apple/iTunes territory.

How is Apple going to challenge a $199 contender to the $499 iPad’s crown? I think we can be certain that Apple will respond forcefully – it can’t afford to take a drubbing in its most strategic consumer sector. Nevertheless, this looks to me like a very smart move on Amazon’s part and I’m not sure which (if any) large company is in a position to challenge this move.

It looks like PC Pro agrees with me. What do you think?

Cannot save photos on jailbroken iPhone 4 camera roll: FIXED

iPhone 4 A few applications recently started failing to save photos to the iPhone camera roll. A quick dig around via SSH confirmed this; it was not that they were saving but were somehow hidden – they were not there at all.

It seems that some kind of permissions error had crept in over time. Connecting via SSH and issuing the command “chmod -R a+rwx /var/mobile/Media/DCIM” fixed this.

NB: Be aware that this grants all users/applications read/write access to the photo directory.

iPhone 4 image copyright © ji young YOON, licensed under Creative Commons. Used with permission.

Windows: keyboard layout has changed spontaneously

keyboard I’ve recently noticed that the keyboard layout on my Windows XP PCs is spontaneously changing from UK to US. I suspect that this is linked to a piece of software I use called Input Director (which allows you to control several PCs/monitors from one keyboard/mouse).

Whatever the cause, from time to time my UK keyboard starts operating in US mode (so, for example, the following symbols are all swapped around: ", ', #, £). This is inconvenient, to say the least.

The logical place to look would be in Regional Settings/Languages, within the Control Panel. Nothing had changed there however: UK was the only layout showing as installed. Advanced Text Services were switched off and all the relevant keyboard shortcuts were disabled.

In my search for a solution, I came across this page, which describes a similar situation. Within the comments on that page, one contributor has posted:

Try holding ‘alt’ and pressing ‘shift’ three times.
alt+shift+shift+shift

The mysterious Jim doesn’t explain why this works – and I have yet to find any documentation, but, sure enough, Alt-Shift-Shift-Shift does indeed reset the keyboard layout back to UK. Further repetitions of the keyboard combo have no other effect, so this is a reset rather than a toggle.

Office has a mind of its own

Microsoft Office products can exhibit a similar behaviour. If you’re finding that some MS Office product is using the wrong keyboard map (but other programs are fine), try this: First click on the Office program, somewhere you can enter text. Then press the left shift and alt keys together. You should be returned to your default keyboard mapping.

You can actually remove the unwanted keyboard layout from Office – note this is separately managed from the Windows keyboard layout, for some reason. In one of the Office programs (for 2010 onwards), click File -> Options -> Language. If you see more than one “Editing Language” in the list, select any you don’t want and click “Remove”.

Keyboard image copyright © yum9me, licensed under Creative Commons. Used with permission.

Install Ubuntu from floppy + network; no CDROM

Ubuntu Logo This blog entry brings together instructions from various other source on the internet, for a particular scenario:

  • Compaq Evo N1015v laptop (“the Laptop”) – should work on many other machines though
  • CDROM drive is faulty – cannot boot or read from it
  • Network card/BIOS cannot natively boot from the network
  • Cannot boot from USB
  • The Laptop does however have a floppy drive
  • Windows is currently installed on the Laptop
  • Another Windows-based computer (“the PC”) is available and both machines are networked and connected to the internet

The objective was originally to have XBMC running on a now-defunct laptop. Unfortunately it transpired that the graphics card was too old and obsolete to be supported by XBMC. Nevertheless, we can still use the Laptop for many other purposes.

The steps

  1. Find out what network card is installed on the Laptop. In my case this was easy – within Windows, the device manager told me it was a Realtek RTL8139 something-or-other. Otherwise, Google can help.
  2. Still within Windows on the Laptop, use the gPXE ROM-o-matic to create a bootable floppy disk for this network card. Very easy. Select the most recent “production release” >here<. Choose the correct NIC type (in my case “rtl8139”) and click “Get Image”.
  3. Still within Windows on the Laptop, write that disk image to a floppy disk using RawWrite.
  4. Following the instructions on the Ubuntu documentation site, download the appropriate netboot files for your architechture and set up tftpd32 on the PC.
  5. With tftpd32 running on the PC, reboot the Laptop from the floppy (set BIOS to boot from floppy first). This should automatically boot into the Ubuntu installer – select “install” and proceed with Ubuntu installation over the internet. This will take a long time, even over a fast internet connection!
  6. If the installation is on an older machine, it would be worth selecting “Xubuntu desktop” (rather than “Ubuntu desktop”) at the software selection screen. This installs a leaner desktop system. After the installation is complete, run aptitude from the command line or Synaptic Package Manager from the desktop to choose any other software packages required.

Image copyright © Canonical Ltd. All rights acknowledged.

Fine-tuning divert to voicemail for iPhones (and Android, BlackBerry, etc.)

Hi! This is the one of the most viewed (and I hope, one of the most helpful) pages on my web site. Thanks for visiting! If you find the information here useful, please feel free to express your appreciation in the comments or, even better, by grabbing a copy of my novel. 😉 >more info<.

– Rob

Answerphone by jypsygen http://www.flickr.com/photos/jypsygen/3461757736/sizes/l/For some reason, the iPhone 4, iPhone 4S and iPhone 5 (with the current or recent versions of iOS) have very few options for call diversion. Fortunately, it is possible to set up all the usual diverts using network codes from the keypad. To divert to voicemail, you need to know your <voicemail> number (see the section below).

The instructions below work regardless of phone operating system, so do work for Android
, BlackBerry and other devices. Those phones tend to provide these features in a more intuitive fashion, but if you can’t find the settings, the procedures here should work anyway.

Press the call button after entering the codes below:

Function Dial
Activate divert all **21*<voicemail>#
Deactivate divert all ##21#
Query divert all *#21#
Activate divert when no answer* **61*<voicemail>#
Deactivate divert when no answer ##61#
Query divert when no answer *#61#
Activate divert when not reachable **62*<voicemail>#
Deactivate divert when not reachable ##62#
Query divert when not reachable *#62#
Activate divert when busy **67*<voicemail>#
Deactivate divert when busy ##67#
Query divert when busy *#67#

*You can vary the number of seconds to wait before diverting when there’s no answer. The delay can be between 5 and 30 seconds. Enter that as a two digit number (05 to 30) in place of “<xx>” in the following code: **61*<voicemail>**<xx>#

Voicemail numbers

On the UK T-Mobile network, your voicemail number is usually +44063, followed by your mobile phone number (without the leading 0), e.g. if your phone number is 07123456789, the appropriate voicemail number is +440637123456789. Long-press the 0 key to get the +. BUT! in the voicemail number prefix (+44063) , the ’63’ may be something different for your phone – e.g. for the Fresh sub-network, the prefix is +44060. You can check the correct prefix by entering *#67#.

Voicemail numbers for other networks are more straightforward:

  • O2 901
  • Orange +447973100123
  • Virgin 212, or try prefixing your mobile phone number with +44002 (dropping the leading 0)
  • Vodafone 121

Answer phone image copyright © jypsygen, licensed under Creative Commons. Used with permission.

Ode to a Wintry Commute

wintry roadOh come on frosty mornings!
Your charms are wearing thin.
I’m weary of commuting in
a car as cold as sin.

It makes me nervous when the engine
doesn’t want to start.
Its coughs and splutters are like a
rhinocerous’s fart.

The wheel has induced frostbite;
the engine’s rattling.
Suspension’s frozen creaking
sounds like chickens have moved in.

The washer jets are iced up
and the roads are slick as grease.
It’ll be a downright miracle if
I get there in one piece.

Jack Frost no longer pleases me
with trees full of dandruff.
So come on frosty mornings,
you’ve held court for long enough!

Winter road image copyright © Thomas Quine, licensed under Creative Commons. Used with permission.

Cleaning and speeding up slow Windows PCs

stupid computer PCs tend to slow down over time, usually as a result of the increased demands placed on them. The good news is that the situation can often be improved dramatically without having to shell out more money. For sure, one of the biggest wins is to install more memory (if possible), but after that, here are the steps that I personally follow when cleaning up machines for colleagues and friends.

  • Check the list of installed programs and ensure that you only have one internet security suite/virus scanner installed. Remove any extras.
  • Uninstall all software that you know you’re not using. If there’s software you’re not sure about or don’t recognise, feel free to add a comment to this post and I’ll try and offer an opinion. The best option for uninstalling software (this point and the previous point) is to use Revo Uninstaller (Freeware version) instead of the Control Panel. It does a more thorough job than most programs’ uninstallation routines:
  • Use CCleaner. Check the options carefully – you don’t necessarily want to select them all. If in doubt about any item, don’t remove it.
  • Use something like SpaceMonger to track down big files that you can afford to delete. If you look at the “Free Software” tab on the download page, you’ll find the old version 1.4.0, which is perfectly adequate.
  • Defrag with JkDefragGUI or its successor MyDefragPowerGUI (once it’s out of beta). The default options should be fine. If you can find the registry optimization options, use them too.
  • So-called “registry cleaners” usually do more harm than good and are best avoided.

On the subject of installing more memory, I invariably use Crucial for home and business. Crucial’s service is very reliable and there is an online scanner that in most cases does a good job of checking your system and advising you on the upgrade possibilities. I don’t get any referral fees from Crucial by the way!

Image ownership unknown. All rights acknowledged.

Tips for improving lithium-ion laptop battery life

Li-ion laptop battery Replacement batteries for laptops can be quite expensive, so here are a few ideas to get the most out of your battery in terms of charge and overall life.

Background information

Most modern laptop batteries are of the lithium-ion (“li-ion”) variety. You can expect them to last two to four years if properly cared for. They will degrade over time, whether or not they are in use. They last best at a temperature of 15°C/59°F, although it is unlikely to be practical to use them at this temperature.

This is a chemical technology, which is under continual development, so it is entirely conceivable that a new li-ion battery for an old laptop will last much better than the original battery did. This and other factors make it fairly hard to predict how long your laptop will last on one charge. All you can do is to try and get the best out of your particular battery.

Did you know? Early li-ion notebook batteries were known to explode! Modern batteries have protection circuits built in which prevent the lithium ion converting into unstable lithium metal.

Power consumption tips

  • Reduce the brightness of your screen when running on batteries.
  • Use a blank screensaver, and set it to operate after a relatively short period of inactivity. Better still, switch off or hibernate your laptop if you won’t be using it for more than 5 minutes or so.
  • Set your power options (e.g. in Windows control panel) to minimize power consumption. This includes having hard drives power down as much as possible.
  • Switch off wireless networking, if you’re not using it.
  • Unplug all PCMCIA cards (or similar) and USB devices that you’re not using. Ditto optical drives (CDROM etc).
  • Certain specialist notebook graphics cards can be set to run “underclocked” at less than their full potential. This saves energy. Do this with caution though, because it can cause conflicts with certain graphics software.
  • Close all the programs you’re not using. This includes all the programs that run in the background, unused services in Windows, etc. Which services to stop is beyond the scope of this blog entry.
  • Certain sound chips have a power saving mode that can be activated via the control panel.

Caution when hot! It is inadvisable to use a li-ion battery when particularly hot. Heat can generally be a problem with laptops, so make sure that all the vents are clear, that the laptop is not sitting on a highly insulating material (e.g. sat on a duvet when used in bed!) and that all fans are operating as they should.

Battery life tips

  • Do not keep the laptop plugged in whilst the battery is installed; this keeps the battery on a permanent charge/ discharge cycle which will cause it to age rapidly. Instead remove the battery if you’re running on AC power.
  • If you’re going to store the battery or leave it unused for any length of time, discharge it first to about 40% capacity – never fully discharge a li-ion battery. Aim to store it at around 15°C/59°F (see above).
  • You do not need to discharge or charge a li-ion battery fully – they do not suffer from the “memory effect” experienced with nickel cadmium or nickel metal hydride rechargeables. So only discharge partially – avoid going below 20% capacity if you can.
  • When the battery is full or at over 95% charge, stop charging it!
  • Don’t bother buying old stock of batteries no matter how cheap, since they will have degraded for the above reasons.

Remember to recycle. Li-ion batteries should be recycled wherever possible. Your council may have suitable local facilities.

For everything you could possibly want to know about batteries in general, visit the Battery University!

Photo based on battery image copyright © photomartimages, licensed under Creative Commons. Used with permission.