Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

December 23, 2002

Embedding PyCrust Revisited

Patrick sent me an even easier way of embedding PyCrust in a wxPython application. Here is If your browser has trouble displaying the text file you can download a copy here.

Patrick pointed out that PyCrust comes with its own frames so there is no need to construct your own - unless you want to of course. So the major difference between this module and the one I posted last week is the reduction in the amount of code need to invoke and display PyCrust. Thanks again Patrick.

Posted by Andy Todd at 09:58 AM | Comments (0)

December 20, 2002

Oracle v SQL Server, Part 5

The hacking continues. The very last thing I have to convert from Oracle to SQL Server is a script which performs some conditional processing on my data.

In Oracle I wrote a fairly simple script in PL/SQL which opened in turn and nested three cursors. The nesting is important here, as values from the outer cursor results sets were passed to the inner cursors when they were opened as cursor parameters. Simple, and fairly routine in PL/SQL.

Taking this and converting it to Transact SQL I found a problem. There didn't seem to be any way of saying "open cursor b with this value I have just retrieved from cursor a". Well, you can, but it cannot be repeated. The problem, it seems, is one of scope.

PL/SQL follows the standard convention of most programming languages in that code units are comprised of a declaration section, followed by an execution section (encloded by some kind of markers, e.g. { } or BEGIN END). Transact SQL doesn't do this. So instead of;

    cursor a ...
    cursor b ...
    open a
    fetch a row from a
      open b with values from current value of a
    end loop

We have to do something like;

  open a
  fetch a value from a
  DECLARE b using the value from a
    fetch a row from b
  end loop

Which took a little bit of getting used to. Still, plus ca change.

Posted by Andy Todd at 03:10 PM | Comments (0)

December 19, 2002

Oracle v SQL Server, Part 4

My colleague has just found a trap for young players. By default SQL Server stores string values (in CHAR and VARCHAR columns) in a case preserving but insensitive format. See this weblog post for a more coherent description of that term.

What this means is that when you compare two string values in SQL Server they will always be the same regardless of capitalisation or any functions you apply to them. This is actually as a result of the default 'collation' that SQL Server is installed with (unless you custom install and pick another one).

What this means is that if your WHERE clause contains;

  charcol1 = charcol2

It will return true for any of the following possible values of the two columns;
"Andy Todd", "andy todd", "ANDY TODD" "ANdy TOdd", etc.

This is even if you use the available string functions lower or upper. For more information I dug up references at experts exchange and IT World.

Posted by Andy Todd at 05:54 PM | Comments (0)

Oracle v SQL Server, Part 3

This post currently breaks my rule of only showing answers and not problems. If I find a solution I'll add an update to the end, but I can't see one coming in a hurry.

Set operations seems especially hard in SQL Server. In Oracle, its quite simple to match on several columns at once in a sub query, e.g.;

  SELECT colx
  FROM   tablea
  WHERE  (col1, col2, col3) IN
          (SELECT colx, coly, colz
           FROM tableb)

It would seem that this is a foreign concept to SQL Server though, as that will allow only one column at a time in the where predicate. From searching around the web it seems that the advice is to avoid sub-queries where possible and to use joins. Although this is mainly for performance reasons.

This is certainly possible in the example I included above unless the number of rows in the two tables are different and the join clause is uneven. Then the ordinality (number of rows) in the two sets of data you are joining together is different and you end up with a cartesian product (i.e. a combination of all of the possible values from both sets). Which is generally not a good thing.

For example, if table a has 100 rows and table b has 250 rows, the result set will contain 25,000 rows. Using a sub query we will only ever get a maximum of 100 rows returned, which is usually what we want. I can't quite figure out how to do this in a join, but if you know please add a comment or drop me an email.

Posted by Andy Todd at 03:16 PM | Comments (0)

December 18, 2002

Embedding PyCrust in a wxPython Application

I wanted to quickly add a PyCrust shell window to an application I was working on. But looking at the documentation I couldn't find a simple (enough for me) example stand alone application.

So I wrote one. With many thanks to Kevin for actually getting the thing to work and Patrick for writing the thing in the first place. Have a look at If your browser has problems displaying the text you can download the Python code here.

To run this code you need Python and wxPython installed.

The thing that confused me, and that wasn't immediately obvious from the code and examples about, was how to structure the different elements of the application.

As you can see the correct order is application (myApp), which contains an operating frame (myFrame) which contains the shell frame (PyCrustFrame). But, when you create the shell frame you pass it a reference to your application object in the parentApp parameter. Simple.

Posted by Andy Todd at 11:36 AM | Comments (0)

December 11, 2002

UK Python Conference 2003

The schedule for the UK Python conference 2003 is up. If you take a glance at 14:00-15:30 on day 2 you will notice a familiar name.

I will be presenting a paper on the delights of developing GUI applications using PythonCard and PyCrust. I shan't, however, be wittering on for an hour and a half. The organisers would like to follow my presentation with something on wxPython. Perhaps a tutorial, or panel discussion. If you have any ideas get in touch with the organisers. Or me at a push.

See you there.

Posted by Andy Todd at 04:39 PM | Comments (0)

December 09, 2002


Phoenix 0.5 has been released. I've been using Phoenix as my browser of choice at work for the last couple of months and it is great. If you don't believe me download it and have a look for yourself.

Posted by Andy Todd at 10:32 AM | Comments (0)

December 06, 2002


I've joined the EFF, have you?

Posted by Andy Todd at 01:03 PM | Comments (0)

December 04, 2002

ADO through Python

Henrik Ekelund has announced ADO DB-API, a bugger to type but a boon to getting to your SQL Server and Access databases from Python. Good work that man.

I'm also going to see if I can get it to access Alterian as I am currently working with it for a client.

Posted by Andy Todd at 10:03 AM | Comments (1)