Saturday 16 June 2007

Generating JSON in ASP/VBScript

JSON is damn cool - we've been using it for a while now at work. Unfortunately though, VBScript lacks the OO functionality to properly handle converting JSON to and from object structures, so we've been painfully generating JSON responses manually from the server via string building.

However, after mixing JScript with VBScript (the other day), I got thinking, had a little play around, and realised that wee bit of server-side JS can make JSON production super easy:

<%@ LANGUAGE=VBScript LCID=5129 %>
<%
 
Dim sJSON, package, book, author
 
Set package          = JsInterop.Object("VbInterop.Json.RpcResponse")
Set package.result      = JsInterop.Object("VbInterop.Json.BooksCollection")
Set package.result.books  = JsInterop.NewArray()
 
For i = 1 to 3
 
  Set book      = JsInterop.Object("VbInterop.Json.Book")
  book.title      = "Slack"
  book.year      = 2002
  book.available    = True
  book.publisher    = "BroadwayBooks"
 
    Set book.authors  = JsInterop.NewArray()
 
    Set author      = JsInterop.Object("VbInterop.Json.Author")
    author.firstName  = "Tom"
    author.lastName    = "DeMarco"
    book.authors.push(author)
 
  package.result.books.push(book)
 
Next
 
Response.Write package.toJSON()
 
%>
<script language="jscript" runat="server" src="vb-interop.js"></script>
<script language="jscript" runat="server">
 
// Books collection
VbInterop.Json.BooksCollection = function () {
  this.books      = OBJ;
};
 
// Book class
VbInterop.Json.Book = function () {
  this.title      = STR;
  this.year      = NUM;
  this.available    = BOOL;
  this.authors    = RG;
  this.publisher    = STR;
};
 
// Author class
VbInterop.Json.Author = function () {
  this.firstName    = STR;
  this.lastName    = STR;
};
 
</script>


