Monday, March 28, 2005

Having fun with the Google APIs

A while back, I downloaded Google's APIs. Essentially they will provide access to their search database for those who want to try experimenting with their site and search tools.

Among the many ideas that I toyed with was the opportunity to find the rank of a specific site for a given keyword. In other words, let's say I sold Widgets - it would be pretty useful to find out on what page in Google's results my site is on when I search for the term Widgets.

So using the google apis, I managed to develop a tool that does just that. It simply iterates through the search results and tells you what number page your result shows up on.

Of course, the Google apis are limited to 1000 hits a day, which can get used up very fast. Instead, I am currently revamping this to search Google, Yahoo, MSN, and A9 without having to use the APIs. When I finish, I will be more than glad to post the code.

Another thing that I think will be useful - coming up with a unique enough term that will almost guarantee that your site will be found when looking for it. When I figure that one out, I will change my blog name, and then let you know :)

Yonah's SQL Tip of the Week #1 - Top and Sorting Views

I am sure that you've stumbled across the Top command. You might have seen it by looking at the SQL statement when browsing tables in Enterprise Manager. You may have seen it in the Syntax for SELECT. No matter what the Top command is a very useful command in more ways than one.

Essentially Top lives up to its name. The most Common syntax is:
SELECT TOP n * from table order by sales_count DESC

The query above will return the first n rows of the result set. This type of query can be useful in many situations. For example, when you are outputting the top ten selling products on your website. Notice that the ORDER BY clause is set. Order by essentially determines the order in which the rows are returned, and since we are filtering out the top 10, it also controls which records get displayed. Without Order By, Top would only return the first ten records in the result set.

Top also has another mode - percent. Instead of returning a fixed number of rows, percent returns the top n percent of a result set. This can be good if you want to find, say, the top ten percent of the most frequently sold items:

Select top 10 percent * from products, order by sales_count desc

The Percent mode will work with any percentage, up to an including 100 Percent. Why would you want to put top 100 percent in a query if not specifying top at all gives the same result? Well I could definitely think of one main reason for this - Creating a View with a Default Sort. In SQL, views cannot include an order by clause, unless the TOP keyword is specified. Obviously, this needs to be there in order to display the appropriate records. But let's say that you want to pre-sort an entire result set in a view without specifying ORDER BY in your query? Since you can't put an Order By clause into the without specifying TOP, simply put the following SQL into your view:

Create View myView as
Select TOP 100 percent from table, order by sales_count DESC

Now you have a view that is pre-sorted.

Yonah's SQL Tip of the Week - Introduction

So one of the new features I want for this blog is a weekly SQL tip. I have worked and chatted with hundreds of developers over the years, and while even the most junior of my colleagues have great experience with the basic SQL commands, very few have mastered even a handful of the less-commonly used functions and features.

I can't begin to tell you how many e-mails and message board posts I've seen where people have created intricate SQL statements where, had they known about some of these features, they could have used only one or two extra words in their query instead of lines.

That's why I am putting these solutions into a weekly tip.

Wednesday, March 23, 2005

Teaching a man to Fish

As someone who likes to contribute to user forums and mailing lists, when I provide a solution, I generally avoid including the code. I might include the Pseudo-code, or suggest how one might code it, but I try to stop just short of the actual code. Why? Becuase I believe that if we just go around posting code snippets everywhere, we will encourage bad coding practices and enable people to import snippets into their programs without even thinking about the ramifications to the system.

By making Joe/Jane Forum Poster devise the code out of a concept, he or she is able to write code that best suits their needs, without importing someone's half-assed attempt at solving a problem that they have limited knowledge of.

I am also limiting my liability of course, as most of the time the code I post - while it will work - isn't necessarily the cleanest, most efficient or safest code - but is really there by example.

This of course, is different from my articles, because I need to write the code to illustrate the concept. The biggest difference there however, is in that I choose the topics, and then Joe or Jane can apply it to their own code needs.

Out of nowhere...

Today I got an e-mail from a complete stranger with a technical question. The question was related to a Java project I had worked on in 2002 - almost 3 years ago. I believe that he got my information from the Java Forum as I was working on developing a framework for sending ringtones and icons via SMS.

The only reason that his e-mail reached me is that I updated my profile on that website. I guess you need to be careful what you write on the 'net, because at some point someone might ask you a question about it.

Monday, March 21, 2005

We're chaging things around

So after debating and debating about what to do with this site, I have decided to devote it to the thing I have been doing on message boards and mailing lists for years - commenting on the technology that I use in my day to day life.

I hope that whoever out there in the Blogsphere is reading this will find the information useful and informative, and please note that I will gladly answer questions about any thing I post. Just send the questions to 'blog at bonerosity dot com'.