Connecting to Your Database
Working with Native Database Interfaces

Provider parameter

The Provider DBParm parameter for the SQL Server (MSOLEDBSQL) interface can connect to various versions including SQL Server 2012, 2014, 2016, 2017, and 2019.

The Provider DBParm parameter for the SQL Native Client (SNC) interface allows you to select the SQL Server version that you want to connect to. You can set this parameter in script to SQLNCLI (for the SNC 9.0 driver that connect to SQL Server 2005), to SQLNCLI10 (for the SNC 10.0 driver that connects to SQL Server 2008), or to SQLNCLI11 (for the SNC 11.0 driver that connects to SQL Server 2012 or later). Otherwise, you can select one of these providers on the Connection tab of the Database Profile Setup dialog box for the SNC interface.

If you do not set or select a provider, the default selection is SQLNCLI (SNC 9.0 for SQL Server 2005). This allows existing SNC interface users to be able to upgrade to PowerBuilder without any modifications. If PowerBuilder fails to connect with the SQLNCLI provider, it will attempt to connect to SQLNCLI10 provider. However, if you explicitly set the provider and the connection fails, PowerBuilder displays an error message.

Failover parameter

The Failover Partner DBParm parameter allows you to set the name of a mirror server, thereby maintaining database availability if a failover event occurs. You can also set the name of the mirror server on the System tab of the Database Profile Setup dialog box for the MSOLEDBSQL or SNC interface.

When failover occurs, the existing PowerBuilder connection to SQL Server is lost. The MSOLEDBSQL or SNC driver releases the existing connection and tries to reopen it. If reconnection succeeds, PowerBuilder triggers the failover event.

The following conditions must be satisfied for PowerBuilder to trigger the failover event:

When failover occurs:

Date and time datatypes

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

CREATE TABLE FSTest (
  GuidCol1 uniqueidentifier ROWGUIDCOL NOT NULL 
  UNIQUE DEFAULT NEWID(), 
  IntCol2 int, 
  varbinaryCol3 varbinary(max) FILESTREAM);

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

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

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;

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;

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:

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.

MERGE statement

The MERGE Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table or view based on the results of a join with a source table. You can use MERGE statement in the ISQL painter and in PowerScript using dynamic SQL. For example

String mySQL
mySQL = "MERGE INTO a USING b ON a.keycol = b.keycol " &
  + "WHEN MATCHED THEN "&
  + "UPDATE SET col1 = b.col1,col2 = b.col2 " &
  + "WHEN NOT MATCHED THEN " &
  + "INSERT (keycol, col1, col2, col3)" & 
  + "VALUES (b.keycol, b.col1, b.col2, b.col3) " &
  + "WHEN SOURCE NOT MATCHED THEN " &
  + "DELETE;"
EXECUTE IMMEDIATE :Mysql;

Grouping sets

GROUPING SETS is an extension of the GROUP BY clause that lets you define multiple groupings in the same query. GROUPING SETS produce a single result set, making aggregate querying and reporting easier and faster. It is equivalent to a UNION ALL operation for differently grouped rows.

The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause. A new function, GROUPING_ID, returns more grouping-level information than the existing GROUPING function. (The WITH ROLLUP, WITH CUBE, and ALL syntax is not ISO compliant and is therefore obsolete.)

The following example uses the GROUPING SETS operator and the GROUPING_ID function:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
  FROM Sales 
  GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month)));
SELECT COL1, COL2, 
  SUM(COL3) AS TOTAL_VAL, 
  GROUPING(COL1) AS C1, 
  GROUPING(COL2) AS C2, 
  GROUPING_ID(COL1, COL2) AS GRP_ID_VALUE
  FROM TEST_TBL GROUP BY ROLLUP (COL1, COL2);

You can use the GROUPING SETS operator in the ISQL painter, in PowerScript (embedded SQL and dynamic SQL) and in DataWindow objects (syntax mode).

Row constructors

Transact-SQL now allows multiple value inserts within a single INSERT statement. You can use the enhanced INSERT statement in the ISQL painter and in PowerScript (embedded SQL and dynamic SQL). For example:

INSERT INTO Employees VALUES ('tom', 25, 5), ('jerry', 30, 6), ('bok', 25, 3);

When including multiple values in a single INSERT statement with host variables, you must set the DisableBind DBParm to 1. If you use literal values as in the above example, you can insert multiple rows in a single INSERT statement regardless of the binding setting.

Compatibility level

In SQL Server 2008, the ALTER DATABASE statement allows you to set the database compatibility level (SQL Server version), replacing the sp_dbcmptlevel procedure. You can use this syntax in the ISQL painter and in PowerScript (dynamic SQL). For example:

ALTER DATABASE <database_name> 
  SET COMPATIBILITY_LEVEL = {80 | 90 | 100}
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Compatibility level affects behaviors for the specified database only, not for the entire database server. It provides only partial backward compatibility with earlier versions of SQL Server. You can use the database compatibility level as an interim migration aid to work around differences in the behaviors of different versions of the database.

Table hints

The FORCESEEK table hint overrides the default behavior of the query optimizer. It provides advanced performance tuning options, instructing the query optimizer to use an index seek operation as the only access path to the data in the table or view that is referenced by the query. You can use the FORCESEEK table hint in the ISQL painter, in PowerScript (embedded SQL and dynamic SQL), and in DataWindow objects (syntax mode).

For example:

Select ProductID, OrderQty from SalesOrderDetail with (FORCESEEK);

The PowerBuilder MSOLEDBSQL and SNC interfaces do not support the User-Defined Table Type (a user-defined type that represents the definition of a table structure) that was introduced in SQL Server 2008.