20 February 2007

32-bit Windows Script Components under 64-bit Windows

We're currently setting up a new suite of 64-bit web and database servers at work all running Windows Server 2003 x64 Edition. We've got quite a few legacy 32-bit Windows Script Components we need to use which by all accounts should run fine in the 32-bit environment provided by WOW64.

Imagine our surprise when, on registering the components, none of them worked - throwing errors at the point of instantiation.

WOW64 is rather an odd beast; residing as it does in it's own SYSWOW64 folder in the Windows directory. This folder essentially contains the 32-bit versions of all the DLLs and things that are available in a 32-bit version of Windows. The caveat being that in order to get your 32-bit fare to work you need to call on the services of these SYSWOW64 versions rather than the ones in the folder still called SYSTEM32 (note the stupid naming convention).

When registering WSC's you actually register the hosting service, scrobj.dll with regsvr32.exe, passing the path to your WSC as the command line for scrobj.dll using the /i switch e.g.

regsvr32 /i:"C:\Components\Display.wsc" "C:\WINDOWS\SYSTEM32\scrobj.dll"

Oddly the Register option in the file association for WSC's seems to mix versions, calling the 64-bit version of regsvr32.exe and the 32-bit version of scrobj.dll.

"C:\WINDOWS\system32\REGSVR32.EXE" /i:"%1" "C:\WINDOWS\SYSWOW64\scrobj.dll"

I'm not sure of the significance of this mixed version thing, however it didn't work in our case so we added a 32-bit Register option which called the 32-bit versions of both files from the SYSWOW64 folder e.g.

"C:\WINDOWS\SYSWOW64\REGSVR32.EXE" /i:"%1" "C:\WINDOWS\SYSWOW64\scrobj.dll"

and a 32-bit Unregister e.g.

"C:\WINDOWS\SYSWOW64\REGSVR32.EXE" /u /n /i:"%1" "C:\WINDOWS\SYSWOW64\scrobj.dll"

which sorted the issue.

13 February 2007

XML namespace prefixes in MSXML

If you're working with XSL or a similar technology that makes use of XML namespace prefixes using the Microsoft XML DOM you'll likely run into problems if you try to do anything more than just load in a file.

Adding elements

The W3 DOM specification includes a createElementNS method for creating an element scoped within a namespace however MSXML doesn't. You can create an element with a prefix using createElement but this doesn't correctly register the namespace of the node and you'll get a schema error something like:

msxml3.dll: Keyword xsl:stylesheet may not contain xsl:include.

In order to create an element and register it correctly you have to use createNode instead which takes node type (1 for an element), node name and namespace URI as arguments e.g.

Set ndIncl = xslDoc.createNode(1, "xsl:include",
"http://www.w3.org/1999/XSL/Transform")

Using XPath

Similar to the createElement problem, even if you've only loaded an XSL document you won't be able to use XPath to query it because oddly the namespaces aren't automatically registered with XPath e.g.

Set nlTemps = xslDoc.documentElement.selectNodes("/xsl:stylesheet/xsl:template")

yields the following error:

msxml3.dll: Reference to undeclared namespace prefix: 'xsl'.

To get this to play ball you have to set the "SelectionNamespaces" second-level property which takes a space delimited list of namespace definitions using a setProperty call of the form:

xslDoc.setProperty "SelectionNamespaces",
"xmlns:xsl='http://www.w3.org/1999/XSL/Transform'"

Links

05 February 2007

XML and SQL Server

In this post i'll cover how you can get SQL Server to return data as XML by using the FOR XML command and how you can use XML as input for updating records and as a rich argument for row returning stored procedures using the OPENXML command.

There are lots of reasons you may want to get data out of a database as XML:

  • You may be building an AJAX app and want to send XML to the client directly for processing by your client-side JavaScript
  • You may want to use XSL to transform your data into some format such as HTML or a CSV
  • You may want to export data and store it in a form which retains its original structure

These reasons also give you reasons for needing to pass XML into your database for example with the AJAX app you may want to receive changes as XML from the client and post them straight to a stored proc that updates your tables.

Examples are in VBScript using ASP and ADO.

Getting SQL Server to return XML

The key to getting SQL Server to return XML is the FOR XML command. It comes in three flavours:

FOR XML RAW
The least useful, RAW mode simply outputs the rows returned by your query as <row> nodes with the columns being either elements within this node or attributes of it as you define.
FOR XML AUTO
Automagically translates your SQL query, joins and all into suitable nested XML elements and attributes. For example if you are joining Orders to OrderItems the XML output will be OrderItem nodes nested within the associated Order node. You can alter the naming of the nodes by aliasing your table and column names but that's about it.
FOR XML EXPLICIT
Explicit mode allows the most customisability but it's also the most fiddly requiring you to alias all your columns names to a specific format which describes which nodes they should belong to.

You'll mostly use AUTO mode because it gives you the most useful results in the least amount of time so here it is in an example:

SELECT Order.*, OrderItem.*
FROM Order
INNER JOIN OrderItem
   ON Order.order_key = OrderItem.order_fkey
WHERE Order.customer_fkey = 1
FOR XML AUTO

All you do is tag FOR XML AUTO on to the end of your query, that's it! The output will look something like this:

