Syntax Highlighter now available for Blogger

I had been putting off using Syntax Highlighter for quite some time as it required me to host the files somewhere and then use them. Also, using it for a Blogger Blog did not seem to be so straight-forward and did not produce clean results. This was the status when I had last checked it out quite some time back.

So when reviewed a post I wrote with a lot of code in it, I was saddened that I had not add Syntax Highlighter to my blog. So I decided to take a second look and was pleasantly surprised to see both my issues resolved! Now there was an option for using the hosted version of the files and also there was a very neat blogger mode!

So now finally, you don't need to look around for someone to host the Syntax Highlighter files for you - you can use the hosted version of the Syntax Highlighter files. And there is a very good bloggerMode! which helps to integrates very cleanly with Blogger.

Implementing reCaptcha in Classic ASP

A friend of mine was very impressed with reCaptcha and wanted me to help him implement on his website that uses classic ASP! Going through the posts mentioned in the reference below gave me a bit of an idea but the solution was not very clear. Here's how I finally implemented it...

There are 3 parts of the code as follows:

1. JavaScript to create reCaptcha on the page that has the input form
2. Insert the created reCaptcha in the desired location
3. Server side component to validate the reCaptcha

1. JavaScript to create reCaptcha on the page that has the input form
Here is the code that needs to be placed in the ASP file to generate the JavaScript which when called, generates the code for reCaptcha. The generated string needs to be placed in your form where you want the reCaptcha to be displayed.

<%
recaptcha_public_key = "XXXXXXXXXXXXXXXX"
function recaptcha_challenge_writer(publickey) 
  recaptcha_challenge_writer = "<script type=""text/javascript"">" & _ 
 "var RecaptchaOptions = {" & _ 
 " theme : 'red'," & _ 
 " tabindex : 0" & _ 
 "};" & _ 
 "</script>" & _ 
 "<script type=""text/javascript"" src=""http://api.recaptcha.net/challenge?k=" & publickey & """></script>" & _ 
 "<noscript>" & _ 
 "<iframe src=""http://api.recaptcha.net/noscript?k=" & publickey & """ frameborder=""1""></iframe>
 " & _ 
 "<textarea name=""recaptcha_challenge_field"" id=""recaptcha_challenge_field"" rows=""3"" cols=""40""></textarea>" & _ 
 "<input type=""hidden"" name=""recaptcha_response_field"" id=""recaptcha_response_field"" value=""manual_challenge"">" & _ 
 "</noscript>" 
end function 
%>

2. Insert the created reCaptcha in the desired location
Here is how you need to place the code in your form where you want the reCaptcha to be displayed.

<%=recaptcha_challenge_writer(recaptcha_public_key)%>

3. Server side component to validate the reCaptcha
Here code that needs to be put in the asp file that processes the submitted form.

'Define some variables to be used on page
dim pubkey, privkey, challenge, form_response, test_captcha, recaptcha_confirm

'Customize your public and private keys and other variables
pubkey = "XXXXXXXXXXXXXXXX"
privkey = "XXXXXXXXXXXXXXXX"

' Get the user input
challenge = Request.Form("recaptcha_challenge_field")
form_response = Request.Form("recaptcha_response_field")

' Generate the POST string for reCaptcha
Dim VarString
VarString = _
"privatekey=" & privkey & _
"&remoteip=" & Request.ServerVariables("REMOTE_ADDR") & _
"&challenge=" & challenge & _
"&response=" & form_response

' Make an AJAX call to validate input reCaptcha
Dim objXmlHttp
Set objXmlHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
if isNull(objXmlHttp) then
Set objXmlHttp = Server.CreateObject("Microsoft.XMLHTTP")
end if
objXmlHttp.open "POST", "http://api-verify.recaptcha.net/verify", False
objXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objXmlHttp.send VarString

' Receive response from reCaptcha server
Dim ResponseString
ResponseString = split(objXmlHttp.responseText, vblf)
Set objXmlHttp = Nothing

if ResponseString(0) = "true" then
'They answered captcha correctly
''' Handle Success scenario
else
'They answered captcha incorrectly
''' Handle Failure scenario
end if

Note:

1. Of course, you need to sign-up for a free reCaptcha account at reCaptcha.net.
2. The code in step 3 above needs to be put in the top of the file which processes your submitted form - the asp file that is mentioned in the action of this particular form. The above code needs to be executed before anything else in that file.
3. It's regular processing steps should be put in the if block (line 37) that has the comment that the user answered the captcha correctly.
4. Error message display logic should go into the else block (line 40) that has the comment that the user answered the captcha incorrectly.
5. Remember to substitute the public & private keys with the ones that you get when you sign up for a free reCaptcha account.
6. This is a very basic, no-frills version. You can modularize, clean-up, optimize and add more error-handling. But this should be good enough to get you started!

