ConnectOption database parameter

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.

Controls

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

Syntax

ConnectOption=' SQL_DRIVER_CONNECT,<span>value</span>;    <br>    SQL_INTEGRATED_SECURITY,<span>value</span>;<br>    SQL_OPT_TRACE,<span>value</span>;    <br>    SQL_OPT_TRACEFILE,<span>value</span>;<br>    SQL_PRESERVE_CURSORS,<span>value</span>;<br>    SQL_USE_PROCEDURE_FOR_PREPARE,<span>value</span> '

1	ConnectOption=' SQL_DRIVER_CONNECT,<span>value</span>; <br> SQL_INTEGRATED_SECURITY,<span>value</span>;<br> SQL_OPT_TRACE,<span>value</span>; <br> SQL_OPT_TRACEFILE,<span>value</span>;<br> SQL_PRESERVE_CURSORS,<span>value</span>;<br> SQL_USE_PROCEDURE_FOR_PREPARE,<span>value</span> '

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 “About ODBC Driver Manager” 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

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.DbParameter="ConnectOption ='SQL_DRIVER_CONNECT,<br>SQL_DRIVER_NOPROMPT;SQL_INTEGRATED_SECURITY,<br>SQL_IS_ON;SQL_OPT_TRACE,SQL_OPT_TRACE_ON;<br>SQL_OPT_TRACEFILE,C:PBodbctrce.log;<br>SQL_PRESERVE_CURSORS,SQL_PC_ON;<br>SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_OFF'"

1	SQLCA.DbParameter="ConnectOption ='SQL_DRIVER_CONNECT,<br>SQL_DRIVER_NOPROMPT;SQL_INTEGRATED_SECURITY,<br>SQL_IS_ON;SQL_OPT_TRACE,SQL_OPT_TRACE_ON;<br>SQL_OPT_TRACEFILE,C:PBodbctrce.log;<br>SQL_PRESERVE_CURSORS,SQL_PC_ON;<br>SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_OFF'"

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 12.6

August 14, 2023

Was this article helpful?

0 Comments

Inline Feedbacks

View all comments

ConnectOption – PB Docs 126