Converting a List to table

This function accepts a String which holds list of id's and returns a table and then you can use this table to join and filter main table data. You can pass the list with your choice of separator.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


Create function [dbo].[ListToTable]
( @list varchar(4000), @separator varchar(10) )

/* this function receives a @separator delimited list and outputs a table with
each item in the list as a row in a table so the following can be used
SELECT * from dbo.Customers
inner join listToTable( '123,456,789', ',' )
on CustomerID = listValue
*/

returns @listTable table( listValue Int null )
as
begin

if @list is null
insert into @listTable values( null )

-- @list is the list we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @list_value varchar(1000) -- this holds each list value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each list value
set @list = @list + @separator

-- Loop through the string searching for separator characters
while patindex('%' + @separator + '%' , @list) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @list)
select @list_value = left(@list, @separator_position - 1)

insert into @listTable values( @list_value )

-- This replaces what we just processed with and empty string
select @list = stuff(@list, 1, @separator_position, '')
end

return

end

Finding file extension in SQL

Here is the SQL code to find the extension of a file:

declare @appurl varchar(50)
set @appurl= reverse('philip.singh.bedi.doc')

select reverse(LEFT(@appurl, CHARINDEX('.', @appurl) - 1)) ext,
reverse(RIGHT(@appurl, LEN(@appurl) - CHARINDEX('.', @appurl))) fname

Visit to Portishead

I went to Portishead, a small town near Bristol to see Jatin and work on one web service, Jatin is a terrific host and amazing friend, He has superb flat, and it was fun staying with him and working together like old times at Tag. I met with Ian Bale, he is a nice chap and talented professional, I think will learn a lot by working with him.
I stayed in Portishead for 4 days, Jatin has nice set up and working environment, his iMAC inspired me to have one and now I have ordered one and can't wait to try this one, I expect it to be with me in 1-2 weeks.

Lets see how it goes, I need to learn more about MAC, and this has latest powerful operating system - Snow.

Really looking forward to working on this.

:)

Snippets in CFEclipse

Found this on Ray's site where Bob wrote it in his comments to one article, thanks Bob (article)

Snippets in CFEclipse

Set directory in Eclipse: Window > Preferences > CFEclipse > alter File Paths To Snippets Directory

To create a snippet in Eclipse, open Snip Tree View (Window > Show View > Snip Tree View). Click the + icon to add a new snippet. 'Insert' inserts the current snippet into your CF document.

Each snippet comprises one XML file, for example here's a basic CFQUERY for a SELECT:

<code>
<?xml version="1.0" encoding="utf-8"?>
<snippet>
<name>select</name>
<help>select</help>
<starttext><!--[CDATA[
SELECT
FROM
WHERE
</cfquery>]]--></starttext>
<endtext><!--[CDATA[]]--></endtext>
</snippet>
</code>

If you're working in a team environment, you could point everyone's snippet file path to the same network directory in order to share snippets.

PS: remove <code> </code> before pasting in Eclipse

Model-Glue - one of ColdFusion frameworks

I am out of job, I came to know this on 22nd of July 09, I was expecting this would happen from few weeks as Markus (CEO of Emojo) was kept mentioning it in our project meetings that co is facing hard challenges in this recession time for getting money from clients which was over due. Even on one day we worked over night to finish one promise which co made to one client and we worked approx. 28 hrs. staying over night. But that didn't help to change the fate of the co.

I am using this time to learn few new things and brush-up some things which I wasn't using from a while, one of the new things I wanted to learn from a while was MODEL-GLUE framework and now it is coupled with ColdSpring which was another thing I wanted to learn. During this journey I have come across few good articles/ tutorials, which I would like to mention here and share with others.

1) Tutorial from Dan Wilson, which is very helpful if you want to learn Model-Glue, I would like to thank Dan about this tutorial. Here is the link to his series:

Dan's Model-Glue Tutorial

2) A series from Raymond Camden about learning Model-Glue, Ray is very talented and knowledgeable person and on top of that he is very kind and helpful and always willing to help other developers by sharing his knowledge and experience. This series is quite old (written in early 2006) but still it helped me to get on with Model-Glue. I will try to write the changes which I had to make in line with latest version of Model-Glue.

Here are the links to his series, I find it very difficult to get a list of his series and decided to put them here.

Building your first Model-Glue application - part 1

Building your first Model-Glue application - Part 2

Building your first Model-Glue application - Part 3

Building your first Model-Glue application - Part 4

Building your first Model-Glue application - Part 5

Building your first Model-Glue application - Part 6

Building your first Model-Glue application - Part 7

Building your first Model-Glue application - Part 8

Building your first Model-Glue application - Part 9

Building your first Model-Glue application - Part 10

Final notes from Ray

I wrote this one before finishing the complete series, I am a huge fan of Ray and big thanks to him for helping the comunity.

SEO Tips and links

I was reading through some good articles about SEO (Search Engine optimization) tips, and come across really good articles and article about how to write Robots.txt file to allow or disallow content or directories of your web site, you want or dont want to be searched/ indexed.

1) Top 10 ways to link popularity - its really a good article on what one should do to improve popularity of his site.

http://www.free-seo-news.com/ways-to-link-popularity.htm

2) Improving your search-engine status - Its a tutorial in 4 parts on how to improve your sites rating score.

http://www.softsteel.co.uk/tutorials/search/searchIndex.html

Part 1: http://www.softsteel.co.uk/tutorials/search/part1.html etc

3) How to improve your Page Ranking: This is an article which helps to improve page ranking and tells seven things to take care of:

http://www.squidoo.com/OptimizationforGoogleRating

4) Tutorial to Improve and Achieve Higher Search Engine Placement and Positions: Its a good tutorial to learn about search engine rankings

http://www.tsworldofdesign.com/search_engine/higher_placement.htm

also one interesting one: http://www.kensavage.com/archives/the-basic-principles-of-seo/

5) Here are few links to help you learning how to write robots.txt

http://www.free-seo-news.com/all-about-robots-txt.htm

http://sandeepdharak.blogspot.com/2009/09/how-write-robotstxt-file-seo-google.html

enjoy above articles

SQL Express DSN creation problem

I had problem creating DSN through ColdFusion administrator for a database in MSSQL 2005 express addition and problem was my tcp port was not enabled for this service and the post/ blog which helped me was this one:

http://www.fusioncube.net/index.php/coldfusion-sql-server-express

Thanks to Steve Brownlee who posted this blog in 2006