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.
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_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:PBodbctrce.log; SQL_PRESERVE_CURSORS,SQL_PC_ON; SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_OFF'" |
Thank you for watching.