Support for new datatypes in SQL Server 2008
Date and time datatypes
The following table lists new SQL Server 2008 date and time
datatypes and the PowerScript datatypes that they map to:
|
SQL Server datatype |
PowerScript datatype |
|---|---|
|
DATE |
Date |
|
TIME |
Time (Supports only up to 6 fractional seconds |
|
DATETIME2 |
DateTime (Supports only up to 6 fractional seconds |
The SQL Server 2008 DATETIMEOFFSET datatype is not supported in
PowerBuilder 2019 R3.
Precision settings
When you map to a table column in a SQL Server 2008 database,
PowerBuilder includes a column labeled “Dec” in the Column view of the
DataWindow painter, and a text box labeled “Fractional Seconds
Precision” in the Column (Object Details) view of the Database painter.
These fields allow you to list the precision that you want for the TIME
and DATETIME2 columns.
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.
Filestream datatype
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 MSOLEDBSQL and SNC interfaces
map 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:
|
1 2 3 4 5 |
CREATE TABLE FSTest ( GuidCol1 uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(), IntCol2 int, varbinaryCol3 varbinary(max) FILESTREAM); |
Do not use PowerScript file access functions with FILESTREAM
data
You can access FILESTREAM data by declaring and using the Win32
API functions directly in PowerBuilder applications. However, existing
PowerBuilder file access functions cannot be used to access FILESTREAM
files. For more information about accessing FILESTREAM data using
Win32 APIs, see the MSDN SQL Server Developer Center website at http://msdn.microsoft.com/en-us/library/bb933877(SQL.100).aspx.
Using CLR datatypes in
PowerBuilder
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.
You can use the ToString method to work with all datatypes that
are implemented as CLR datatypes, such as the HierarchyID datatype, Spatial datatypes, and
User-defined
types.
HierarchyID datatype
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.
ISQL Usage
You can use HierarchyID columns with CREATE TABLE, SELECT, UPDATE,
INSERT, and DELETE statements in the ISQL painter. For example:
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 |
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:
|
1 2 |
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:
|
1 2 |
Update Emp Set EmpNode = '/1/2/' where EmpId=4; Delete from Emp where EmpNode = '/1/2/'; |
PowerScript Usage
You can use HierarchyID columns in embedded SQL statements for
SELECT, INSERT, UPDATE, and DELETE operations. HierarchyID data can be
retrieved either as a String or as a Binary(Blob) datatype using the
SelectBlob statement.
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:
|
1 2 3 4 5 6 7 8 9 10 |
long id String hid,name Select 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; |
DataWindow Usage
DataWindow objects do not directly support the HierarchyID
datatype. But you can convert the HierarchyID to a string using the
ToString method or an associated HierarchyID method in the data source
SQL. For example:
|
1 2 |
SELECT EmpId, EmpName, EmpNode.ToString() FROM Emp; SELECT EmpId, EmpName, EmpNode.GetLevel() FROM Emp; |
Spatial
datatypes
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 MSOLEDBSQL and SNC interfaces do 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:
|
1 2 3 4 5 6 7 8 9 |
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
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 MSOLEDBSQL and SNC interfaces do 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.