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.
Prev. page
[1]
2
next page 
