Before beginning this tutorial, it is assumed that:

• You have installed a Relational Database Management System (RDBMS)
• This system uses the Structured Query Language
• You have configured the RDBMS
• You have read the documentation and know how to access the RDBMS
• You know how to execute a query statement within the RDBMS

Part I - What is SQL?

The goal of these tutorials is to teach you everything you need to know about SQL. We will start off easy, the first few tutorials will be extremely simple, and somewhat short. As we progress into the more detailed features of SQL, they will grow in both length and knowledge. I used Microsoft SQL Server for this tutorial. The queries presented within this tutorial, however, should work on any ANSI SQL-92 RDBMS. I hope you enjoy.

Abridged Background of SQL

The acronym, SQL, stands for Structured Query Language. It is commonly pronounced as ‘Sequel’, though this is not the proper way to pronounce it. SQL is simply pronounced, ‘S Q L’. You say each letter individually. It is an acronym, not an abbreviation; there by it has no spoken form. Now that you know how to pronounce it, let’s discuss what it is.

SQL is a Relational Calculus developed by Codd in the early 70’s. It is commonly used in Relational Database Management Systems (known throughout the rest of this tutorial as RDBMS). It is, without a doubt, the easiest ‘language’ you will ever learn. Actually knowing the language, and being able to use it, however, are two separate issues. There are very few keywords, phrases or ‘methods’ within SQL. The power behind your queries is not in the language, but the developer using the language. You will learn, throughout this tutorial and in general practice, that no matter how much you already know about relational data … their is always more to learn.

(more…)

I installed a Wordpress blog on my development server the other day and began playing with it. The first real challenge I faced was how to pull my Wordpress installation into my Web site’s template.

My issue is, I’m using a content management system (CMS) to manage the bulk of my Web site’s content. However, I wanted to use Wordpress to manage my various blogs. I obviously wanted my blogs to look like the rest of my Web site, so I needed to come up with a plan to integrate my Wordpress installation into my CMS, somehow.

Basically, what it came down to was that I needed to find a way to store all of my Wordpress output into PHP variables. Once I had done that, I could plug those variables into my template. The main problem I ran across, however, was the fact that 99% of the functions Wordpress uses to build its output utilize echo commands rather than simply returning the output.

That was no good for me, obviously, as it started printing content onto my page before the template had been processed.

PHP came to my rescue, and with very little headache. PHP’s output buffer was the simple answer to my problem.

(more…)

Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.

The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.

Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.

Name    | Size 

T-Shirt | L 

T-Shirt | M 

T-Shirt | S 

T-Shirt | XL 

T-Shirt | XS

And here is how you typically retrieve records:

SELECT Name, Size FROM Products ORDER BY Size

This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.

The solution to this problem is a simple CASE (see: Control Flow Functions):

SELECT Name, Size, 

CASE Size 

WHEN XS THEN 1 

WHEN S THEN 2 

WHEN M THEN 3 

WHEN L THEN 4 

WHEN XL THEN 5 

END 

AS Sort_Order 

FROM Products 

ORDER BY Sort_Order ASC

This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.

That’s it, enjoy.

Last week at work, we unveiled one of the new Web sites I’ve been working on for a while.  In doing so, we removed the old Web site completely from the server and replaced it with the new site.

Of course, when we did so, a lot of our old links became obsolete, so I had to find an effective solution to keep our regular visitors from going bananas when they tried to access some of their favorite pages.  That’s when I began to look into some options for redirecting without having to create placeholder pages just for those redirects.  After quite a few fruitless searches, I was able to piece together a fairly good solution, and also discovered an easy way to achieve URL rewriting on an IIS server (similar to the way you would with htaccess on an apache server).

First, you need to set up your “404″ so that it points to a VBScript page.  That’s the most important part.  Once you’ve got that done, navigate to a page that you know does not exist on your server, just so you can test the behavior of your 404 page.

(more…)

What is VRML?

VRML stands for Virtual Reality Modeling Language. It was designed to create a more “friendly” environment for the World Wide Web. VRML incorporates 3-D shapes, colors, textures, and sounds to produce a “virtual world” that a user could walk and fly through. VRML is an interpreted language. That is, commands written in text are interpreted by the browser and displayed on the user’s monitor. Many of these worlds can be found on the web today. The current specification, VRML 97, supports JAVA, sound, animation, and Javascript. It allows the world to be dynamic; always changing.

HTML and VRML

HTML and VRML are similar, and very different. Both languages use a text editor as standard equipment. You don’t need anything other than a text editor for HTML, as is the same with VRML. However, the similarities stop there. By creating the 3rd dimension, a lot more creativity, as well as frustration arises. VRML requires that “objects” be placed in an X, Y, and Z space. HTML only uses the X and Y plane. Also, HTML can be viewed in only one way, which the page designer specifies. VRML can be viewed from all angles, making a world be much more enjoyable to experience. VRML files are inherently much bigger than HTML files. Due to this, you should use some methods to reduce your file size.

