Microsoft SQL Server

This section describes how to use the Microsoft SQL Server Native Client database interface in InfoMaker.

Supported versions for SQL Server

You can access Microsoft SQL Server 2008 R2, 2012, 2014, 2016, or 2017 databases using the SQL Native Client interface. The SQL Native Client interface uses a DLL named PBSNC170.DLL to access the database. The interface uses the SQL Native Client (sqlncli.h and sqlncli.dll) on the client side and connects using OLE DB.

To take advantage of these features, you need to use the SNC interface. The SQL Server 2008 R2 or later SQL Native Client software must be installed on the client computer.

PBODB initialization file not used

Connections made directly through OLE DB use the PBODB initialization file to set some parameters, but connections made using the SNC interface do not depend on the PBODB initialization file.

Supported SQL Server datatypes

The SQL Native Client database interface supports the datatypes listed in the following table. 

Binary

Real

Bit

SmallDateTime

Character (fewer than 255 characters)

SmallInt

DateTime

SmallMoney

Decimal

Text

Float

Timestamp

Identity

TinyInt

Image

VarBinary(max)

Int

VarBinary(n)

Money

VarChar(max)

Numeric

VarChar(n)

NVarChar(max)

XML

NVarChar(n)

 


The XML datatype is a built-in datatype in SQL Server 2005 that enables you to store XML documents and fragments in a SQL Server database. You can use this datatype as a column type when you create a table.

Additional datatypes are supported for SQL Server 2008. For more information, see Support for new datatypes in SQL Server 2008.

In SQL Server 2005, the VarChar(max), NVarChar(max), and VarBinary(max) datatypes store very large values (up to 2^31 bytes). You can use these datatypes to obtain metadata, define new columns, and query data from the columns. You can also use them to pipeline data.

Basic software components for Microsoft SQL Server

You must install the software components in the following figure to access a database with the SQL Native Client interface. Microsoft SQL Server Native Client software contains a SQL OLE DB provider and ODBC driver in a single DLL.

Figure: Components of a Microsoft SQL Server connection

Preparing to use the SQL Server database

Before you define the database interface and connect to a Microsoft SQL Server database in InfoMaker, follow these steps to prepare the database for use:

  1. Install and configure the required database server, network, and client software.

  2. Install the SQL Native Client database interface.

  3. Verify that you can connect to the Microsoft SQL Server server and database outside InfoMaker.

Step 1: Install and configure the database server

You must install and configure the database server, network, and client software for SQL Server.

