Using Updategrams to Store Data in an Image Column

I'm attempting to use a parameterized updategram and default mapping to insert data into an image-type column in a database, but so far I've been unsuccessful. Why isn't this technique working?

You can use an updategram to insert data into an image field, but you can't use default mapping or a parameterized updategram. Let's look at the limitations of default mapping and parameterized updategrams and how you can achieve your goal.

Default mapping. Your updategram is failing because default mapping assumes that the name of the XML element corresponds to a table in the database and that the names of the attributes on the element correspond to columns in that table. The default mapping also assumes that columns store one of the string data types—varchar, char, nvarchar, nchar—or that a conversion exists from a string to the mapped column's data type. Because the column stores an image data type and SQL Server doesn't support a conversion between string and image, the updategram fails. Fortunately, you can avoid this problem by using a mapping schema with your updategram. (For more information about default mapping, see SQL Server 2000 Web Release 2—WR2—Books Online—BOL.)

A mapping schema maps the elements and attributes of an XML document to a set of tables—and columns within those tables—inside SQL Server. Unlike default mapping, which is an implied mapping for any XML document, a mapping schema provides an updategram with explicit instructions for mapping the data in the XML document to the SQL Server database. Typically, the mapping instructions need to specify only how an element or attribute maps to a column within the database. When SQL Server executes the updategram, it converts any incompatible data types before it inserts the data—provided SQL Server supports conversion between the incompatible types. But when SQL Server doesn't support a conversion—such as string to image—the updategram fails. To avoid this problem, you need additional information in your mapping schema.

The sql:datatype annotation lets you add the needed information. XML for WR1 introduced the new annotation to mapping schemas, and WR2 supports it. (The Web releases are available at http://www.microsoft.com/sql/downloads/default.asp.) The sql:datatype annotation specifies the type of the mapped SQL Server database column. By using sql:datatype, the updategram processor can correctly specify data-type conversions and the corresponding query constructs that SQL Server requires to update image and binary column types.

You might wonder why Microsoft didn't build directly into the updategram processor the appropriate logic for converting data types that SQL Server doesn't support. The reason is for efficiency, because the updategram processor doesn't read metadata from the database to determine if special data conversion logic is required. Instead, the updategram processor was built to run on the middle tier and generate SQL queries that SQL Server processes. (You can observe the generated queries by using SQL Server Profiler to run a trace.) So instead of executing a query that retrieves metadata for each updategram, Microsoft opted to introduce the sql:datatype annotation to provide the needed metadata to the updategram processor. This decision took into account how often an explicit conversion was needed in real-world scenarios, the ease with which the metadata could be specified, the overhead associated with querying the metadata, and an alternative solution that cached metadata on the middle tier. By including a sql:datatype annotation in your mapping schema, you can easily instruct the updategram processor to perform the appropriate conversion on your behalf. Let's look at a simple example that illustrates the use of sql:datatype.

Listing 3 shows an example of an updategram that you use to insert a record into a database's image column. The updategram references a mapping schema by specifying the mapping-schema attribute in the updg:sync element. The value of the mapping-schema attribute is the filename of the mapping schema that you're going to use—in this case albumschema.xml. When the updategram processor executes the updategram from Listing 3, it first processes the mapping schema that Listing 4 shows. The mapping schema in Listing 4 specifies a simple mapping of the serialno, picture, and description attributes of the Album element to the identically named columns in the Album table in the SQL Server database. But because the picture attribute is mapped to an image column, you need the additional sql:datatype annotation in the picture attribute's declaration. The mapping schema also includes an XML data type on the picture attribute dt:type="bin.base64". This XML data type indicates that the value of the picture attribute is a base64-encoded representation of the actual binary value. (Base64 is an encoding of binary data into printable characters that can be included in XML.) By using the XML data type and the sql:datatype annotation, the updategram can store the binary data (not the base64-encoded data) within an image column in the database.

To run Listing 3's updategram, save it to a file called updategram.xml in the directory you created for the virtual root in the previous example. Also, save the mapping schema in Listing 4 to a file called albumschema.xml in the same directory. Using Query Analyzer, run the SQL script that Listing 5 shows to create the Album table in the database you chose for the previous example. Finally, type the URL http://localhost/December/template/updategram.xml into your Web browser. This action will execute the updategram and store the data in the database. If the updategram executes successfully, the root XML element update will be returned. If an error occurred, XML processing instructions will be returned to indicate the error. (See Exploring XML, November 2001, for a description of error processing.)

Parameterized updategrams. Using a parameterized updategram to update the database would be quite convenient, especially to post data from an HTML Web page. However, a problem exists in the updategram processor. Parameterized updategrams pass parameters the same way XML templates pass them. The updategram processor constructs parameterized queries and submits the query to the database along with a collection of parameters. But the updategram processor treats all parameters as strings, thereby overriding the conversion logic that the mapping schema specified. A data-conversion error from the query processor results.

Unfortunately, you can't currently modify this behavior. Until Microsoft introduces data types to updategrams and templates, you can work around this problem by avoiding parameterized updategrams for binary data types. Listing 6, page 67, shows a Web page that uses Dynamic HTML (DHTML) to build an updategram when a user presses the Add Data button on the page. This code uses JavaScript to collect data from the HTML form and dynamically insert the data into the updategram. The updategram is then assigned to a hidden form element named template. When a user presses the Add Data button, the form values post to the virtual root, and the updategram is processed. To run this program, configure the virtual root to accept template= URL queries and Allow posted updategrams because the browser will post the updategram as a named parameter corresponding to the name of the hidden field (template). To configure this setting, use the Settings tab in the IIS Virtual Directory Management for SQL Server 2.0 snap-in that's installed with WR2. (See the WR2 documentation for more information about parameterized updategrams.)