tsJensen

A quest for software excellence...

SQL Server TOP vs MySQL LIMIT

I've recently begun to explore MySQL a bit more, mostly out of curiosity. Primarily I work with SQL Server 2005 in my day job but it seems prudent to know a bit more about some of the other databases servers and it's been a while since I dusted off MySQL.

Happily I found that MySQL AB has released a really great ADO.NET provider for MySQL.

If you have used the SQL Server specific ADO.NET provider, you'll easily make the transition to using this one.

But I digress. The reason for this posting was to permanently remind myself and anyone else making a transition between these two database engines that TOP = LIMIT but not in the same place. That is to say, the TOP keyword is used in SQL Server to limit the number of returned rows just prior to the column specifiers. The LIMIT keyword however is used at the end of the select statement. Thus:

SELECT TOP 1 * FROM MYTABLE WHERE FKID = 3

And for MySQL use:

SELECT * FROM MYTABLE WHERE FKID = 3 LIMIT 1;

Yes, I know. It ought to be obvious, but having a quick reminder here will help to seal this minor difference into my brain.

Value of Schema Driven XML Configuration

If you've written an ASP.NET application, you've already taken advantage of schema driven XML configuration perhaps without even realizing it. You may have even ignored the web.config file if you're just starting out with ASP.NET. Or you may have used it extensively without considering the usefulness of using such a construct in your own applications.

My day job involves writing a complex application that implements a myriad of business rules, nearly all of which non-coders need to be able to modify from time to time without bothering the development team. That means using XML. But not just any old XML. I'm talking about well formed, validated XML using a nice XSD schema file controlled by the development team.

I recommend the reader purchase O'Reilly's book XML Schema by Eric van der Vlist. It's been a most trustworthy companion. And then to make the use of this XML configuration dreamscape even easier, I recommend Christian Weyer's contract first tool. I use this extremely useful tool to generate the XAL (XML access layer) so I don't have to write it by hand.

With these two tools in hand, you can create something like this in Visual Studio:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="watchconfig" targetNamespace="http://example.com/v1001/watchconfig.xsd"
    elementFormDefault="qualified"
    xmlns="http://example.com/v1001/watchconfig.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="watchconfig">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="watchgroup" minOccurs="0" maxOccurs="unbounded" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:element name="watchgroup">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="watcher" minOccurs="0" maxOccurs="unbounded" />
            </xs:sequence>
            <xs:attribute name="id" type="xs:string" use="required" />
            <xs:attribute name="literalsconfigpath" type="xs:string" />
        </xs:complexType>
    </xs:element>
    <xs:element name="watcher">
        <xs:complexType>
            <xs:attribute name="sourcepath" type="xs:string" use="required" />
            <xs:attribute name="wippath" type="xs:string" use="required" />
            <xs:attribute name="outpath" type="xs:string" use="required" />
            <xs:attribute name="ssispath" type="xs:string" use="required" />
            <xs:attribute name="intakemap" type="xs:string" use="required" />
        </xs:complexType>
    </xs:element>
</xs:schema>

And an XML file like this:

<?xml version="1.0" encoding="utf-8" ?>
<watchconfig xmlns="http://example.com/v1001/watchconfig.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <watchgroup id="clientname" literalsconfigpath="H:\temp\literals_client">
        <watcher sourcepath="H:\temp\client\watch"
                    wippath="H:\temp\client\wip"
                    outpath="H:\temp\client\out"
                    ssispath="H:\temp\client\ssis"
                    intakemap="D:\Contracts\Code\map.xml" />
    </watchgroup>
</watchconfig>

And the nicely generated code like this:

/// <remarks/>
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "2.0.50727.42")]
[System.SerializableAttribute()]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType=true, TypeName="watchconfig")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://example.com/v1001/watchconfig.xsd", IsNullable=false, ElementName="watchconfig")]
[System.ComponentModel.TypeConverterAttribute(typeof(System.ComponentModel.ExpandableObjectConverter))]
public partial class Watchconfig
{

/// <remarks/>
private System.Collections.Generic.List<Watchgroup> watchgroup;

public Watchconfig()
{
}

public Watchconfig(System.Collections.Generic.List<Watchgroup> watchgroup)
{
this.watchgroup = watchgroup;
}

[System.Xml.Serialization.XmlElementAttribute("watchgroup", Order=0)]
public System.Collections.Generic.List<Watchgroup> Watchgroup
{
get
{
return this.watchgroup;
}
set
{
if ((this.watchgroup != value))
{
this.watchgroup = value;
}
}
}
}

