Blog

Blog Archives - 7 Record(s)

Remove Filter Year: '2010' - Month: '5'

New Ortus Solutions Website

Posted by Luis Majano
May 21, 2010 10:13:20 UTC

I just wanted to point out that we have released a new corporate website: http://ortussolutions.com which is now filled with lots of goodies, news and some upcoming products.

Need an eclipse ftp synchronizer, help!

Posted by Luis Majano
May 21, 2010 10:11:22 UTC

I just wanted to open a topic of discussion to see what are your recommendations for an FTP synchronization plugin for Eclipse.  I know Aptana has one and CFBuilder also, which is pretty sweet.  However, what if you don’t want any of those two IDEs for some reason.  What would you use?  Comments? Suggestions?  By the way, I use CFBuilder and it is fantastic.

SQL splitting a varchar list argument to row of data

Posted by Luis Majano
May 14, 2010 10:35:48 UTC

I got into yet another issue with my SQL optimizations this week at work and one of them was that I needed to pass a list of IDs to a function that would be then used in a SQL statement via an “in” clause.  The problem is that the list argument is a varchar:

   1: @docTypeIds varchar(500) = '0',

 

Then I needed to use it in my SQL statement like this:

   1: SELECT *

   2: FROM table

   3: WHERE (

   4:   (@docTypeIds = '0' OR @docTypeIds = '')

   5:   OR

   6:   docType in (@docTypeIds)  

   7: )

The problem with this approach is that the datatype for the docType column was tinyint and then the argument is a varchar list, so it would fail as it cannot cast it. Bummer!  So what to do?  Well, after some searching and searching, the best idea so far (brought up by our local dev guru Sanjib Panda), was to break the list into a simple table with one column of type tinyint, but how?  Here is the simple logic which could easily be created as a UDF:

   1: declare @testString as varchar(20)

   2: set @testString = '1,2,3,4,5,6,7,8,9'

   3:  

   4: declare @tempTable table

   5: (

   6: docType tinyint

   7: )

   8:  

   9: while charindex(',',@testString) > 0

  10: begin

  11:   INSERT INTO @tempTable select substring(@testString,1,1)

  12:   SET @testString = substring(@testString,3,len(@testString))

  13: end

  14:  

  15: select * from @tempTable

So if you run the above, that will give you our temp table with one docType column with all the values we needed.  Man, sometimes I wish SQL could have some ColdFusion goodness and create a cool function like: listToTable() or something.  Anyways, after splitting the list into a table then I can change my SQL to something like this:

   1: AND(

   2:   (@docTypeIds = '0' OR @docTypeIds = '')

   3:   OR

   4:   d.docType in ( select docType from @tempTable )  

   5: )

There you go! The solution is working really fast and given us a nice little split to rows function that can be used on any variable.  Hope this helps.

 

UPDATE

After much trying out and pains, what I had did not work fully :(  So here is an updated function:

   1: -- Temp tables for lists

   2: DECLARE @docTypes TABLE( docType tinyint )

   3: DECLARE @docTypeIds varchar(300)

   4: DECLARE @delimIndex int

   5:  

   6: set @docTypeIds = '11,1,3,54'

   7:     

   8: -- If docTypes sent in, then convert to table rows

   9: IF LEN(@docTypeIds) > 0 AND @docTypeIds <> '0'

  10: BEGIN

  11:     -- Get Delimiter index

  12:     SET @delimIndex = CHARINDEX(',',@docTypeIds)

  13:     -- We have at least one delimiter, start on it

  14:     WHILE @delimIndex > 0

  15:     BEGIN

  16:         INSERT INTO @docTypes SELECT SUBSTRING(@docTypeIds,1,@delimIndex-1)

  17:         SET @docTypeIds = SUBSTRING(@docTypeIds,@delimIndex+1,LEN(@docTypeIds))

  18:         SET @delimIndex = CHARINDEX(',',@docTypeIds)

  19:     END

  20:     -- Insert the last entry or the first single no delim entry

  21:     INSERT INTO @docTypes SELECT @docTypeIds

  22: END

  23:  

  24: select * from @docTypes

SQL Conditional Where

Posted by Luis Majano
May 13, 2010 17:01:00 UTC

Today I needed to create a conditional where statement in MSSQL and I had no idea how?  It was an incoming variable to a stored procedure and I needed to ignore it in my SQL if the value was 0, if it was not 0 then I needed to do a where statement as a list.  The solution was so simple and elegant I was like WOW!! I like that.

My first thought was to do a if-else split on the SQL with the incoming variable like this:

   1: if @i = 1

   2:     select * from mytable where mycol = 1

   3: else

   4:     select * from mytable where mycol = 2

 

However, this does not hold true to DRY principles, so the solution I ended up using was the following with a simple OR statement!

   1: AND(

   2:   @docType = 0 

   3: OR

   4:   d.docType in (@docType)  

   5: )

A simple OR statement made my life much easier, so there you go, simple conditional statements when you at least expect certain values on incoming variables.

Regex Negative Lookahead, so powerful!

Posted by Luis Majano
May 11, 2010 12:21:39 UTC

I am a big fan of regular expressions.  They are so mysterious and elegant, I love them and hate them, cherish them and despise them.  In other words they are both beautiful and nasty, but they can really get the job done.  I was intrigued today when a colleague of mine was asking how to match a string but if it started with a specific sequence then do not match.  In my regex coolness I said “Sure, that’s easy man!”, whipped out QuickREx in Eclipse, wrote down a quick jolt of brilliance and BAMM!! I hit the brick wall at 90! Nothing I was trying would work, characters where matched not the word:

   1: ^([^search].*)dev\.domain

Basically match any incoming domain that ends with dev.domain but it should not be preceded by the word search.  I really thought I nailed it with that regex, but NOOO, it matches character classes not full words.  So I had to revert my humble ego and go back to the books and voila: Positive and Negative LookAhead!

Granted, some regex engines do not support look behinds, but thankfully java does.  Here is a cool definition:

(?!regex)


Zero-width negative lookahead. Identical to positive lookahead, except that the overall match will only succeed if the regex inside the lookahead fails to match.

And finally and AHA!! moment.  I can use the lookahead:

   1: ^(?!search|training).*dev\.domain

AND BUYAAA!!!  I think this can help somebody out there. It helped me!

Live Writer is Cool!

Posted by Luis Majano
May 05, 2010 22:23:00 UTC

Since I got my new netbook, I decided to dual boot windows 7 and ubuntu 10.4.  I have been a long time Mac user, but I can finally say that windows 7 is so much better than its predecessors.  It doesn’t mean to say that it has its inevitable quirks and weird's things an operating system of these days should not have anymore, like when I want to be an admin, I WANT TO BE AN ADMIN!!! LET ME DO WHAT I WANT!!!

Anyways, I found that permissions in windows 7 are absolute nonsense and just plain stupid.  Apart from that, I have been trying out the suite of live apps, like, live writer, live photo gallery and live mail.  I can totally say I really like them, they are simple, intuitive and easy to setup.  So maybe the OS is still crappy, but the apps are really good.

Anyways, that is my rant of the night, as I type this up in live writer and really liking it.

Training headed to the East Coast

Posted by Luis Majano
May 03, 2010 23:27:56 UTC
Finally after several months of getting this training prepared, we are heading to the DC area and holding our first 2 day ColdBox intensive training in July, 2 days before CFUnited.  So head on over to the ColdBox blog and find out about it. Hope to see you there!

Site Updates

Archives

Entries Search