Danny's Weblog

2008 Aug 31 [ Sun ]

Adding a new function to an Excel right-click menu

A few months ago I posted a short routine that adds a (hopefully) useful function to Excel's right-click menu when you have a range of cells selected: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Excel/filldown01.html]

At that time I did not explain the tricks for adding that function to the right-click menu. This posting lists those tricks. They aren't rocket science, and I don't go into every detail, but I think it's a better overview than most. You should read through the whole thing before you start trying stuff out.

This information is for Excel 2003. It is probably also good for Excel 2000, but is probably rather misleading for Word, and basically irrelevant for Access. I haven't tried Excel 2007, but I understand MS redesigned the whole menu structure for Excel 2007, so it probably won't work.

The following links may also be useful:

Adding menu items via VBA: www.fontstuff.com [http://www.fontstuff.com/vba/vbatut07.htm]

Creating an Excel addin: www.fontstuff.com [http://www.fontstuff.com/vba/vbatut03.htm]

If you are not very familiar with VBA, note that when you switch to the VBA window many of the top menu items do not work the same way. In particular, you can only get VBA help while you are in the VBA editor, not when you are in normal mode (and vice versa). Also, you cannot do "save as" (including "save as addin") while you are inside the VBA window.

1. The standard term in Windows for what I'm calling "right-click menus" is actually "context menus". However, neither term is relevant to VBA. VBA considers such menus to be simply a type of "commandbar", just like the main menu. In both Word and Excel you can assign a macro to a *non-context* menu via the GUI, but you can only assign a new function to a *context* menu via the GUI in Word; in Excel you need to use VBA, because the GUI doesn't let you (funnily enough).

The term "shortcut menu" usually means a menu accessed by a keystroke combination like ctrl-G.

2. For the sake of trying all this out, here's a very simple function: it makes the text in the currently selected cell (or cells) bold. Starting from a new empty workbook, open the VBA editor with Alt-F11, select Insert Module (to create Module1) and enter the following code in Module1:

Sub MakeBold()
selection.Font.Bold = "True"
End Sub

(When you test this later, remember that the right-click menu while you are actually editing a cell is a different menu from the one which comes up while you have the cell selected; the MakeBold routine will only work in the latter case.)

3. Then the basic code for creating a new right-click menu item to call that routine is like this:

Private Sub Workbook_Open()
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
  .Caption = "Make bold"
  .OnAction = "Module1.MakeBold"
End With
End Sub

You need to put this in the "ThisWorkbook" module. You can do that by opening the VB editor (if it's not already open) with Alt-F11, double-clicking on "ThisWorkbook" (under "Microsoft Excel Objects" under "VBAProject" in the Project window) to open the "ThisWorkbook (Code)" window, and selecting "Workbook" on the left-hand dropdown menu (which initially said "General").

Then the effect is: when the workbook is (next) opened, the "make bold" function gets added to the context menu for each cell (or range of cells). And that feature will *still be there* after you close the workbook, on any other workbook, even after you close and re-open Excel! (Excel has stored the link from the menu to the workbook with the actual code, and will reopen the workbook as soon as you try to execute the menu item.)

4. Now there are two snags. One is that in some cases the above code can add the menu item *every time* the workbook is opened, accumulating more and more copies... and it can happen that they never get removed. The other snag is that it's a pain having to add utility code like this to each workbook where you need it.

5. There are several ways to get around the first problem.

Whatever you do, you need to do two things: don't add a menu item which already exists; and don't forget to remove the item when you exit. But it turns out that the best way to do those two things depends on what you do about the second problem above.

6. Your decision on how to install code like this (any kind of VBA code, not just context menus) depends on whether you want to distribute the code. The big problem is macro security. Excel by default (at least in Excel 2003) refuses to load macros, giving a scary error message and a misleading prompt about how to enable macros which just creates confusion. Unless your users are very used to receiving workbooks containing macros (which allows you to use the signing mechanism – Google search for selfcert.exe), it seems to me to be impractical to distribute macros inside a regular workbook.

7. Additionally, even if the macros are for your own use, it is very tedious to have to reinstall multiple functions and features in each new workbook, and especially to update an existing workbook with new or improved code. (And you have to re-run the signing mechanism for each change, however small.)

8. For these reasons, I think the only practical way to deploy Excel macros is by using the "Add-in" feature. An add-in is just a regular workbook which you put in a special directory for special handling; under 2003 the location is c:\Documents and Settings\dannyw\Application Data\Microsoft\Addins. This is the default location which Excel automatically switches to when you do "save as" "Microsoft Excel Add-in (.xla)".

Instead of manually copying the file to the addin folder, you can use the Tools - Addins - dialog, which allows you to "browse" for another location. Once you select an addin in another location, it will ask you if you want to copy the addin to the standard addins. Even if you don't, Excel will remember to load the addin from the nonstandard location the next time it starts up.

To be any use, an add-in has to contain VBA code. Also, there's no point including regular worksheet data in an add-in, unless it's used by the VBA code – for example, boilerplate text with copyright information which macros might add to every saved workbook.

Once the user has trustingly installed the addin, the macro code will always be available: for instance, the function names are all global, so workbooks can use the functions without needing to specify a module name. And without causing intrusive prompts about enabling macros. (Which makes me wonder about what happens if you just haplessly click on an .xla file link inside a browser.)

However, the function names for some reason don't appear in the list when you insert a function via the GUI; you have to know the name already.

Because the function names are global, you can rename the addin if desired: for instance, if you are a system administrator, you can check that everyone has now installed the latest version of an addin by checking that the filename is "utils-23.xla", and if anyone still has "utils-19.xla" you can grumble at them.

Also, you only need to update a function in one place, and then the updated function is automatically available in every workbook.

9. MS suggests using the "Personal Macro Workbook" for this kind of purpose. But there's only *one* of those per user. If you have multiple users, one or two of them will be already using theirs, and they won't be happy if you overwrite it. Even if the macros are only ever going to be used by you, you may want to keep different sets of macros in different files.

10. Your users will inevitably be exchanging workbooks all the time. If they include macros, every user will need to trust every other user, and/or spend time checking that any macros are safe. If all the workbooks use macros from a single addin, users need to trust only one source.

Additionally, Excel includes an optional weak encryption feature to prevent users opening an addin to view/edit it. This may be cool if you are *selling* the addin, but of course it prevents the educated user from checking that your macros are safe.

11. Because an addin is loaded once at startup, and the functions it provides are intended to stay in memory until Excel closes, the risk of adding menu items more than once is lessened. However, it is still worth putting in defensive code (especially while you are figuring all this out). Also, I felt I should provide some code in case you do *not* deploy the code as an addin.

12. You can check whether a menu item already exists with code like this:

Function Check_MakeBold()
Check_MakeBold = False
For Each Myitem In Commandbars("Cell").Controls
    If (Myitem.Caption = "Make bold") 
        Check_MakeBold = True
    End If
End For
End Function

If you open the Immediate window, you can run the routine like this:

debug.Print(check_makebold())

The equality test is case-sensitive, although the function name is not.

13. If necessary, you can delete the context menu item when a regular workbook closes like so:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
	On Error Resume Next
	Application.Commandbars("Cell").Controls("Make Bold").Delete
End Sub

By including the "on error" you don't need to check whether the "Make Bold" item exists before attempting to delete it.

The above routine needs to be entered in the "ThisWorkbook" module, using a similar approach as for the code running on workbook load (see above).

Another approach is to reset the entire r-click menu to its default state, ie (search Excel Help for "reset method") all the original items and no new ones:

Application.CommandBars("Cell").Reset

This is good if all of your menu additions are created by a single module, but if you have multiple menu items from different modules you will probably want to handle them individually.

Another approach – the one I hinted at in the previous posting – is to include the "temporary" parameter when you add the menu item:

Set newControl = Application.CommandBars("Cell").Controls.Add(, , , , temporary)

14. Once you have the addin installed and running, how do you debug it? It turns out that even the creator of the addin can't open it directly to edit it while it's running as an addin. But if you load it as a normal workbook instead, the functions aren't globally accessible from any workbook.

There is an interesting workaround that I haven't seen described elsewhere: you simply load the file both as an addin at startup, and then as a regular workbook. It seems like that would cause some sort of namespace conflict, but I haven't run into that. I haven't figured out the details of why.

15. If you try all this stuff out, you will accumulate a bunch of functions and want to implement the install procedure by looping through them. I leave that as an exercise for the reader.

2008 Aug 28 [ Thu ]

Some grumbles about Ubuntu 7.1 Gutsy Gibbon

I installed Ubuntu for the first time a few months ago. I have installed many versions of Linux, but for a long time I had been using Windows for most things, and Linux (mostly Damn Small Linux) basically for security in internet cafes.

A Linux distribution naturally consists mainly of applications, most of which are the same for different distributions. In this post I will try to address issues which are mainly Ubuntu-related.

1. The main reason I picked Ubuntu was that I had the impression that the majority of Linux apps were available for Ubuntu as binaries. That's also why I installed 7.1, not 8.04 which had already been issued at that time: the apps were already ported and debugged to 7.1.

2. Indeed, I have generally been pleased with installing apps under Ubuntu. The system is easy and fast, with few bugs. I mostly (lazily) used the Synaptic GUI, but occasionally used apt-get when a webpage specified a command-line.

3. I am not quite so happy with the update process. I personally hate any app – including the update app – if it insists on running at at startup and hogging the machine till it completes. OK, it wasn't tough to go in and change the setup so that it doesn't run until manually triggered, but actually what I would have preferred is an automatic reminder, plus – even better – a setup which ran the update at very low priority.

4. An even *worse* point about various processes like the update process was that they *completely took over the interface*. I was unable to run the GUI system tools to even find out what was happening. I was also unable to swap out of X Windows and open a console – at least after I had previously tried to alt-tab. I think this is quite serious: how can it *possibly* happen? I have always hated the way MS Windows takes many seconds to respond to a ctrl-alt-del, but this was way worse. Perhaps Red Hat etc are no better.

5. Actually, I was surprised that ctrl-alt-del does not bring up an interface which allows you to kill an amok process. I would think the most urgent need for an unmaskable interrupt is exactly that: things like logging out are just shortcuts.

6. The system does have multiple language support, but it is not that easy to find, and since there are multiple approaches what info there is seems a little muddled. I was able to set up multiple foreign-language keyboards, but it turns out the interface only allows a maximum of four, which doesn't seem to be documented. You will need to install something to indicate the current keyboard state separately: search for "Gnome indicator applet".

7. I was quite surprised that the internal hard drive was reported as SCSI. The drive hardware is certainly IDE. I haven't seen any other reports of this.

8. The initial install (and operation with the live CD) showed several strange little video problems. For instance, the command bar was initially above the area that the mouse could get to. After some guesswork it seems to work quite reliably now, but I don't really know what I did to fix things.

9. My Motorola V360 works about the same under Linux as Windows – ie the software is full of bugs.

10. For fun I installed "desktop drapes", a utility for automatically switching your desktop background at startup. This turned out to have serious bugs and I have now completely deleted it, mainly because it needs Mono, which I am ideologically opposed to: I don't want anything running MS. NET code on my machine. The only other thing needing Mono was some sort of photo sorting utility which I also deleted.

Also, it turned out that the Mono process was the *worst* offender as far as taking over the machine was concerned.

11. I have still not found a way to avoid the chocolate-colored background that covers the screen at startup. It clashes with my desktop colors and I cannot imagine anyone actually preferring it. (The "splash" screen is something else, which only appears for a second or two on my machine.)

12. Overall I think I might have preferred the KDE desktop. A lot of features seem more mature in KDE, like support for sshfs URLs in the file browser.

13. I found it surprisingly tough to install new fonts, at least by following the docs.

14. I was surprised that the firewall was not running initially. This should be part of the setup.

15. For simple backups you may want to check out grsync. There does not seem to be any backup utility in the default setup.

16. The location where apps get installed, both in the filesystem and in the screen menu, is inconsistent. Some apps do not get a link in the menu system at alll. Perhaps this info is stored somewhere by Synaptic: it was quite irritating to have to check it manually.

17. Firefox would occasionally cause heavy, uninterruptable disk activity – initially I assumed Firefox was the culprit in all such cases. Actually, increasing the size of the cache to 250 MB seems ot have fixed it.

18. Setting up to use the Medibuntu depository (for non-open-source drivers) was surprisingly clumsy and poorly-documented. Also, it didn't appear to work after I installed it, but now seems to be working by magic.

19. I have not been able to make my wifi card work, although it is returning info about the local wireless networks (four or five are visible from my location). It may be something strange about trying to run with both a wifi and a hardwired connection. Certainly the user interface for doing so is full of peculiar bugs.

20. I looked all over for how to set the default handler for files by extension. It turns out that Ubuntu provides a utility for setting a default multimedia handler etc – System - Preferences - Preferred applications. However, it doesn't go by file extension. You set that in your file browser, ie Nautilus in my (standard) case. I suppose that makes sense, but it took me a long time to think of it.

Btw, the problem I was trying to fix was .swfs. Totem had been set to handle them by default, but it couldn't open any .swf I tried. Gnash, on the other hand, works quite well.

21. I was unable to install VMWare, and found a bug report that said it had something to do with having the machine offline during the install. I would have thought that should be the normal procedure.

22. Generally, I have been disappointed with the default Ubuntu setups with respect to security. For instance, the default Firefox setup provided with 7.1 leaves all functions enabled. On the other hand, the default Adblock Plus setup seems designed to actively mislead.

2008 Aug 10 [ Sun ]

CPAN install didn't work too well

Although I was able to make CPAN work to the extent of downloading new modules which Perl would *attempt* to run, there were *thousands* of error messages during the install which I could not evaluate. (I have *more than a megabyte* of log files from the various install steps.)

And indeed, the module I really wanted – DBD::sqlite, as I am too cheap to pay for MySQL at Panix – crashes as soon as I try to do anything more with it than load it.

It is not easy to find information on how to handle such problems. I found the following page: sial.org [http://sial.org/howto/perl/life-with-cpan/debug/] which gives the impression the writer has been through the process, but is not very useful to the newcomer (unless you like finding "vendor-supplied packages" and compiling them from source).

Fortunately, an alternative showed up shortly after I posted the previous article: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Perl/cpan01.html] Panix happened to upgrade Apache to PHP5 along with Sqlite (perhaps related to me figuring out a way I could make PHP5 run even though PHP4 was installed in Apache), so I can do a lot of the things I thought I needed DBD::sqlite for. Heigh ho, suck air.

2008 Jun 16 [ Mon ]

Setting up CPAN on panix with only user privileges

One of the reasons the perl programming language is so great is that there is a huge variety of modules avilable that you can use to add functionality. The CPAN site (http://search.cpan.org) has thousands. But installing them manually can be so clumsy and error-prone that I've found it easier to write my own code.

I had been aware for *years* that the best way to install modules was by using the CPAN module itself. But each time I tried to set it up at previous hosts or panix, I failed. (It works much easier when you are root, eg on my own Linux boxes.)

This time I seem to have succeeded. ("Danny, 1995 called and wants its issues back.")

Apparently the CPAN module *itself* was installed at panix, so I didn't have to do the whole "make install" thing on CPAN itself. However, the first time I tried running CPAN with something like
perl -MCPAN -e 'install Archie'
it apparently recognized that I didn't have any configuration defined and started asking me a bunch of questions that I didn't really know how to answer. But I knew that the *defaults* for CPAN would be to install to *system-wide* directories which I didn't have write access to. I tried using the "PREFIX" switch for the directories, but it didn't quite work. It looked like the ftp get was failing to bring in the source file, perhaps because of an error reported about "MD5 security checks". (Later I realized that impression was false – that message always appears.)

Incidentally, it's good to use a module like Archie as a test when you're setting up CPAN, because you don't want to mess up the links to a module you actually *need*. You can check whether a module is already installed (assuming your general perl setup has not been screwed up in the meantime!) like this:
perl -MArchie -e 1
which will silently succeed or produce an error message if Archie is not available.

Another irritating issue was that the CPAN setup script presents you with a ginormous list of possible mirrors, and the way to navigate and select them is not obvious. Apparently the list is fed to you via the "more" utility. Here are the tricks: when you get to the list, just press space to get another screenful, and repeat enough times to get to the end. Then you can enter one of the mirror addresses. I'm not sure how to add more than one – I wouldn't bother at this point.

Having licked my wounds overnight, I tried again this morning.

The following webpage would probably be a good guide for most people: www.dcc.fc.up.pt [http://www.dcc.fc.up.pt/~pbrandao/aulas/0203/AR/modules_inst_cpan.html]

However, actually what I did was manually edit the ~/.cpan/CPAN/MyConfig.pm setup file that had been produced yesterday. This is what I ended up with:

> cat .cpan/CPAN/MyConfig.pm 
$CPAN::Config = {
  'build_cache' => q[10],
  'build_dir' => q[/home/1/dannyw/.cpan/build],
  'cpan_home' => q[/home/1/dannyw/.cpan],
  'ftp' => q[/usr/bin/ftp],
  'ftp_proxy' => q[],
  'getcwd' => q[cwd],
  'gzip' => q[/usr/bin/gzip],
  'http_proxy' => q[],
  'inactivity_timeout' => q[0],
  'index_expire' => q[1],
  'inhibit_startup_message' => q[0],
  'keep_source_where' => q[/home/1/dannyw/.cpan/sources],
  'lynx' => q[/usr/local/bin/lynx],
  'make' => q[/usr/bin/make],
  'make_arg' => q[],
  'make_install_arg' => q[/home/1/dannyw/myperllib],
  'makepl_arg' => q[PREFIX=~/perl5lib/ \_
    LIB=~/perl5lib/lib INSTALLMAN1DIR=~/perl5lib/man1 \_
    INSTALLMAN3DIR=~/perl5lib/man3],
  'ncftp' => q[/usr/local/bin/ncftp],
  'no_proxy' => q[],
  'pager' => q[/usr/bin/less],
  'shell' => q[/usr/local/bin/tcsh],
  'tar' => q[/usr/bin/tar],
  'unzip' => q[/usr/local/bin/unzip],
  'urllist' => [q[ftp://mirror.nyi.net/CPAN/],  \_
    q[ftp://ftp.wayne.edu/cpan/]],
  'wait_list' => [q[wait://ls6.informatik.uni-dortmund.de:1404]],
};
1;
__END__

Note the following:

1. Info on the web suggested that the "~" should work in this file, and I did use it for the "make5pl_arg", but in general, I used a fully qualified path for the home directory.

2. Web info suggested creating the .cpan etc folders manually first, but I guess running the initial CPAN setup script had done that for me

3. The install script probably also guessed the correct locations for the executables like 'pager' for me. I did verify every one manually (with eg "whereis less").

4. It wasn't obvious what syntax to use to allow multiple urls in the urllist, but I guessed right the first time.

5. The INSTALLMAN1DIR value looks like it would break something, but apparently didn't. Maybe it's not essential, because I don't know how it could work.

Now I could run "perl -MCPAN -e 'install Archie' and it seemed to be OK. But I had not fixed the path for modules in my shell, so trying "perl -MArchie -e 1" failed.

The web info provides the following examples for the bash shell:
export PERL5LIB=${PERL5LIB}:~/perl5lib
export MANPATH=~/perl5lib

I am running tcsh, so I knew I had to make some changes to that. But this is the output of the "setenv" command that works:
PERL5LIB=~/perl5lib/lib
having used eg "setenv PERL5LIB perl5lib/lib". This is what I needed in the .tcshrc file:
setenv PERL5LIB ~dannyw/perl5lib/lib
Remember that you can use "source .tcshrc" to run the commands in that file again without having to log out and back in – although some commands may have unwanted results, for example if you append a directory to a path repeatedly.

Note that my version has an extra "/lib" on the end. It didn't work without it. I don't know why the web example apparently did work for them without it.

Incidentally, the default perl at panix is indeed perl5:
This is perl, version 5.005_02 built for i386-netbsd
I don't know what to do if you're running perl4 or 6 (is anybody?)

I'm not sure why the web example tried to bring in an existing value for PERL5LIB. There wasn't one when I checked (perhaps too late) and running eg "perl -MCGI -e 1" still works. – Later: according to this website:
www.perl.com [http://www.perl.com/doc/manual/html/pod/perlrun.html] the PERL5LIB variable is

A colon-separated list of directories in which to look for Perl library files before looking in the standard library and the current directory.

so it seems to be a *user-defined* variable, so it is normally empty and it doesn't smash any existing definition if I just set it directly as above. Maybe the bash shell doesn't complain like tcsh if the variable is empty when you try to append to it. – Confirmed.

The MANPATH thing just doesn't work for me at all. Doing "perldoc Archie" works fine without it. Trying to append to MANPATH causes an error message. And if I set MANPATH without appending to the existing value, it makes "man bash" (or anything else) fail.

Some webpages say you need to do this: setenv MANPATH $HOME/perl5lib:${MANPATH} but that failed too. The really funny thing is that $MANPATH doesn't seem to be set to anything in the set or setenv lists, but setting it to something seems to screw up "man". I guess setting $MANPATH *completely overrides* the path set up in man.conf.

When I checked, the default search path for man set in /etc/man.conf seems to be really long and complicated. I couldn't see an easy way to get the current value for it into the environment variable. However it turns out that there's a "-m" switch to man which *adds* a search path to the existing (invisible) path, so I put the following line in .tcshrc:
alias man "man -m ~/perl5lib"
and it worked fine, ie both "man Archie" and "man bash" worked.

Now I have to actually try it with a module I need! Maybe I'll write that up later. If you have been following along and want to try it yourself, remember that cgi programs don't get your path from your user environment, so they will need something like
push(@INC, "/usr/lib/bliv/");
as I discuss in an old posting:
www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Perl/perltip01.html]

2008 Mar 16 [ Sun ]

Useful subroutine in Excel: "filldown"

This is not the most complex stuff I've done in Excel but it is all my own code and does something which I think is generally useful, so I thought I'd list it.

It's intended to be linked to a right-click; I leave that out as an exercise for the reader... especially as I now realize that I wrote the code for that part incredibly badly, although it does work. Hint: there is a parameter for this: application.CommandBars("Cell").Controls.add which prevents an option being added multiple times. See one of the books (I forget which) in my post: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Excel/books01.html]

It's intended to make it easier to enter data in a typical Excel table where the same values recur multiple times. For instance, if you are entering vehicle expenses in a company garage, you probably need to enter the expenses for each car multiple times with the same info so that you can easily sort the data with a pivot table. With this sub, you can just leave a blank for repeating fields, and when you're ready you can just right-click on the table and select the sub, and a value will fill down into all the empty cells below it for each record and field. In many cases it will halve the entry time and cut out a lot of errors as well (ie ensure that each repeated value is identical).

Sub filldown()
' dW 2007-12-20 Loops through a range
' if a cell is blank, copy from previous row
' (we hope)
Dim cellnum, cellrow, cellcolumn, rowcount, columncount As Long
If TypeName(Selection) <> "Range" Then
  Beep
  MsgBox "This command requires a cell/range to be selected."
  Exit Sub
End If
If Selection.Areas.Count > 1 Then
  Beep
  MsgBox "This command only works on a single range."
  Exit Sub
End If
rowcount = Selection.Rows.Count
' You can't fill down if less than 2 rows
If rowcount < 2 Then
  Exit Sub
End If
'
columncount = Selection.Columns.Count
' MsgBox ("Rows = " & rowcount & " Columns= " & columncount)
'
cellnum = 0
For Each cell In Selection
  cellnum = cellnum + 1
  ' The \ operator does integer division
  ' First row is row 0
  cellrow = (cellnum - 1) \ columncount
  ' Don't do anything on first row
  If cellrow > 0 Then
    cellcolumn = (cellnum - 1) Mod columncount
    ' MsgBox ("Cell: " & cellnum & "Row: " & cellrow & " Col: " & cellcolumn)
    ' I seem to have defined cell locations starting at 0,
    ' but Excel has cell x,y starting at 1,1.
    ' Need to handle cells with formulas separately
    If cell.Value = "" Then
        If Selection.Cells(cellrow, cellcolumn + 1).HasFormula Then
            cell.Formula = Selection.Cells(cellrow, cellcolumn + 1).Formula
        Else
            cell.Value = Selection.Cells(cellrow, cellcolumn + 1).Value
        End If
    End If
  End If
Next cell
End Sub

When you play with this, remember that Excel resets the undo stack every time you run a macro. If someone wants to fix this code so that it implements its own undo feature, that would be cool.

Some basic setup info for PHP

I haven't looked at PHP for years and needed to read through Sams' "Teach yourself PHP in 24 hours" recently.

Here are some basic tips for setting things up:

1. Assuming your Apache server allows you to use .htaccess files, you can set a bunch of useful stuff here, eg:

AddType application/x-httpd-php .php

For instance, if most of your site is real .html files but you want to put .php files in a certain folder, you can put an .htaccess file in that folder that tells Apache to run the php interpreter on .html files in that folder:

AddType application/x-httpd-php .html

(You don't want to do that in your root folder because it would waste time running the php interpreter on all the ordinary html files.)

2. The phpinfo() command inside php of course produces a nicely-formatted html file with all the setup information for php, and you need to look at it. In particular, you want to check which php version you have and what modules are loaded (irritatingly Panix has a rather old version with few modules compiled in).

Also, it's good to check the "config-file-path". This (assuming you have shell access) allows you to find the actual php.ini file to check any items not shown by the phpinfo() command.

3. If you don't control the server, you can still set php setup variables inside .htaccess. You use the "php_flag" directive to set a php variable and the php_value directive to set a php.ini variable to a value, eg:

php_flag short_open_tag On

php_value include_path ".:/home/corrdev"

4. Even if you can't use .htaccess files, as of php 4.0.5 you can set some .ini values within php pre itself, eg:

ini_set( "include_path", ".:/home/corrdev" );

5. Don't forget the main php website, eg:

www.php.net [http://www.php.net/manual]

6. Here's the entire contents of a file you can save as test.php:

<?php
phpinfo();
?>

(I know putting that here makes me look like not the greatest programmer in the world, but every time I start looking at php again I find myself having to work out the basics of the syntax all over again.)

7. Here's some code which looks simple – ok, it is simple – but I find myself wondering whether it will work every time I start playing with php again (this is basically from the book's Listing 5.14 Returning to HTML Mode within a pre block):

<?php
$display_prices= true;
if ( $display_prices ) {
?>
<p>Interesting stuff to show in this case</p>
<?php
}
?>

I don't know about you, but if I had to figure out that syntax from scratch it would take a while.

2008 Feb 02 [ Sat ]

The benefits of learning Excel VBA

The following link explains in amusing detail just how unexceptional the Societe Generale trader M. Kerviel is in his skills, or indeed modus operandi: www.theregister.co.uk [http://www.theregister.co.uk/2008/01/31/kerviel/]

From what little I know about the situation in such companies, the article is not misleading.

2008 Jan 29 [ Tue ]

Some books I've used for Excel VBA programming

Recently I've had to use Excel quite a lot. I used to be quite snooty about it (although I recognized that – after SQL Server – it was Microsoft's most respectable product). Now, having gotten into VBA programming, I have to admit you get a rather solid development environment.

Anyhow, I wanted to put in links to a couple of books I can recommend:

1. Excel 2007 VBA Programmming for Dummies - John Walkenbach

I actually read his version for Excel 2000 first. The two books are much the same. I had a feeling that he caught some errors in the new version, or maybe I just understood stuff more the second time through (I was unable to compare them except by memory).

I don't think Walkenbach ever did a version of this book for 2003. I don't think there was much difference between Excel 2000 and 2003, but there are a lot of changes in 2007 for the new menu ("commandbar") structure. And the 2007 book *only* documents the 2007 code - people expecting the book to be backwards-compatible with 2003 are going to be very disappointed.

But in general, I found the book quite ueseful.

Walkenbach recommends this "blog": www.dailydoesofexcel.com [http://www.dailydoesofexcel.com/]

He also recommends reading Microsoft's own newsgroups via Google. Search Google Groups for microsoft.public.excel.programming, as well as several others under microsoft.public.excel.

2. The Absolute Beginner's Guide to VBA

The advantage of this book is it explains how to run multiple Office apps using VBA: for instance, to read your Outlook mailbox (or at least the header fields and a smidgen of the text) into Excel.

Also, it does show various useful examples in non-Excel apps, and I may need to take a shot at Word VBA sometime.

Incidentally, I rather wish the titles of these books weren't so embarrassing. Don't bring them to your client site! I've actually done some fairly complex stuff with Excel, and if clients ever see this blog they'll get the wrong idea. Hmm, then again if they see some of my other posts my goose is cooked anyhow!

2007 Jul 08 [ Sun ]

Scanning and database-based file access

I have always wanted to transfer my bulging paper files to disk, and I've bought two scanners, but only got around to actually doing a lot of scanning just recently.

When I use the term "OCR" below I am referring to all the software features needed to produce readable text. It turns out for instance that almost every document you ever want to scan – forms, magazine stories, invoices, legal documents – is formatted in blocks on the page, not in single lines. So simple OCR, even if it can read the multiple fonts involved, jumbles together words from separate blocks. If the OCR system can handle block text at all, it usually deals with it by converting to MS Word format, which is actually not very good at handling text blocks, so you wind up with a document which is very hard to edit without smashing the block structure.

At the time I bought the first scanner (in the USA), my idea was to run OCR on everything so that the material was searchable. The OCR performance however was unusably bad, and I let the idea languish. Furthermore, it takes a surprisingly long time to scan and OCR each sheet, especially if there is any difference at all between sheets. (You think you can scan everything and OCR it later, but it turns out that you need to fiddle with all the scan settings to make the OCR work.) Another thing that bothered me was that it was clear that to scan everything at reasonable quality was going to fill more than one disk, especially if I scanned everything at top quality to give OCR the best chance.

Well, technology progressed. About a year ago I bought a new scanner. It had much better resolution, speed and contrast range, but the OCR was still unusably bad. I let it sit there for a long time, until I really had to cut down on my paper files.

The following is some conclusions after doing quite a lot of scans over a couple of weeks.

1. Even if OCR never worked, I really wish I had done scanning on my files before leaving the USA. I really don't need any of the physical stuff I have stored there and could have happily let it go and saved the storage fee, but I can't bring myself to discard the paper files (and photos). I could do so if I had human- readable scans.

2. Even if OCR doesn't work well enough to let the optical scan be discarded, it may still be quite useful in allowing a scan to be located. However, this means that the text is separate from the scan, which requires some sort of database system. I don't actually know of a system that works quite like that; the products I've seen seem to want to pretend that their OCR is perfect, and make it clumsy to get to the original scan from the text. So I haven't implemented this yet. Presumably though my scans are of sufficient quality to get something useful from OCR once I find a suitable product.

3. OCR of course only works in one language. (I was able to use OCR on a Thai subtitles file from a DVD, but of course it doesn't work well with subscript and superscript vowels and tone marks: you have to train it to recognize *each combination of consonant and vowel and tone mark separately* – which I found actually do- able. But the situation with Cambodian, where you have subscript *consonants* too, would be far worse.) Hmm... it occurs to me there has to be some sort of mechanism in the OCR I was using to handle superscript accent marks, because it is rated to work on French. Hmmm...

4. I was able to compare several scanning software packages, and none of them really impressed me. I wound up going back to the basic scan utility that came with my scanner. While it doesn't include OCR, it has a lot of convenience features relative to the software on my first scanner. For instance, you can set up multiple scan areas and create multiple files from a single process. (I can't say "single pass" because the software actually tells the scanning head to scan each area separately – it doesn't scan the whole page and then output slices. On the upside, it allows you to have different setups for different areas. On the downside, I've never figured out how it decides which area to do first.)

The only big disadvantage of the software I was using was that there was no option to save as gif. It would have fit the image type much better. I wound up using jpg for everything. Surprisingly, I found that selecting color did not make a big increase in the jpg filesize: perhaps 30% larger on typical documents. Don't forget to use the descreening filter whenever there's a halftone area in the original: even if it the scan looks OK, the output filesize will be bloated – perhaps by a factor of two or three! – if the software has to try and store all those little blotches. (Your software may call this function something else – probably "moire filter".)

I've seen some examples of very compressed scans using .tif. I think one of the standard compressions for tif is fax compression, which of course is optimized for black text on white paper. My sw actually will produce .tifs, but it doesn't seem to have an efficient compression algorithm – it always looks worse than jpg for an equivalent filesize. I just checked and a tif using CCITT compression was 4 times larger than my jpg for no apparent quality gain.

5. The database issue is important for even fairly simple cases, even though I can't find references to it on the web. For instance, suppose you go to the doctor and get a receipt. Do you think you should store it under the name of the doctor, under "receipts", or under "medical"? Or under "2007"? What about your *girlfriend's* receipts? What if the receipt covers procedures on you *and* your girlfriend?

Suppose you're scanning bank statements. Suppose the statement is dated 2006-01-03, for the period 2005-11-22 through 2005-12-21. What date should it go under? Suppose you don't receive the statement till 2006-06-17? Suppose the bank is taken over by a bank with a different name, but you continue to get items with either name for months?

6. What I'm getting at is that a tree structure is not satisfactory for arranging the kind of item which is typically scanned. You need a database system which allows a file to be tagged and located by a combination of data fields.

7. I had previously been using a product called ThumbsPlus. It is intended for handling photos, especially where you have a website with thumbnail pages allowing you to choose the full-size pictures to download. Other products can produce thumbnail layouts like this, but TP has a lot of convenience and automation features. Its only real limitation is it doesn't allow you to create a page template in Dreamweaver or whatever and use it for its created pages. – Oops – actually it *does* have that feature. Wow.

A particular strong point is its handling of metadata. As well as allowing internal fields inside the jpg to be set – EXIF etc – and read in various automated ways, it can also hold user-defined fields in a separate database. (There is nothing to stop you using predefined fields for your own purposes, but of course it's confusing, and may prevent some automated features from working.)

The separation between the actual file and where the metadata is stored is always a little worrying. For instance, if you do a folder move in Windows instead of using the built-in TP file management, TP loses track of all the external metadata. (It does keep track of volumes by name however, and I think you could use that so that if a directory tree was on a different drive letter for some reason, TP could still match up the files to the separately stored metadata. But I haven't actually verified that.)

I'm surprised that this functionality isn't a bigger part of similar products. At any rate, if it's provided it's not a major part of the specs on their websites.

Database features are provided using a Microsoft Access database. However, you can also point TP at any ODBC data source, including SQL Server and MySQL. (I haven't actually tried that yet, but I have been noticing a slowdown on moving between folders with a database over 100 MB in size, and so I may get around to trying one of them – probably MySQL as it uses less resources.)

8. I therefore think that I should have stored scans simply by scan date – ie, created a folder with the date, and then numbered them 20070605-001.jpg, 20070605-002.jpg. I would then need to set data fields on every file so that they would be locatable. (What I actually did was to try and put files in folders and subfolders with meaningful names, but as explained above they don't really fit that structure.)

I haven't actually *done* this yet because I was busy with the scanning. However there's no reason you can't do it subsequent to scanning, assuming your software has reasonable facilities for applying data to groups of files. Another reason for holding off is that I keep thinking there's a more powerful product than ThumbsPlus out there somewhere – for instance, one which integrates database features for image files and text files (or files which have readable text inside them – for instance a Corel Draw image file might contain a reference to "passport.jpg").

9. An alternative way of accessing files multiple ways would be to make each file part of multiple galleries. A "gallery" is a TP feature which allows you to form groups of files which can easily be browsed through or copied to another location, without needing multiple copies, and means that if you modify the original file the version in the gallery now displays the modified file.

This is probably a little clumsier than the database approach, and does not allow combining choices. On the other hand, the database search featurein TP does not allow searches which are much more complex, eg combining ands and nots as in "country eq "thailand" and (city ne "pattaya") ". Presumably you could do this sort of thing by opening the database file inside Access, but I have been too lazy to try.

10. I have just realized that the "IPTC" fields *inside* the jpg can be used to avoid the necessity for user fields stored in a separate database using two tricks.

First: there are a *lot* of fields inside the IPTC area. You can just decide to use them for your own purposes, for instance you could use the "Urgency" field to mean how good the shot was on a range of 0 through 9.

Second: the IPTC editor allows you to copy info back and forth between IPTC fields and other database fields. So you can use separate database fields for normal functions, but intermittently copy them into IPTC so that they are safely "backed up" in case anything goes wrong with the database.

11. Fortunately a TP database, at least when it's still in Access, is just a single (large) file. So it can easily be backed up. I haven't encountered a case of TP database file corruption, and I've been too lazy to do a disaster test (doesn't mean you shouldn't) so I haven't tried using an old version of a database file deliberately, but I've certainly (by mistake) made major changes to the actual image directory structure outside of TP, and when I reload TP there are no apparent problems – it just isn't up to date on folders which have been added or removed (or moved).

12. Now a few notes on the physical side of scanning.

13. On my scanner the bed is (as you might expect) a little larger than the actual scannable area. What that means is that if you align the workpiece with the sides of the bed, you lose part of the image. (Quite often marginal scribbled notes are the most important part of the image.) But if you don't locate the workpiece somehow, it's in a different location every time, and probably way off perpendicular too.

As a cheap way round this, I scotch-taped two plastic rulers to a horizontal and vertical edge of the bed, allowing me to rapidly and repeatably locate the workpiece at an exact location. If I had realized how much easier it would make everything, I would have cut two 30-cm rulers so that they fit exactly at a mitred edge (and fit fully inside the bed instead of slopping over at one end). Ideally, they would be cut down in width too, so that you could still use them to locate a full page. It might be better to try creating both axes at once by cutting an existing right-angled thing, like a drafting triangle, and then gluing ruler sections on top of it.

When I think about it I'm a little surprised that you don't get a plastic gizmo like this when you buy the scanner.

Incidentally. the sides of the scanner are not quite perpendicular. But amazingly the sides of sheets of paper are quite often out of whack too. I think my bank actually feeds paper off a roll and trims it to size after printing, possibly to allow full bleed, or possibly because roll feed is more reliable than sheet feed.

14. Clearly you can scan a book much better if you cut the pages out of the binding first. What I did not realize initially is that it is worth taking a little extra effort to make a *straight* cut along the binding. This makes it possible to locate the pages distinctly more repeatably. If the cut is not straight, at least make sure that the scan is perpendicular to the workpiece by aligning one of the machine-cut edges of the page to one of the ruler axes, rather than trying to jam the ragged edge against the (other) ruler axis.

I used an L-shaped metal ruler to guide the knife when I cut the spine. This worked well enough. Of course, you need a cutting board under the workpiece.

15. If you don't cut the spine off, it helps a lot if you have something against the side of the scanner that the excess part of the book can rest on, so it doesn't drop out of the scanner. You will probably need such a support on both sides of the scanner.

16. The book will still tend to twist around as you close the scanner cover on it. You can try just not putting the hood down, but I think that will cause ambient-light issues. Another thing to do is use a thin, bendy metal ruler to hold the book in place while you close the lid, but you may still get some movement when you withdraw the ruler. What would be really good would be a similar device attached to the exterior of the scanner at one end so that you could just leave it in place during the scan and lift it off later.

17. Now a few miscellaneous issues.

18. How do you handle all your (paper) files *during* the scan process? As you scan you will have a perfect opportunity to mark files for disposal, which means you can't just transfer them back to where they were. I did not think this through initially. What I wound up doing is having one big bag of files which were really junk – not worth scanning. Then I had another area for files which I had already gone through and (largely) scanned, but intended eventually to discard. In this area, I kept the files in their original folders, so although they were in a big heap, they could be re-accessed if necessary. I also needed a (hopefully much smaller) area for files where it was essential to retain the paper originals – tax returns etc.

I had a fourth area for files curently being worked on – because I would realize that I could use the current setup for several sets of files, for instance.

If you are doing something like this for an office you probably should set up at least three trestle tables for this.

19. Once you think you've *finished* the scanning, how should you proceed? First you should make a *backup* of the scans! Then you need to store all the paper files you want to retain. Then go through all the items for destruction again quickly. Then destroy them. Since all trash in Cambodia is opened and examined at the curb, it must be shredded with a crosscut shredder. It may also be advisable to randomly separate each bag of shredded material into two bags, and discard the two halves at an interval of a week or so. Burning is better, but hard to manage. On the good side, Cambodians seem to burn anything anywhere, so they're unlikely to complain unless you burn down the building.

2007 May 22 [ Tue ]

Google problem for people in Cambodia seems partially fixed

A few days ago I described a problem with trying to access Google from a cambodian IP: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Internet/google-kh01.html]

Weirdly, the next day I noticed that the problem had been fixed. I had noticed that somebody from Google's IP range had been snooping round the site, repeatedly hitting it first from Google and then an IP in Singapore or somewhere; maybe he checked it out when he happened to notice I had a Google gripe.

The other possibility is that Google was *always* working in Windows, because it's *still* not working in Linux Firefox. I can't believe that though, because I had been noticing the problem for weeks, and I still use Windows occasionally. ...I just checked and the problem is gone from one of the machines I use most, in Windows Firefox.

Hmm. Why is it still *not* working for me in Linux Firefox? ...Hmmm... I tried turning Javascript on but it diodn't help.

2007 May 17 [ Thu ]

Irritating problem with Google in Cambodia

For a year or so Google has had a Cambodian version. Apparently, as soon as it was available, the main Google page at www.google.com would notice if you accessed it from a Cambodian IP and would add the option of clicking a button for the Cambodian version.

However, something was not very well thought out: they tried to display the link in Khmer script, but on 99% of the machines I've seen it displayed as boxes. I never tracked down what the real problem was – the code for Google's main page is much more complex than its appearance suggests – but I'm guessing they tried to use Unicode, which very few browsers here are set up to support. I'm sure that if they had just tried to use Limon they would not have had the problem, but it probably doesn't fit into their setup, and it's not politically-correct. (Even if the browser happens to be set up to display the fonts correctly, the Cambodian users don't know how to enter text even if the Khmer Unicode keboard handler is installed.)

Presumably Google people noticed that almost nobody was using their link, so they decided to go the fascist route: now all attempts to reach www.google.com are redirected to www.google.com.kh. Considerately, they left a link to "Google.com in English", which is the only thing you can read on the page (for 99% of befuddled users).

Regrettably however when you click on the link (to www.google.com/ncr) it *doesn't work*; you are redirected *again* to www.google.com.kh.

I assumed this would be a temporary thing, and started accessing www.google.com by IP number. However, after a couple of weeks that IP number stopped working. Presumably somebody noticed a bunch of accesses by IP number and said "we'll fix him! we'll deny accesses by IP!". I found a different IP number which is working right now, but I fear Google may implement a system-wide fix for my "problem".

Google has an excellent reputation (although I have always maintained that they lucked into search as one of the few applications of computers where results may still be useful although provably wrong, and better yet difficult for the hoi polloi to prove wrong) but this problem makes them seem not only utterly clueless but offensively intrusive.

2007 Jan 10 [ Wed ]

Debugging CGI programs

In trying to add new features to this site recently I have had to figure out, or dredge out of my fading memory, the following tricks for debugging CGI programs.

1. Of course you can run perl from the command line, like so:

perl ~dannyw/htdocs/bin/blosxom.cgi

With older versions of perl, this will automatically prompt you for CGI variables. You can enter these one after another like this:

startdate=200610

listqty=20

and finish with a ctrl-D. Except ctrl-D didn't work for me! It turns out my shell was set up to accept a ctrl-D at the shell prompt to log out, but when *not* at the shell prompt the shell just silently discarded it – not I think what the docs claimed. I turned off the logout feature and then my shell passed ctrl-D to perl as advertised.

2. You can also put the parameters in right on the command line:

perl ~dannyw/htdocs/bin/blosxom.cgi startdate=200610 listqty=20

but for some reason I could not make that work to set the "Extra path information", ie the parameter PATH_INFO which Apache passes to cgi programs in the environment and which Blosxom expects to contain the folder path eg "/Reviews/Movies".

What I had to do instead was set that parameter in my shell environment:

setenv PATH_INFO /Reviews/Movies/

which of course does not interfere with the value set by Apache if you're running from the command line – duh (it took me a long time to realize that). (The "setenv" built-in function works for my shell but probably not for yours.)

3. However, now I discovered that my suspect code did *not* bomb from the command line, so I still couldn't see the error message! So I had to put a call to the Carp module in Blosxom.cgi:

use CGI::Carp qw(fatalsToBrowser);

which told me the file I was trying to write to was "no such file". That's weird, I thought: it's there, when I check from the command line.

Then I noticed that another filespec in the same section of code – one which worked – used a different path. Aargh! In Apache's environment it doesn't understand the same filespecs that I can use in my shell – including when I run a .cgi program from the shell. I changed the filespec to match the other file's path and all was well.

It's really strange: I could have sworn that the bad filespec *did* work a few times... oh well.

2006 Dec 25 [ Mon ]

Interesting vulnerability in Windows/Word

I noticed while reading the following Slashdot discussion: ask.slashdot.org [http://ask.slashdot.org/askslashdot/06/12/15/0144253.shtml] that the default behavior of Windows in opening files is not what it appears to be.

You would think that Windows goes by the extension, and if it doesn't recognize the extension it asks the user to select an app. But what actually happens before that is Windows somehow checks whether it's openable by Word, and if it is Windows opens it in Word.

I just tested this by renaming a .DOC file .XXX and Word opened it without a qualm.

The significance of this is that if you are a system administrator and you have been filtering MS Office files because of the danger of macro viruses etc, you actually need to filter *any* extension.

Presumably the reason for this was to support Word Perfect users who were used to naming their files with arbitrary extensions. But surely the person who did this must have realized it was horrible for security, right? It's an example of the bad things that can happen when the company that makes the OS also makes the app.

2006 Oct 13 [ Fri ]

Sorry about "how to reattach to a job"

A few days ago an article showed up by mistake on my log. I had written it years ago but found that the content wasn't correct before I posted it, so I put it aside by the expedient of changing the filedate to a date unimaginably far in the future.

That happened to be this September 27th. The blogging software obediently started displaying it.

I didn't notice for a while and when I did I wasn't sure if I should erase it or publicly disavow it as I am doing now; eventually I came down on the side of not rewriting history (for the sake of my two faithful readers – hi, Seymour and Piotr!).

The article: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Opsystems/Linux/jobs01.html]

Incidentally, I still haven't really figured out the problem. I tried sending a msg to panix help, who didn't know either. However, the workaround is to use the "screen" utility, which has a built-in command to reattach to broken sessions. Do "man screen".

2006 Sep 27 [ Wed ]

How to reattach to a job

Some tasks in Unix are relatively easy: "You want to just read a text file? Sure! Just do man less!!". (You think "less? Less?? wtf?".)

Other tasks are less easy to find. In particular, certain aspects of the shell are hard to find because they are documented under the shell rather than as a separate utility. Also, even if you have a hint about what function or keyword they are related to, it is very difficult to search the gigantic man file for your shell because the name of the function tends to be short or common.

This is I guess my "mea culpa" for not finding the answer to the following problem: if you lose the connection to a login session and the server retains a suspended version of the task you were running (ie the editor, mail client, etc) how do you reattach to it?

Here's the answer, at least for the cshell. First you probably want to examine the list of suspended jobs in order to be sure which one you want:

jobs -l

Then you can restart it with fg:

fg %job

Restart a suspended vi with "%vi" or "%?string" to specify a job containing "str ing"

2006 Jul 05 [ Wed ]

Yes, you can find dual-layer DVDs in Phnom Penh -- from China

In my previous posting on DVDs available in Phnom Penh, I pointed out that all the DVD copies you see are single-layer, whatever the box says. I also speculated that genuine Chinese DVDs may be available in dual-layer.

This week I experimented. There's a large Chinese-oriented DVD/CD store on the 3rd floor of the Soraya Mall at the northeast corner; it has a lot of Chinese movies, plus software with Chinese packaging. (It even has a Korean section, which I'd never noticed anywhere else in PP.)

The staff did not speak very good English, but they had heard of the term "dual layer", although they were unclear on what it meant (as indeed I would have been up till quite recently). They started off by waving boxes with a logo saying "9" at me, until I pointed out that these did not actually say "dual layer", the way the kosher labels do, and I would not believe them if they did.

Eventually they came up with what appeared to be a kosher DVD from China *of a Chinese movie* (I was assuming that like Thailand the Chicoms protect their own industry even though they allow unfettered piracy of other countries' products, much like the USA in the 18th century). I was won over because it was labelled "Region 6" – I have never seen that on any other DVD in PP, although one sees "Region 1" quite often (and it is not true). So I crossed my fingers and hoped that the "dual layer" claim was true.

I did not ask the manager (evidently Chinese) to check it, and paid 4 USD, so I felt rather foolish until I checked it and verified that it was a dual-layer – I think the capacity was 6.5 GB. (I usually don't pay more than 2 USD for a DVD, and that day I happened to buy seven DVDs at a dollar each that were quite good.)

It's called "The Music Box": www.lifeofguangzhou.com [http://www.lifeofguangzhou.com/node_10/node_35/node_109/node_266/2006/03/31/11437845481570.shtml]

There were a few more interesting elements:

1. My software actually showed it was *not* a region 6 disk; it displayed as Region 1, 2, 3... etc for apparently all regions. (It occurs to me that this may have been the *very first* disk my player has ever played which was not Region 0.)

2. Despite the extra data, and with a not very long movie, the quality was not very good. There were plenty of little specks of dirt on the image that showed it had been made from an optical print of routine theater-issue quality. I wonder if these imperfections caused a high noise floor that wasted the bitrate leaving nothing extra for the picture.

Still the image was adequate, even when zoomed in to fill the screen from the 16:9 source. Shadow detail was fair. I don't think it was simply captured from a theatrical screening.

There was one section, about two minutes long, with several read glitches. My DVD player seems to show read errors that other players don't, so I won't hold that against the DVD; however, a similar region did show up when I did a surface scan on my computer's DVD drive.

If I had to guess, I'd say it was a kosher digitization from a poor print by a service company that is inexpert. The menu system, for instance, looks more amateurish than most ripoff disks.

3. Again suggesting a theatrical print, it had Chinese and English subtitles burned onto the image; there were no separate sub tracks on the DVD. (I'm guessing the producers had US distribution in mind, and apparently there was an official DVD release in 2006-05, although I can see no reference to a theatrical run.)

2006 Jun 21 [ Wed ]

DVD video copies in Phnom Penh

Here are some things I've noticed about the cheap video copies available in Phnom Penh. Much of this will apply to Thailand and any other country in Asia.

1. The information on the outside of the box cannot be trusted at all, even on very significant details like the subtitle or dialogue track languages. My impression is that for movies which already exist on DVD, the copy just shows a scan of the artwork from the product it was stolen from – for instance, the recent "Doom" was available with a cover with the slogan "Keiner kommt hier lebend raus!". This is then overlaid with a text box giving the language info, because the copier frequently downloads "fansubs" in other languages from the internet and adds them to the data streams.

Even when the desired languages are present on the disk, they are surprisingly often mislabelled or unlabelled. My guess is that the person who knows how to specify the language name when burning the master is not someone who *knows* a lot of languages.

2. For instance, most DVD covers show something like the "DVD 9" symbol. Even when the "logo" carries the words "dual layer", it is not. I bought a DVD writer today and asked to check it; the (very helpful) technician had suggested I bring a DVD 9 to check the dual-layer aspect, but I went to all three stores I normally frequent and could not find *one*.

At one store, the salesman said he knew of a few real DVD 9's but when I asked him to check them on a PC he discovered that only one out of 5 had more than 4.7 GB: it was 4.85 GB! I thought it was highly unlikely that anyone would burn a dual-layer DVD so close to fitting on a single-layer, and concluded it was probably a single-layer too.

Actually, when I showed up to check the drive, the tech had found what he assured me was a real dual-layer DVD. It was a Chinese movie; perhaps I should try some of the Chinese-language video stores.

3. When you buy DVDs, you should always ask the store to check the quality. I have several times discovered that the disk does not work at all in their drive; once or twice they tried several of the same title and they were all bad. My DVD player has problems with perhaps 20% of disks and cannot play them to the end (although I've been having a run of good luck lately). I therefore like to go through the chapters checking that they're all accessible, although this by no means guarantees that the disk is also usable on my drive, and I have been getting lazy on this.

Irritatingly, in one store the owner has taken it upon himself to not even demo DVDs which are not what he calls "top" quality to me. Sometimes, if I feel I will never watch the movie again, I'll put up with a cam copy, as long as the sound is reasonably clear.

4. Even the best copies have typically been compressed to fit on a single layer DVDR. My copy of "The Lion, the Witch and the Wardrobe" is mostly clear, but has a few patches with severe digitizing artifacts. Irritatingly, they do not always take the opportunity to remove the lock on the "FBI Warning" and similar infuriating stuff that you have to sit through on the legit version.

It would be nice if there were some sort of standard labelling system so that you could check the quality of a disk at a glance. Even if the *stores* didn't want to use it, the customers could theoretically *ask* for all the details, if there were some concise, standardized jargon. At the moment the only word I can use with the Cambos is "clear". One shop uses "90%" and "95%", but has not succeeded in explaning to me what those terms mean.

5. In order to help me learn Khmer, I have looked around for DVDs with Khmer audio and subtitles.

I have found *no* Hollywood movie available on DVD with Khmer subtitles. What you *can* get is VCDs with a Khmer audio track, no English, but sometimes English subtitles hardcoded on the video. I find them very hard to learn Khmer from because the Khmer seems to be a very free translation (to the extent I can figure out what they're saying at all) so the English subs are little use.

I have found a few *Khmer* movies with English and Khmer subs, but they tend to be about kings, wizards and giant snakes, so the vocabulary involved is not ideal. (Also, the quality of the English subs is not wonderful.)

I have experimented with subtitle software and it seems surprisingly easy to rip the English subtitles from a Hollywood DVD so that you have a text file which can easily be used to carry out a translation to Khmer. If I get around to it I may produce a DVD with Khmer subtitles and see how long it takes before it appears at Soriya.

When I ask vendors why DVDs are not available with Khmer subtitles they just say that Cambodia is a small market. It seems to me it would not be that difficult to set up fansubbing groups as are common in the US anime world. This could be emailed back to the manufacturers in Malaysia and Vietnam. The subtitle files are not hard to add and are amazingly small, even though they are stored on the disk as bitmaps. It's hard to see exactly how to *make money* of course, although surely Khmers would prefer DVDs with subs to no Khmer at all. Perhaps Khmers are just not very good readers and would only respond to products with Khmer audio tracks.

It occurs to me that any Khmer fansubbers would have to be very careful to wait until the kosher English subs are available from the official DVD release. Their chance of producing anything comprehensible from the subs available on zero-day releases would be zero.

6. I can see no sign of an official crackdown on copyright evasion. Around the same time as I saw reports that there was some sort of crackdown in Malaysia, I noticed a slowdown in supply, and then I saw that one store was reduced to receiving DVDs shipped in suitcases, but that's about it.

The porno isn't on display, but if you are a single man walking around without your girlfriend you will find several stores eager to demonstrate that they have it for sale (I was not brave enough to ask them to demo it however). OTOH the porno available seems pretty drab and flyspecked.

2006 May 13 [ Sat ]

Weird problem with Putty pscp

"Putty" is the Windows program I use to connect to my servers using the secure ssh protocol. It's freely downloadable. It includes the "pscp" program for doing file transfers via the encrypted ssh link.

A few days ago I needed to do a file transfer but pscp failed with a strange "out of memory" error. Feeling superior, I booted my little CD with "Damn Small Linux" and ran scp, the equivalent version of pscp but otherwise completely unrelated, only to get the same error msg!

As I discovered on that same day that the internet cafe had put a virus on my USB key that was designed to capture passwords, I was a little concerned. Indeed I have not been back there since (when I told them that at least two of their machines had the virus, and they needed to install an up-to-date virus checker on their machines the guy said "Oh yeah? Uh-hmmm?") although I probably will, after a decent interval has elapsed. (Incidentally I usually add a shortcut or two to the browser on machines I use, so I can detect when they reload them: apparently not for months, which I suppose is some sort of tribute to WIndows XP/2000.)

To cut a long story short it eventually occurred to me to Google for the error message, and here's the answer: the.earth.li [http://the.earth.li/~sgtatham/putty/0.55/htmldoc/Chapter10.html#S10.6]

What had actually happened was that I had added a string to my Unix prompt, and bozoically added it to my .shrc instead of my .login, so that the string was generated not only on login but by every process which spawned a shell. Unknown to me, this was actually what my pscp/scp were trying to do at startup, but they interpreted the string as a call to reserve a huge amount of memory at the client end, causing the client to throw up its hands with a stupid misleading error msg. Aargh.

2006 Apr 26 [ Wed ]

Another reason for my laptop overheating problem?

A few days ago my laptop abruptly died during a video game. I figured it was overheating again. As I folded it up to put it away I noticed that, as usual, the steel tabletop under it was quite hot.

Then it occurred to me that the little rubber feet originally stuck to the bottom of the case had fallen off a year or two ago. Hmm. I tried sticking some domestic rubber feet on – after cutting them to size with scissors – and the laptop now sits *much* higher above the table surface. Now you might think that reduces heat transfer to the table – lose – but the intake port for the fan is *under* the laptop, so win, big. The outlet airflow feels distinctly faster.

Now I feel a little silly for not thinking of this before. At the time I first noticed the original feet were dropping off I vaguely figured the separation was defined by some other plastic protrusions in the case molding but now I look at it they are clearly inadequate.

2006 Mar 29 [ Wed ]

Surprising bug in MS Access

My girlfriend is doing a MS Access course, and asked me to help her to set up a form for an employees table. I looked at the printed version of the form, which showed a mugshot of the current employee, and looked at the instructions she had noted down from the teacher on how to set up that (image) object on the form.

"Hmm, don't really understand this." She said "oh, the teacher said don't worry about that bit."

Well, she wanted me to address her current problems – things like she didn't understand that the "retirement date" object on the form needed to be an unbound text object whose control source was the birth date field of the data table, rather than being a separate field in the table. And then I got sidetracked because the version of the date-calculating function that she had noted down from the class assumed that a year was exactly 365.25 days.

But I came back to that image field. There was definitely something funny about it. It turns out that this is a well-known issue: www.jamiessoftware.tk [http://www.jamiessoftware.tk/articles/handlingimages.html]

The above link is quite good but does not give you the full picture. In particular it does not apologize for the misinformation in Access's own help files. It also does not give you a full sense of how many webpages you can find relating to this which give misinformation or lack the full story.

Microsoft has several pages relating to this issue. This one is probably the most helpful: support.microsoft.com [http://support.microsoft.com/kb/q210100/#EJADAAA]

Here is my summation of what I discovered:

1. Access allows you to put image files into the database as an OLE object, but it seems to actually store them as uncompressed .BMPs. When I started experimenting with this I was using fullsize 2 MP .jpgs. I have since stopped using the OLE object technique, but my tiny database file – with only six records – is still over 20 MB. I suppose I need to compact it somehow, but I haven't checked that out yet. So as the first link suggests, it's really impractical. (Incidentally, it occurs to me that even a few *static* images could really bloat up a database.)

2. Even when I used the OLE object technique, I *still* wasn't able to display images on the form (except static images of course). I don't know why. Some references on the web say that you need to install Microsoft Picture Editor to make sure Access can find an OLE server for your graphics file, but my system already has umpteen apps registered to handle .jpgs. I tried changing the registered app to various things including IE, and it still wouldn't work.

3. Anyway, I wanted to specify the image file by storing the path and filename in a text field. It turns out that you need to put code in both the form itself and a module in the database. (As described in the Microsoft link above.) This was not hard to do by cutting-and-pasting, but the fieldnames and tablenames are hard-coded for the example, so you would need to duplicate it for every image field in your database. (I think – actually, I really don't understand what the code is doing.)

4. Even then, it only worked if your filespec included the *full* path to the file – eg something like "L:\data\msaccess\imgtest\pics\danny-small.jpg" This contradicted the help file, which specifically suggested that a path relative to the location of the database file would work. Eg, if the database file was "L:\data\msaccess\imgtest\test01.mdb" then your filespec could be "pics\danny-small.jpg". I thought this really sucked – it would make it very hard to move the database file around. Eventually, I realized that the following *does* work: "imgtest\pics\danny-small.jpg"

5. I almost forgot the first way I made it work. The MS documentation refers to "data access pages" and for a long time I assumed this was some sort of generic term for "forms". However, in microsoftspeak it refers to a specific kind of database object which you can create. It is like a form, but can be accessed by Internet Explorer. I don't know exactly how it's supposed to work if you have multiple clients; presumably the form actually tells IE to use the client's locally installed copy of Access to access (aargh) the Access file on a simple fileserver. Anyhow, it works for displaying images, with no need to add code.

6. I don't remember the details, but Access did not seem to automatically resize an image to fit the control size you set on the form. I think maybe the docs are wrong. I needed to resample all of my images to make sure they fit in the pixel area allocated on the form.

7. The "Northwind" sample database somehow manages to make images work without using the sample code. On the other hand, they are very low-resolution images. Additionally, they are only used to illustrate product *categories*, which if you think about it is pointless – the database does *not* have images of the products themselves, probably because then it would need to be about 500 MB.

All in all, I thought this behavior was incredibly buggy and kludgy. I would have thought that handling images was not rocket science.

Also, it makes me wonder whether *all* the documentation produced for software is produced based on its *specs* and never tested against actual behavior.

Also, I would like to point out that Microsoft appears to have a policy of taking over a common term whether it has a generic or specific meaning and redefining it. For instance, "Word". More interestingly "domain", which has quite different senses on the Internet and in connection with Microsoft server setup, even though (obviously) you are usually discussing situations where either sense might be intended.

Likewise, here, "data access pages". It makes me feel less irritated about Unix naming conventions. When you encounter a name like "snort" you don't know what it means, but you *do* know it's a name and you need to look it up.

Finally, I suspect that my girlfriend's teacher never got images to work on his form at all.

2006 Mar 02 [ Thu ]

Fun reinstalling Windows 2000

I ran out of space on my hard drive and figured I should reinstall Windows – maybe that would fix the problem with the scanner.

I ran into several problems though.

One big one appears to be overheating. (I say "appears to be" because the error pattern in the LEDs on this machine never matches the manual.) Presumably during the install the setup software doesn't handle the powersaving stuff, runs the CPU at max, and causes overheating within a few minutes. Still, I've managed to do it before... but now I come to think of it, that was probably in an airconditioned room.

Still, the *irritating* thing was being unable to copy the files from the setup folder on the CD, boot and then just run everything from hd. I was stymied in several ways:

1. I had put the setup files on the NTFS partition. But my "Ultimate Boot CD" needs to load a memory manager to load NTFS support... and it crashed on my laptop hardware.

2. I tried copying the files to a DOS partition. This would have worked, if Windows 2000 Setup could run under DOS. D'oh!

3. I tried running Bart PE, which could see and write to the NTFS partition just fine... but Windows 2000 Setup refused to run an install on a machine running a later version of Windows. D'oh! (If I remember rightly Bart PE has to be used with XP – you can't make it boot W2K.)

Oh well. Back to the Windows CD and SP4... overheats again... aargh...

2006 Feb 15 [ Wed ]

Firefox memory problem a "feature"?

According to this Slashdot thread: developers.slashdot.org [http://developers.slashdot.org/article.pl?sid=06/02/14/2154224] Firefox takes more and more memory because it keeps old pages in RAM (so that they can be reloaded faster) even after you close their tabs.

I had been getting problems using Damn Small Linux because the Firefox was using more and more memory – not good when you can't page to disk.

I'm running IE now (aargh) so I can't check it, but apparently you can fix this by putting about:config in the URL box, then scrolling to this value:

browser.sessionhistory.max_total_viewers 

and set the value to 0.

...hmmm. Later posts in the Slashdot thread deny it. Oh well, I'll try the above fix anyway and see what happens.

2006 Feb 14 [ Tue ]

The "Honeynet" project has various anti-hacking tools

Their main page is notr very exciting. Try this one instead: www.honeynet.org [http://www.honeynet.org/tools/index.html]

A "honeynet" is a system of servers which are set up to act as "honeypots" – ie, machines which will appear to be hackable so that hackers will find them and attempt to hack into them. But they have special software running which allows a great deal of information to be gathered *from the hacker*. For instance, as soon as a hacker gets into a machine, he will download his own tools package somehow. Often this process will allow the honeynet machine to capture the machine name, user name and password of the accounts that he is using, which provides both a lead to his identity and a way to gather more information (eg by logging into his IRC group).

honeynet.org provides a CD which can be used to easily set up a honeynet server. They also provide the tools individually, as in the link at the top.

It strikes me that the dividing line between hacker tools and anti-hacker tools is rather small. For instance, if a hacker manages to subvert DNS (or ARP), client workstations can be fooled into thinking the hacker's machine is their normal server. The hacker merely has to use the honeynet software on his machine: when they attempt to log in, their usernames and passwords can be captured.

One of the individual tools is "sebek": www.honeynet.org [http://www.honeynet.org/tools/sebek/]

It has two components. The first is a client that runs on the honeypots, its purpose is to capture all of the attackers activities (keystrokes, file uploads, passwords) then covertly send the data to the server. The second component is the server which collects the data from the honeypots.

If this software can be used to gather all this info from relatively wily hackers, it can certainly be used against normal users. Like your girlfriend. Hmmm.

2006 Jan 31 [ Tue ]

Microsoft is apparently still allowing sites to share your cookies

I have posted about this before: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Internet/cookieexploit02.html]

A description on c/net: news.com.com [http://news.com.com/2100-1023-245680.html]

A recent posting on Slashdot says that this is still happening: it.slashdot.org [http://it.slashdot.org/comments.pl?sid=175615&cid=14603033]

The existence of this security issue makes a mockery of Microsoft's public pledges to concentrate on security – as do many other similar issues.

2006 Jan 06 [ Fri ]

My laptop woes abate

My Atec laptop has had various problems, although it's never needed an expensive repair.

Recently it started shutting down very rapidly, amid apparently bogus "low-battery" warnings. In fear and trembling, I disassembled it. I was expecting to find the fan compartment was clogged with dust, but it was almost clean. However, there was almost no heatsink compound detectable and what was visible had hardened. I figured this matched up with the fact that the case of the machine got red-hot but the exhaust air was scarcely warm, so I went looking for heatsink compound.

I had assumed this would be available because there are a ton of places that assemble PCs, but I had no luck. Eventually a tech at PTC on Monivong took pity on me and gave me a little hypodermic for free (hmmm... I wonder if he filled it with toothpaste... nah...).

I opened up the laptop again and reassembled it with the heatsink compound. (I have at least a dozen screws left over – no joke.)

Then I didn't get the fast bogus low-battery shutdowns; now I was getting *instantaneous* shutdowns.

I figured that when I reassembled the case I had left something loose enough that a slight flexing could short out the power supply. I experimented with using an external mouse and keybd, and that worked very well.

After a while I tried running without the external devices, and the laptop *still* worked. Then it occurred to me that the period when I suffered the instananeous shutdowns was exceptionally cold and dry, with nighttime temperatures going as low as 70 F; and right around the start of the problem I had actually felt a static zap in an air-conditioned store. Hmmm! I guess what I had actually done was loosened one of the earthing straps which, when you open up a laptop, you can always see were added in desperation at the last moment before the ship date, and get in your way until the tacked-on solder joint snaps. Static zap: foom. I guess I was lucky it just reset the machine rather than burning something out.