• ILAsoft.net Blog


(Hacking) Professional Weather Station for under $100

A huge array of Personal Weather Stations is what makes Weather Underground (aka WUnderground) be so much more precise than any other weather service. In fact, the company’s forecasts are so precise (and cheap?), that “even” Google chose them to be their primary source for searches and other weather related cloud services (i.e. see http://www.google.com/intl/en/help/features_list.html#weather). But while there are “more than 25,000 weather stations across the globe [that] already send data to Weather Underground“, there are still many areas of the world that are not properly covered by this extensive network. In my case the topography of the region is so extreme, that the nearest station was constantly off by up to 15 degrees. Needless to say, even the next day forecasts were all over the place. Introducing (cheap consumer grade) La Crosse Technology Wireless Weather Station with La Crosse Alerts (aka C84612) being sold at offline Costco stores. The station costs roughly $80 and comes with wireless wind (speed, gust, direction), rain (still not working for me), temperature, humidity, and pressure sensors, as well as a mediocre dashboard panel, and an “ internet gateway” (connects the panel to La Crosse propitiatory website). This station is capable of properly reporting current information in close to real-time onto the dashboard and roughly every 20 minutes to the attractive La Crosse Alerts secured website.

Read More

SQL - The Power of OUTPUT

Insert..Select is a great command, but sometimes you just need more- sometimes you need to return bits of the information back. The first thing you will attempt, of course, is to declare the few variables you need. Yes, you will see that you can indeed do that, along with updating columns, all in one call- neat! [syntax_prettify linenums=”linenums”][email protected] = ID,OldValue = OriginalTable.ValueSELECTValueFROM OriginalTableWHEREOriginalTable.AddDate < GETDATE() - 30[/syntax_prettify] Unfortunately not only other types of statements (i.e. delete and insert) will fail to do that, but your variable(s) will hold only one value. Yes, you could concatenate it…but are you really going to be doing this crazy 20th century workaround nonsense again? Our old OUTPUT keyword to the rescue- it redirects the processed information back to where you tell it, including a variable table that you might have created just for that purpose.

Read More


We’ve already talked about batch processing in SQL - Cursor and Batching, but it’s not just human labor that batching helps us with. One of the best ways to optimize any type of coding is to limit the amount of hits it has to do against storage (physical or otherwise). It could come at an expense of more CPU cycles, so proper compromises need to be made to ensure true performance improvements. In case of SQL, this is usually achieved by limiting the number of separate calls to the databases/tables and finally limiting the number of requests themselves (select/insert/update/delete), wrapping them into as few as possible… Oh, how many times have I seen very simple procedures with cursors or even just check requests (ie IF EXISTS), followed by possible change requests, followed yet again by closing check or pull requests. Why would you do that if you can easily do all of this at once and allow the engine to properly optimize out of the box?? The harsh cultural weight of our 20th century coding backgrounds, perhaps?

Read More

SQL - Cursor and Batching

Somewhat surprisingly, batch processing stays as one of the most gaping holes in DB power-users’ knowledgebases… Processing records one by one, searching, fetching, updating- they seem to be fine with all of this no matter the type of the DB, but there is something about batching that many do not seem to grasp. What’s worse, is that they don’t even KNOW that they are really missing out…

Read More

SQL - Error Handling

Yet another “new” feature of MS SQL 05 is an often overlooked “error handling”. The Transact-SQL Try…Catch is designed to operate similar to the exception handler in the Visual/.NET languages- if an error occurs inside the TRY block of a query, control is passed to another group of statements that is enclosed in a CATCH block (and on)… Or so it should- the handler will not help with errors of severity 20+, KILLs, and, of course, with various warnings and messages, so be careful and remember to test all of the scenarios right away.
[syntax_prettify linenums=”linenums”]BEGIN TRY
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
SELECT ‘Continue the run’[/syntax_prettify]

Note that this behavior can also be triggered manually, via the RAISERROR command (notice the spelling, with only one E):
[syntax_prettify linenums=”linenums”]RAISERROR(‘Fatal error’,16,1)[/syntax_prettify]

At last, sometimes, when using this functionality, you might need to ensure that you do exit and don’t run the rest of the statements- feel free to add RETURN command in such cases…

Read More

SQL - The Power of APPLY

MS SQL 2005 and up adds support for the APPLY clause, which, in turn, lets you join a table to any dynamic sets, such as table-valued-functions or even derived tables. While we can argue over the benefits and dangers of the latter (another set of article perhaps?), being able to do things more than one way is certainly always awesome.
The two new functions (CROSS APPLY and OUTER APPLY) are essentially INNER and OUTER JOINs for table-functions (you cannot directly join them like tables). Here is just a simple example of the usage (don’t hesitate to expand on it):

[syntax_prettify linenums=”linenums”]SELECT All.CustomerID
Customer as C
fnSelectAllChildren(C.CustomerID) AS All
WHERE C.Status = 1[/syntax_prettify]

This technique can also be used in more complex queries alongside derived tables, FOR XML, etc., covering the need for things such as inline multi-row CONCAT (multiple rows into a single column):

[syntax_prettify linenums=”linenums”]SELECT
ISNULL(LEFT(o.list, LEN(o.list) - 1), ‘Unknown’)
Orders AS ito2
CONVERT(VARCHAR(12), ServiceType) + ‘,’ AS [text language=”()”][/text]
OrderService AS itos2 (NOLOCK)
OrderServiceType AS itost (NOLOCK)
itost.Id = itos2.OrderServiceType
itos2.OrderId = ito.ID
) o (list)
ito2.ID = ito.ID
) AS AllTypes
Orders (NOLOCK) ito
ito.Status = 0[/syntax_prettify]

Read More

SQL - The Danger of NULLs