To install and configure the database server, network, and client software:

  1. Make sure the Microsoft SQL Server database software is installed and running on the server specified in your database profile.

    You must obtain the database server software and required licenses from Microsoft Corporation. For installation instructions, see your Microsoft SQL Server documentation.

    Upgrading from an earlier version of SQL Server

    For instructions on upgrading to a later version of SQL Server or installing it alongside an earlier version, see your Microsoft SQL Server documentation.

  2. If you are accessing a remote SQL Server database, make sure the required network software (for example, TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the SQL Server database server at your site.

    For installation and configuration instructions, see your network or database administrator.

  3. Install the required Microsoft SQL Native Client software on each client computer on which InfoMaker is installed.

    You must obtain the SQL Native Client software from Microsoft. Make sure the version of the client software you install supports all of the following:

    The operating system running on the client computer

    The version of the database that you want to access

    The version of InfoMaker that you are running

    For installation instructions, see your Microsoft SQL Server documentation.

  4. Make sure the SQL Native Client client software is properly configured so that you can connect to the SQL Server database server at your site.

    For configuration instructions, see your Microsoft SQL Server documentation.

  5. Make sure the directory containing the SQL Native Client software is in your system path.

  6. Make sure only one copy of the Sqlncli.dll file is installed on your computer.

Step 2: Install the database interface

In the InfoMaker Setup program, select the Custom install and select the SQL Native Client database interface.

Step 3: Verify the connection

Make sure you can connect to the SQL Server server and database you want to access from outside InfoMaker.

To verify the connection, use any Windows-based utility that connects to the database. When connecting, be sure to specify the same parameters you plan to use in your InfoMaker database profile to access the database.

Defining the SQL Server database interface

To define a connection through the SQL Native Client interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup - SQL Native Client dialog box. You can then select this profile at any time to connect to your database in the development environment.

For information on how to define a database profile, see Creating a database profile. For new features that require special settings in the database profile, see SQL Server 2008 features. For a comparison of the database parameters you might have used with existing applications and those used with the SNC database interface, see Migrating from the MSS or OLE DB database interfaces.

Migrating from the MSS or OLE DB database interfaces

In earlier releases of InfoMaker, the MSS native interface was provided for connection to Microsoft SQL Server. This native interface was based on Microsoft DB-LIB functionality, which is no longer supported by Microsoft and is not Unicode-enabled. The MSS interface was removed in InfoMaker 10.0.

Prior to the introduction of SQL Server 2005 and SQL Native Client, Microsoft recommended using the OLE DB database interface and MDAC to connect to SQL Server. You can continue to use this solution if you do not need to take advantage of new features in SQL Server 2005 or later versions.

This section provides a comparison between database parameters you might have used in existing applications with the parameters you can use with the SNC database interface.

MSS database parameters supported by SNC

The following table shows the database parameters and preferences that could be set in the Database Profile Setup dialog box for the discontinued MSS native database interface for Microsoft SQL Server, and indicates whether they are supported by the SNC interface.

The column on the left shows the tab page in the Database Profile Setup dialog box for MSS. The parameters and preferences may be on different tab pages in the SNC profile.

MSS

SNC

Connection tab:

 

Language

Not supported

Lock

Supported (Transaction tab)

AutoCommit

Supported

CommitOnDisconnect

Supported

System tab:

 

Log

Not supported

SystemProcs

Not supported

PBCatalogOwner

Supported

Transaction tab:

 

Async

Not supported

DBGetTime

Not supported

CursorLock

Not supported

CursorScroll

Not supported

StaticBind

Supported

MaxConnect

Not supported

Syntax tab:

 

DBTextLimit

Supported (as PBMaxTextSize on Transaction tab)

DateTimeAllowed

Not supported

OptSelectBlob

Not supported

Network tab:

 

AppName

Supported (System tab)

Host

Supported (System tab)

PacketSize

Supported (System tab)

Secure

Supported (as TrustedConnection on General tab)


OLE DB database parameters supported by SNC

The following table shows the database parameters and preferences that can be set in the Database Profile Setup dialog box for the OLE DB standard interface for Microsoft SQL Server, and indicates whether they are supported by the SNC interface.

The column on the left shows the tab page in the Database Profile Setup dialog box for OLE DB. The parameters and preferences may be on different tab pages in the SNC profile.

OLE DB

SNC

Connection tab:

 

Provider

Not supported

DataSource

Supported at runtime (as SQLCA.ServerName)

DataLink

Supported

Location

Not supported

ProviderString

Supported

System tab:

 

PBCatalogOwner

Supported

ServiceComponents

Not supported

AutoCommit

Supported (General tab)

CommitOnDisconnect

Supported (General tab)

StaticBind

Supported (Transaction tab)

DisableBind

Supported (Transaction tab)

Init_Prompt

Not supported

TimeOut

Supported

LCID

Not supported

Transaction tab:

 

Block

Supported

PBMaxBlobSize

Supported

Mode

Not supported

Lock

Supported

Syntax tab:

 

DelimitIdentifier

Supported

IdentifierQuoteChar

Not supported

DateFormat

Supported

TimeFormat

Supported

DecimalSeparator

Supported

OJSyntax

Supported

Security tab:

 

EncryptPassword

Not supported

CacheAuthentication

Not supported

PersistSensitive

Not supported

MaskPassword

Not supported

PersistEncrypted

Not supported

IntegratedSecurity

Supported (TrustedConnection on General tab)

ImpersonationLevel

Not supported

ProtectionLevel

Not supported


Additional database parameters

The SNC interface also supports the Encrypt, TrustServerCertificate, and SPCache parameters (on the System tab page) and the Identity parameter (on the Syntax tab page).

SPCache database parameter

You can control how many stored procedures are cached with parameter information by modifying the setting of the SPCache database parameter. The default is 100 procedures. To turn off caching of stored procedures, set SPCache to 0.

For more information about database parameters supported by the SNC interface, see Connection Reference.

SQL Server 2008 features

InfoMaker support for connections to SQL Server 2008 databases includes new database parameters as well as support for new SQL Server datatypes. To connect to SQL Server 2008 from InfoMaker, you must install the SNC 10.0 driver.

New database parameters

Provider parameter

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 SQLNCLI11 (for the SNC 11.0 that connectc 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 migrate to InfoMaker 2017 and later without any modifications. If InfoMaker 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, InfoMaker displays an error message.

Failover parameter

The FailoverPartner 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 SNC interface.

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

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

  • The FailoverPartner DBParm is supplied at connect time

  • The SQL Server database is configured for mirroring

  • InfoMaker is able to reconnect successfully when the existing connection is lost

When failover occurs:

  • InfoMaker returns an error code (998) and triggers the failover event

  • Existing cursors cannot be used and should be closed

  • Any failed database operation can be tried again

  • Any uncommitted transaction is lost. New transactions must be started

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 InfoMaker 2017 and later.

Precision settings

When you map to a table column in a SQL Server 2008 database, InfoMaker includes a column labeled "Dec" in the Column Specifications view of the Report 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, InfoMaker 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 InfoMaker 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);

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 InfoMaker applications. However, existing InfoMaker 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 InfoMaker

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 InfoMaker applications to update other columns in the database. If their return values are compatible with InfoMaker datatypes, you can use CLR datatype methods in PowerScript, dynamic SQL, embedded SQL or in report 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

Reports 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 InfoMaker 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 reports. 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 InfoMaker 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 InfoMaker applications.

T-SQL enhancements

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;

Using the MERGE statement in ISQL

A MERGE statement must be terminated by a semicolon. By default the ISQL painter uses a semicolon as a SQL terminating character, so to use a MERGE statement in ISQL, the terminating character must be changed to a colon (:), a forward slash (/), or some other special character.

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

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 reports (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 reports (syntax mode).

For example:

Select ProductID, OrderQty from SalesOrderDetail 
   with (FORCESEEK);

Unsupported SQL Server 2008 features

The InfoMaker SNC interface does 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.

Notes on using the SNC interface

SQL batch statements

The SNC interface supports SQL batch statements. However, they must be enclosed in a BEGIN...END block or start with the keyword DECLARE:

  • Enclosed in a BEGIN...END block:

    BEGIN
    INSERT INTO t_1 values(1, 'sfdfs')
    INSERT INTO t_2 values(1, 'sfdfs')
    SELECT * FROM t_1
    SELECT * FROM t_2
    END
  • Starting with the keyword DECLARE:

    DECLARE @p1 int, @p2 varchar(50)
    SELECT  @p1 = 1
    EXECUTE  sp_4 @p1, @p2 OUTPUT
    SELECT @p2  AS  'output'

You can run the batch of SQL statements in the Database painter. For example:

String batchSQL //contains a batch of SQL statements
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :batchSQL ;
OPEN DYNAMIC my_cursor ;
//first result set
FETCH my_cursor INTO . . .
//second result set
FETCH my_cursor INTO . . 
. . .
CLOSE my_cursor ;

Connection pooling

The SNC interface pools connections automatically using OLE DB pooling. To disable OLE DB pooling, type the following in the Extended Properties box on the Connection tab page in the Database Profile Setup dialog box:

OLE DB Services=-4

Triggers and synonyms in the Database painter

In the Objects view for SNC profiles in the Database painter, triggers display for tables in the Tables folder and Microsoft SQL Server 2005 synonyms display for tables and views.