I don't really understand the workings of JS and VBs interop, but in a very rough sense it seems that:
a) Any JS features implemented via the IDispatchEx interface (see http://blogs.msdn.com/ericlippert/archive/2004/10/07/239289.aspx) cannot be used in VB.
b) JS/VB arrays do not play dice (see http://blogs.msdn.com/ericlippert/archive/2003/09/22/53061.aspx).

So, the two key features needed for JSON generation in a 'nice' fashion are:
1. New object creation (which requires a constructor) and
2. Arrays

#1 can easily be achieved by using an intermediary JS function to instantiate new objects from JS, then return them to VB:

JsInterop.Object = function ( sClass ) {
  var obj      = eval('new '+ sClass + '()');
  obj.toJson    = Object.prototype.toJSONString;
  return obj;
}


#2 is slightly more fiddly. Instead of an actual array, create an object with a public .push() method and a private array variable:

JsInterop.NewArray: function () {
  return new VbInterop.Array();
}
 
VbInterop.Array = function () {
 
  var rg = [];
 
  this.push = function( variant ) {
    rg.push(variant);
  }
 
  this.toJson = function () {
    return rg.toJSONString();
  }
}

Pop() and Get/Set methods could also be added here if desired.

Note the augumentation of a .toJson() method that utilizes Douglas Crockford's json.js jsonification. This is where the disco happens.

Suffice to say it's all pretty simple, and the code is self-explanatory, so take a look if you're interested:

VB-INTEROP.JS

//  vb-interop.js
//  Utility code to build an object structure in VBScript that can be serialized into JSON
 
//  VBScript-facing static class
//  An intermediary for new object creation, required as we cannot directly instantiate JS classes from VBScript
var JsInterop = {
 
  NewObject: function () {
    var obj      = {};
    obj.toJson    = Object.prototype.toJSONString;
    return obj;
  }
 
  , NewArray: function () {
    return new VbInterop.Array();
  }
 
  , Object: function ( sClass ) {
    var obj      = eval('new '+ sClass + '()');
    obj.toJson    = Object.prototype.toJSONString;
    return obj;
  }
};
 
//  JScript-facing static class
//  Interop namespace
var VbInterop = {};
 
//  Seperate namespace for JSON class definitions
VbInterop.Json = {};
 
//  VBScript-compatible Pseudo-array class
//  Note that the actual array variable *must* be private, if it is public then VBScript will setup-the-bomb on it and launch all ships..
VbInterop.Array = function () {
 
  var rg = [];
 
  this.push = function( variant ) {
    rg.push(variant);
  }
 
  this.toJson = function () {
    return rg.toJSONString();
  }
};
 
//  Simple constants for clean data-typed class definitions
var STR   = '';
var OBJ   = {};
var BOOL   = false;
var RG     = {};     // We are using objects to emulate arrays in VBs
var NUM   = -1;
var DT    = '';     // No literal syntax exists for dates, so we will treat them as strings.
 
// Example JSON-RPC Response class
VbInterop.Json.RpcResponse = function () {
  this.id        = NUM;
  this.result      = OBJ;
  this.error      = OBJ;
};


JSON.JS MODIFICATIONS

            object: function (x) {
                if (x) {
                    if (x instanceof Array) {
                        return s.array(x);
                    }
 
                    /* begin vb-interop.js addition */
                    if (VbInterop && x instanceof VbInterop.Array) {
            return x.toJson();
          }
          /* end vb-interop.js addition */


INTEROP-TESTS.ASP

<%@ LANGUAGE=VBScript LCID=5129 %>
<%
 
' VALID
Response.Write    test1.property
 
' INVALID (Class not defined)
'Set foo = New test2
 
' VALID
Set foo = test2a()
Response.Write foo.property
Response.Write test3.property.[0]
Response.Write test3.property.[1]
 
' INVALID (Property/method not supported)
'i = 0
'Response.Write test3.property.[i]
 
' VALID
Set foo = test4()
Response.Write foo.get(0)
 
' VALID
Set foo = test5()
Response.Write foo.get(0).bar
 
%>
<script language="jscript" runat="server">
 
var test1 = {
  property: 'test1'
}
 
var test2 = function () {
  this.property = 'test2';
}
 
var test2a = function () {
  return new test2();
}
 
var test3 = {
  property: ['test3', 'test3b']
}
 
var test4 = function () {
  return new test4a();
}
var test4a = function () {
  var property = ['test4'];
 
  this.get = function (ix) {
    return property[ix];
  }
}
 
var test5 = function () {
  return new test5a();
}
var test5a = function () {
  var property = [
    { bar: "test5" }
  ];
 
  this.get = function (ix) {
    return property[ix];
  }
}
 
</script>


Finally, muchos graci to Eric Lippert for helpful responses to ignorant queries I had in on all this.

Convert UTC dates into 'normal' Dates

I recently had to import a Basecamp XML dump into a database and had muchos fun working with their UTC dates in the form 'Fri Sep 15 07:13:01 UTC 2006'. I don't know jack about date formats and so on but VBScript did not want to recognize these, nor did SQL Server.

Thus I came up with the following:

<script language="jscript" runat="server">
 
function TimestampFromUTC( sUTC ) {
  return Date.parse(sUTC) / 1000; // seconds rather than milliseconds
}
 
</script>
 
<script language="vbscript" runat="server">
 
Function DateFromTimestamp( iTimestamp )
  DateFromTimestamp = DateAdd( "s", iTimestamp, "01/01/1970 00:00:00" )
End Function
 
</script>

Usage: DateFromTimestamp( TimestampFromUTC( s ) )

Launch TortoiseSVN from SQL 2005 Management Studio

Keep all your SQL scripts under version control?
Just saved a new SQL script to your projects working copy and want to commit it do you?



SQL Server Management Studio supports 'External Tools' which can have arguments and so on. Tortoise SVN can be configured to open a Commit dialogue for the working directory of a script file as follows:
Tools
External Tools
Add

Title: TortoiseSVN
Command: "C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe"
Arguments: /command:commit /path:"$(ItemDir)" /notempfile

Then, if you want a toolbar shortcut:

Tools
Customize
Commands
Categories
Tools
External Command 1

Drag this onto a toolbar, and then right-click to edit properties.

Tidy.

References:
http://www.codeproject.com/useritems/SSMSScriptRunner.asp
http://www.sqldbatips.com/showarticle.asp?ID=78

Howto: Debug classic ASP pages with Visual Studio 2003

Right. This was damn fiddly to get going on my particular setup (Windows 2003 SP1/R2, IIS6 in native mode, Visual Studio 2003) and took much googling.
However, I've now got my dev machine nicely asking me if I want to open a new instance of VS to debug local ASP errors that occur when testing with IE (incidently, I have the same occuring for Javascript - much win).

So, I'm posting this a reference. Key changes I had to make are below, with the various source links down the bottom.

IIS
Web Sites > Properties
Web Site
Connections
(1) Tick 'Enable HTTP Keep-Alives'
Home Directory
Configuration
Mappings
(2) Tick 'Cache ISAPI extensions'
(3) Edit the '.asp' Application extension and add DEBUG to the 'Verbs' section
Debugging
(4) Tick 'Enable ASP server-side script debugging'
Cache Options [1]
(5) Select 'Cache limited ASP files in memory' and set it to '1'
Directory Security
Edit 'Authentication and access control'
(6) Tick 'Integrated Windows authentication'

[1] Note that 'Cache Options' can only be set at the root 'Web Sites' level (as far as I know).

Visual Studio
Tools
Options
Debugging
Just-In-Time
(7) Tick 'Script'


Internet Explorer
Tools
Internet Options
Security
Trusted Sites
(8) Add the FQDN of the site you are debugging as a trusted site


RegEdit (May be needed on Win2003 SP1, requires a reboot to take effect)
(9) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\DisableLoopbackCheck, DWORD, 1


Troubleshooting
a) JIT debugger kicks in, but VS can't find your ASP 'debug symbols'? See #5. It seems like the ASP debug symbols must be held in the IIS cache.
b) Receiving an 'unable to find webserver' error? See #9.

References:
VS.NET Debugging Whitepaper
MSDN VS Debugging Reference
General notes
http://aspadvice.com/blogs/ssmith/archive/2006/10/04/Debuggin-Failed-Because-Authentication-is-not-enabled.aspx(Added 07/10/06)