Following up on the previous article SQL – The Power of NULLs, here are some of the things to keep in mind when you do decide to use NULL in your work.

By far, one of the most common mistakes is to assume that <> (not equal) or even = (equal) logic covers NULLs. That is completely incorrect in both cases; NULL is neither equal to anything nor is it not equal to anything, including itself. Consider the following code:

[syntax_prettify]SELECT CASE WHEN 1<>NULL THEN 1 WHEN NULL=NULL THEN 0 END[/syntax_prettify]

Of course the end result will be neither- NULL :)

What about this one: will WHEN help us be able to do this comparison on the fly?

[syntax_prettify]SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END[/syntax_prettify]

Just a little- the ELSE logic will kick in covering “all other” possibilities, but the CASE logic still relies on = and <> just the same way- NULL is still nothing, it still never rings TRUE.

So what are we left with? Well, you can always try using ISNULL (the great counterpart of NULLIF) and COALESCE in more advanced cases. Either way, remember- NULLs can help you do really interesting things with your DB and your queries, you just need to be careful, that’s all.

Read More

SQL - The Power of NULLs

Some people despise NULLs for their unusual behavior and apparent complexity, while some remain suspicious simply because of a lack of experience with them. Although at times it is not a good idea to use them in database design, they often come in handy in queries alone. They let us easily test for the “third case”- complete lack of data. Assume you have a bit column that only allows 0 and 1, so you can distinguish if something is “on” or “off”, what better way than the NULL is there for you to test for the row’s presence, or vice versa?..

[sql]SELECT *
BitColumn IS NOT NULL[/sql]

This approach can also be easily extended to filter vast chunks of data. Suppose you have a big table of users and a table of their last logins (i.e. audit). By left joining the latter and checking for the absent rows, you can say which users haven?t logged-in in the last 3 weeks (or never logged-in for that matter):

[sql]SELECT *
LEFT JOIN AuditUsers
ON AuditUsers.UserID = Users.ID
AND ChangeDate > DATEADD(week, -3, GETDATE())
AuditUsers.ID IS NULL[/sql]

As you can see, the possibilities are really endless. Here is another neat trick that probably deserves an article just by itself. I actually use this more often than I would like, but it does allow me to easily set “preferences” in the data output (or filter it altogether) based on seemingly unrelated data fields. All of this is done by simply having a specific CASE statement. Here is an example derived from the one above. This approach is best when used against multiple tables and columns, but this should suffice just to show how to use it. In this example we will first see users that never logged in at all, then all users who logged in over 3 weeks ago, and then all the rest. Note that you can also use TOP to limit just to the first couple of rows that come out, very handy:

[sql]SELECT *
LEFT JOIN AuditUsers
ON AuditUsers.UserID = Users.ID
END ASC[/sql]

Edited by Katya Pupko

Read More

Fighting Spam at the Source

There are a lot of ways for a spammer to get your e-mail. They can buy it in a package of a thousand e-mails from a third-party; they can get it by asking you to “register” with them; they can use a random name generator and then “ask” your mail-server if that name exists (most servers support that and do not have time protection)… These are very “successful” tactics, but the cheapest and safest (hence, most popular) is to “snag” e-mails from websites. Lists compiled this way have many advantages for the spammer (a href=”http://www.awael.net/tools/antispam.shtml" target=”_blank”>find out how some people try to fight back).

You can protect yourself from these e-mail-mongers, but not every protection is bulletproof.

1. How do you make an e-mail link? Just like the URL, only the “href” attribute says “mailto:” and then your e-mail. This is the default way that makes it easier for the automatic programs to gather your e-mail addresses.

2. Those who know about spammers go a bit further. They make a form that uses some script/program (i.e. Matt Script Archive’s “FormMail“) to send the e-mails. This way they don’t have the “mailto” word and some of the programs are unable to get their e-mail. Still, most spammer bots are advance enough to get that e-mail from the source code (you will have a “hidden” tag with your e-mail that follows strict standards like the “@” tag and at least one dot after it).

3. Some know about the “intelligent” spammer bots or simply don’t want to use scripts or programs to send e-mail. They use either JavaScript to hide their e-mail or HEX (or both). What these people fail to realize is that internet has same principles as the real life. If you have a key, somebody else will be able to copy it. As Steve Williams puts it: “it’s trivial to write a spambot that recognizes character entities and/or follows the redirect.” In other words- if your browser can see the address, spammers will be able to see it and so will their programs.

4. I know of only one bulletproof solution (even though all of the solutions above are pretty robust). “My” solution is step above the second method as it involves making a form. In fact, that’s the only problem with it- if user decides not to give you their real e-mail, they could make something up. On the other hand, most people would be able to live with it as long as their inbox stays clean.

Simply change whatever FormMail script you want to use (Perl, PHP, whatever) so that it takes any string (no need for a check if the e-mail address exists and etc.). Then write something like this:


if ($post[‘recipient’]==”john”) { $post[‘recipient’]=”john@domain.com“; }

elseif ($post[‘recipient’]==”mary”) { $post[‘recipient’]=”mary@somedomain.com“; }

else { $post[‘recipient’]=”nonexistent@domain.com“; } /you might want to play with this line to make sure that your script is more secure/

This tactic doesn’t EVER show the e-mail address and unless the spammer gets access to the source of your script he cannot see the e-mails being used.

This approach prevents bots from gathering your e-mail from YOUR website, but what about other people’s websites? Guys at SpamGourmet.com provide a first-rate solution. I really like it and recommend it to anyone who doesn’t fall for schemes like “Work full time while earning your degree” or “Loose 100 pounds in only 5 days for FREE“.

Edited by Alex Pupko (Pupko.com)

Read More

Contact Me

Information you submit through the form below will only be used in answering the email.