Adaptive Server Enterprise

This section describes how to use the Adaptive Server Enterprise database interface in InfoMaker.

Client Library API

The Adaptive Server database interface uses the Open Client CT-Library (CT-Lib) application programming interface (API) to access the database.

When you connect to an Adaptive Server database, InfoMaker makes the required calls to the API. Therefore, you do not need to know anything about CT-Lib to use the database interface.

Supported versions for Adaptive Server

You can access Adaptive Server versions 15.x and 16.x using the Adaptive Server database interface. Use of this interface to access other Open Server programs is not supported.

The Adaptive Server database interface uses a DLL named PBSYC190.dll to access the database through the Open Client CT-Lib API.

Supported Adaptive Server datatypes

The Adaptive Server interface supports the SAP datatypes listed in the following table in reports.

Binary

Numeric

Bit

NVarChar (ASE 12.5 only)

Char (see Column-length limits)

Real

DateTime

SmallDateTime

Decimal

SmallInt

Double precision

SmallMoney

Float

Text

Identity

Timestamp

Image

TinyInt

Int

VarBinary

Money

VarChar

NChar (ASE 12.5 only)

BigTime (ASE 15.5 or later)

BigDateTime (ASE 15.5 or later)

 


Accessing Unicode data

InfoMaker can connect, save, and retrieve data in both ANSI/DBCS and Unicode databases. When character data or command text is sent to the database, InfoMaker sends a DBCS string if the UTF8 database parameter is set to 0 (the default). If UTF8 is set to 1, InfoMaker sends a UTF-8 string.

The database server must be configured correctly to accept UTF-8 strings. See the description of the UTF-8 database parameter in the online Help for more information.

A Unicode database is a database whose character set is set to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All data must be in Unicode format, and any data saved to the database must be converted to Unicode data implicitly or explicitly.

A database that uses ANSI (or DBCS) as its character set might use special datatypes to store Unicode data. These datatypes are NCHAR, NVARCHAR, and NVARCHAR2. Columns with this datatype can store only Unicode data. Any data saved into such a column must be converted to Unicode explicitly. This conversion must be handled by the database server or client.

Column-length limits

Adaptive Server 12.0 and earlier have a column-length limit of 255 bytes. Adaptive Server 12.5.x supports wider columns for Char, VarChar, Binary, and VarBinary datatypes, depending on the logical page size and the locking scheme used by the server.

In InfoMaker, you can use these wider columns for Char and VarChar datatypes with Adaptive Server 12.5.x when the following conditions apply:

  • The Release database parameter is set to 12.5 or 12.5.1.

  • You are accessing the database using Open Client 12.5.x.

The database must be configured to use a larger page size to take full advantage of the widest limits.

For detailed information about wide columns and configuration issues, see the Adaptive Server 12.5.x documentation on the SAP web site. For more information about the Release database parameter, see the online Help.

Basic software components for Adaptive Server

You must install the software components in the following figure to access an Adaptive Server database in InfoMaker.

Figure: Components of an Adaptive Server Enterprise connection

Preparing to use the Adaptive Server database

Before you define the interface and connect to an Adaptive 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 Adaptive Server database interface.

  3. Verify that you can connect to Adaptive Server outside InfoMaker.

  4. Install the required InfoMaker stored procedures in the sybsystemprocs database.

Preparing an Adaptive Server database for use with InfoMaker involves these four basic tasks.

