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 precision although SQL Server datatype supports up to 7 fractional seconds precision.)
DATETIME2	DateTime (Supports only up to 6 fractional seconds precision although SQL Server datatype supports up to 7 fractional seconds precision.)

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

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 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);

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 Web site 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:

CREATE TABLE Emp ( 
  EmpId int NOT NULL, 
  EmpName varchar(20) NOT NULL, 
  EmpNode hierarchyid NULL);

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);

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;

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:

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

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:

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

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:

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;

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:

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

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 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;

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 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.

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 2018

August 20, 2023

Was this article helpful?

Connection Information

Support for new datatypes in SQL Server 2008 – PB Docs 2018