MySQL load data infile and foreign key constraints

I’ve been up against a(nother) MySQL 4/PHP 5 ‘not doing all I want it
to’ type problem. This time, it involved trying to do a bulk update on
potentially tens of thousands of lines. Now, I don’t want to do an
INSERT for each line and I’ve already written some classes to handle
outputting data objects into csv files which handles all of the primary
keys. There’s an option with LOAD DATA INFILE to REPLACE values where
primary key conflicts would occur so I thought if I used that with
existing rows then I should be okay.

What I have found, though, is that LOAD DATA INFILE with REPLACE appears
to try to delete the row and then re-insert it. This breaks other
foreign constraints and so the database engine won’t let it. Fair
enough, but it would have been nice if the manual mentioned it.

So: next step is to disable the foreign keys temporarily with SET
FOREIGN_KEY_CHECKS=0. From the command line this works fine, but it
seems that within PHP the foreign key checks are re-enabled
automatically after each command is run, which means the net result of
running SET FOREIGN_KEY_CHECKS=0 is absolutely nothing. So when I try
and to the LOAD DATA INFILE the constraints are still there and it
doesn’t work.

If we trusted MySQL 5 fully yet (and wanted to go through the pain of
installing it) I could probably write a stored procedure, with whatever
headaches that would bring, but as it is I’m stuck with MySQL 4 for the
moment. The solution I’ve come up with is to write the SQL query to a
text file and then use an exec() command from within PHP to execute the
commands in the file. I don’t like it, and it doesn’t feel very
portable, but it seems to work. In fact, it seems so handy that I’ve
created a static method so I can do it with any batch of SQL queries.

The handy class follows (with constants in place of the database
names/connection values/temporary folder):

public static function multipleDbQueries($sql)
{
 $file = fopen(TEMP_CSV_LOCATION .
  "temp_query.sql","a+");
 fwrite($file,$sql);
 fclose($file);
 exec("mysql -u " . DB_USERNAME .
  " --password=='" . DB_PASSWORD .
  "' " . DB_NAME . " < "
  . TEMP_CSV_LOCATION . "temp_query.sql");
}

Motorbike hire in east London

I usually hire motorbikes from a place called Raceways in Surrey Quays, East London
but a) it’s quite expensive,
and b) they’re the wrong side of the river. I’m going to be on a Honda
VFR800 this weekend

I’d definitely be interested in finding something a bit closer, though, but East London doesn’t seem particularly well served by motorbike hire.

Hiring an ASP.Net developer

My company is looking for an ASP.Net developer, so here seems as good a
place as any to put an ad. Job description below:

London based Internet development company seeks permanent ASP.NET
developer with an absolute minimum of 1.5 years production experience.

Desktop application programming and web services experience are a must
as is extensive VB.NET and SQL Server 2000 knowledge. ‘Can-do’ attitude,
a problem ownership mindset, excellent common sense and the ability to
communicate clearly to internal and external clients are also
pre-requisites.

Exposure to SOAP and programming languages such as C#, XSL and PHP would
be a distinct advantage.

This is an excellent opportunity to join an ambitious, young and
profitable business at the beginning of an exciting growth phase.

No agencies.

Email: asp dot net at exponetic.com

PHP 5 garbage collection

The object-oriented features of PHP 5 are a really positive step forward
for the language. One of the biggest improvements, to my mind, is that
you no longer have to choose to pass things around by reference with a
liberal smattering of ‘&’ symbols: references are now the default way of
passing objects.

