SQL Server Magazine December Reader Challenge Submission

Back in November I did something I had never done before. I submitted an entry for the monthly "Reader Challenge" contest SQL Server Magazine hosts in their "SQL Server Magazine UPDATE" e-mail newsletter.

I was a little disappointed when I did not win, but was happy to see the winning solution was the same as my own submission. Here is the question for December:

Manoj is a database developer for a company that develops business applications that use SQL Server 2000 as a database server. In Manoj's environment, the database that the applications use contains a table that stores sequential numbers used by various features. The table stores the name of the sequence and the next number. The schema of the table is shown in the following code:

USE tempdb
GO
CREATE TABLE dbo.Sequences ( SeqName varchar(30) NOT NULL PRIMARY KEY, NextNum
bigint NOT NULL DEFAULT 0)
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #1', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #2', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #3', DEFAULT )
GO

The database also contains a GetNextSeqNum stored procedure, which the applications use to get the next number in a particular sequence. The stored procedure is shown in the following code:

USE tempdb
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30), @NextNum bigint =
NULL OUTPUT)
AS
BEGIN

BEGIN TRANSACTION

SET @NextNum = (SELECT NextNum FROM dbo.Sequences WHERE SeqName = @SeqName)

UPDATE dbo.Sequences
SET NextNum = NextNum + 1
WHERE SeqName = @SeqName
COMMIT TRANSACTION

END
GO

When Manoj tests the stored procedure with concurrent SQL Server connections, he notices that some of the calls encounter deadlocks and fail. Modify the stored procedure logic to help Manoj resolve the deadlock problem.

Here is my solution to the problem:

CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30),
@NextNum bigint = NULL OUTPUT)
AS
BEGIN
UPDATE dbo.Sequences
SET @NextNum = NextNum, NextNum = NextNum + 1
WHERE SeqName = @SeqName
END
GO

The above stored procedure both fetches the current and updates the next sequence value in one operation. Testing this updated stored procedure with concurrent SQL Server connections verifies this new stored procedure eliminates the deadlock problem seen in the original stored procedure.

Since the updating and fetching occurs in the same statement, it is safe to remove the "BEGIN TRANSACTION" and "COMMIT TRANSACTION" statements from the stored procedure.

You can read about the winning submission at http://www.sqlmag.com/Article/
ArticleID/94572/sql_server_94572.html https://www.itprotoday.com/sql-server/converting-hexadecimal-string-varbinary-value.

IE 6 ComboBox shows through DIV bug

I discovered an annoying IE 6 bug the other day that had to do with div areas and combo boxes. Fortunately this bug is not present in IE 7 or Firefox, but since most people are still using IE 6 at this time, it is worth noting.

If you have a div area appear in front of a combo box, the combo box will still be visible even though it should be covered by the div area.

VB.NET COM Interface Issues

I am a fan of VB.NET, but sometimes, for no apparent reason, it lacks a feature that its brother C# supports. I recently ran across the following issues that affect VB.NET, but not C#.

Both VB.NET and C# allow you to create classes with COM interfaces. This allows unmanaged code (VB 6, C++, etc.) to interact with your VB.NET or C# class. This can be very useful for environments were you have old legacy code that must be still be maintained, but you would like to do new development in a .NET language.

Issue #1 – No COM Help Strings on Interface Properties
One of the things COM allows you to do is specify a help string for any just about anything in a type library. An easy way to view these help strings is to use the Object Browser in VB 6. (You can get to the Object Browser by pressing the F2 key in VB6.) VB.NET and C# support COM help string through the use of the DescriptionAttribute class. The example below shows how you use this class to add help strings to class methods and properties.

Public Property Name() As String
    <Description("This is a sample property.")> _
    Get
        Return mName
    End Get
    <Description("This is a sample property.")> _
    Set(ByVal value As String)
        mName = value
    End Set
End Property

<Description("This is a sample method.")> _
Public Sub SampleMethod()
End Sub

This is how the descriptions look in the VB6 Object Brower.

Property SampleProperty As String
    Member of VbComExample.SimpleSampleClass
    This is a sample property.

Sub SampleMethod()
    Member of VbComExample.SimpleSampleClass
    This is a sample method.

If you take a look at the library in OLE View, you will see a coclass with the name of your class and an interface with the same name as your class preceded by an underscore (_). My class was named SimpleSampleClass, so the automatically generated interface name was _SimpleSampleClass.

This technique works fine, but this technique is discouraged by Microsoft. In the “.NET Framework Developer's Guide” section of the Visual Studio 2005 help on the page titled “Qualifying .NET Types for Interoperation” the following guideline appears:

- Classes should implement interfaces explicitly.

Although COM interop provides a mechanism to automatically generate an interface containing all members of the class and the members of its base class, it is far better to provide explicit interfaces. The automatically generated interface is called the class interface.

Once again in the “.NET Framework Developer's Guide” section of the Visual Studio 2005 help on the page titled “Introducing the Class Interface” it states:

Caution
Using the class interface, instead of explicitly defining your own, can complicate the future versioning of your managed class.

The following example implements the same class shown in the example above, only this time explicitly defining the COM interface separately:

<ComVisible(True)> _
<Guid(SampleClass.InterfaceId)> _
<Description("This is the interface for the SampleClass class.")> _
Public Interface ISampleClass

    <Description("This will not be used.")> _
    Property Name() As String

    <Description("This is a sample method.")> _
    Sub SampleMethod()

End Interface

