Noisy Maps is dead...long live Noisy Maps!

Some time ago I started a project called Noisy Maps. The idea being an easy way to find nearby utilities like cash points, mail boxes and phone boxes. This project quickly became a labour of love and something that at times was really challenging. I've learnt a lot about linux, apache tomcat, railo and coldfusion. Not to mention some awesome sql procs for finding nearby things.

However the time has come when the project has out-grown me....plus I can't afford to keep hosting it ;)

So I've decided to open source it and launch it on github.

If you feel you can contribute or indeed host it, go for it. Good luck to you:

https://github.com/jimbo1299/noisymaps

Odd bug in Query of Queries

Not sure if this is a known bug or if it will really help anyone but it stumped me for a bit. As you can see ordering of uppercase values is handled completely differently than lowercase values! Frustrating.

cfscript
 hello = queryNew('lastname,firstname');
 queryAddRow(hello);
 querySetcell(hello,'lastname','YANKEE');
 querySetcell(hello,'firstname','DOODLE');
 queryAddRow(hello);
 querySetcell(hello,'lastname','ableson');
 querySetcell(hello,'firstname','abe');
/cfscript
cfdump var="#hello#"



cfquery name="qhello" dbtype="query"
 select lastname, firstname from hello 
 order by lastname,firstname
/cfquery
cfdump var="#qhello#"

Obviously the solution is "select lower(lastname), lower(firstname) from"....

I'd love to see CFML improve query of queries, it is a useful tool.

SQL "list" Functionality

Hey

I recently had to take a FullName field in sql and split it into first and last names. To complicate matters further the fullname had an initial in it and the fields were backwards:

Adams, Douglas B
Fox, Michael J
Spears, Britney M
Rose, Axel

Obviously in cfml this would be easy-peasy using list functions. I'm sure aspx, jsp and php make it fairly trivial too.

SQL However, wasn't built for this, but alas, that was the way it had to be. If it helps anyone the lastname was fairly simple:

left(column_1,charindex(',',column_1)-1) as lastName

Of course firstname become infinitely more complicated as you have to get everything after the comma and THEN strip everything after the space. PLUS not every user has a middle name (eg Axel Rose). Sounds easy..it's not.
Now subqueries make this a little more straightforward but in this case i didn't have that luxury.
If you need to do that try something like:

select fullString, lastname, calculateFirstName(otherName) as firstname from
(
    select fullstring, lastname, calculateWhatsLeft(lastname) as otherName from
    (
        select fullString, calculateLastName() as lastname from tablename
    )
)

(excuse the functions in there, it's just shorthand / pseudocode, you actually need to do the left(...charindex(...)) type stuff i just don't wanna retype.

Anyway as i said i couldn't use subqueries so onward with my plight. I tried coalesce but it needs nulls...urg! I ended up with some hideous case statement.
....back to the drawing board.

i stumbled accross this excellent idea by someone i've never met Daryl Banttari:
http://cfprimer.blogspot.com/2009/01/listfirst-listrest-in-sql-server.html

User functions, not normally a big fan of using custom functions in sql, but this was desperation. I modified the functions to take in a delimiter parameter:

CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
 IF(@delim is null) SET @delim = ','
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(@delim, @list)
   IF @pos > 0
       SET @ret = left(@list, @pos-1)
   ELSE
       set @ret = @list
   RETURN @ret
END
GO

CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
   IF(@delim is null) SET @delim = ','
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(@delim, @list)
   IF @pos > 0
       SET @ret = substring(@list, @pos+1, len(@list)-@pos)
   ELSE
       SET @ret = ''
   RETURN @ret
END

and hey presto:

select column_1, 
dbo.listFirst(column_1) as lastname,
dbo.[listFirst2](ltrim(dbo.[listRest](column_1)),' ') as firstname
from tableName

Using If exists drop table in Apache Derby and CF8

All credit to Giancarlo Gomez for this blog post, awesome:

http://fusecf.blogspot.com/2007/08/coldfusion-8-and-derby-db-create-and.html

Using apache derby as a testing database is really useful and this article describes a great method of checking if a table exists and dropping it or re-creating it.

I find it very useful when testing and developing a new site to create a cfm page that just drops all tables and re-creates them with new data in. This saves a lot of time fiddling when you suddenly decide you need a new column or something.

Downloading files from the database with CF

If you use your database as a file store and coldfusion as your webserver, you'll doubtless one day want to download those files from a cfm page.

here are two useful snippets that had me stuck for a while:

To download the image:

< cfheader name="Content-Disposition" value="attachment; filename=" >
< cfcontent type="#fileType#/#fileSubtype#" variable="#fileData#" >


ALWAYS CHECK:
this is important, you need to enable blob downloads in cfadmin.

1. Open up your datasource in cfadmin,


2. click advanced,


3. check the checkbox marked: "Enable binary large object retrieval (BLOB)."

coldFusion and SQL Express

Hey Boys and girls. I recently tried to install ColdFusion 8 and SQL express 2005.

Quite easy but a couple of points to remember for next time:

1) TCP/IP needs to be enabled in sql connection manager (weird that its disabled)
2) TCP/IP needs to be given a default port (cf uses 1433)

After you've done this restart sql (or the whole computer) and you should be ready to go.

Thanks

hash function in sql

Just stumbled across this cool sql 2005 feature:

SELECT HashBytes('MD5','password')

Why did no one tell me about this before? This could have saved me HOURS of short pointless cf script writing.

SQL Update

Try as i might, i NEVER remember how to do this:

UPDATE table1
SET snakesOnAPlane = t2.data
FROM table1 t1
JOIN table2 t2 ON t2.row1 = t1.row1
WHERE t1.row3 = 'helloWorld'

SQL Transaction

bit like in cfmx here's a useful bit of t-sql / sql for rolling back on error.

BEGIN TRAN
insert into ()--....sql goes here

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN

It's not perfect but it seems to work...sometimes....if the wind is blowing north, and it's a wednesday.