ConnectOption

Description

Sets driver-specific connection options when you are accessing an ODBC data source in PowerBuilder. These options specify the following:

How the ODBC driver prompts for additional connection information
What type of security to use for a Microsoft SQL Server connection
Whether the ODBC Driver Manager Trace is on or off and what trace file it uses
Whether cursors are closed or left open on a SQLTransact call
How temporary stored procedures are treated for a SQLPrepare call

Certain ConnectOption parameters apply to all ODBC drivers, whereas others apply only to particular ODBC drivers.

For information on each ConnectOption parameter and whether you can use it with your ODBC driver, see the table in the Syntax section.

When to specify ConnectOption

You must specify the ConnectOption parameter before connecting to an ODBC data source. The ConnectOption settings take effect when you connect to the database.

Applies to

ODBC (if driver and back-end DBMS support this feature)

Syntax

ConnectOption=' SQL_DRIVER_CONNECT,value;   
   SQL_INTEGRATED_SECURITY,value;
   SQL_OPT_TRACE,value;   
   SQL_OPT_TRACEFILE,value;
   SQL_PRESERVE_CURSORS,value;
   SQL_USE_PROCEDURE_FOR_PREPARE,value '

The following table lists the applicable ODBC drivers, purpose, and values for each ConnectOption parameter.

Parameter	Description
SQL_DRIVER_CONNECT	Driver Any ODBC driver that supports the SQLDriverConnect API call. Purpose Specifies how the ODBC driver prompts for additional connection information (such as the user ID and password) when connecting to an ODBC data source. Values The values you can specify are: SQL_DRIVER_COMPLETE (Default) If the connection string contains correct and sufficient information to connect, the driver connects to the specified data source. If any information is incorrect or missing, the driver displays one or more dialog boxes to prompt for the required connection parameters. The driver then connects to the specified data source. SQL_DRIVER_COMPLETE_REQUIRED The driver takes the same actions as it does when SQL_DRIVER_COMPLETE is set. In addition, the driver disables the controls for any information not required to connect to the data source. SQL_DRIVER_PROMPT The driver displays one or more dialog boxes to prompt for the required connection parameters. The driver then connects to the specified data source and builds a connection string from the information specified in the dialog boxes. SQL_DRIVER_NOPROMPT If the connection string contains correct and sufficient information to connect, the driver connects to the specified data source. If any information is incorrect or missing, the driver returns an error.
SQL_INTEGRATED_SECURITY	Driver Microsoft SQL Server ODBC driver (not supplied with PowerBuilder). Purpose Specifies the type of connection to the Microsoft SQL Server database server. Values The values you can specify are: SQL_IS_OFF (Default) Request a normal (nontrusted) connection to SQL Server using standard security. If you specify SQL_IS_OFF, you cannot request a trusted connection to SQL Server using integrated security. SQL_IS_ON Request a trusted connection to SQL Server using integrated security regardless of the login security currently in use on the database server. For more about security mechanisms in Microsoft SQL Server, see the Microsoft documentation.
SQL_OPT_TRACE	Driver Any ODBC driver. Purpose Turns on or turns off the ODBC Driver Manager Trace in PowerBuilder to troubleshoot a connection to an ODBC data source. The ODBC Driver Manager Trace provides detailed information about the ODBC API function calls that PowerBuilder makes when connected to an ODBC data source. Values The values you can specify are: SQL_OPT_TRACE_OFF (Default) Turns off the ODBC Driver Manager Trace. SQL_OPT_TRACE_ON Turns on the ODBC Driver Manager Trace. For instructions on using the ODBC Driver Manager Trace, see the section called “About ODBC Driver Manager Trace” in Connecting to Your Database.
SQL_OPT_TRACEFILE	Driver Any ODBC driver. Purpose Specifies the name of the trace file where you want PowerBuilder to send the output of the ODBC Driver Manager Trace. PowerBuilder appends the output to the trace file you specify until you stop the trace. To display the trace file, you can use the File Editor (in PowerBuilder) or any text editor (outside PowerBuilder). Values You can specify any filename for the trace file, following the naming conventions of your operating system. By default, if tracing is on and you have not specified a trace file, PowerBuilder sends ODBC Driver Manager Trace output to the file \SQL.LOG.
SQL_PRESERVE_CURSORS	Driver Microsoft SQL Server ODBC driver (not supplied with PowerBuilder). Purpose Specifies whether cursors are closed or left open on a SQLTransact call. Values The values you can specify are: SQL_PC_OFF (Default) Close all cursors on a SQLTransact call. SQL_PC_ON Keep server cursors open on a SQLTransact call.
SQL_USE_PROCEDURE_FOR_PREPARE	Driver Microsoft SQL Server ODBC driver (not supplied with PowerBuilder). Purpose Specifies how temporary stored procedures are treated for a SQLPrepare call. Values The values you can specify are: SQL_UP_ON (Default) Generate temporary stored procedures for a SQLPrepare call. SQL_UP_OFF Do not generate temporary stored procedures for a SQLPrepare call. The SQL statement is stored, compiled, and run at execution time. Syntax error checking does not occur until execution time. SQL_UP_ON_DROP Explicitly drop temporary stored procedures for a subsequent SQLPrepare call or when a statement handle (hstmt) is freed for reuse.

Default value

ConnectOption='SQL_DRIVER_CONNECT, SQL_DRIVER_COMPLETE;

SQL_INTEGRATED_SECURITY,SQL_IS_OFF;

SQL_OPT_TRACE,SQL_OPT_TRACE_OFF;

SQL_PRESERVE_CURSORS,SQL_PC_OFF;

SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_ON'

Usage

Microsoft Server ODBC driver

The ConnectOption parameter applies only if you are accessing a SQL Server database with the Microsoft ODBC SQL Server driver.

You must obtain the Microsoft SQL Server ODBC driver from Microsoft Corporation. This driver is not supplied with PowerBuilder.

Examples

To specify nondefault options for the ConnectOption parameter:

Database profile

Complete the Options page in the Database Profile Setup - ODBC dialog box. Each ConnectOption parameter corresponds to an option in the dialog box, as follows:

ConnectOption parameter	Corresponding option
SQL_DRIVER_CONNECT	Connect Type
SQL_INTEGRATED_SECURITY	Integrated Security
SQL_OPT_TRACE	Trace ODBC API Calls
SQL_OPT_TRACEFILE	Trace File
SQL_PRESERVE_CURSORS	Preserve Cursors
SQL_USE_PROCEDURE_FOR_PREPARE	Use Procedure for Prepare

Application

Type the following in code:

SQLCA.DBParm="ConnectOption ='SQL_DRIVER_CONNECT,
SQL_DRIVER_NOPROMPT;SQL_INTEGRATED_SECURITY,
SQL_IS_ON;SQL_OPT_TRACE,SQL_OPT_TRACE_ON;
SQL_OPT_TRACEFILE,C:\PB\odbctrce.log;
SQL_PRESERVE_CURSORS,SQL_PC_ON;
SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_OFF'"

Prev	Up	Next
ConnectAs	Home	ConnectString

Connection ReferenceDatabase parameters and supported database interfaces

ConnectOption

When to specify ConnectOption

Connection Reference
Database parameters and supported database interfaces