Connecting to a Data Source

CONNECTING TO A DATA SOURCE

This chapter briefly introduces data sources. It then describes how to establish a connection to a data source.

About Data Sources

A data source consists of the data a user wants to access, its associated DBMS, the platform on which the DBMS resides, and the network (if any) used to access that platform. Each data source requires that a driver provide certain information in order to connect to it. At the core level, this is defined to be the name of the data source, a user ID, and a password. ODBC extensions allow drivers to specify additional information such as a network address or additional passwords.

If the used data source name can be interpreted as a valid SOLID Server network name, the client first connects using the information given in the data source name. A valid network name consists of a communication protocol, and optional host computer name and a server name. See SOLID Server Administrator's Guide for more information about listen names.

If the data source name is not a valid SOLID Server listen name, the information needed to locate a server in the network is read from the ODBC.INI file or registry.

The connection information for each data source is stored in the ODBC.INI file or registry, which is created during installation and maintained with an administration program. A section in this file lists the available data sources. Additional sections describe each data source in detail, specifying the driver name, a description, and any additional information the driver needs in order to connect to the data source.

Applications that directly access the SOLID SQL API must connect to the server using a valid listen name. If the data source name is not a valid SOLID Server listen name, all SOLID client applications search for a valid listen name from
1) the SOLID.INI file
2) the ODBC.INI or registry
See SOLID Server Administrator's Guide for more information about the use of data source names.

For example, suppose a user has five data sources: 1)Personnel and 2)Inventory, which use an Rdb DBMS, 3)Payroll, which uses an SQL Server DBMS, 4)tcp spiff 1313, which uses the SOLID Server network name to specify the server to connect to, and 5)Test, which uses a Logical Data Source Name. The section that lists the data sources might be:

[ODBC Data Sources]

Personnel=Rdb

Inventory=Rdb

Payroll=SQL Server

tcp spiff 1313=SOLID Server

Test=SOLID Server

Suppose also that an Rdb driver needs the ID of the last user to log in, a server name, and a schema declaration statement. The section that describes the Personnel data source might be:

[Personnel]

Driver=c:\windows\system\rdb.dll

Description=Personnel database: CURLY

Lastuid=smithjo

Server=curly

Schema=declare schema personnel filename "sys$sysdevice:[corpdata]personnel.rdb"

The sections that describe the SOLID Server data sources might be:

[tcp spiff 1313]

Driver=c:\windows\system\scliw16.dll

Description=Customer database

NetworkName=
[Test]

Driver=c:\windows\system\scliw16.dll

Description=Test database: Local

NetworkName=shmem solid

Initializing the ODBC Environment

Before an application can use any other ODBC function, it must initialize the ODBC interface and associate an environment handle with the environment. To initialize the interface and allocate an environment handle, an application:

1. Declares a variable of the type HENV. For example, the application could use the declaration:

   HENV henv1;

2. Calls SQLAllocEnv and passes it the address of the variable. The driver initializes the ODBC environment, allocates memory to store information about the environment, and returns the environment handle in the variable.

These steps should be performed only once by an application; SQLAllocEnv supports one or more connections to data sources.

Allocating a Connection Handle

Before an application can connect to a driver, it must allocate a handle for the connection. To allocate a connection handle, an application:

1. Declares a variable of the type HDBC. For example, the application could use the declaration:

   HDBC hdbc1;

2. Calls SQLAllocConnect and passes it the address of the variable. The driver allocates memory to store information about the connection and returns the connection handle in the variable.

Connecting to a Data Source

Next, the application specifies a specific driver and data source. It passes the following information to the driver in a call to SQLConnect:

Data source name The name of the data source being requested by the application.

User ID The login ID or account name for access to the data source if appropriate (optional).

Authentication string (password) A character string associated with the user ID that allows access to the data source (optional).

When an application calls SQLConnect, the Driver Manager uses the data source name to read the name of the driver DLL from the appropriate section of the ODBC.INI file or registry. It then loads the driver DLL and passes the SQLConnect arguments to it. If the driver needs additional information to connect to the data source, it reads this information from the same section of the ODBC.INI file.

If the application specifies a data source name that is not in the ODBC.INI file or registry, or if the application does not specify a data source name, the Driver Manager searches for the default data source specification. If it finds the default data source, it loads the default driver DLL and passes the application-specified data source name to it. If there is no default data source, the Driver Manager returns an error.

When an application uses SOLID SQL API directly and calls SQLConnect and does not specify a SOLID Server network name, it is read from the parameter Connect in the [Com] section of the SOLID.INI file. The SOLID.INI file must reside in the current working directory of the application or in path specified by the SOLIDDIR environment variable.

ODBC Extensions for Connections

ODBC extends the X/Open and SQL Access Group Call Level Interface to provide additional functions related to connections, drivers, and data sources. The remainder of this chapter describes these functions. To determine if a driver supports a specific function, an application calls SQLGetFunctions.

Connecting to a Data Source With SQLDriverConnect

SQLDriverConnect supports:

Data sources that require more connection information than the three arguments in SQLConnect.

Dialog boxes to prompt the user for all connection information.