References:

1. This Google Groups post
2. reCaptcha resources page
3. This post on Jeff Niblack's Blog
4. This post on World of Code Blog

Are campaigns like "Big Picture" really effective?

I sure got my travel mug today and got free coffee from Starbucks. Starbucks is offering free coffee today (April 15th) to encourage people to reduce usage of paper cups - read my post about that here. This opens up the debate that whether these kind of programs really work? Here's my take ...

First hand experience - lugging around the travel mug is a pain which I am not ready to do everyday. The coffee was good, but cleaning the travel mug that has a narrow bottom was real pain as well. And God save you in case you got caught up in work / meeting and the leftover coffee in the travel mug dries up - cleaning it will require way more effort than you can imagine. These are the non-material negatives to using the travel mug.

Now coming to the material aspect - buying a travel mug costs some dough and if you go in for a "cool" one, get ready to be set back by at least 10 bucks. But that's only one time for however long you can manage to hold onto the mug. However, washing the travel mug with (running) hot water and soap after every use will require investing in soap & scrubber, add to the bill for heating water and use up your time {Remember - Time = Money!}. Also, producing the energy to heat the water may not be exactly soft on the planet either - but we may not know or notice that exactly.

Now for the psychological aspect of this issue. Most people will take this campaign as an opportunity to get *FREE* coffee. Many people may even drive extra miles just for this and in the process end up spending the cost of the coffee on gas instead! And from the very next day - most people will revert to their normal routine and use (and throw away) paper cups, tissues, plastic cutlery left and right as usual. Very very very few people would actually have a change of heart and
change their habits in a single attempt of such a one day campaign.

Even if I had not got the chance to get the free coffee, I would have sub-consciously noted that this is a good company which I should patronize the next time I am going to buy a product that they offer. This gives the company huge advantage over the competition. So in the end, what is gained is that people got free coffee and the company scores brownie points for being & encouraging people to go green as well as get a lot of cheap, effective & useful advertisement.

So, what do you think? And what would you do?!

Starbucks to Make a Difference with the Big Picture

Starbucks is trying to make a difference through its The Big Picture Green Project. Starbucks is asking general public to join the movement and on April 15th, it is encouraging people to reduce their usage of paper cups by giving free brewed coffee to those who bring in a reusable travel mug into their local Starbucks store. Their quote: One person can save trees, together we can save forests. is really worth working towards!

It is good to such big corporations like Starbucks sharing their responsibilities towards our communities and the planet and trying to make a difference. I wish more corporations follow the lead and do such campaigns to make this planet a better place to live in!

URL Rewrite very slow on IIS7

My friend has an ASP shopping cart running on Windows 2008 Server + IIS7 + SQL Server. This cart does not support SEO / user friendly URLs and apparently due to that, his site was getting very low rankings on the search engines. He called me in to help him out implement URL Rewriting to resolve this. Here is my experience with this problem and finally how I went about resolving this.

So the first step was to install the URL Rewrite Module 2.0 for IIS. Next was to configure the rewriting of the URLs - the cart produced URLs of the form store/listItems.asp?cat=123&item=456. The item id number and the corresponding category id number changed for different items so putting in a generic regular expression would not work. So we would need to create static rewrite maps to achieve our goal.

So I created a rewrite map with entries like /store/listItems.asp?cat=123&item=456 mapped to /Item1 and /store/listItems.asp?cat=123&item=457 mapped to /Item2 and so on. This worked but the page did not come up properly. Investigation revealed that this was due to the HTML having relative URLs for CSS, Image & JavaScript files. I changed paths to absolute paths and lo-behold it was working fine! However, when using the user-friendly URL, the pages would come up quite slow compared to the page display using the old URLs.

In fact the pages came up so slow that we had to scrap this idea and look for other alternatives. Finally, after a lot of investigation, we fixed on the ages-old method of creating static HTML index files in folders named by each category. The header, footer & navigation menus would be common and kept in common folder. I know that this is not such a good idea and will result in creation of hundreds of folders with only an index file. But what the heck - it is simple, it works and it gets the job done!

Cheap Webhosting Deals

It seems that April is the month for Cheap Web Hosting Deals!!!
Here is a round-up of the cheapest (but not necessarily the best) web hosting deals I found out.

However, my personal favorite is always BlueHost for their excellent support & fast servers. Check out my post on the same here.

NOTE: No guarantees on the validity of the offers or the quality of the services. I am in no way connected to any of these hosting companies and the links provided are direct links (no affiliate tracking code). I do not gain in any way from you clicking on any of these links and/or signing up for any of these offers!!!


Lifetime Webhosting for $79
HostSure is offering a full featured lifetime web hosting for only $79 where most companies offer that for $60-$100/year.

