Tuesday, April 29, 2008

having problems with your pear install on a drive other than c (d?) on iis? you must run the pear install .bat from the cmd prompt referencing the pear install .bat on that drive. otherwise, pear is configured to run on the c drive, and therefore doesn't work. this tip from the cml dba, karl dailey.

Wednesday, April 23, 2008

a common task I come across is the need to create a table of "distinct" records based upon "uniqueness" of values in a given field.
SELECT * INTO new_distinct_table FROM [old_non_distinct_table] WHERE [some_unique_field] In (SELECT Max([some_unique_field
]) FROM [old_non_distinct_table
] GROUP BY [non_unique_field_of_interest]);
I had earlier shown how to do an update query based on a field from another table in MS Access. Here is the syntax for postgres:

UPDATE to_table SET to_column = from_column from from_table where to_table.primary_key= from_table.foreign_key;

handy notes for this query:
  • append in postgres is || (double pipe)
  • cast in postgres is like column_to_cast::type_to_cast

Sunday, April 20, 2008

using xxamp for a lamp (linux,mysql, php) environment to do development on php? perhaps you are already running an iis server (or even the others through iis) ... in that case the default ports for webhost and mysql db will already be in use. to get apache to listen on other ports you will have to change the "listen" and "server" parameters in respective httpd.conf. For mysql .cnf the parameters are "port" at 20 and 27

you should do this at least for:
  1. http: in C:\xampp\apache\conf\httpd.conf
  2. ssl: in C:\xampp\apache\conf\extra\httpd-ssl.conf
  3. mysql: C:\xampp\mysql\bin\my.cnf
you may also want to change port values for mysql in:
  • C:\xampp\mysql\bin\mysqld-nt.exe
  • C:\xampp\apache\bin\php.ini (line 795)
add the following lines to administer your server with phpmyadmin (at line 20 in C:\xampp\phpMyAdmin\config.inc.php), with whatever your new port number is in the section noted below
  • $cfg['Servers'][$i]['host'] = 'localhost';
  • $cfg['Servers'][$i]['port'] = 'whatever your new port number is';

Wednesday, April 16, 2008

alright, there is actually a way to do this built into excel! good, it is as it should be! format > column > autofit selection

obsolete idea:
ever wondered how to adjust all columns to the smallest width necessary to fit all data, all at once? i just figured this one out, but wish i knew before:

1. hit select all twice (i.e. ctrl + a, ctrl + a)
2. right click on the header area of your table, select 'column width'
3. set your column width to 1

voila ... your columns are all now the proper width.
I often get tripped up doing an update of one table from fields in other table in access sql. i'm not sure if its because its different in other sql's, but in access it is required to do an inner join to the table which is being updated within the foreign update query.

Here is the general syntax in ms access sql to do one of these babies:

UPDATE [toTable] INNER JOIN [fromTable] ON [toTable].[primaryKey] = [fromTable].[foreignKey] SET [toTable].[toFieldUpdate]] = [fromTable].[fromFieldUpdate];
Gone are my days of crawling ftp uploads. Now I just upload zip archives and expand with this nifty little php file, which works on linux machines, and even provides a gui listing all .zip files in the folder it is located within.

Read more here.

Monday, April 14, 2008

openlayers wms manager error on iis

The WMS Manager library for openlayers is a great tool for allowing users to dynamically select layers from one or many wms servers. While demonstrating setup of this library on our localhost for the advanced gis class I teach, I came across a confounding error "Unhandled Request return Object Not Found."

The example page I am attempting to view is at "lib/openlayers/examples/wms_manager.html"

First, an aside of sorts: This error was actually one of many due to some issues with paths (in the WMS manager example versus our local version). To avoid this error get an openlayers install with WMS manager and required libraries already integrated. You can find this on SVN at: http://svn.openlayers.org/sandbox/ominiverdi/openlayers (you can use a SVN program like tortoise for windows.

This error occurs because you do not have a proxy host defined. A proxyhost is necessary when making sending a request to a remote host from javascript, since javascript is not normally allowed to do that.

Openlayers comes with a proxyhost cgi called proxy.cgi which is located in the examples directory. If your server is not setup to run python cgi's you must do so now. If you are running windows 2000 or have experience with iis (and/or have already installed python on your machine) I recommend this tutorial, if you are installing on windows server 2003 check out this one. If those don't get you going here is the one from microsoft with each and every step. The funny thing about the microsoft tutorial is that they actually give a non-working example to test your iis-python-cgi install, which causes your browser to attempt to save the test file every time you try to access it. Use the example here ("Problem 3") to correct that issue

Once you have IIS set up to deal with python cgi's, drag the proxy.cgi file into your new cgi-bin folder. Finally change OpenLayers.ProxyHost = "http://localhost/cgi-bin/proxy.cgi?url=" (if your cgi is at the root of your localhost like mine). For WMS manager you must change this variable in "/lib/OpenLayers/Ajax.js " and in "lib/openlayers/examples/wms_manager.html"

But, lo, you may find yourself stuck with an "Unhandled request return Bad Gateway" error when you try to pull up a wms host -- NO NEED TO WORRY -- this error occurs when you have not yet added the host to your proxy.cgi file. Just open up that baby and stick your desired host name in there and you should be good to go (just try another wms host to see it work if you don't believe me).

URI limit in sajax

another embarrassingly simple fix which took me a little while to figure out:

While passing a hierarchical list to sajax to write to a file, I failed to get a return response. I thought this might have something to do with the heavily nested hiearchy which I was having problems with earlier in the day. As is often the case the reason was much simpler: by default sajax can only take a limited (and relatively small) number of characters in variables, since these are all being passed through the URI.

The solution here is to use post instead of get to send ajax requests. Here is a snippet to change that setting in your sajax head (probably at the top of your page):

$sajax_request_type = "POST";   // or "GET"

Tuesday, April 1, 2008

MS Access Error on running "join/select into" query

Was getting the following error when running a "join/select into" query in MS Access

"Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt."

I followed a bunch of suggestions I had found on the web ... especially this post which had suggested that I should reinstall Jet (which led me to this post which suggested I ultimately needed to reinstall Windows XP SP2 ... which in a slipstream install means that the entire OS needs to be reinstalled!)

Ultimately, I was able to solve this problem by simply saving the database in MS Access 2003 format (.mdb) from the 2007 format I had stored it in (.accdb). I was able to run the query from the mdb and then resaved back into Access 2007 format, where I am again able to re-rerun the query.