Data sources that are not defined in the ODBC.INI file or registry.

SQLDriverConnect uses a connection string to specify the information needed to connect to a driver and data source.

A connection string contains the following information:

Data source name or driver description

Zero or more user IDs

Zero or more passwords

Zero or more data source-specific parameter values

The connection string is a more flexible interface than the data source name, user ID, and password used by SQLConnect. The application can use the connection string for multiple levels of login authorization or to convey other data source-specific connection information.

An application calls SQLDriverConnect in one of three ways:

Specifies a connection string that contains a data source name. The Driver Manager retrieves the full path of the driver DLL associated with the data source from the ODBC.INI file or registry. To retrieve a list of data source names, an application calls SQLDataSources.

Specifies a connection string that contains a driver description. The Driver Manager retrieves the full path of the driver DLL. To retrieve a list of driver descriptions, an application calls SQLDrivers.

Specifies a connection string that does not contain a data source name or a driver description. The Driver Manager displays a dialog box from which the user selects a data source name. The Driver Manager then retrieves the full path of the driver DLL associated with the data source.

The Driver Manager then loads the driver DLL and passes the SQLDriverConnect arguments to it.

The application may pass all the connection information the driver needs. It may also request that the driver always prompt the user for connection information or only prompt the user for information it needs. Finally, if a data source is specified, the driver may read connection information from the appropriate section of the ODBC.INI file or registry.

After the driver connects to the data source, it returns the connection information to the application. The application may store this information for future use.

If the application specifies a data source name that is not in the ODBC.INI file or registry, the Driver Manager searches for the default data source specification. If it finds the default data source, it loads the default driver DLL and passes the application-specified data source name to it. If there is no default data source, the Driver Manager returns an error.

The Driver Manager displays the following dialog box if the application calls SQLDriverConnect and requests that the user be prompted for information.

On request from the application, the driver displays a dialog box similar to the following to retrieve login information.

Connection Browsing With SQLBrowseConnect

SQLBrowseConnect supports an iterative method of listing and specifying the attributes and attribute values required to connect to a data source. For each level of a connection, an application calls SQLBrowseConnect and specifies the connection attributes and attribute values for that level. First level connection attributes always include the data source name or driver description; the connection attributes for later levels are data source dependent but might include the host, user name, and database.

Each time SQLBrowseConnect is called, it validates the current attributes, returns the next level of attributes, and returns a user-friendly name for each attribute. It may also return a list of valid values for those attributes. (Note, however, that for some drivers and attributes, this list may not be complete.) After an application has specified each level of attributes and values, SQLBrowseConnect connects to the data source and returns a complete connection string. This string can be used in conjunction with SQLDriverConnect to connect to the data source at a later time.

Connection Browsing Example for SQL Server

The following example shows how SQLBrowseConnect might be used to browse the connections available with a driver for Microsoft’s SQL Server. Although other drivers may require different connection attributes, this example illustrates the connection browsing model. (For the syntax of browse request and result strings, see SQLBrowseConnect in Chapter 13, Function Reference.)

First, the application requests a connection handle:

SQLAllocConnect(henv, &hdbc);

Next, the application calls SQLBrowseConnect and specifies a data source name:

SQLBrowseConnect(hdbc, "DSN=MySQLServer", SQL_NTS, szBrowseResult, 100, &cb);

Because this is the first call to SQLBrowseConnect, the Driver Manager locates the data source name (MySQLServer) in the ODBC.INI file and loads the corresponding driver DLL (SQLSRVR.DLL). The Driver Manager then calls the driver’s SQLBrowseConnect function with the same arguments it received from the application.

The driver determines that this is the first call to SQLBrowseConnect and returns the second level of connection attributes: server, user name, password, and application name. For the server attribute, it returns a list of valid server names. The return code from SQLBrowseConnect is SQL_NEED_DATA. The browse result string is:

"SERVER:Server={red,blue,green,yellow};UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?"

Note that each keyword in the browse result string is followed by a colon and one or more words before the equal sign. These words are the user-friendly name that an application can use as a prompt in a dialog box.

In its next call to SQLBrowseConnect, the application must supply a value for the SERVER, UID, and PWD keywords. Because they are prefixed by an asterisk, the APP and WSID keywords are optional and may be omitted. The value for the SERVER keyword may be one of the servers returned by SQLBrowseConnect or a user-supplied name.

The application calls SQLBrowseConnect again, specifying the green server and omitting the APP and WSID keywords and the user-friendly names after each keyword:

SQLBrowseConnect(hdbc, "SERVER=green;UID=Smith;PWD=Sesame", SQL_NTS, szBrowseResult, 100, &cb);

The driver attempts to connect to the green server. If there are any nonfatal errors, such as a missing keyword-value pair, SQLBrowseConnect returns SQL_NEED_DATA and remains in the same state as prior to the error. The application can call SQLError to determine the error. If the connection is successful, the driver returns SQL_NEED_DATA and returns the browse result string:

"*DATABASE:Database={master,model,pubs,tempdb}; *LANGUAGE:Language={us_english,Français}"

