OLE DB connection parameters

OLE DB connection parameters are defined by Microsoft. The SQL Anywhere OLE DB provider supports a subset of these connection parameters. A typical connection string looks like this: 

"Provider=SAOLEDB;Data Source=myDsn;Initial Catalog=myDbn;
    User ID=myUid;Password=myPwd"

Below are the OLE DB connection parameters that are supported by the provider. In some cases, OLE DB connection parameters are identical to (for example, Password) or resemble (for example, User ID) SQL Anywhere connection parameters. Note the use of spaces in many of these connection parameters.

  • Provider   This parameter is used to identify the SQL Anywhere OLE DB provider (SAOLEDB).

  • User ID   This connection parameter maps directly to the SQL Anywhere UserID (UID) connection parameter. For example: User ID=DBA.

  • Password   This connection parameter maps directly to the SQL Anywhere Password (PWD) connection parameter. For example: Password=sql.

  • Data Source   This connection parameter maps directly to the SQL Anywhere DataSourceName (DSN) connection parameter. For example: Data Source=SQL Anywhere 12 Demo.

  • Initial Catalog   This connection parameter maps directly to the SQL Anywhere DatabaseName (DBN) connection parameter. For example: Initial Catalog=demo.

  • Location   This connection parameter maps directly to the SQL Anywhere Host connection parameter. The parameter value has the same form as the Host parameter value. For example: Location=localhost:4444.

  • Extended Properties   This connection parameter is used by OLE DB to pass in all the SQL Anywhere specific connection parameters. For example: Extended Properties="UserID=DBA;DBKEY=V3moj3952B;DBF=demo.db".

    ADO uses this connection parameter to collect and pass in all the connection parameters that it does not recognize.

    Some Microsoft connection windows have a field called Prov String or Provider String. The contents of this field are passed as the value to Extended Properties.

  • OLE DB Services   This connection parameter is not directly handled by the SQL Anywhere OLE DB provider. It controls connection pooling in ADO.

  • Prompt   This connection parameter governs how a connection attempt handles errors. The possible prompt values are 1, 2, 3, or 4. The meanings are DBPROMPT_PROMPT (1), DBPROMPT_COMPLETE (2), DBPROMPT_COMPLETEREQUIRED (3), and DBPROMPT_NOPROMPT (4).

    The default prompt value is 4 which means the provider does not present a connect window. Setting the prompt value to 1 causes a connect window to always appear. Setting the prompt value to 2 causes a connect window to appear if the initial connection attempt fails. Setting the prompt value to 3 causes a connect window to appear if the initial connection attempt fails but the provider disables the controls for any information not required to connect to the data source.

  • Window Handle   The application can pass the handle of the parent window, if applicable, or a null pointer if either the window handle is not applicable or the provider does present any windows. The window handle value is typically 0 (NULL).

Other OLE DB connection parameters can be specified but they are ignored by the OLE DB provider.

When the SQL Anywhere OLE DB provider is invoked, it gets the property values for the OLE DB connection parameters. Here is a typical set of property values obtained from Microsoft's RowsetViewer application. 

User ID 'DBA' 
Password 'sql' 
Location 'localhost:4444' 
Initial Catalog 'demo'
Data Source 'testds' 
Extended Properties 'appinfo=api=oledb' 
Prompt 2 
Window Handle 0

The connection string that the provider constructs from this set of parameter values is:

'DSN=testds;HOST=localhost:4444;DBN=demo;UID=DBA;PWD=sql;appinfo=api=oledb'

The SQL Anywhere OLE DB provider uses the connection string, Window Handle, and Prompt values as parameters to the database server connection call that it makes.

This is a simple ADO connection string example.

connection.Open "Provider=SAOLEDB;UserID=DBA;Location=localhost:4444;Pwd=sql"

ADO parses the connection string and passes all of the unrecognized connection parameters in Extended Properties. When the SQL Anywhere OLE DB provider is invoked, it gets the property values for the OLE DB connection parameters. Here is the set of property values obtained from the ADO application that used the connection string shown above. 

User ID ''
Password ''
Location 'localhost:4444'
Initial Catalog ''
Data Source ''
Extended Properties 'UserID=DBA;Pwd=sql'
Prompt 4
Window Handle 0

The connection string that the provider constructs from this set of parameter values is:

'HOST=localhost:4444;UserID=DBA;Pwd=sql'

The provider uses the connection string, Window Handle, and Prompt values as parameters to the database server connection call that it makes.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1