/// <remarks/>
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "2.0.50727.42")]
[System.SerializableAttribute()]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType=true, TypeName="watchgroup")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://example.com/v1001/watchconfig.xsd", IsNullable=false, ElementName="watchgroup")]
[System.ComponentModel.TypeConverterAttribute(typeof(System.ComponentModel.ExpandableObjectConverter))]
public partial class Watchgroup
{

/// <remarks/>
private System.Collections.Generic.List<Watcher> watcher;

/// <remarks/>
private string id;

/// <remarks/>
private string literalsconfigpath;

public Watchgroup()
{
}

public Watchgroup(System.Collections.Generic.List<Watcher> watcher, string id, string literalsconfigpath)
{
this.watcher = watcher;
this.id = id;
this.literalsconfigpath = literalsconfigpath;
}

[System.Xml.Serialization.XmlElementAttribute("watcher", Order=0)]
public System.Collections.Generic.List<Watcher> Watcher
{
get
{
return this.watcher;
}
set
{
if ((this.watcher != value))
{
this.watcher = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="id")]
public string Id
{
get
{
return this.id;
}
set
{
if ((this.id != value))
{
this.id = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="literalsconfigpath")]
public string Literalsconfigpath
{
get
{
return this.literalsconfigpath;
}
set
{
if ((this.literalsconfigpath != value))
{
this.literalsconfigpath = value;
}
}
}
}

/// <remarks/>
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "2.0.50727.42")]
[System.SerializableAttribute()]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType=true, TypeName="watcher")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://example.com/v1001/watchconfig.xsd", IsNullable=false, ElementName="watcher")]
[System.ComponentModel.TypeConverterAttribute(typeof(System.ComponentModel.ExpandableObjectConverter))]
public partial class Watcher
{

/// <remarks/>
private string sourcepath;

/// <remarks/>
private string wippath;

/// <remarks/>
private string outpath;

/// <remarks/>
private string ssispath;

/// <remarks/>
private string intakemap;

public Watcher()
{
}

public Watcher(string sourcepath, string wippath, string outpath, string ssispath, string intakemap)
{
this.sourcepath = sourcepath;
this.wippath = wippath;
this.outpath = outpath;
this.ssispath = ssispath;
this.intakemap = intakemap;
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="sourcepath")]
public string Sourcepath
{
get
{
return this.sourcepath;
}
set
{
if ((this.sourcepath != value))
{
this.sourcepath = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="wippath")]
public string Wippath
{
get
{
return this.wippath;
}
set
{
if ((this.wippath != value))
{
this.wippath = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="outpath")]
public string Outpath
{
get
{
return this.outpath;
}
set
{
if ((this.outpath != value))
{
this.outpath = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="ssispath")]
public string Ssispath
{
get
{
return this.ssispath;
}
set
{
if ((this.ssispath != value))
{
this.ssispath = value;
}
}
}

[System.Xml.Serialization.XmlAttributeAttribute(AttributeName="intakemap")]
public string Intakemap
{
get
{
return this.intakemap;
}
set
{
if ((this.intakemap != value))
{
this.intakemap = value;
}
}
}
}

(Sorry, indentation got hosed, but you won't need it since you'll be generating this code with one click.)

 

 

 

 

IBM Going After MySQL or SQL Server 2005 Express with DB2 Express-C

I subscribe to CodeProject's newsletter and "product information" emails. Today I received an interesting missive with the headline: "Introducing the new DB2 Express-C". And I noted the "no charge edition" limits you 2 (dual core) processors, 4GB ram limit but with unlimited database size and number and unlimited users.

SQL Server 2005 Express is limited to one CPU and 1GB of ram with a max database size of 4GB. Hmm... I'll bite. I want to know more.

A little exploration reveals a nice article on the wiki about leveraging your MySQL skills. And there seems to be ample information on using DB2 with .NET.

Having never used DB2, I'm completely unaware of what I'm getting myself into, but I'm seriously considering diving into this latest addition to the free, and increasingly capable, database engines.

So I'm downloading now and I'll give it a whirl and report what I find back here. No promises on when.


 Followup - Who am I kidding. The install is HUGE and I have had no time to eval this thing. Toss another distraction overboard.

ASP.NET Theme Assignment

You learn something new every day. I just don't have time to blog about it every time. This one seemed significant enough.

I'm writing a little ASP.NET app to keep up my web skills since my day job keeps me busy cranking out back-end code. For the first time, I've tried playing with themes. I wanted to assign a theme based on a user configuration, so I unwittingly assigned it in the Page_Load event.

Bo no... Nasty little error message telling me I can't do that, so a bit of digging revealed it has to be done earlier in the life of the page.

protected void Page_PreInit(object sender, EventArgs e)
{
    this.Theme = GetTheme();
}

That did the trick.

A Kit for Writing Your Own Programming Language

I'm having fun. In the last couple of months, I've carved out about 20 hours of fun playing with some code and tools I found on www.devincook.com. If you're interested in the creation of programming languages, I want to encourage you to to check out that site and the code (link below) that I've derived and organized from code found on that site.

I even tried to share my enthusiasm with the .NET Users Group I like to attend, though as most attendees will confirm, my presentation was ill prepared, disorganized and rambled. Hopefully the 20 or so new attendees at the users group meeting will realize that my presentation is not representative of the excellent presentations the group is use to and will come back.

In the code you can download below, you'll find my first attempt at a language: TROLL — Tyler's Really Obtuse Little Language. It's based on the concepts in the sample SIMPLE interpreter I downloaded from Devin Cook's site under the C# Engines (I recommend Morozov's engine).

Why would anyone want to write their own programming language, you ask. Especially when we have C#, VB.NET, Boo and yes, even Java. Yeah, yeah, there's C++ and D as well, and a hundred others.

So why one more programming language? Because now you can.

It's a great learning experience. You'll learn about BNF, LALR and other fun acronymns. What's more, you may find it an empowering experience to write your own programming language and see your own made-up code executed. You might even find a real use for creating a 4th+ generation language for a specific, vertical solution.

Well, whatever your reason, download the code, download Devin Cook's GOLD Builder and have some fun.

Download code (659KB)

ManagedThreadPool - Managing the Thread Pool

Where's the response?
I couldn't figure out why I was not getting an immediate response. I ran through my code several times before I realized I was missing a key test at a crucial decision making point. I was failing to check whether or not the ManagedThreadPool in my application could get to my request now or whether the request would be pooled.

ManagedThreadPool.ActiveThreads < ManagedThreadPool.MaxThreads - 2

Simple as that. A simple test in a more complex if statement. Why the - 2? Well, I needed a margin to allow for the fact that other threads would be jamming their own jobs in. So I figured a margin of 2 unused threads would give me that. We'll see how it does in the real world.

Lesson Learned
If you plan to use a thread pool, be sure to pay attention to how many babies are in the water at the moment you decide to jump in. Otherwise, you may not get what you expect when you expect, especially in a system like I have been working on where hundreds or even thousands of items might be queued up.

(Note: This is being reposted because I inadvertently allowed trackbacks and the previous post got hammered with spam trackbacks and dasBlog would log me out when I tried to go to the post to delete them. Go figure...)

Image MIME Type from the Byte[]

A little puzzle came up today. Fetch an image out of a database and stream it down to the browser. The trick: we don't know what type of image is stored in the database. So I borrowed a little from our Python friends and this is what I ended up with.

internal static class ImageTypeFinder
{
    internal static string GetMimeType(byte[] image)
    {
        string retval = "image/jpeg";
        string imgtype = GetImageType(image);
        switch (imgtype)
        {
            case "bmp":
                retval = "image/bmp";
                break;
            case "gif":
                retval = "image/gif";
                break;
            case "tif":
                retval = "image/tiff";
                break;
            case "png":
                retval = "image/png";
                break;
        }
        return retval;
    }

    internal static string GetImageType(byte[] image)
    {
        int len = image.Length;
        if (len > 32) len = 32;
        string first32 = ASCIIEncoding.ASCII.GetString(image, 0, len);
        return GetImageType(first32);
    }

    internal static string GetImageType(string first32)
    {
        string retval = string.Empty;
        if (first32.StartsWith("GIF87a") || first32.StartsWith("GIF89a"))
            retval = "gif";
        else if (first32.StartsWith("MM") || first32.StartsWith("II"))
            retval = "tif";
        else if (first32.Substring(6, 4) == "JFIF" || first32.Substring(6, 4) == "Exif")
            retval = "jpg";
        else if (first32.StartsWith("BM"))
            retval = "bmp";
        else if (first32.Substring(1, 3) == "PNG")
            retval = "png";
        return retval;
    }

}

It's a bit of an esoteric puzzle, but if you're trying to solve this one, the above code should help.

Take a POST for REST without a Form in ASP.NET

Some time ago and again today, I had occasion to write an ASP.NET page that had no form in the .ASPX page but would accept and handle POST 'ed data. This was in an effort to support a REST-like interface for non-ASP.NET developers. Here's the way it turned out.

The .ASPX page looks something like this:

<%@ Page Language="C#"
 
AutoEventWireup="true"
  CodeBehind="extract.aspx.cs"
  Inherits="KeyExtractWeb.extract" %>

There is nothing else in the file. Now the code behind looks like this:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace KeyExtractWeb
{
    public partial class extract : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string alldata = string.Empty;
            using (StreamReader sr = new StreamReader(this.Request.InputStream))
            {
                alldata = sr.ReadToEnd();
            }

            //convert to strings - assumes URL encoded data
            string[] pairs = alldata.Split('&');
            NameValueCollection form = new NameValueCollection(pairs.Length);
            foreach (string pair in pairs)
            {
                string[] keyvalue = pair.Split('=');
                if (keyvalue.Length == 2)
                {
                    form.Add(keyvalue[0], HttpUtility.UrlDecode(keyvalue[1]));
                }
            }

            if (alldata.Length > 0 && this.Request.HttpMethod.ToUpper() == "POST")
            {
                if (form["text"] != null)
                {
                    //TODO - do something with the data here
                }
                else
                    Response.Write("*** 501 Invalid data ***");
            }
            else
                Response.Write("*** 599 GET method not supported. ***");

            Response.End();
        }
    }
}

Well, there you have it. There are probably better ways to do this, but I didn't find one.