Facade Host
Facade Host is offering shared web hosting packages for as low as only only $0.85/month where most companies offer that for $2-$5/month. Use coupon code april for a further 20% discount!

Cookie Host
Cookie Host is offering shared web hosting packages for as low as only only $5/year where most companies offer that for $60-$100/year. Note that the promotion starts on April 7th 2010 and is valid only for new hosting accounts. The good thing is that they do not automatically rebill you at the end of the first year and you will be given the option to renew at the then prevailing regular price.

Just Host
Just Host is offering a full featured unlimited web hosting with free domain for the life of hosting for only $3.45/month when prepaying for 2 years. Use coupon code 50OFF for a further discount to bring down the price to $2.95/month when prepaying for 2 years!

Fat Cow
Fat Cow is offering a full featured unlimited "green" web hosting with free domain for the life of hosting for only $66/year!

Dream Host
Dream Host is offering a full featured unlimited "green" web hosting with free domain for the life of hosting for only $9.24/year with coupon code 777!

Daily Razor
Daily Razor is offering a full featured unlimited web hosting for only $2.95/month. Use coupon code 15PD for another 15% discount!

NOTE: Not all of these plans may suit your needs

Deleting duplicate rows in Sybase

I had to delete a set of duplicate rows in Sybase and that had me in a quandry. I had done that many-a-times in Oracle using the ROWID psuedo-column but nothing similar is available in Sybase or for that matter in SQL Server. Here's how to go about this daunting task in Sybase!


The deletion of duplicate rows cannot be done in a single SQL query like it can be done in Oracle. Hence it needs to be done in 3 steps:

1. Create temporary table with one instance of each distinct row
2. Delete all rows having multiple instances
3. Move back the unique data from temporary table to actual table

Step 1:
Create temporary table with one instance of each distinct row with the below query.

select distinct t.* into temp_table_name
from data_table_name t
group by keycol1, keycol2, keycol3 
having count(0) > 1

Note: that this step creates the temp_table_name table so it will fail if a table by that name already exists.

This step puts one instance of each distinct row into the temporary table. However, some of the rows may contain identical values in the key columns but different data in the remaining columns. At this point you need to check the data in the temp_table_name and reconcile which of the rows you wish to keep for a given duplicate key value. You may even need to do these three steps on the temporary table to achieve a set of unique records in the temporary table.

Step 2:
Delete all rows having multiple instances with the below query.

delete data_table_name from temp_table_name 
where data_table_name.keycol1 = temp_table_name.keycol1 
and data_table_name.keycol2 = temp_table_name.keycol2 
and data_table_name.keycol3 = temp_table_name.keycol3

Step 3:
Move back the unique data from temporary table to actual table with the below query.

insert into data_table_name 
select * from temp_table_name

Remember to change the table & column names in the above queries.
Replace data_table_name with your actual table name,
temp_table_name with your choice of name for temporary table and
keycol1..n with the actual table column names!

Check out my previous post here for doing this same thing in Oracle.

Deleting duplicate rows in Oracle

Deleting duplicate rows in Oracle seems to be the simplest to me (may be because I have used it more or may be its quite simple really!). Lets get down straight to the code and do a recap of how to delete duplicate rows in Oracle.

Simple Solution
Note that this simple solution assumes that there are no primary or unique keys on the table but the combination of data in a set of columns defines the duplicates. The example shows three key columns, but you can use more or less columns to identify unique rows based on your actual data.

DELETE FROM data_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM data_table
GROUP BY keycol1, keycol2, keycol3

However, there could be cases where the table has a sequence / auto-generated column as the primary key. In such cases too, you can use the above query after substituting rowid with the id column name.

Advanced Solution
If you are looking for an advanced and faster solution with more features & flexibility, you can use the following solution that I found on devx. This solution uses RANK() function's capabilities to provide the extra features.

DELETE
FROM data_table
WHERE ROWID IN
(SELECT tmp_key
FROM
(SELECT tmp_key, keycol1, keycol2,
RANK() OVER (PARTITION BY keycol1, keycol2 ORDER BY tmp_key) AS seq_num
FROM
(SELECT rowid as tmp_key, keycol1, keycol2
FROM data_table
WHERE (keycol1, keycol2) IN
(SELECT keycol1, keycol2
FROM data_table
GROUP BY keycol1, keycol2
HAVING COUNT(0) > 1)))
WHERE seq_num > 1)

Let me break up this complicated query into parts and explain in detail.

1. The innermost sub-query (lines 12 - 15) finds those records that have duplicates. This gives us the sub-set of data that are only duplicate rows.
2. The next outer sub-query (lines 9 - 11) uses ROWD psuedo-column to create a single key column. This adds a key column to the set of data rows we are interested in.
3. The next outer sub-query (lines 6 - 8) uses RANK() function to dynamically assign sequence numbers to rows in the set of duplicate rows and orders them. Note that there are a set of sequence numbers each for each set of duplicate rows. This assigns a sequence number to and orders the rows inside each set of duplicates.
4. The next outer sub-query (lines 4, 5 & 16) selects the duplicate rows based on the sequence number assigned by the RANK function is above step.
5. The outer-most query (lines 1 - 3) deletes the duplicate rows.

