tjanczuk/edge-sql — pull request #6 by atifaziz (2014-05-08 17:19:39+00:00)
Title: NULL support for command input parameters

Consider the following CoffeeScript that invokes a SQL script accepting a parameter that can be a string or `NULL`:

```coffee
edge = require 'edge'

getObjects = edge.func 'sql',
->
###
SELECT name
FROM sys.objects
WHERE @name IS NULL OR name LIKE @name
ORDER BY name
###

argv = process.argv[2..]

getObjects
name: argv[0]
(err, objs) ->
if err?
console.error err
else
console.log (obj.name for obj in objs).join('\n')
```

When the script is run without any arguments, the `@name` parameter should be `NULL`. Instead the program crashes with the error:

```
System.Data.SqlClient.SqlException: The parameterized query '(@name nvarchar(4000))SELECT name
FROM sys.objects
' expects the parameter '@name', which was not supplied.
```

The full error is:

```
System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: The parameterized query '(@name nvarchar(4000))SELECT name
FROM sys.objects
' expects the parameter '@name', which was not supplied.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at EdgeCompiler.d__c.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at EdgeCompiler.<>c__DisplayClass4.<b__0>d__6.MoveNext()
--- End of inner exception stack trace ---
---> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): The parameterized query '(@name nvarchar(4000))SELECT name
FROM sys.objects
' expects the parameter '@name', which was not supplied.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at EdgeCompiler.d__c.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at EdgeCompiler.<>c__DisplayClass4.<b__0>d__6.MoveNext()
ClientConnectionId:0f49c7f9-4143-41fd-b88b-52a141f7b829<---
```

The change proposed here fixes the problem by translating incoming `null` for a command parameter to always mean `NULL` (via [`DBNull.Value`](http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx)) as the least element of surprise.

---
For the CoffeeScript illiterates, the compiled JavaScript of the demo CoffeeScript is:

```javascript
(function() {
var argv, edge, getObjects;

edge = require('edge');

getObjects = edge.func('sql', function() {
/*
SELECT name
FROM sys.objects
WHERE @name IS NULL OR name LIKE @name
ORDER BY name
*/

});

argv = process.argv.slice(2);

getObjects({
name: argv[0]
}, function(err, objs) {
var obj;
if (err != null) {
return console.error(err);
} else {
return console.log(((function() {
var _i, _len, _results;
_results = [];
for (_i = 0, _len = objs.length; _i < _len; _i++) {
obj = objs[_i];
_results.push(obj.name);
}
return _results;
})()).join('\n'));
}
});

}).call(this);
```

Also assuming Edge with issue tjanczuk/edge#132 fix applied.