VRML History

VRML was an idea that was expressed in the First International Conference of the World Wide Web. Mark Pesce, Tony Parisi, Tim Berners-Lee, and David Ragget discussed VRML at this conference. VRML would have to be platform independent to be implemented on the internet. Also, the language had to be able to place objects in 3-D space, as well as include attributes such as shape, color, and size. Since VRML would be used in the internet, all platforms should be able to support it: UNIX workstations to humble desktop PC’s. Silicon Graphics introduced the Open Inventor format, and it was widely accepted. Since then, slight changes were made, and VRML 1.0 was introduced in May 1995. A more clarified version, VRML 1.0c, was issued January 1996. Now, VRML 2, called Moving Worlds, is in full swing.
Differences between VRML 1.0 and 2.0

The major differences that VRML 2.0 has is that it is more interactive, more realistic. VRML 1.0 had static worlds, that is, no interaction and the shapes you see in the world are the shapes that will stay, no movement. VRML 2.0 enhances this by adding JAVA and Javascript support, as well as sound and animation. Now instead of just looking at an unexciting house, you can see the windows flutter, the doors open, cars entering and exiting the garage. Also you can hear music playing, and interactivity like opening the door, using an elevator, or flying through space, automatically! VRML 2.0 was such a major leap from 1.0 that a whole new language had to be created. VRML 1 and 2 are not, compatible. There are conversion tools available, but the best thing is to learn VRML 2.0 from scratch, and experience the new dimension of reality it offers.

Sometimes when trying to access a web page, an error code will appear. Have you ever wondered what that code meant? Here is a list of the most popular error codes and their description. The first thing you should do anytime you get an error code is to make sure that you have entered the correct web page addressed. Everyone has incorrectly spelled a company name or added too many periods.

401 - Authorization Required

This error code means that you must have special access to view this page. The developers may want only certain people to access this page. There are several ways to limit access to a web page, including password protection. You may get the “401 - Authorization Required” error message when you try to view a web page with limited access. (A web site may have a customized version of this error message, displaying “Access Denied” or “Unavailable.”)

403 - Forbidden

This standard error message is generated by web servers when you try to access a file that has not been correctly configured by whomever maintains it. (The file needs to be set with “read permissions” for all users.) What this code most likely means is that you can’t view the page because whomever maintains the site set it up incorrectly.

404 - Page not found

A 404 - Not Found error means that there was no web page with the name you specified at the web site. This could happen for a variety of reasons:

Make sure that the web address (URL) that you typed in exactly matches the address you were given. Check that the capitalization matches, that all words are spelled properly, and that all the punctuation, like dots (.) and slashes (/), are correct. Be sure you are using the forward slash (/) and not the backward slash (\). (Remember, there are no spaces allowed in Web addresses, and a proper Web address will look like http://www.htmlcenter.com).

The page may have been renamed, moved, or deleted. Another possibility is that the person maintaining the web page may no longer have an account at that location.

500 - Page not available

If the server has been incorrectly set up or is experiencing technical problems, it will return this error. Again the first thing to do is make sure that you have typed in the address correctly.

501 - Not Implemented

This error is a direct result of problems in the web page’s HTML (HyperText Markup Language). It usually occurs with web pages that contain forms in which you type information and send it to a third party.

Check out the world famous color chart for all web safe RGB colors.

XML stands for Extensible Markup Language. The development of XML started somewhere around the 1996 timeframe. In 1998, the W3C made it a official standard. XML’s derived directly from SGML, another document language from the 80’s (standardized in 1986). The developers of XML took parts of SGML together with their experience from HTML and create a language which is not less powerful or capable, but a lot more structured and easier in use. Where SGML was primarily used for technical documentation and less for formating data, it’s the other way around with XML.

If you open a XML document in a text editor, you will notice that it looks a lot like HTML. At first glance you will probably notice that the first tag is XML, instead of HTML, but other than that, the tags are different and looks familiar. However, if you open the same XML document with a browser, you will see that it looks just like what you just saw when you opened it with a text editor. And this is where the journey begins.

HTML has tags to define and format font-type, -size, tables, frames and colors. XML by its very nature knows nothing about those tags. XML has to rely on an external source that tells the browser how to format the XML tags.

You might get that feeling that you have heard about that before, and if you are “in love” with Cascading Stylesheets (CSS)(ver. 1 & 2), you have. CSS is a helper to HTML and XML in some ways. In HTML usage, Stylesheets are referenced in the source code of your website to format the output without them, HTML would look a lot less pretty. And CSS are one way to format XML output as well.

Two roads diverge in a yellow wood

The other method is utilizing XSL-files. XSL means Extensible Stylesheet Language. The components within XSL is made of are called XSLT - XSL-Transformations - and XSL-FO - XSL-Formating Objects. Together they perform two very important tasks. Number one being the formating
(XSL-FO) and number two the converting, or the (XSL)tranformation, of XML documents over to HTML, XML-FO (of course), TeX and even PDF.

United we stand?

HTML has been responsible for a couple of my nervous breakdowns while working as an HTML monkey. The phrase, “It works with Internet Explorer, but it doesn’t work with Netscape!”, has been present for some time in my world or any webmaster’s world each day. Seven out of ten times that I am asked to fix and clean up some source code, it is because of a cross-browser issue. In recent years Opera has added to the list that no longer just contains Netscape.

To find out how Internet Explorer, Netscape Navigator/Mozilla, Opera and Konqueror (Linux only) support XML, stay tuned, as it will be covered in another tutorial soon.

If you have any comments, questions or suggestions, feel free to post them on the forums.

As an IIS administrator it sometimes gets downright annoying having to fend off all the insults from Apache admins I meet claming innate server superiority. Generally the discussion about Web administration starts first with all the various security holes plaguing IIS and the negative press the platform garnered over the last year. Then it invariably moves to a discussion about how Netcraft and other stats sites show Apache as the dominant server on the Web, or how a certain big site uses Apache, or how there are so many cool modules to add to Apache. Pointing out that scads of non-identified corporate in-house servers run IIS, or that it too is a free server (since it comes with the operating system), or that there are in fact plenty of cool add-ons for IIS (including many that provide source code) — all this does little to dissuade these server chauvinists of their opinion. Rather than whining about rude Apache admins, however, I thought it would be a more useful response simply to write down some of the ways I’ve found of improving IIS. So without further delay here are my top ten tips for making the most of your IIS.

Tip 10: Customize Your Error Pages

Although this is quite simple to do, few people seem to take advantage of it. Just select the “Custom Errors” tab in MMC and map each error, such as 404, to the appropriate HTML or ASP template. Full details can be found here. If you want an even easier solution — or if you want to let developers handle the mapping without giving them access to the MMC — use a product like CustomError.

Tip 9: Dive into the MetaBase

If you think Apache is powerful because it has a config file, then take a look at the MetaBase. You can do just about anything you want with IIS by editing the MetaBase. For example, you can create virtual directories and servers; stop, start and pause Web sites; and create, delete, enable and disable applications.

Microsoft provides a GUI utility called MetaEdit, somewhat similar to RegEdit, to help you read from and write to the MetaBase. Download the latest version here. But to really impress those UNIX admins — and to take full advantage of the MetaBase by learning how to manipulate it programmatically — you’ll want to try out the command-line interface, officially called the IIS Administration Script Utility. Its short name is adsutil.vbs and you’ll find it in C:\inetpub\adminscripts, or else in %SystemRoot%\system32\inetsrv\adminsamples, together with a host of other useful administrative scripts.

A word of caution though: Just like Apache conf files, the MetaBase is pretty crucial to the functioning of your Web server, so don’t ruin it. Back it up first.

Tip 8: Add spell checking to your URLs

Apache folks always brag about cool little tricks that Apache is capable of — especially because of the wealth of modules that can extend the server’s basic functionality. One of the coolest of these is the ability to fix URL typos using a module called mod_speling. Well, thanks to the folks at Port80 Software, it now appears that IIS admins can do this trick too, using an ISAPI filter called URLSpellCheck. You can check it out right on their site, by trying URLs like www.urlspellcheck.com/fak.htm, www.urlspellcheck.com/faq1.htm — or any other simple typo you care to make.

Tip 7: Rewrite your URLs

Cleaning your URLs has all sorts of benefits — it can improve the security of your site, ease migration woes, and provide an extra layer of abstraction to your Web applications. Moving from a ColdFusion to an ASP based site, for example, is no big deal if you can remap the URLs. Apache users have long bragged about the huge power of mod_rewrite — the standard Apache module for URL rewriting. Well, there are now literally a dozen versions of this type of product for IIS — many of them quite a bit easier to use than mod_rewrite, which tends to presume familiarity with regular expression arcana. Check out, for example, IIS ReWrite or ISAPI ReWrite. So brag no more, Apache partisans.

Tip 6: Add browser detection

There are a lot of ways to build Web sites, but assuming everybody has a certain browser or screen size is just plain stupid. Simple JavaScript sniff-scripts exist for client-side browser detection, but if you are an IIS user you can do better with a product called BrowserHawk from CyScape. The Apache world doesn’t really have something comparable to this popular, mature and well-supported product. Speaking of CyScape, they’ve recently added an interesting-looking related product called CountryHawk that helps with location detection, but so far I haven’t had the language- or location-sensitive content to warrant trying it out.

Tip 5: Gzip site content

Browsers can handle Gzipped and deflated content and decompress it on the fly. While IIS 5 had a gzip feature built-in, it is pretty much broken. Enter products like Pipeboost to give us better functionality — similar to what Apache users have enjoyed with mod_gzip. Don’t waste your bandwidth — even Google encodes its content, and their pages are tiny.

Tip 4: Cache your content

While I’m on the topic of improving performance, remember to make your site cache friendly. You can set expiration headers for different files or directories right from the MMC. Just right click on an item via the IIS MMC, flip to the “HTTP Headers” tab, and away you go. If you want to set cache control headers programmatically — or even better, let your site developers do it — use something like CacheRight. If you want to go further and add reverse proxy caching, particularly for generated content, use a product like XCache — which also throws in compression.

It might involve more time and expense to take full advantage of caching, but when you watch your logs shrink because they don’t contain tons of pointless 304 responses, and your bandwidth consumption drop like a stone, even while your total page views increase over the same period, you’ll start to understand why this particular tip was so important. Cache friendly sites are quite rare, but there is plenty of information available online about the enormous benefits to be had by doing it right: Check out Brian Davidson’s page, this nifty tutorial from Mark Nottingham, and what AOL has to say on the subject.

Tip 3: Tune your server

Tuning IIS is no small topic — whole books and courses are dedicated to it. But some good basic help is available online, such as this piece from IIS guru Brett Hill, or this Knowledge Base article from Microsoft itself. However, if you don’t feel like getting your hands dirty — or can’t afford the time and expense of turning yourself into an expert — take a look at XTune, from the makers of XCache. It’s performance tuning wizards step you through the process of tuning your IIS environment, making expert recommendations along the way.

Tip 2: Secure your server with simple fixes

Sure people are going to attack sites, but you don’t have to be a sitting duck if you’re willing to make even a small effort. First off, don’t advertise the fact that you are running IIS by showing your HTTP server header. Remove or replace it using something like ServerMask — probably the best twenty-five bucks you’ll ever spend. You can go farther than this by removing unnecessary file extensions to further camouflage your server environment, and scanning request URLs for signs of exploits. There are number of commercial products that do user input scanning, and Microsoft offers a free tool called URLScan which does the job. URLScan runs in conjunction with IISLockDown, a standard security package which should probably be installed on every IIS server on the planet. These are simple fixes that could pay off big, so do them now.

Tip 1: Patch, patch, patch!

Okay, we in the IIS world do have to patch our systems and make hotfixes. However, as a former Solaris admin I had to do the same thing there, so I am not sure why this is a big surprise. You really need to keep up with the patches, Microsoft is of course the definitive source, but if you can also use the highly-regarded www.cert.org. Simply search on “IIS”.

Well there you have it: 10 tips for IIS admins to improve their servers. Some of the tips might become obsolete once IIS 6 is gold, but, for now at least, W2K and NT IIS admins should apply a few of these today and sleep a little better at night.

Matt Foley is a former Solaris sysadmin who was turned to the “darkside” and is now works for a large southern California hosting and Web agency. He quite likes Windows now in spite of himself.

A couple of weeks ago, I ran into a problem. I found this very nice looking color on a site while I surfed the net, and decided to use it on a graphic. After copying the hexadecimal from the source code, I didn’t know how get the RGB values for Photoshop from it. I thought, easy task, there should be a program for it, but to my very surprise I couldn’t find any. This small tutorial requires the Microsoft Calculator (If you don’t have it, install it over Control Panel/Software/Windows Setup/.).

First of all, I needed to switch the Calculator in “Scientific” mode, because otherwise there is no key for the hexadecimals. To switch, I just had to go to “View” and click on “Scientific”. After I did that, I had to make sure before I started to convert, that “Hex” was checked on the calculator.

The colors we use on the web, come in a certain format. It always looks something like: FF000A. In order to be able to convert it, I split it up in 3 parts: “FF”, “00″ and “0A”. From RGB we know, that every color has certain Red, Green and Blue values. Hexadecimals are not different from that. It has Red, Green and Blue values (In order.). In order to convert the Red value, I put “FF” in the Calculator (Make sure that “Hex” is check first.) and clicked at “Dec”, which stands for decimal. If you did it, you should see a “255″ appear in your calculator.

The next would be “00″, but this one is special, zero is always zero, it doesn’t matter how many zeros are there. That is why I dropped the zeros, and continue to “0A”. This time I just entered the “A” in the calculator and click at “Dec” again, and got a “10″. If the hex would have been “A0″, I would have entered “A0″ in the calculator, but when the zero is first, you do not need to do it. It is like if you enter 01 it is still 1.

“FF000A” in decimals (and RGB values) is 255, 0 and 10. Isn’t that neat and easy way, and it is completely free too. Yeah!

Have fun converting, and just in case: “Yes it works backwards too.” (RGB to Hex).