Note: In case the table has a unique / primary key, substitute that instead of the ROWID psuedo-column in the above solution to get the same results.

Now, the extra features that using RANK provides are that you can choose the number of rows you want to keep. For example, you want to keep only the top 10 rows, you can change the numbers in lines 15 & 16 of the above query from 1 to 10 and add a proper ORDER BY to line 7 to get the data sorted in the required manner to determine the "top 10 rows".

Similarly, if you want to keep only the latest row, you can add a proper ORDER BY clause to line 7 to get the data sorted in the required manner to determine the latest row.

Remember to change the table & column names in the above queries.
Replace data_table with your actual table name and
keycol1..n with the actual table column names!

Check out my next post here for doing this same thing in Sybase.

Install WordPress on IIS7

A friend of mine wanted me help him install WordPress on his website - no big deal as WP is famous for it's 5 minute installs and so I figured this should be a cake walk for me. Little did I know that my friend's web-server was IIS7 on Windows Server 2008 and this would translate to a 5 hour install :(

Google, pointed me to a very good site that helped me out - trainsignaltraining.com.

Here are the components that you need to download:

• PHP 5.3.2
• MySQL 5.1.45
• PHPMyAdmin 3.3.1
• WordPress 2.9.2
• URL Rewrite Extension

Here are the Installation steps:

1. Install & configure PHP
2. Install & configure MySQL
3. Install & configure PHPMyAdmin
4. Install & configure URL Rewrite Extension
5. Install & configure WordPress
6. Configure Search Engine Friendly URLs on WordPress

However, here are a few "gotchas" that need to be taken care of:

• For PHPMyAdmin to work, you may need to change in config.inc.php
  $cfg[$i]['host'] = ‘localhost’; to
  $cfg[$i]['host'] = ‘127.0.01’; or
  $cfg['Servers'][$i]['host'] = ‘127.0.0.1′
• For PHPMyAdmin to work, you may need to change in config.inc.php
  controluser = ‘pma’ to
  controluser = ‘root’
• For PHPMyAdmin to work, you may need to change windows\system32\drivers\etc\hosts file:
  Remove the comment (#) in front of the line 127.0.0.1
  Comment out the line ::1 localhost
• Read the comments in all the pages as they have solutions to a lot of common problems

Note: I am assuming that IIS7 is already installed. If not, then there's a good tutorial for that as well here!

SQuirreL behaving weird on Ubuntu

I needed to access MySQL & MS SQL databases from my Ubuntu 9.04 laptop and was at a total loss. After a bit of research, SQuirreL sql client looked like the best choice.
However, since this is not available through the Synaptics Package Manager so it had to be downloaded and installed manually - which is the way software used to be installed on *nix boxes originally.

At that time, SQuirreL 3.0.2 was the latest version and all versions starting from 3.0 require Java6 - this I was able to install from the Synaptics Package Manager. Installing SQuirreL was simple: Run the command sudo java -jar squirrel-sql-3.0.2-install.jar and it was installed.

However I was unable to access MS SQL Server DB yet as I did not have the proper JDBC drivers for that. After narrowing down my choices, I found I was left with jTDS which is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008) and Sybase (10, 11, 12, 15). Just put the jtds jar file in the squirrel install lib folder and then add the new driver from the UI.

After this, I was able to access MS SQL server but often, the program would just freeze or become unresponsive after a few minutes of use (or lack thereof). I would need to kill the java process and restart it. Also, the automatic updates never worked and so the software remained the same version till I decided to take things into my hands.

At this time the latest version available was 3.1 which was good news indeed - I was expecting a full solution to my problems! However, the website offered no documentation on how to upgrade the software, only how to install it. So I went ahead and installed it in a similar fashion - but this time it installed in a different folder. The previous install was in the folder /usr/local/SQuirrel SQL Client/ and the new one was in the folder /usr/local/squirrel-sql-3.1/

I then tried to uninstall the older version by running the uninstall.jar from the Unintall folder of the previous install. However that did not do much and so I just removed that whole folder - just like we used to remove software from *nix in the "good old days"! The good news was that the configuration was stored in my home folder in the .squirrel-sql folder so all aliases/connections/drivers etc were intact and I was able to use them immediately.

But alas! Even after all this effort, the problem of SQuirreL freezing after a few minutes of use still did not go away!

Please let me know if you see any solution to this problem or if you come across a better SQL Client for Ubuntu which can access MySQL & MS SQL!