SQL CONNECT DATASOURCE Statement

< > Connection Statements Example Flow Chart Table of Contents

The SQL CONNECT DATASOURCE statement establishes a connection to a named data source. A user name and user password may be supplied for data sources with security constraints. A login timeout value may also be specified.

ConnectionHandle (output). This argument must refer to a numeric integer data item with at least six digits of precision. The argument specifies the data item where the connection handle value that identifies a successful connection is to be stored.

DataSourceName (input). This argument must specify a nonnumeric value. The value identifies the data source name of the data source to which the connection handle will refer after a successful connection is established. The data source name value may be all spaces, in which case the driver manager will prompt the user for the data source name, except that, if ConnectionCompletion is configured as "NoPrompt", an ODBC error would occur (for more information on configuration, see the topic InstantSQL Configuration).

UserName (input/output). This argument must specify a nonnumeric data item. The value of the data item identifies the user name for the user that is attempting to connect to the data source. This argument is optional and only needs to be specified for data sources that have security constraints. If the argument is to be omitted when a user password or login timeout is specified, the keyword OMITTED must be specified as a placeholder. On return from the SQL CONNECT DATASOURCE statement, the user name that was used to login to the database is placed into this data item if this argument is specified.

UserPassword (input/output). This argument must specify a nonnumeric data item. The value of the data item identifies the user password for the user that is attempting to connect to the data source. This argument is optional and only needs to be specified for data sources that have security constraints. If the argument is to be omitted when a login timeout is specified, the keyword OMITTED must be specified as a placeholder. On return from the SQL CONNECT DATASOURCE statement, the user password that was used to login to the database is placed into this data item if this argument is specified.

LoginTimeout (input). This argument must specify a numeric integer value. The value indicates the number of seconds to wait for a login request to complete before returning to the application. This argument is optional. The default value is driver-dependent; ODBC 2.0 specified that the default be nonzero. If the value is 0, the timeout is disabled and a connection attempt will wait indefinitely.

A data source must be connected before any queries may be created for the data source. The only query not requiring a prior connection is the one created by SQL QUERY DATASOURCES.

Valid connection handle values returned by InstantSQL are in the range 1 through 9999. The application program may use the value zero in a connection handle data item to indicate that the connection has not been made or has been disconnected.

After a successful SQL CONNECT DATASOURCE statement, the value returned in the ConnectionHandle argument can be used to refer to the connected data source in any of the following statements:

SQL COMMIT TRANSACTION

SQL DESCRIBE CONNECTION

SQL DISCONNECT DATASOURCE

SQL QUERY PROCEDURE-COLUMNS

SQL QUERY TABLES

SQL QUERY TYPES

SQL ROLLBACK TRANSACTION

SQL START TRANSACTION

The statement SQL QUERY DATASOURCES can be used to obtain the names of available data sources.

The SQL SET USER statement can be used prior to the SQL CONNECT DATASOURCE statement to establish a user name and user password for connections. If the SQL CONNECT DATASOURCE statement specifies either the UserName or UserPassword arguments, then the values specified in the SQL CONNECT DATASOURCE statement override any values set by an SQL SET USER statement for that connection attempt only.

When the user name or password is not provided either in the SQL CONNECT DATASOURCE statement or in a prior SQL SET USER statement or if the specified argument values are spaces, the user will normally be prompted for this information with a login dialog box if it is needed by the data source. The configuration option ConnectionCompletion may be set to "NoPrompt" to prevent prompting, in which case the connection will fail if insufficient or incorrect information is provided. The values of the user name and password used to login to the database are returned in the respective argument data items; this allows the application to specify this information on subsequent connects so that the user is not prompted again for login information.

SQL CONNECT DATASOURCE Statement Examples:

        CONNECT-DB2.
            MOVE "DB2 SAMPLE Database" TO sql-DataSourceName.
            PERFORM ISQL-CONNECT-DATASOURCE.
            MOVE sql-ConnectionHandle TO DB2-ConnectionHandle.

        CONNECT-SQLServer.
            MOVE "SQL Server NWIND Database" TO sql-DataSourceName.
            PERFORM ISQL-CONNECT-DATASOURCE.
            MOVE sql-ConnectionHandle TO SQLServer-ConnectionHandle.

        ISQL-CONNECT-DATASOURCE.
            SQL CONNECT DATASOURCE
                sql-ConnectionHandle,
                sql-DataSourceName.

        ISQL-CONNECT-DATASOURCE-USER.
            SQL CONNECT DATASOURCE
                sql-ConnectionHandle,
                sql-DataSourceName
                sql-UserName
                sql-UserPassword.