Step 1: Install and configure the database server

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

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

  1. Make sure the Adaptive Server database software is installed on the server specified in your database profile.

    You must obtain the database server software from SAP.

    For installation instructions, see your Adaptive Server documentation.

  2. Make sure the supported network software (for example, TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the database server at your site.

    You must install the network communication driver that supports the network protocol and operating system platform you are using. The driver is installed as part of the Net-Library client software.

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

  3. Install the required Open Client CT-Library (CT-Lib) software on each client computer on which InfoMaker is installed.

    You must obtain the Open Client software from SAP. Make sure the version of Open Client you install supports all of the following:

    The operating system running on the client computer

    The version of Adaptive Server that you want to access

    The version of InfoMaker that you are running

    Required client software versions

    To use the SYC Adaptive Server interface, you must install Open Client version 10.0.4 or later.

  4. Make sure the Open Client software is properly configured so that you can connect to the database at your site.

    Installing the Open Client software places the SQL.INI configuration file in the Adaptive Server directory on your computer.

    SQL.INI provides information that Adaptive Server needs to find and connect to the database server at your site. You can enter and modify information in SQL.INI by using the configuration utility that comes with the Open Client software.

    For information about setting up the SQL.INI or other required configuration file, see your Adaptive Server documentation.

  5. If required by your operating system, make sure the directory containing the Open Client software is in your system path.

  6. Make sure only one copy of each of the following files is installed on your client computer:

    • Adaptive Server interface DLL

    • Network communication DLL (for example, NLWNSCK.DLL for Windows Sockets-compliant TCP/IP)

    • Database vendor DLL (for example, LIBCT.DLL)

Step 2: Install the database interface

In the InfoMaker Setup program, select the Typical install, or select the Custom install and select the Adaptive Server Enterprise (SYC) database interface.

Step 3: Verify the connection

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

Some possible ways to verify the connection are by running the following tools:

  • Accessing the database server

    Tools such as the Open Client/Open Server Configuration utility (or any Ping utility) check whether you can reach the database server from your computer.

  • Accessing the database

    Tools such as ISQL (interactive SQL utility) check whether you can log in to the database and perform database operations. It is a good idea to specify the same connection parameters you plan to use in your InfoMaker database profile to access the database.

Step 4: Install the InfoMaker stored procedures

InfoMaker requires you to install certain stored procedures in the sybsystemprocs database before you connect to an Adaptive Server database for the first time. InfoMaker uses these stored procedures to get information about tables and columns from the DBMS system catalog.

Run the SQL script or scripts required to install the InfoMaker stored procedures in the sybsystemprocs database.

For instructions, see Installing InfoMaker stored procedures in Adaptive Server databases.

What to do next

For instructions on defining the Adaptive Server database interface in InfoMaker, see Defining the Adaptive Server database interface.

Defining the Adaptive Server database interface

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

For information on how to define a database profile, see Using database profiles.

Using Open Client security services

The Adaptive Server interface provides several DBParm parameters that support Open Client 11.1.x or later network-based security services in your application. If you are using the required database, security, and InfoMaker software, you can build applications that take advantage of Open Client security services.

What are Open Client security services?

Open Client 11.1.x or later security services allow you to use a supported third-party security mechanism (such as CyberSafe Kerberos) to provide login authentication and per-packet security for your application. Login authentication establishes a secure connection, and per-packet security protects the data you transmit across the network.

Requirements for using Open Client security services

To use Open Client security services in your application, all of the following must be true:

  • Accessing Adaptive Server through Open Client 11.1.1x or later

    You are accessing an Adaptive Server database server using Open Client Client-Library (CT-Lib) 11.x or later software

  • Have network security mechanism and driver

    You have the required SAP-supported network security mechanism and SAP-supplied security driver properly installed and configured for your environment. Depending on your operating system platform, examples of supported security mechanisms include: Distributed Computing Environment (DCE) security servers and clients, CyberSafe Kerberos, and Windows NT LAN Manager Security Services Provider Interface (SSPI).

    For information about the third-party security mechanisms and operating system platforms that Appeon has tested with Open Client security services, see the Open Client documentation.

  • Can access the secure server outside InfoMaker

    You must be able to access a secure Adaptive Server server using Open Client 11.1.x or later software from outside InfoMaker.

    To verify the connection, use a tool such as ISQL or SQL Advantage to make sure you can connect to the server and log in to the database with the same connection parameters and security options you plan to use in your InfoMaker application.

  • Using database interface

    You are using the SYC Adaptive Server interface to access the database.

  • Release DBParm parameter set to the appropriate value for your database

    You have set the Release DBParm parameter to 11, 11.5, 12, 12.5, or 12.5.1 to specify that your application should use the appropriate version of the Open Client CT-Lib software.

    For instructions, see Release in the online Help.

  • Security mechanism and driver support requested service

    The security mechanism and driver you are using must support the service requested by the DBParm parameter.

Security services DBParm parameters

If you have met the requirements described in Requirements for using Open Client security services, you can set the security services DBParm parameters in the Database Profile Setup dialog box for your connection.

There are two types of DBParm parameters that you can set to support Open Client security services: login authentication and per-packet security.

Login authentication DBParms

The following login authentication DBParm parameters correspond to Open Client 11.1.x or later connection properties that allow an application to establish a secure connection.

Sec_Channel_Bind

Sec_Cred_Timeout

Sec_Delegation

Sec_Keytab_File

Sec_Mechanism

Sec_Mutual_Auth

Sec_Network_Auth

Sec_Server_Principal

Sec_Sess_Timeout

For instructions on setting these DBParm parameters, see their descriptions in online Help.

Per-packet security DBParms

The following per-packet security DBParm parameters correspond to Open Client 11.1.x or later connection properties that protect each packet of data transmitted across a network. Using per-packet security services might create extra overhead for communications between the client and server.

Sec_Confidential

Sec_Data_Integrity

Sec_Data_Origin

Sec_Replay_Detection

Sec_Seq_Detection

For instructions on setting these DBParm parameters, see their descriptions in online Help.

Using Open Client directory services

The Adaptive Server interface provides several DBParm parameters that support Open Client 11.1.x or later network-based directory services in your application. If you are using the required database, directory services, and InfoMaker software, you can build applications that take advantage of Open Client directory services. 

What are Open Client directory services?

Open Client 11.1.x or later directory services allow you to use a supported third-party directory services product (such as the Windows NT Registry) as your directory service provider. Directory services provide centralized control and administration of the network entities (such as users, servers, and printers) in your environment.

Requirements for using Open Client directory services

To use Open Client directory services in your application, all of the following must be true:

  • You are accessing an Adaptive Server database server using Open Client Client-Library (CT-Lib) 11.x or later software

  • You have the required SAP-supported directory service provider software and SAP-supplied directory driver properly installed and configured for your environment. Depending on your operating system platform, examples of supported security mechanisms include: the Windows NT Registry, Distributed Computing Environment Cell Directory Services (DCE/CDS), Banyan StreetTalk Directory Assistance (STDA), and Novell NetWare Directory Services (NDS).

    For information about the directory service providers and operating system platforms that Appeon has tested with Open Client directory services, see the Open Client documentation.

  • You must be able to access a secure Adaptive Server server using Open Client 11.1.x or later software from outside InfoMaker.

    To verify the connection, use a tool such as ISQL or SQL Advantage to make sure you can connect to the server and log in to the database with the same connection parameters and directory service options you plan to use in your InfoMaker application.

  • You are using the SYC Adaptive Server interface to access the database.

  • You must use the correct syntax as required by your directory service provider when specifying the server name in a database profile. Different providers require different syntax based on their format for specifying directory entry names.

    For information and examples for different directory service providers, see Specifying the server name with Open Client directory services.

  • You have set the Release DBParm parameter to 11, 11.5, 12, 12.5, or 12.5.1 to specify that your application should use the behavior of the appropriate version of the Open Client CT-Lib software.

    For instructions, see Release (Adaptive Server Enterprise) in the online Help.

  • The directory service provider and driver you are using must support the service requested by the DBParm parameter.

Specifying the server name with Open Client directory services

When you are using Open Client directory services in a InfoMaker application, you must use the syntax required by your directory service provider when specifying the server name in a database profile to access the database.

Different directory service providers require different syntax based on the format they use for specifying directory entry names. Directory entry names can be fully qualified or relative to the default (active) Directory Information Tree base (DIT base) specified in the Open Client/Server configuration utility.

The DIT base is the starting node for directory searches. Specifying a DIT base is analogous to setting a current working directory for UNIX or MS-DOS file systems. (You can specify a nondefault DIT base with the DS_DitBase DBParm parameter. For information, see DS_DitBase in the online Help.)

Windows NT Registry server name example

This example shows typical server name syntax if your directory service provider is the Windows NT Registry.

Node name: SALES:software\sybase\server\SYS12NTDIT base: SALES:software\sybase\serverServer name: SYS12NT

To specify the server name in a database profile:

  • Type the following in the Server box on the Connection tab in the Database Profile Setup dialog box. Do not start the server name with a backslash (\).

    SYS12NT

DCE/DCS server name example

This example shows typical server name syntax if your directory service provider is Distributed Computing Environment Cell Directory Services (DCE/CDS).

Node name: /.../boston.sales/dataservers/sybase/SYS12DIT base: /../boston.sales/dataserversServer name: sybase/SYS12

To specify the server name in a database profile:

  • Type the following in the Server box on the Connection tab in the Database Profile Setup dialog box. Do not start the server name with a slash (/).

    sybase/SYS12

Banyan STDA server name example

This example shows typical server name syntax if your directory service provider is Banyan StreetTalk Directory Assistance (STDA).

Node name: SYS12@sales@chicagoDIT base: chicagoServer name: SYS12@sales

To specify the server name in a database profile:

  • Type the following in the Server box on the Connection tab in the Database Profile Setup dialog box. Do not end the server name with @.

    SYS12@sales

Novell NDS server name example

This example shows typical server name syntax if your directory service provider is Novell NetWare Directory Services (NDS).

Node name: CN=SYS12.OU=miami.OU=sales.O=sybaseDIT base: OU=miami.OU=sales.O=sybaseServer name: SYS12

To specify the server name in a database profile:

  • Type the following in the Server box on the Connection tab in the Database Profile Setup dialog box. Do not start the server name with CN=.

    SYS12

Directory services DBParm parameters

If you have met the requirements described in Requirements for using Open Client directory services, you can set the directory services DBParm parameters in a database profile for your connection.

The following DBParm parameters correspond to Open Client 11.1.x or later directory services connection parameters:

DS_Alias

DS_Copy

DS_DitBase

DS_Failover

DS_Password (Open Client 12.5 or later)

DS_Principal

DS_Provider

DS_TimeLimit

For instructions on setting these DBParm parameters, see their descriptions in the online Help.

Using PRINT statements in Adaptive Server stored procedures

The SYC Adaptive Server database interface allows you to use PRINT statements in your stored procedures for debugging purposes.

This means, for example, that if you turn on Database Trace when accessing the database through the SYC interface, PRINT messages appear in the trace log but they do not return errors or cancel the rest of the stored procedure.

Creating a DataWindow based on a heterogeneous cross-database join

This functionality is available through the use of Adaptive Server's Component Integration Services. Component Integration Services allows you to connect to multiple remote heterogeneous database servers and define multiple proxy tables that reference the tables residing on those servers.

For information on how to create proxy tables, see the Adaptive Server documentation.

What to do next

For instructions on connecting to the database, see Connecting to a database.