JohnBs personal blog

Whilst I was deploying a web site yesterday for a client I ran into a problem. After deploying to the client server and them restoring the database to their SQL server, every login resulted in an error whilst trying to insert an audit record of the login.

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error was regarding converting a char to a datetime field. Now this code ran just fine on our servers, we checked everything, from regional settings, locales etc - to cut a long story short it turned out that the SQL user the client had created for us to use had it’s default language set to ‘British English’ instead of ‘English’ - switching it to English it all started working fine - probably not something the majority would run into but posting it here for prosperity

I thought it’s been a while since i posted a proper code blog entry, so here’s one. It’s based on how i handle updating a database when multiple values for checkboxes have been posted, the same applies for multiple selections too from a drop down box.

First off, let’s simplify things. Imagine if you will, three tables, users, groups and users_to_groups. - pretty standard type stuff with the users_to_groups table allowing a many to many relationship between the users and the groups.

So when we want to assign a user to a group we add a new row to the users_to_groups table with the userid and groupid for each group we’re adding the user to. This is based on say, the following form submission;

Various code I’ve seen before handles this by taking the list of groupid’s and looping over it and doing an insert each for each item - usually having purged existing assignments beforehand, for example;

Now having worked for a large company with a team of DBA ninja’s - where possible we left the DB do as much processing as possible and this is one such case where our logic was wrapped up into a stored procedure.

So what do we have, a userID and a list of groupIDs that we want assigned to the user. I’m not going to look at the stored procedure, but my SQL is pretty simple;

I’m using a nested select statement in my insert statement. The nested select statement is selecting the userID that we passed into it and a column named item (aliased to groupid) into a sql (2000/2005) function udf_List2Table() - this as you’d expect, takes a list of values and converts it into a table on the fly. The code for the sql function can be found here - NOTE: I didn’t write this function.

I much prefer this method to looping over a list and performing an insert each time - keeping it all in SQL just seems so much cleaner. This logic can then be wrapped up inside a CFC or in this case a stored procedure which can then also handle the delete processing too before the inserts. Obviously, as is often the case implementations of DB functions vary across different servers but I’m pretty sure almost all the current servers have some form of custom functions so this behaviour can be duplicated there.

Another argument is that using RDBMS specific SQL, eg functions, stored procedures, views etc makes your app none portable between between the different DB servers - this usually only comes into play when you plan on distributing an application - but if it’s more performant to have the RDBMS handle this type of stuff then are these applications sacrificing performance for their own ease of maintenance of the underlying CF code?

Introduced in SQL 2005, CTEs or Common Table Expressions are a pretty cool feature to use. Rather than using a temporary table, a CTE is able to create temporary tables on the fly and perform then performing recursive queries - perfect for building a tree heirarchy.

Take for example, the following code - a pretty normal pageid, parentid relationship exists between nodes (in this case pages within a CMS system).


WITH PageTree(pageid, pageparentid, level,pathstr)
AS
(
select
pageid, pageparentid, 0,cast(pageid as varchar(max)) from pages where pageparentid is null
UNION ALL
Select v.pageid, v.pageparentid, t.level +1 , t.pathstr + ',' + cast(v.pageid as varchar(max)) from pages v
INNER JOIN
PageTree t
ON
t.pageid = v.pageparentid
)

select pageid, pageparentid, level, pathstr
from pagetree
order by pathstr, pageid


Firstly, notice the WITH statement at the top that defines the CTE and the columns that are going to be in your resultant table. Then the initial ’seed’ row is retrieved before the recursion begins. Outside of the WITH statement is the SELECT statement that selects against the CTE. In this example, i’ve create a column named ‘pathstr’ which will contain a comma seperated list of the pages in their heirarchy. Here’s the results;