<Guid(SampleClass.ClassId)> _
<ClassInterface(ClassInterfaceType.None)> _
<Description("This is a sample COM class.")> _
Public Class SampleClass
    Implements ISampleClass
…
    Public Property Name() As String Implements ISampleClass.Name
        <Description("Sample property description.")> _
        Get
            Return mName
        End Get
        <Description("Sample property description.")> _
        Set(ByVal value As String)
            mName = value
        End Set
    End Property
    Private mName As String

    <Description("This string will not be used.")> _
    Public Sub SampleMethod() Implements ISampleClass.SampleMethod

    End Sub

End Class

This is how the descriptions look in the VB6 Object Brower.

Property Name As String
    Member of VbComExample.SampleClass

Sub SampleMethod()
    Member of VbComExample.SimpleSampleClass
    This is a sample method.

If you take a look at the library in OLE View, you will see a coclass with the name of SampleClass and an interface with the name ISampleClass.

The descriptions on the Name property are lost because there is no way to specify the DescriptionAttribute on the Get and Set declarations in a VB.NET interface. C# does not have this problem, because it includes the get and set keywords in its interface definitions.

The following is the same example in C#:

[ComVisible(true)]
[Description("This is the interface for the SampleClass class.")]
[Guid(SampleClass.InterfaceId)]
public interface ISampleClass
{
    string Name
    {
        [Description("Name property description.")]
        get;
        [Description("Name property description.")]
        set;
    }

    [Description("This is a sample method.")]
    void SampleMethod();
}

[ClassInterface(ClassInterfaceType.None)]
[Description("This is a sample C# COM class.")]
[Guid(SampleClass.ClassId)]
public class SampleClass : ISampleClass
{

…
    string ISampleClass.Name
    {
        [Description("This won't be used.")]
        get
        {
            return mName;
        }
        [Description("This won't be used.")]
        set
        {
            mName = value;
        }
    }
    private string mName;

    [Description("This won't be used.")]
    void ISampleClass.SampleMethod()
    {
    }
}

Unlike its VB.NET brother the C# version includes the helper string, even when the interface is explicitly defined. This issue is really just annoying, but the next one actually prevents one from explicitly defining an interface properly.

Issue #2 – Broken Currency Support on Interface Properties
Consider the following property declared in a class with an automatically generated COM interface:

Public Property Money() As <MarshalAs(UnmanagedType.Currency)> Decimal
    <Description("Currency Example.")> _
    Get
        Return mMoney
    End Get
    <Description("Currency Example.")> _
    Set(<MarshalAs(UnmanagedType.Currency)> ByVal value As Decimal)
        mMoney = value
    End Set
End Property

If you lookup the propget and propput methods for this property in OLE View, you will find the following definitions:

[id(0x00000003), propget, helpstring("Currency Example.")]
CURRENCY Money();
[id(0x00000003), propput, helpstring("Currency Example.")]
void Money([in] CURRENCY rhs);

As you can see the help string is successfully captured and the .NET Decimal datatype value is marshaled as a VB6 Currency datatype.

However, if you use the recommended approach and explicitly define your COM class interface, the marshaling will be incorrect for the propput method. The following example demonstrates this problem:

<ComVisible(True)> _
<Guid(SampleClass.InterfaceId)> _
<Description("This is the interface for the SampleClass class.")> _
Public Interface ISampleClass

    <Description("This will not appear")> _
    Property Money() As <MarshalAs(UnmanagedType.Currency)> Decimal

End Interface

<Guid(SampleClass.ClassId)> _
<ClassInterface(ClassInterfaceType.None)> _
<Description("This is a sample COM class.")> _
Public Class SampleClass
    Implements ISampleClass
…
    Public Property Money() As _
        <MarshalAs(UnmanagedType.Currency)> Decimal _
        Implements ISampleClass.Money
        <Description("Currency Example.")> _
        Get
            Return mMoney
        End Get
        <Description("Currency Example.")> _
        Set(<MarshalAs(UnmanagedType.Currency)> ByVal value As Decimal)
            mMoney = value
        End Set
    End Property
    Private mMoney As Decimal

End Class

If you lookup the propget and propput methods for the Money property of the ISampleClass interface in OLE View, you will find the following definitions:

[id(0x60020003), propget]
CURRENCY Money();
[id(0x60020003), propput]
void Money([in] wchar_t rhs);

The propget method is marshaled correctly, but the propput is marshaled as a wchar_t. In this situation not only is the helpstring missing, but the property will not work correctly. If you want to marshal a Decimal property as a Currency, it is impossible to implement your solution using VB.NET using the “recommended” approach.

C#, however, does not have this problem as demonstrated in the code sample below:

[ComVisible(true)]
[Description("This is the interface for the SampleClass class.")]
[Guid(SampleClass.InterfaceId)]
public interface ISampleClass
{
    decimal Money
    {
        [Description("Money property description.")]
        [return: MarshalAs(UnmanagedType.Currency)]
        get;
        [Description("Money property description.")]
        [param: MarshalAs(UnmanagedType.Currency)]
        set;
    }
}

If you lookup the propget and propput methods for the Money property of the ISampleClass interface in OLE View, you will find the following definitions:

[id(0x60020003), propget, helpstring("Money property description.")]
CURRENCY Money();
[id(0x60020003), propput, helpstring("Money property description.")]
void Money([in] CURRENCY rhs);

In the C# version, not only is the helpstring present, but the property is correctly marshaled.

It is issues like these that give VB.NET a bad rap.