Coldfusion jdbcURL Query

I was looking for a way to have coldfusion accept a jdbcURL and query the database.  Well I found it: http://cfsilence.com/blog/client/index.cfm/2007/11/7/DSNLess-CFQuery did a great job explaining it, and even wrapped it in a customTag called "query".

Great Job.


Here is the guts of the query.cfm file

<cfif thisTag.ExecutionMode eq "start">
<!--- custom tag attributes --->
<cfparam name="attributes.jdbcURL" default="" />
<cfparam name="attributes.username" default="" />
<cfparam name="attributes.password" default="" />
<cfparam name="attributes.qName" default="" />

<cfelse>
<cfset variables._queryString = thisTag.GeneratedContent>
<cfset thisTag.GeneratedContent = "" />
<cfset _jSQL = createObject("java", "java.sql.DriverManager") />
<cfset _connectURL = attributes.jdbcURL />
<cfset _jSQL.setLoginTimeout(2) />
<cfset _conn = _jSQL.getConnection(_connectURL,"#attributes.username#","#attributes.password#") />
<cfset _stmt = _conn.prepareStatement(javacast("string", preserveSingleQuotes(variables._queryString))) />
<cfset _result = _stmt.ExecuteQuery() />
<cfset caller[attributes.qName] = createObject( "java", "coldfusion.sql.QueryTable").init(_result) />

</cfif>

MSSQL 2005 - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Today another error is appearing where the ORDER BY has a field in it where the SELECT DISTINCT list doesn't.  And SQL server is throwing an error.  How nice.

Here is a code example that will cause an error:

select distinct
     datepart(dd,visitDate), fld2
from tbl
order by visitDate

Fix  :

select distinct

     datepart(dd,visitDate), fld2

from tbl

order by datepart(dd,visitDate)

SQL Server 2005 The multi-part identifier "tblPrefix.columnName" could not be bound.

We just went into Production with MSSQL 2005, and even though I knew the change of interpreter where Union / Order by is processed a little different, i thought the error thrown would make sense.  For example, the below query in 2000 will run fine, but in 2005 will Error.

select
     a.aCol,
     b.bCol
from a inner join b on a.id = b.id

union

select
     a.acol,
     c.bcol
form a inner join c on a.id = c.id

order by b.bCol

The known interpreter change gives a semi-cryptic error of "The multi-part identifier "tblPrefix.columnName" could not be bound."  This basically means that in the order by b.bCol doesn't exists in both union queries.  Therefore, the order by needs to drop the "b." becoming "bCol".

Solution:

select

     a.aCol,

     b.bCol

from a inner join b on a.id = b.id



union



select

     a.acol,

     c.bcol

form a inner join c on a.id = c.id



order by b.bCol

CSS Styled Day View of a Calendar

I've helped make a css styled day-view calendar (or in our case the schedule of an event).  Here is what we developed.  But my question is has anyone out there created a similar thing that is better "accessible".  This layout looks great on screen when CSS is enabled, but have a screen reader read it and it doesn't go as well.  It still works, so I'm happy with that, but I would like it to read better instead of reading all the timeslots (7am 15,30,45, etc), then read all the actual sessions of the event.  It would be nice if there was a way it would read the time slots and if a session exists at that time slot it would read that session then.