Month: December 2009

Categories
SQL / Database

Using MySQL variables to limit a select query

I discovered today when writing a stored procedure for a web reporting application that MySQL doesn’t support using variables for the LIMIT clause in a SELECT statement. I didn’t really want to write a dynamic query, one workaround that I found while Googling looked promising:

SET SQL_SELECT_LIMIT = RowLimit (can be a variable)
SELECT ….
SET SQL_SELECT_LIMIT = default

However it is applied at a global level and I needed different limits within a sequence of four queries formed into a union to get the top N results for a series of categories. Because I was giving the user the limited choice of retrieving the top 10, 20, 50 or 100 items I ending up using a CASE statement with each WHEN clause containing a duplicate of the entire query. Certainly not a pretty solution but works and is reasonably maintainable by leaving the LIMIT clause on a seperate line so any changes can be cut & pased down the procedure.

Categories
GPS / GIS Programming

Manifold GIS IMS (Internet Map Server) error 0x80004005

I’m working on an iPhone 3GS mapping application using Manifold as a map tile server via the Web Map Service (WMS) protocol. I did initial development using a Vista x64 development machine on my intranet via wifi but had considerable difficulty moving to my Windows Server 2008 64-bit machine so that I could access the map tiles remotely via the 3G network. It’s been a while since I’d deployed a Manifold IMS solution but I remembered the usual steps:

  • Web site must be running under 32-bit mode because the COM object is 32-bit only
  • Map files, config.txt and any linked resources must be available to the ‘NETWORK SERVICE’ account
  • Same account needs access to Windows\serviceprofiles\networkservice\AppData\Local\Temp

    After my initial attempts failed I did numerous searches of the Manifold site and other resources and worked through the Manifold “Problems with the Internet Map Server” guide but kept getting the 80004005 error that I thought pointed at a permissions error. I thought to install the excellent SysInternals Process Monitor utility and the first error that became apparent was a file permissions error writing to the file “c:\Manifold.log”. I created a blank file with that name and granted full control permissions to the NETWORK SERVICE account used by IIS 7 and found several errors including the following:

    Invalid component name
    Incompatible component type
    Can’t generate unique name for temporary file

    Creating a new blank map file solved the problem so it appeared to be a problem within the Manifold map file itself, I decided to copy back a fresh copy of the map from my development machine so I could try removing a few elements until it worked but found it worked first go with the fresh copy. I didn’t modify anything with the map file so can only conclude it got corrupted at some initial stage while I had configuration problems. Anyway to avoid similar problems in the future I’ll make sure that during Manifold IMS deployment I always include a blank “c:\Manifold.log” file with appropriate permissions and be careful to archive any changes no matter how small they seem in source code control as a recovery point.

    I love Manifold GIS in general and it represents great value for money, but it would be nice if the object model was structured so that it would throw back a response code in the form of a text message on the call to MapServer.Create so it could be passed back to the client browser. Additionally rather than the non-standard practice of writing a text file to the root directory it would also be nice to see it write a meaningful errors to the Windows application event log.