Support for new datatypes in SQL Server 2008

The following table lists new SQL Server 2008 date and time datatypes and the PowerScript datatypes that they map to:

The SQL Server 2008 DATETIMEOFFSET datatype is not supported in PowerBuilder 12.5.

The precision setting is for table creation only. When retrieving or updating the data in a column, PowerBuilder uses only up to six decimal places precision for fractional seconds, even if you enter a higher precision value for the column.

The FILESTREAM datatype allows large binary data to be stored directly in an NTFS file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.

The SQL Server Database Engine implements FILESTREAM as a Varbinary(max) datatype. The PowerBuilder SNC interface maps the Varbinary(max) datatype to a BLOB datatype, so to retrieve or update filestream data, use the SelectBlob or UpdateBlob SQL statements, respectively. To specify that a column should store data on the file system, you must include the FILESTREAM attribute in the Varbinary(max) column definition. For example:

CREATE TABLE FSTest (

  GuidCol1 uniqueidentifier ROWGUIDCOL NOT NULL 

  UNIQUE DEFAULT NEWID(), 

  IntCol2 int, 

  varbinaryCol3 varbinary(max) FILESTREAM);

The binary values of the .NET Common Language Runtime (CLR) datatypes can be retrieved from a SQL Server database as blobs that you could use in PowerBuilder applications to update other columns in the database. If their return values are compatible with PowerBuilder datatypes, you can use CLR datatype methods in PowerScript, dynamic SQL, embedded SQL or in DataWindow objects, because the SQL script is executed on the SQL Server side.

The CLR datatypes can also be mapped to Strings in PowerScript, but the retrieved data is a hexadecimal string representation of binary data.

HierarchyID is a variable length, system datatype that can store values representing nodes in a hierarchical tree, such as an organizational structure. A value of this datatype represents a position in the tree hierarchy.

CREATE TABLE Emp ( 

  EmpId int NOT NULL, 

  EmpName varchar(20) NOT NULL, 

  EmpNode hierarchyid NULL);

To insert HierarchyID data, you can use the canonical string representation of HierarchyID or any of the methods associated with the HierarchyID datatype as shown below.

INSERT into Emp VALUES (1, 'Scott', 

  hierarchyid::GetRoot());

INSERT into Emp VALUES (2, 'Tom' , '/1/');

DECLARE @Manager hierarchyid 

SELECT @Manager = hierarchyid::GetRoot() FROM Emp INSERT into Emp VALUES (2, 'Tom',     

  @Manager.GetDescendant(NULL,NULL));

DECLARE @Employee hierarchyid 

SELECT @Employee = CAST('/1/2/3/4/' AS hierarchyid) 

INSERT into Emp VALUES (2, 'Jim' , @Employee);

You cannot select the HierarchyID column directly since it has binary data, and the ISQL painter Results view does not display binary columns. However, you can retrieve the HierarchyID data as a string value using the ToString method of HierarchyID. For example:

Select EmpId, EmpName, EmpNode.ToString() from Emp;

You can also use the following methods on HierarchyID columns to retrieve its data: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, and Reparent. If one of these methods returns a HierarchyID node, then use ToString to convert the data to a string. For example:

Select EmpId, EmpName, EmpNode.GetLevel() from Emp;

Select EmpId, EmpName,  

   EmpNode.GetAncestor(1).ToString() from Emp;

HierarchyID columns can be updated using a String value or a HierarchyID variable:

Update Emp Set EmpNode = '/1/2/' where EmpId=4;

Delete from Emp where EmpNode = '/1/2/';

When using a String datatype to retrieve HierarchyID data, use the ToString method. Otherwise the data will be a hexadecimal representation of the binary HierarchyID value.

The following example shows how you can use HierarchyID methods in embedded SQL:

long id

String hid,nameSelect EmpId, EmpName, EmpNode.ToString() 

  into :id, :name, :hid   

  from Emp where EmpId=3;

Select EmpId, EmpName, EmpNode.GetLevel() 

  into :id, :name, :hid   

  from Emp where EmpId=3;

Blob b

Selectblob EmpNode into :b from Emp where EmpId =2;

SELECT EmpId, EmpName, EmpNode.ToString() FROM Emp; 

SELECT EmpId, EmpName, EmpNode.GetLevel() FROM Emp;

Microsoft SQL Server 2008 supports two spatial datatypes: the geometry datatype and the geography datatype. In SQL Server, these datatypes are implemented as .NET Common Language Runtime (CLR) datatypes.

Although the PowerBuilder SNC interface does not work with CLR datatypes, you can convert the spatial datatypes into strings (with the ToString function) and use them in PowerScript, in the ISQL painter, in embedded SQL, and in DataWindow objects. This is similar to the way you use the HierarchyID datatype. The SelectBlob SQL statement also lets you retrieve binary values for these datatypes.

The geography and geometry datatypes support eleven different data objects, or instance types, but only seven of these types are instantiable: Points, LineStrings, Polygons, and the objects in an instantiable GeometryCollection (MultiPoints, MultiLineStrings, and MultiPolygons). You can create and work with these objects in a database, calling methods associated with them, such as STAsText, STArea, STGeometryType, and so on.

For example:

CREATE TABLE SpatialTable (id int IDENTITY (1,1),      

  GeomCol geometry);

INSERT INTO SpatialTable (GeomCol) VALUES (

  geometry::STGeomFromText(

   'LINESTRING (100 100,20 180,180 180)',0));

select id, GeomCol.ToString() from SpatialTable;

select id, GeomCol.STAsText(), 

  GeomCol.STGeometryType(),

  GeomCol.STArea() from SpatialTable;

User-defined types (UDTs) are implemented in SQL Server as CLR types and integrated with .NET. Microsoft SQL Server 2008 eliminates the 8 KB limit for UDTs, enabling the size of UDT data to expand dramatically. Although the PowerBuilder SNC interface does not directly support UDT datatypes, you can use the ToString method to retrieve data for UDTs in the same way as for other CLR datatypes such as HierarchyId or the spatial datatypes. However, if a UDT datatype is mapped to a String datatype in PowerScript, UDT binary values will be retrieved as hexadecimal strings. To retrieve or update data in binary form (blob) from a UDT, you can use the SelectBlob or UpdateBlob SQL statements, respectively.

You can use any of the associated methods of UDT or CLR datatypes that return compatible data (such as String, Long, Decimal, and so on) for PowerBuilder applications.