SQL Funny Business

Today at work we were talking about mixing old (table, table where join criteria) and new (table join table on join criteria) style joins in the same query. So that got me to thinking about what ON really does. I ran the following queries against the infamous Northwind Database:

-- The modern style join:
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON od.OrderID = o.OrderID 
WHERE o.CustomerID = 'SUPRD'

-- The classic style join:
SELECT *
FROM dbo.Orders o, [Order Details] od
WHERE od.OrderID = o.OrderID
 AND o.CustomerID = 'SUPRD'

-- Inverted modern join (join criteria in WHERE, selection criteria in ON):
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON o.CustomerID = 'SUPRD'
WHERE od.OrderID = o.OrderID

All three queries gave me the same results set.

Is the ON clause just a different place to shove selection criteria (a phantom where). I like the modern style. You can put all the join information together. You could mix things up when you practice Job Security Based Programming.

I think it would be fun to have a obscure SQL programming competition, like the obscure C programming competitions in the days of yore.

Is it too late to wrap it into a function?

Today I was refactoring several properties in a Business Class from int to int?. I was in a hurry, so I was implementing the 10 properties using cut and paste inheritance (as if that is ever really faster).

After the 8th or 9th properties, the light went off in my mind: I should write a couple of functions to do the hard work and call the functions from the property get and set functions. Was it too late to to do it right?

I said NO.It did take me longer to write the functions than it would have taken to cut and past the 2 remaining properties. However, the code is easier to read, validate and change.

I guess I justify my earlier sloppy coding time as an opportunity to think of the right way. During the first go round I was planning to do the second one; I needed to do it wrong to see how to do it right.

New Team, New Ways of Thinking

I have recently started working on a new team.

The new team uses a different architecture, different naming conventions, file organization, source control, etc. The new team is uptight about different things than the old team.

I am learning to think differently, reevaluate my old ways and adapt the new things from the new team.

In the long run, I think it is a good thing; I need change to avoid ruts and learn new ways of approaching problems

Access 2007 Trap: Life without @@identity()

I came across some interesting behavior in Access 2007 that tripped me up for a little while.I needed to add a record to a table and then get the primary key value of the newly added record.

I wrote the code that I expected to work and I always got back the same number for the primary key every time I ran the code; the value of the primary key value of the first record.But I wanted the primary key value of the last record, the record that I just created. So, I added .MoveLast to get the last record.

   Dim rs As Recordset2
   Dim recordId As Integer
   Set rs = Application.CurrentDb.OpenRecordset("Table_1")
   With rs
       .AddNew
       !field_1 = "Field_1"
       !field_2 = "Field_2"
       ' I expected the the primary key value to be loaded here:
       .Update
       ' When you open a recordset, there is an implied "MoveFirst" call
       ' For whatever reason, Access doesn't refresh values after writing
       ' the record:
       .MoveLast
       ' Without the MoveLast, you get the record_id of the first record:
       recordId = !record_id
       .Close
   End With
   Set rs = Nothing

I guess the thing that screwed me up is that I expected all of the fields in the current record of a RecordSet2 point to the same record. In my mind, when I call Update, the value of the primary key should be retrieved and ready for me to reference.

In ADO.NET, the DataSet has the AcceptChanges() method. This is logical to me because a DataSet is disconnected. I guess a Recordset2 is "loosly" connected.

Access continues to weird me out.

Stackoverflow Flair without the image

Scott Hanselman tweeted about how cool it would be to have a Stackoverflow flair without a tje gravatar. This is my solution using CSS

<html>
<head>
    <title>Stack Overflow Flair Demo</title>
    <style type="text/css">
.valuable-flair
{
    background-color: #ffffff;
    font-family: Arial, Helvetica, sans-serif;
    font-size: 12px;
    height: 50px;
    padding: 3px;
    width: 50px;
}
.gravatar
{
 display: none;
}

.valuable-flair .badge1
{
    color: #ffcc00;
}
.valuable-flair .badge2
{
    color: #c0c0c0;
}
.valuable-flair .badge3
{
    color: #CC9966;
}

</style>
</head>
<body>
<script src="http://stackoverflow.com/users/flair/3819.js?theme=none" type="text/javascript"></script>
</body>
</html>

ASP.NET Cookieless sessions and JQuery AJAX

I was trying to get some jquery ajax calls to work after converting a site to cookie-less sessions (don't ask). I noticed the following code wouldn't work:

$.post("/bin/getsomedata.dll", {'id': id},
   function(data)
   {
       doSomething(data);
   }
);

I changed it to:

$.post("<%=Response.ApplyAppPathModifier("/bin/getsomedata.dll")%>", {'id': id},
   function(data)
   {
       doSomething(data);
   }
);

and all was groovy

I also noticed that when I turn off cookieless sessions, everything still works. It appears that Response.ApplyAppPathModifier() only alters the URL when I am in cookieless sessions

Photoviewer

Today I needed to write a pop up form that would show any image without any extra IE (or Firefox, Chrome, Safari, etc.) toolbars, etc. To get a web form to display an arbitrary image, I have a simple page that takes the URL of the image in the query string. The web form takes that string and uses it to set ImageUrl of an asp.image control.

photoviewer.aspx:

<%@ Page Language="C#" EnableViewState="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">    private void Page_Load()
    {        string file = Request.QueryString["file"];
        if (file != null)
            myImage.ImageUrl = Server.UrlDecode(file);
    }
</script>
<html>
<head>
    <title>My Image Viewer</title>
</head><body>
    <form id="Form1" method="post" runat="server">
    <table style="border:0;">
        <tr>
            <td>
                <asp:Image runat="server" ID="myImage" EnableViewState="false" />
            </td>    
        </tr>
        <tr>
            <td>
                <input type="button" onclick="javascript: window.close();" 
                    value="close" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

To get rid of extra browser features and to size the popup, I call window.open() I set the height and width to values that will ensure that I have enough room for the image (I can use to get the dimensions of an image and add some buffer around the image so the popup isn’t all picture). I suppress status, toolbar, menubar. Location and scrollbars by setting them all to “0”.

feature_win.js

// declared outside of the scope of the function so it will persist on the page:
var newWindow;
function featureWin(height, width, url)
{
    var lHeight = height;
    var lWidth = width;
    
    if (newWindow && !newWindow.closed) 
    {
      newWindow.close()
    }
    newWindow = window.open(url, null, 'height=' + height +  
       ',width=' + width + ',status=0,toolbar=0,menubar=0,location=0,scrollbars=0');
}

Here we put it all together.

default.html

<html>
<head>
    <title>Test My Image Viewer</title>
    <script type="text/javascript" src="feature_win.js"></script>
</head>
<body>
<a href="javascript: featureWin(825, 1050, 'photoviewer.aspx?file=Garden.jpg');">
    Garden</a>
</body>
</html>