Since the attributes in this string are optional, the application can omit them. However, the application must call SQLBrowseConnect again. If the application chooses to omit the database name and language, it specifies an empty browse request string. In this example, the application chooses the pubs database and calls SQLBrowseConnect a final time, omitting the LANGUAGE keyword and the asterisk before the DATABASE keyword:

SQLBrowseConnect(hdbc, "DATABASE=pubs", SQL_NTS, szBrowseResult, 100, &cb);

Since the DATABASE attribute is the final connection attribute of the data source, the browsing process is complete, the application is connected to the data source, and SQLBrowseConnect returns SQL_SUCCESS. SQLBrowseConnect also returns the complete connection string as the browse result string:

"DSN=MySQLServer;SERVER=green;UID=Smith;PWD=Sesame;

DATABASE=pubs"

The final connection string returned by the driver does not contain the user-friendly names after each keyword, nor does it contain optional keywords not specified by the application. The application can use this string with SQLDriverConnect to reconnect to the data source on the current hdbc (after disconnecting) or to connect to the data source on a different hdbc:

SQLDriverConnect(hdbc, szBrowseResult, SQL_NTS, szConnStrOut, 100, &cb, SQL_DRIVER_NOPROMPT);

Connection Browsing Example for DAL

The following example shows how SQLBrowseConnect might be used in conjunction with a driver that uses Apple’s Data Access Language (DAL) to access an Oracle host. To browse the available connections, an application repeatedly calls SQLBrowseConnect:

retcode = SQLBrowseConnect(hdbc, szConnStrIn, SQL_NTS, szConnStrOut, 200, &cb);

In the first call, the application specifies a data source name in szConnStrIn. In each subsequent call, the application bases the value of szConnStrIn on the value of szConnStrOut returned by the previous call. The application continues to call SQLBrowseConnect as long as the function returns SQL_NEED_DATA. The following list shows, for each call to SQLBrowseConnect, the value that the application specifies for szConnStrIn and the values that the driver returns for retcode and szConnStrOut. (For the syntax of the strings used in szConnStrIn and szConnStrOut, see SQLBrowseConnect in Chapter 13, Function Reference.)

szConnStrIn : "DSN=DAL"
szConnStrOut: "HOST:Host={MyVax,Direct,Unix};UID1:Host User Name=?;PWD1:Password=?"
retcode     : SQL_NEED_DATA
szConnStrIn : "HOST=MyVax;UID1=Smith;PWD1=Sesame"
szConnStrOut: "DBMS:DBMS={Oracle,Informix,Sybase};UID2:DBMS User Name=?;PWD2:Password=?"
retcode     : SQL_NEED_DATA
szConnStrIn : "DBMS=Oracle;UID2=John;PWD2=Lion"
szConnStrOut: "DATABASE:Database={DalDemo,Personnel,Production};*ALIAS:Alias=?;*UID3:User Name=?;*PWD3:Password=?"
retcode     : SQL_NEED_DATA
szConnStrIn : "DATABASE=DalDemo;ALIAS=Demo"
szConnStrOut: "DSN=DAL;HOST=MyVax;UID1=Smith;PWD1=Sesame;DBMS=Oracle;UID2=John;PWD2=Lion;DATABASE=DalDemo;ALIAS=Demo"
retcode     : SQL_SUCCESS

Note that the database alias, database user name, and database password are optional, as indicated by the asterisk before those attribute names. The application chooses not to specify the user name and password.

Translating Data

An application and a data source can store data in different formats. For example, the application might use a different character set than the data source. ODBC provides a mechanism by which a driver can translate all data (data values, SQL statements, table names, row counts, and so on) that passes between the driver and the data source.

The driver translates data by calling functions in a translation DLL. A default translation DLL can be specified for the data source in the ODBC.INI file or registry; the application can override this by calling SQLSetConnectOption. When the driver connects to the data source, it loads the translation DLL (if one has been specified). After the driver has connected to the data source, the application may specify a new translation DLL by calling SQLSetConnectOption.

Translation functions may support several different types of translation. For example, a function that translates data from one character set to another might support a variety of character sets. To specify a particular type of translation, an application can pass an option flag to the translation functions with SQLSetConnectOption.

Additional Extension Functions

ODBC also provides the following functions related to connections, drivers, and data sources. For more information about these functions, see Chapter 13, Function Reference.

Function	Description
SQLDataSources	Retrieves a list of available data sources. The Driver Manager retrieves this information from the ODBC.INI file or registry. An application can present this information to a user or automatically select a data source.
SQLDrivers	Retrieves a list of installed drivers and their attributes. The Driver Manager retrieves this information from the ODBCINST.INI file or registry. An application can present this information to a user or automatically select a driver.
SQLGetFunctions	Retrieves functions supported by a driver. This function allows an application to determine at run time whether a particular function is supported by a driver.
SQLGetInfo	Retrieves general information about a driver and data source, including filenames, versions, conformance levels, and capabilities.
SQLGetTypeInfo	Retrieves the SQL data types supported by a driver and data source.
SQLSetConnectOption SQLGetConnectOption	These functions set or retrieve connection options, such as the data source access mode, automatic transaction commitment, timeout values, function tracing, data translation options, and transaction isolation.