|
SQL Server Data Type |
Iron Speed Designer Validation Type |
|
Geography |
Very Large String |
|
Geometry |
Very Large String |
|
Xml |
Very Large String |
There are several limitations on the SQL statements that Iron Speed Designer creates:
Images in tables that have a primary key are displayable and downloadable (exportable).
Binary data in tables that have a primary key are downloadable (exportable), but are not displayable.
Images in tables that do not have a primary key are not downloadable (exportable) or displayable.
Binary data in tables that do not have a primary key are not downloadable (exportable) or displayable.
The reason for the latter two restrictions is that binary data cannot be embedded inside an HTML response stream. A pointer to it must be embedded so that the browser can subsequently request each piece of binary data using a separate HTTP request. For tables without primary keys, there is no way to create a unique identifier for specific binary data in the table. Binary data is programmatically accessible using the data access classes for such tables, but are not displayable or downloadable directly from a page without application customization.
The sql_variant data type in Microsoft SQL Server is used to store data of various types. Iron Speed Designer treats sql_variant fields as binary data.
sql_variant is useful if you want to store some data but don't care about the type of the source data and don't want to perform any explicit type conversion when setting its value. If you have other fields of type bigint, datetime, decimal, varchar, etc., you can store their values in sql_variant fields.
For example, suppose you have a table called VariantTest with a CreateDate field of type datetime and a VariantDate field of type sql_variant. You can do things like:
UPDATE VariantTest
SET VariantDate = CreateDate
When you read values from a sql_variant field, it is treated as binary data. You can easily convert it to a string, but if you know that it's really datetime data and you want to treat it as such, then the burden is on you to explicitly convert it back to datetime data. For example, to copy from a sql_binary back into a datetime, you would have to do:
UPDATE VariantTest
SET CreateDate = CONVERT(datetime, VariantDate)
In this example, Iron Speed Designer treats the VariantTest.VariantDate field as binary data, exactly as if it were a field of type "binary" (which Iron Speed Designer also supports). In Iron Speed Designer, you can change the validation type to a Date, String, Decimal, US State, or as any other supported data type.
The XML data type is mapped to a Very Large String data type in Iron Speed Designer. While no XML validation is done by the application, you can enter XML in text boxes and it is saved appropriately. You can, if you like, request XML validation in the database. If you do, you specify an XML schema and your XML will be validated against that schema by your database, and an error message is reported if your XML does not map properly.
In Microsoft SQL Server, these data types support Unicode data storage:
nchar
ntext
nvarchar
Note: The ‘N’ prefix for these data types is the SQL-92 standard for National (Unicode) data types. Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter ‘N’. Without the ‘N’ prefix, the string is converted to the default code page of the database, which may not recognize certain characters.
The geometry and geography data types of SQL Server 2008 are mapped to the Very Large String data type in Iron Speed Designer. While no validation is done by the application for these data types, you can enter tagged text in text boxes and it is validated by the database when saved.
Tagged text must conform to the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.
Valid WKT Tags:
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
Geometry Examples:
POINT (100 100)
LINESTRING (100 100, 200 200)
POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))
MULTIPOINT ((100 100), (200 200))
MULTILINESTRING ((100 100, 200 200), (3 4, 7 8, 10 10))
MULTIPOLYGON (((5 5, 10 5, 10 10, 5 5)), ((10 10, 100 10, 200 200, 30 30, 10 10)))
GEOMETRYCOLLECTION ( POLYGON((5 5, 10 5, 10 10, 5 5)), POINT(10 10) )
Geography Examples:
POINT (-122.349 47.651)
LINESTRING (-122.36 47.656, -122.343 47.656)
POLYGON ((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))
MULTIPOINT ((-122.36 47.656), (-122.343 47.656))
MULTILINESTRING ((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653), (-122.357 47.654, -122.357 47.657, -122.349 47.657, -122.349 47.65, -122.357 47.654))
MULTIPOLYGON (((-122.358 47.653, -122.348 47.649, -122.358 47.658, -122.358 47.653)), ((-122.341 47.656, -122.341 47.661, -122.351 47.661, -122.341 47.656)))
GEOMETRYCOLLECTION (POINT (-122.349 47.651), LINESTRING (-122.36 47.656, -122.343 47.656))
When creating applications that use the spatial data types, the client machine must install some additional assemblies that are not included in the base .NET framework. If not, errors may be raised about a missing the Microsoft.SqlServer.Types component. If Microsoft SQL Server 2008 is not already installed on the client machine, make sure the following are installed:
SQLSysClrTypes.msi
See http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en for download instructions.
CRT (C Run-Time Library)
See http://support.microsoft.com/kb/955850 for instructions.