1316-1319

Previous Table of Contents Next

Page 1316

The transport_code is a single character used to specify the SQL*Net driver to be used (T for TCP/IP, X for IPX/SPX, and so on). The host_name is the name, alias, or network address of the server. The database argument is necessary only if more than one Oracle database exists on the host. In this case, the argument should be the system name of the database, as specified when the database was created with the CREATE DATABASE command. Consult the Oracle ODBC driver release notes and the SQL*Net documentation for further information on the SQL*Net Connect String.

For the purposes of this example, assume that the database resides on a UNIX host and will be accessed from the Windows workstation using TCP/IP. Enter ORACLE for the data source name, Oracle 7.1 for the description, and T:ORACLE_SERVER for the SQL*Net connect string. The dialog box should now look like the one shown in Figure 57.8.

Figure 57.8.

The Oracle7 ODBC
Setup dialog box
requires a name and a
SQL*Net Connect
String. The description
is optional.

The Options button enables the user to select a code page translator, assuming that a translator was installed with the driver. In most cases, no translation is necessary. Click OK to complete the data source setup. The new data source should appear in the Data Sources dialog box as shown in Figure 57.9.

Figure 57.9.

The Data Sources
dialog box shows that
the new Oracle7 Data
Source was successfully
added.

The data source is now fully configured and ready to be accessed by an application. Note that the setup routine in Figure 57.7 and Figure 57.8 is specific to the driver (Oracle 7.1 version 1.11.0002, in this case). This setup dialog will vary slightly from driver to driver, but it always requires similar information.

Page 1317

Connecting to an ODBC Data Source Using
the ODBC API

Before your application connects to the ODBC data source, some memory allocation and initialization must be performed. First, the application must call SQLAllocEnv, passing a pointer to memory allocated to store an environment handle. This handle is used to establish connections and for transaction processing. The application might need to establish more than one environment handle, but a single environment handle is usually sufficient, except in multithreaded environments.

Next, the application should call SQLAllocConnect, passing the previously established environment handle and a pointer to storage allocated for the connection handle. The driver manager allocates storage for connection information and associates the resulting connection handle with the environment handle. Multiple connections can be established for a single environment handle, but each connection can only be associated with a single environment. The connection handle is used to allocate statement handles and process embedded SQL transactions.

Finally, the application may call either SQLConnect or SQLDriverConnect, passing the connection handle instantiated by the call to SQLAllocConnect. The primary difference between these two functions is that SQLDriverConnect accepts a full connection string, rather than separate arguments for the data source name, userid , and password. This allows for additional database-specific parameters to be passed to the driver as part of the connection string. Additionally, SQLDriverConnect provides an argument used to define the behavior of the driver manager and a window handle to be used as the parent of the data sources dialog box (if one is presented). The arguments to SQLDriverConnect are, in order:

• An allocated connection handle.
• The handle of the parent window from the Data Sources dialog box (or NULL, if no dialog is presented).
• A connection string.
• The length of the connection string.
• A pointer to storage for the connection string actually used by the driver. (It may add information to the connection string it receives.)
• A pointer to storage to hold the length of the completed connection string.
• An integer constant used to control the behavior of the driver manager.

Page 1318

NOTE

A typical connection string looks like this: DSN=ORACLE;UID= scotty ;PWD=tiger; Additional database-specific parameters may be provided, or the connection string may be partial, or empty, in which case the driver provides a dialog box requesting the information required to connect. If SQL_DRIVER_NOPROMPT is passed as the completion constant, the application must provide all required information in the connection string.

C applications should include ODBC.H (or SQL.H and SQLEXT.H, depending on the compiler), which contains all function prototypes, data types, and constants available in the ODBC API. When using other development tools, the developer must provide prototypes for all ODBC functions used by the application. Listings 57.1 and 57.2 demonstrate connecting to an ODBC data source in C and Visual Basic, in a Microsoft Windows application context.

Listing 57.1. This C function establishes a single connection to a data source.

 int ConnectToDataSource( HENV *hEnv,     /* used to store the environment handle */ HDBC *hDBc)     /* used to store the connection handle  */ {      UCHAR        *szConnect;     UCHAR        szConnectOut[SQL_MAX_MESSAGE_LENGTH];      SWORD        iConnectOutLen;      RETCODE      iError;       szConnect = strdup("DSN=ORACLE;UID=scotty;PWD=tiger;");      iError = SQLAllocEnv(hEnv);     if (iError == SQL_SUCCESS)           iError = SQLAllocConnect(*hEnv, hDBc);     if (iError == SQL_SUCCESS)    iError = SQLDriverConnect(*hDBc,                                   NULL,                                     szConnect,                                   SQL_NTS,                                     szConnectOut,                                   (SQL_MAX_MESSAGE_LENGTH - 1),                                     &iConnectOutLen,                                   SQL_DRIVER_NOPROMPT);     return(iError); } 

Listing 57.2. This Visual Basic function establishes a connection to a data source.

 ` include these prototypes in the module: Declare Function SQLAllocEnv                  Lib "odbc.dll" (hEnv As Long) As Integer 

Page 1319

 Declare Function SQLAllocConnect                  Lib "odbc.dll" (ByVal hEnv As Long,                                  hDBc As Long) As Integer Declare Function SQLDriverConnect                  Lib "odbc.dll" (ByVal hDBc As Long,                                  ByVal hWnd As Integer,                                  ByVal szCSin As String,                                  ByVal iCSinLen As Integer,                                  ByVal szCSOut As String,                                  ByVal iCSOutMaxLen As Integer,                                  iCSOutLen As Integer,                                  ByVal iDriverComplete As Integer)                                  As Integer ` also define these constants: Global Const SQL_SUCCESS = 0 Global Const SQL_SUCCESS_WITH_INFO = 1 Global Const SQL_STILL_EXECUTING = 2 Global Const SQL_NEED_DATA = 99 Global Const SQL_NO_DATA_FOUND = 100 Global Const SQL_ERROR = 1 Global Const SQL_INVALID_HANDLE = 2 Global Const SQL_NTS = 3 Global Const SQL_DRIVER_NOPROMPT = 0 Global Const SQL_MAX_MESSAGE_LENGTH = 512 Function ConnectToDataSource(hEnv As Long, hDBc As Long) As Integer     Dim    szConnect As String     Dim    szConnectOut As String     Dim    iConnectOutLen As Integer     Dim    iError As Integer   szConnectOut = Space$(SQL_MAX_MESSAGE_LENGTH)     szConnect = "DSN=ORACLE;UID=scotty;PWD=tiger;"     iError = SQLAllocEnv(hEnv)     If (iError = SQL_SUCCESS) Then         iError = SQLAllocConnect(hEnv, hDBc)     End If     If (iError = SQL_SUCCESS) Then         iError = SQLDriverConnect(hDBc, 0, szConnect, SQL_NTS,       szConnectOut,                                   (SQL_MAX_MESSAGE_LENGTH - 1),                                      iConnectOutLen,                                   SQL_DRIVER_NOPROMPT)     End If       ConnectToDataSource = iError End Function 

Previous Table of Contents Next