<Order order_key="1" customer_fkey="48" date_placed="24/08/2006 12:31">
   <OrderItem orderitem_key="123" order_fkey="1" product_fkey="234" list_price="£14" />
   <OrderItem orderitem_key="124" order_fkey="1" product_fkey="64" list_price="£3" />
   <OrderItem orderitem_key="125" order_fkey="1" product_fkey="73" list_price="£27" />
</Order>

If you run this in Query Analyzer you'll notice in the results pane it looks like the XML has been split into rows. We need to use an ADODB.Stream object to get at the output properly, thus:

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
Set strm = Server.CreateObject("ADODB.Stream")

conn.Open "Provider=SQLOLEDB;Data Source=myServerAddress;" & _
   "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

strm.Open

Set cmd.ActiveConnection = conn

cmd.Properties("Output Stream").Value = strm
cmd.Properties("Output Encoding") = "UTF-8"
cmd.Properties("XML Root") = "Root"  'this can be anything you want

cmd.CommandType = adCmdText
cmd.CommandText = strSQL

cmd.Execute , , adExecuteStream

Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"

xmlDoc.LoadXML(strm.ReadText)

strm.Close : Set strm = Nothing
Set cmd = Nothing

xmlDoc now contains our XML to do with as we will.

Passing XML into SQL Server

The easiest way to get XML into SQL Server is as a parameter of a stored procedure thus:

cmd.Parameters.Append cmd.CreateParameter("somexml", adVarChar,
adParamInput, 8000, xmlDoc.xml)

You then use two System Stored Procedures along with the OPENXML command to SELECT from the contents of the XML parameter as if it were a table:

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @somexml

SELECT * FROM OPENXML (@idoc, '/Root/Order') WITH (Order)

EXEC sp_xml_removedocument @idoc

OPENXML takes the prepared XML document and an XPath expression telling it which nodes it is taking into account. The WITH statement in this case tells OPENXML that the nodes it is working on are of the same schema type as the rows in the Order table.

The result of this call is a list of records of the same schema as the Order table but which have actually come from the passed in XML document. Because the schema is that of Order you can put an INSERT INTO [Order] in front of the SELECT and this will add the rows from the XML to the Order table. You probably wouldn't want to do that but you get the idea.

You don't have to have a table representing the schema of the XML you're passing in in your database. WITH also accepts a normal schema declaration i.e. comma delimited column names with their types and which node this maps to in the XML:

SELECT order_key, customer_fkey, description
FROM OPENXML (@idoc, '/Root/Order') 
WITH (
   order_key     int           '@order_key',
   customer_fkey int           '@customer_fkey',
   description   nvarchar(100) 'description'
)

The advantage of being able to do this is that you can pass complex structured criteria into one of your stored procedures and use OPENXML to turn it into a rowset which you can use to JOIN to the tables in your database. Powerful stuff with a large number of applications in both improving querying data and updating it.

02 February 2007

A room with a Vista

I've been toying with the idea of buying Vista for a some time now. I have a beta version installed in a spare partition but some hard disk corruption issues prevented me from giving it a good run for its money. So, having not looked at it for a while, I've been reading CNET's Seven days of Vista series over the past week to see what's what in the final release.

Being a technology obsessed geek, the Ultimate Edition was the only viable option but it came as a roundhouse kick to the face when i learnt this would retail at £350! "Guess I'll be sticking to XP for now then", I though mournfully.

Not so, for at the end of day 7's post was the pièce de résistance:

Our final tip would be to consider buying OEM versions of Vista ... the consumer version of Ultimate is [£349], yet it's just £121.68 for the OEM version

I was aware you could buy OEM software but had always thought the difference in price was similar to that of retail and OEM hardware. "Surely that can't be right", I though, but sure enough dabs.com has both versions retail boxed and OEM.

The only differences with the OEM being you don't get any support and it's tied to the motherboard it's first installed on. I don't need support and, in the unlikely event that my mobo dies and i have to buy another OEM copy, I'll still be £50 richer. Besides you can get three OEMs for the price of one retail so I could just get a few spares!

01 February 2007

SQL Server Oddness

I recently came across a rather odd bug in SQL Server 2000 which, although fixed in SP4, i had to find a work-around for as an immediate solution was required that couldn't wait for a scheduled server patching.

In my case it concerned a JOIN subselect which contained a JOIN using a user-defined scalar function as one of its predicates e.g.

...
LEFT JOIN (
   SELECT foo.col1, MIN(bar.col1)
   FROM foo
   INNER JOIN bar
      ON foo.col1 = bar.col2
      AND bar.col3 = dbo.fn_GetVal("derek")
   GROUP BY foo.col1 
)
...

which produced the rather unhelpful error:

Server: Msg 913, Level 16, State 8, Line 4
Could not find database ID 102. Database may not be activated yet or may
be in transition.

The work-around in this case was to move the predicate to the WHERE clause rather than the ON e.g.

...
LEFT JOIN (
   SELECT foo.col1, MIN(bar.col1)
   FROM foo
   INNER JOIN bar
      ON foo.col1 = bar.col2
   WHERE bar.col3 = dbo.fn_GetVal("derek")
   GROUP BY foo.col1 
)
...