One problem I have come across, though, is that the reference counting
feature of PHP’s garbage collection
(http://www.zend.com/zend/art/ref-count.php) means that objects with
mutual references are not deleted even when I thought the object was out
of existence. E.g:

class ParentObject()
{
  protected $childObject;

  function __construct()
  {
    $this->childObject = new ChildObject($this);
  }
}

class ChildObject()
{
  protected $parentObject;

  //Pass in a reference to the parent
  //object and store it internally
  function __construct($parentObject)
  {
    $this->parentObject = $parentObject;
  }
}

Then if I call $foo = new ParentObject(); then it automatically creates
a child object with a reference to the parent. The parent also keeps a
reference to its child. If I then unset($foo); the two objects are still
referencing each other and so are not deleted. The only way I’ve found
to clear this is to create a new method (which I call destroy()) to
delete references to the child. Calling destroy() on the parent first
calls destroy() on its child, which dereferences the parent, and then
the parent dereferences the child. So the classes are now:

class ParentObject()
{
  protected $childObject;

  function __construct()
  {
    $this->childObject = new ChildObject($this);
  }

  public function destroy()
  {
    $this->childObject->destroy();
    unset($this->childObject);
  }
}

class ChildObject()
{
  protected $parentObject;

  //Pass in a reference to the parent object
  //and store it internally
  function __construct($parentObject)
  {
    $this->parentObject = $parentObject;
  }

  public function destroy()
  {
    unset($this->parentObject);
  }
}

And I have to call

$foo->destroy();
unset($foo);

To clear the thing out completely.

This can cause a number of problems which I won’t go into in detail here
(they occur in more complex design patterns), but suffice to say that
there are a number of occassions where I don’t necessarily want to
destroy a child at the same time as a parent, or vice-versa. E.g. a
child references multiple parents. The end result is that I’m writing
code to deal with garbage collection where it is having a big effect on
memory and just leaving it out where it doesn’t seem to make as much
difference. This suffices for a known set of data but doesn’t feel very
satisfactory in terms of future-proofing.

I would appreciate it if anyone else has a better way of doing things.

Sony Clie and Nokia 6600 infra red GPRS internet settings

Instructions for connecting the Sony Clie UX50 to the internet via GPRS through a Nokia 6600’s infra red port. I’m on the Orange phone network so some of the settings may be specific to this. I also assume your phone already works with internet/GPRS by itself and has a connection called ‘orangeinternet’ set up (which it is by default).

  1. Go to Preferences.
  2. Select ‘Connections’ and ‘New..’.
  3. Call it something like ‘IR Internet’.
  4. Set:
    • Connect to: Modem
    • Via: Infrared
    • Dialing: TouchTone
    • Volume: Low
  5. Click on ‘Details’ and set speed: 115,200, flow ctl: Automatic, Init string: leave empty.
  6. Click ‘OK’ then ‘OK’ from the ‘Edit Connection’ screen.
  7. Select ‘Network’ from within Preferences.
  8. Open up the top menu and select ‘New’.
  9. Call the service something memorable (e.g ‘Working IR Internet’).
  10. Some of the following settings are specific to the Orange phone network so you may have to play around:
    • Username: a
    • Password: a
    • Connection: select the IR connection you just made (‘IR Internet’)
    • Phone: *99#
  11. Click on ‘details’.
  12. Set:
    • Connection type: PPP
    • Idle timeout: Never
    • Query DNS: ticked
    • IP Address: tick automatic

Now the hard bit… Click on ‘Script’ and enter through the menus:

Send: AT&FO;&D2&EO [both capital “o”, not zeros]
Send CR:
Send: ATV1W1S95=47
Send CR:
Send: AT&K3
Send CR:
Send: AT+CGDCONT=1,”IP”,”orangeinternet”
Send CR:
Send: ATDT*99#
Send CR:
End:

Click on ‘OK’.

Now to try and connect the phone to the Clie. Go to ‘Modem’ option in the Nokia 6600’s menu (probably inside the ‘Connect’ folder if you haven’t moved it).
Click on the ‘Modem’ icon and select ‘Connect via infrared’. The phone should start looking for something to connect to.
Now go to the network setting you’ve just made in the Clie, point the infra red port at the phone’s IR port and click ‘Connect’.
Then hope for the best….

This obviously isn’t as good as bluetooth would be because a) you have to point the devices at each other and b) you have to navigate through a few menus just to enable the modem on the 6600, but it’s the best we seem to have for a while.

Sony Clie and Nokia 6600 bluetooth gprs internet access

I’ve been spending quite a lot of time trying to get internet access from my Sony Clie through a Nokia 6600 mobile phone’s bluetooth connection and have had some minor success but still not been able to get it to work completely. A major break through came with getting infra-red access (taking a step back seemed like the best way to start out) but Bluetooth/Nokia 6600/Sony Clie just don’t seem to like each other very much.

There are rumours about an upgrade to the 6600’s firmware which may improve things, but it doesn’t seem particularly easy to get hold of at the moment: either send your phone away for god knows how many weeks, or try and grab a dodgy copy and do it yourself invalidating the warranty. I think i’ll wait until there’s positive news on it actually fixing the problem first.

The stage I’ve now gotten to is:

  1. Can connect to the internet, e-mail and ssh fine via infra-red.
  2. The Nokia 6600 and Clie are paired via Bluetooth.
  3. Sending files back anf forth between the 6600 and the Clie works fine.
  4. Sending SMS messages from the Clie via Bluetooth works fine.
  5. Can make a connection to the internet via Bluetooth but unable to sustain it for any download. The strangest thing is that I can send e-mails.

A great source for the Palm dial-up script and settings for connecting to Orange can be found here:
Orange/Palm GPRS script

A Palm OS Nokia 6600 phone driver can be downloaded here: http://www.clieuk.co.uk/upload/Nokia6600.prc

There’s an interesting thread on the matter here: Clie/Nokia 6600 GPRS internet discussion.

At last I’ve gotten round to writing something!

Having signed up for blogger back in september 2003 it’s now taken me just over a year to get round to writing a first proper post. A short intro: I’m a director of a web development company called Exponetic based in Bethnal Green, London.

If you haven’t been to Bethnal Green: it’s a marvellous place. Where else could you buy something like this?:

Bethnal Green curiosity