An ADO and VBScript Adventure
Saturday, October 28, 2006
In a recent work-related project I was asked to provide a simple VBScript code snippet that called a SQL stored procedure. No one knew what the stored proc looked like or even how it worked. To make matters worse, I am neither a T-SQL nor VBScript guru by any degree. The task of writing that small bit of code is the topic of this article. If you are a VBScript or ADO guru, you can stop here as you'll probably not learn anything. You might get a laugh out of it though. ;-) If you are new to writing ADO code in VBScript (as I was), read on as I'll show you some pitfalls and things I didn't know (but unfortunately hit).
The stored procedure in question lived on a SQL Server 2000 machine, and to my surprise, contained over 100 parameters (all of which contained "default" values but 1)! The only required parameter was a GUID and was easily found in another table. So I figured I would test the stored proc out first in Microsoft's SQL Query Analyzer and make sure it worked as described. With my very little SQL experience, I just created the syntax that would call the stored proc and pass in 1 GUID and 99 NULL values. I thought that a NULL value parameter would cause the stored proc to use its default value. Oh how I was wrong! Instead of using default values, NULL was treated as an acceptable value and therefore passed in to the remaining 99 parameters. You can imagine the damage that was done. I basically had used this stored proc to set every one of this table's foreign keys to NULL. So I pretty much destroyed the database. Nice... After many database restores, a coworker explained to me the problem with using NULL. The correct thing to do was to pass the T-SQL keyword DEFAULT as the parameter value and the stored proc will then use its default values (if any). Man, I felt like an idiot. What programmer thinks that NULL wouldn't be an acceptable value? ;-) Even though I was thoroughly embarrassed, I figured the frustration was over and that I was golden. So I proceeded to write the sample VBScript which would do the same thing as the code in Query Analyzer. Here's a subset of the code I wrote (error checking removed for simplicity):
Const adCmdStoredProc = 4
Const adGUID = 72
Const adParamInput = 1
Const adVarChar = 200
Const MyGuid = "1A8A3DA2-1564-40FA-8F4B-4B27B5B2BAD5"
'# Create your connection object to the SQL database
Dim objConnect
Set objConnect = CreateObject("ADODB.Connection")
objConnect.ConnectionString = "Provider=SQLOLEDB; " &_
"Data Source=MyServer; " &_
"Initial Catalog=MyDb; " &_
"Trusted_Connection=yes"
'# Setup your command object that will call the stored proc
Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnect
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "MyStoredProcName"
'# Supply the required parameters to the stored proc
Dim objParam1
Set objParam1 = objCommand.CreateParameter("@Param1", _
adGUID, _
adParamInput, _
, _
MyGuid)
objCommand.Parameters.Append objParam1
Dim objParam2
Set objParam2 = objCommand.CreateParameter("@Param2", _
adVarChar, _
adParamInput, _
32, _
"BlahBlah")
objCommand.Parameters.Append objParam2
objCommand.Execute
'# Tada! It should work right?
As you can see, I created ADODB.Connection and ADODB.Command objects. I then passed in only those values that I needed (just 2 in this case) for the stored proc to execute. The first of which was the only parameter that was required by the stored procedure. When I ran the code, all I did was foul up the database again. The parameters I supplied were passed in as the first and second parameters to the stored proc. It didn't even matter that I supplied the name of parameter as well. Wait, how can that be?!! Well of course, I was fuming after this. The deadline had passed and people were beginning to wonder why this little snippet of code wasn't complete yet. Drama sucks! After a few cigarettes and some time to calm down, I sat down with my favorite search tool. Google. So, as usual, I started googling, googling and more googling. On about the 5th try, I found a hit about 20 links down (who looks down 20 hits on a Google search?) that showed how to specify your Command.Parameters collection when you want to use the default value for some of the parameters. Here's the fix:
...
Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnect
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "MyStoredProcName"
objCommand.Refresh
...
objCommand.Parameters("@Param1") = MyGuid
objCommand.Parameters("@Param2") = "BlahBlah"
objCommand.Execute
The Refresh routine queries the database for the parameter list before you make your query. This means that you don't have to "create" any parameter objects for the Parameter collection that is in the Command object. All you have to do is assign a value to the parameters that don't define a default value. Everything else not assigned a value to will not be set and therefore the stored proc will use the default values it defined. So as usual, I learned something new. Oh well, I'm not a big fan of VBScript or stored procedures anyway. In fact, I think languages that aren't compiled were created by the Devil. ;-) Just kidding, they have their place and uses I guess. Anyways, here's what I can pass on to you (unless you just read this for a good laugh):
NULLis a valid value type when passed into a stored procedure parameter and therefore does override the default value (if one is defined). In a tool like Query Analyzer, you need to specify either theDEFAULTkeyword, or the@ParamName=DEFAULTalternative syntax. I'm sure there might be other ways to do this, but those are the ones I found.- In VBScript, make sure you query the database for a list of parameters before you try and pass them to a stored procedure (unless there are no parameters of course). You do this with the
ADODB.Command.Parameters.Refreshroutine, and then you will be able to supply only those parameters you need and the rest will be treated as having no value at all. A side benefit is not having to write the code that creates and defines those parameter objects.
This might have been a silly article, but maybe by some freak chance I'm not the only person in the world who didn't know that stuff. Hopefully somebody else will find it useful. Many more articles like this and people won't read this blog anymore. ;-) Until next time...
- Gilemonster
Labels: General
posted by Gilemonster @ 8:26 PM,
, links to this post
.NET Generics and Native Code Sharing
Tuesday, October 17, 2006
Sorry for the break in articles but work has been extremely time consuming. Hopefully, I can find some time to write more articles. Anyways, now for the goodies. If you are like you me, you have been playing ".NET catch-up" trying to read as many version 2.0/3.0 .NET articles as quickly as possible. I recently have been reading up on Generics in the MSDN's C# Programming Reference and it is a very exciting addition to the .NET framework. Specifically I want to talk about how it reduces code bloat through native code sharing. I'll start with a quick overview of Generics, how it is compiled, and then how code sharing is provided at runtime.
Generics who?
Generics is a language extension that allows programmers to parameterize the use of types. It allows classes, interfaces, structs, delegates, and methods to parameterize the data they manipulate and store (if you've ever used C++ templates the syntax will appear familiar). When declaring the constructs just mentioned, you simply replace the type of object being manipulated with what is called a type parameter. You are also allowed to specify more than one type parameter if needed. Here is a simple example of a generic class in C#:
using System;
using System.Text;
using System.Collections.Generic;
namespace WriteBetterCode
{
public class MySimpleStack<T>
{
private int m_StackSize = 0;
private int m_StackTopIndex = 0;
T[] m_StackItems;
public MySimpleStack(uint stackSize)
{
if (0 == stackSize)
{
m_StackSize = stackSize;
m_StackItems = new T[m_StackSize];
}
else
{
throw new InvalidOperationException(
@"MySimpleStack must have a size greater
than zero!!!");
}
}
public T Pop()
{
m_StackTopIndex--;
if(m_StackTopIndex >= 0)
{
return m_StackItems[m_StackTopIndex];
}
else
{
m_StackTopIndex = 0;
throw new InvalidOperationException(
"You popped an empty MySimpleStack!!!");
}
}
public void Push(T newStackItem)
{
if(m_StackTopIndex >= m_StackSize)
{
throw new InvalidOperationException(
"The MySimpleStack is full!!!");
}
m_StackItems[m_StackTopIndex] = newStackItem;
m_StackTopIndex++;
}
}
public class TestMySimpleStack
{
static void Main()
{
try
{
MySimpleStack<int> stackOfInts =
new MySimpleStack<int>(5);
stackOfInts.Push(10);
stackOfInts.Push(15);
int intValue = stackOfInts.Pop();
}
catch (InvalidOperationException e)
{
Console.WriteLine(e.Message);
}
}
}
}
// Figure 1 : MySimpleStack<T> generic class declaration
// and instantiation
As you can see in the above example, the MySimpleStack class uses the type parameter T to specify that it is a generic type. In order to instantiate the generic type, you must replace that identifier with a type argument (e.g. int in the above example). The resulting type is called a constructed type (e.g. MySimpleStack<int>), of which there are two types:
- Closed constructed type: a constructed type that does not contain any type parameters (e.g.
MySimpleStack<int> stackOfInts). - Open constructed type: a constructed type that contains at least one type parameter (e.g.
MyOpenType<int, U> stackOfOpenTypes).
The .NET Framework also provides a number of built-in generic types for you. You can use those types in your code by referencing the System.Collections.Generic namespace (click here for a full list of generic types provided by the framework). One of the built-in generic types is the Queue<T> class. Here is an example of how to use it:
using System;
using System.Text;
using System.Collections.Generic;
namespace WriteBetterCode
{
public class TestQueue
{
static void Main()
{
Queue<int> myQueue = new Queue<int>();
myQueue.Enqueue(5);
int queuedVal = myQueue.Dequeue();
}
}
}
So how is generic code compiled? At compile-time, the generic type is converted into IL and metadata, which contain additional information specifying that a type parameter is being used (this was a required update to the CLR in version 2.0 of the framework so that Generics could supported). Other than that, a generic type is compiled just like any other type.
Native Code Sharing
Now we can get into the interesting stuff with native code sharing. This is when two or more “compatible” method instantiations point to the same x86 code. So the shared code is the method code (e.g. myQueue.Enqueue(int)) that operates on a particular type, not the instances themselves (e.g. myQueue). They are still independent as expected.
Of course, native code sharing is provided at runtime by the JIT compiler and it reacts differently for value and reference type arguments. At runtime, when the JIT is fed a type argument that is a value type, it replaces the type parameters in the IL with the actual value type and then generates the native code. So your runtime generated type becomes a collection where the value types are actually contained in the collection. The first time the JIT compiler is fed a type argument that is a reference type, it replaces the type parameters in the IL with Object and then compiles that into native code. The native code sharing comes into play if the JIT is given a type argument for which it has already generated native code.
When the JIT is fed a type argument, that is a value type, for which it has already generated native code (an exact value type match), a reference to that native code is returned. It can do this because the JIT compiler keeps track of previously generated value-type-specific code. Note that native code sharing does not apply globally to value types in general. This means that all value types do not share a single implementation. Conversely, reference types do share a single implementation of the JIT generated native code. Since Object replaces the type argument in the IL code, this code can be shared for any further requests (b/c reference sizes are the same).
So you might be wondering why there is single implementation being shared for reference types, but not for value types. The reasoning for the reference type implementation is obvious. The size of a reference is always the same for any object and therefore the generated native code (that operates on the reference type) can be shared. Value types don't have the luxury of always being the same size. Even if two value types are the same size, you still can't share native code because the operations on different value types are not always the same. The only way to ensure that all types share a single implementation is to box/unbox all value types at runtime. The performance hit from this type of operation is detrimental to the performance of Generic types and therefore was intentionally left out of the .NET framework 2.0 implementation. But, if you have two constructed types whose type arguments are an exact value type match, they do share their method implementations at the native code leve. Here's a quick example:
using System;
using System.Text;
using System.Collections.Generic;
namespace WriteBetterCode
{
public class TestQueue
{
static void Main()
{
Queue<int> myQueue1 = new Queue<int>();
Queue<int> myQueue2 = new Queue<int>();
myQueue1.Enqueue(5);
myQueue2.Enqueue(10);
int queuedVal = myQueue1.Dequeue();
}
}
}
The two Queue variables above share method implementations generated by the JIT compiler (e.g. Enqueue(<int>)).
C++ templates, do not have any type of native code sharing. For example, if you instantiate 2 vector<int> objects, the method code that operates on a vector<int> object is generated separately twice and is a part of your program. This is the code bloat that I was talking about earlier. I'm sure you can imagine a scenario where your own STL applications could have a much smaller memory footprint if native code sharing was supported.
- Gilemonster
Labels: dotNet
posted by Gilemonster @ 6:25 PM,
, links to this post
