|
   
3.
Connecting to a Database
3.1
Allocating an Environment Handle
3.2
Allocating a Connection Handle
3.3
Connecting to a Data Source
3.4
SQLSetConnectOption and SQLGetConnectOption
3.5
Disconnecting and Freeing Handles
3.6
Program Flow for Connecting and Disconnecting
3. Connecting to a Database
In any
ODBC application, you must properly set up the ODBC environment and
connect to a data source before you can execute any SQL statements or
perform queries. Similarly, you must disconnect from the database and
free the memory you allocated for the ODBC environment when your program
terminates. This chapter provides an introduction to the functions necessary
for setting up a connection to a data source, and how to use them. In
this chapter you will learn how to:
Initialize the ODBC environment by allocating environment and connection
handles using the SQLAllocEnv and SQLAllocConnect functions.
Establish a connection to a predetermined data source using the SQLConnect
function or to an unknown data source using the SQLDriverConnect
functions.
Get and set connection options for a data source using the SQLGetConnectOption
and SQLSetConnectOption functions.
Disconnect from a data source using the SQLDisconnect function.
Free the environment and connection handles when your application terminates
using the SQLFreeConnect and SQLFreeEnv functions.
Note:
You allocate environment and connections handles, and get and set connection
options differently using DBMaker 3.5 (ODBC 3.0) from what is described
in this chapter. Refer to Appendix E, What's New in DBMaker 3.5 for
more information.
3.1 Allocating an Environment Handle
In an ODBC
application, you must use the SQLAllocEnv function to set up
the ODBC environment before you can call any other ODBC functions. When
you call the SQLAllocEnv function, the DBMaker driver allocates
an area of memory for environment information and returns a handle to
your application. This handle is known as the environment handle.
The environment
handle identifies the area of memory the DBMaker driver uses to
store global information about the ODBC environment. This may include
information such as a list of the valid connection handles and the currently
active connection handle.
Allocating
an environment handle should be the first step in your application;
you cannot use any other ODBC functions until you have allocated an
environment handle. In addition, you should allocate one and only one
environment handle in your application.
The function
prototype for SQLAllocEnv is:
RETCODE SQLAllocEnv(HENV *FAR phenv)
|
To allocate an environment handle:
1. Declare
a variable of type HENV.
2. Call
SQLAllocEnv and pass the address of the HENV variable you declared.
retcode = SQLAllocEnv(&henv1);
|
You should
perform this procedure only once in your application. After you call
the SQLAllocEnv function, the environment handle is allocated
and initialized. The environment handle is a valid handle, and you can
use it later in your application.
If your
application calls SQLAllocEnv with a pointer to a valid environment
handle, the driver will overwrite the previous contents of the environment
handle.
3.2 Allocating a Connection Handle
After allocating
an environment handle, you must allocate a connection handle before
you can connect to an ODBC data source. A connection handle identifies
memory storage for each connection in an ODBC application, and contains
information such as the database name and user name.
In your
ODBC application, you must allocate a connection handle for each data
source you want to connect to. Use the SQLAllocConnect function
to allocate memory for a connection handle.
The function
prototype for SQLAllocConnect is:
RETCODE SQLAllocConnect(HENV henv, HDBC *phdbc)
|
To allocate a connection handle:
1. Declare
a variable of type HDBC.
2. Call
SQLAllocConnect and pass the address of the variable you declared.
retcode = SQLAllocConnect(henv1, &hdbc1);
|
You need
to perform this procedure for every connection you want to make to a
data source. You should only perform this procedure once in your application.
After you call SQLAllocEnv, the environment handle is allocated
and initialized. The environment handle is a valid handle, and you can
use it later in your application.
3.3 Connecting to a Data Source
Before
you access the data contained in a data source, you must first connect
to that data source. In the previous section, you learned how to allocate
environment and connection handles. The next step is connecting to a
data source.
Connecting to a Specific Data Source
You use
SQLConnect to establish a connection between a data source and
a valid connection handle.
The prototype
for SQLConnect is:
RETCODE SQLConnect(
HDBC hdbc,
UCHAR FAR *szDSN,
SWORD cbDSN,
UCHAR FAR *szUID,
SWORD cbUID,
UCHAR FAR *szAuthStr,
SWORD cbAuthStr
);
|
An application
has to pass all or part of the following information to SQLConnect:
A valid connection handle. The connection handle must not currently
be connected to another data source.
The name of the data source and length of the name.
A user identification and its length.
A password and its length.
The detailed
program flow when connecting to a data source using SQLConnect
is shown below in Figure 3-1.
Figure
3-1: Program flow for connecting to a data source
In step
one SQLAllocEnv is called to allocate an environment handle henv.
In step two SQLAllocConnect is called to allocate a valid connection
handle hdbc with a valid environment handle henv. In step
three SQLConnect is called to connect to a data source with a
valid connection handle hdbc. If the return code is SQL_SUCCESS
or SQL_SUCCESS_WITH_INFO, the connection has been established correctly.
The following
example connects to data source TEST_DB with user identification
MYNAME and password PASS.
retcode = SQLConnect(hdbc, "TEST_DB", SQL_NTS,
"MYNAME", SQL_NTS,
"PASS", SQL_NTS);
|
SQL_NTS
means the string is NULL terminated, and the length of the string is
calculated by the driver. When using SQLConnect the data source
name is a required argument, and the user identification and password
are optional.
With DBMaker,
you can put a default user name and password in the dmconfig.ini file,
and omit the user name and password in SQLConnect. The driver
will then use the user name and password specified in dmconfig.ini.
With this method, you don't have to specify the user name and password
in your program. For example, you can set DB_USRID=MYNAME and
DB_PASWD=PASS in dmconfig.ini, then call:
retcode = SQLConnect(hdbc, "TEST_DB", SQL_NTS,
"", SQL_NTS,
"", SQL_NTS);
|
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. Then, it loads the driver DLL and
passes the user name and password arguments to the driver.
Connecting to a Data Source with SQLDriverConnect
When you
want to connect to a specific, predetermined data source, you use SQLConnect.
However, in many cases you want the driver manager to display all the
available data sources and let the user choose one to connect to. In
this case, you should use the function SQLDriverConnect.
The prototype
for SQLDriverConnect is:
RETCODE SQLDriverConnect(
HDBC hdbc,
HWND hwnd,
UCHAR FAR *szConnStrIn,
SWORD cbConnStrIn,
UCHAR FAR *szConnStrOut,
SWORD cbConnStrOutMax,
SWORD FAR *pcbConnStrOut,
UWORD fDriverCompletion
);
|
An application
has to pass the following information to SQLDriverConnect:
A valid connection handle not yet associated with a data source.
A valid window handle to provide a parent window for the dialog box.
The input connection string (szConnStrIn) and its length. The
connection string has its own special syntax (see the section on connection
strings) and contains specific information needed to connect to a data
source. If the input information is not complete, SQLDriverConnect
will use a dialog box to request more information from user before it
sends connection information to the database driver.
The output connection string (szConnStrOut) and its length. This
is the final connection information that gets sent to the database driver.
A prompt flag (fDriverCompletion) which governs the policies
used when prompting for data source information.
The detailed
connection flow when using SQLDriverConnect is the same as the
connection flow when using SQLConnect as described in Figure
3-1. First, you must allocate environment and connection handles.
Then, you can call the SQLDriverConnect function to connect to
a data source.
The Input Connection String
The input
connection string specifies the information needed to connect to a data
source. It contains several keyword value pairs in the form:
The most
commonly used keywords are:
DSN - The name of data source name
UID - The user name
PWD - The password
For example:
DSN=TEST_DB; UID=myname; PWD=abc;
DSN=TEST_DB; UID=myname;
UID=myname;
|
If an input
connection string has more than one DSN, UID, or PWD, DBMaker will use
the first one.
The Prompt Flag
The prompt
flag indicates whether Driver Manager or the DBMaker driver need to
use a dialog box to get connection information from the user. Possible
values of the prompt flag are:
SQL_DRIVER_PROMPT
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
SQL_DRIVER_NOPROMPT
|
When the
value of the prompt flag is set to either SQL_DRIVER_COMPLETE or SQL_DRIVER_COMPLETE_REQUIRED,
Driver Manager will perform the actions listed below based on the following
conditions:
if the DSN is specified in the input connection string, it copies the
input connection string and passes the string to the driver.
if the DSN is not specified in the input connection string, Driver Manager:
·
displays the Data Source dialog box (see Figure
3-2) to let the user choose a data source.
·
constructs the data source name returned from the dialog box and any
other UID or PWD values found in the input connection string, then
passes the string to the driver.
Figure
3-2: Data source dialog box
If the
data source name returned from the dialog box is empty, the Driver Manager
specifies the keyword-value pair DSN=Default (if a Default data
source section exists in ODBC.INI)
In comparison,
the DBMaker database driver perform the actions listed below based on
the following conditions:
if the input connection string contains enough information (user id
and password), the driver connects to the data source. If successful,
it copies the input connection string to the output connection string.
If the user ID, password or both are missing, the DBMaker driver:
·
displays a dialog box (see Figure 3-3)
to allow users to fill in the values missing from the input connection
string.
·
connects to the data source after the user leaves the dialog box.
·
constructs a connection string from the value of the DSN in the input
connection string and the information returned from the dialog box.
It places this connection string in the output connection string.
When the
prompt flag is set to SQL_DRIVER_PROMPT, the ODBC Driver Manager will
always use a dialog box to prompt for the data source whether you provide
a DSN in the input string or not.
Figure
3-3: UID and PWD dialog box
When the
prompt flag is set to SQL_DRIVER_PROMPT, the DBMaker driver behaves
the same way as when the prompt flag is set to SQL_DRIVER_COMPLETE or
SQL_DRIVER_COMPLETE_REQUIRED. That is, if all the information is provided
in the input string, the DBMaker driver will not prompt for additional
information using a dialog box.
When the
prompt flag is set to SQL_DRIVER_NOPROMPT, neither the ODBC Driver Manager
nor the DBMaker driver will display a dialog box to prompt for information.
If the DSN is not specified in the input connection string, the ODBC
Driver Manager specifies the keyword-value pair DSN=Default (if
the Default data source section exist in ODBC.INI). If the input
connection string contains enough information, the driver will connect
to the data source and place this connection string in the output connection
string. Otherwise, the driver returns an error.
Multiple Connections
You can
easily connect to more than one data source simultaneously in an application.
However, some applications want to connect to the same database multiple
times. For example, a task (process) may have two windows, and each
window has a connection to the same database. One window is used to
scan one table and the other is used to update another table. DBMaker
allows a program using SQLConnect to connect to the same data
source multiple times, but all the connections must use the same user
name and all database changes associated with all the connections must
belong to the same active transaction.
For example,
one user (user1, pass1) may connect to data source DB1
twice by using two valid connection handles. These two connections belong
to the same transaction.
retcode = SQLAllocConnect(henv, hdbc1);
retcode = SQLAllocConnect(henv, hdbc2);
retcode = SQLConnect(hdbc1, "DB1", SQL_NTS, "user1", SQL_NTS, "pass1",
SQL_NTS);
retcode = SQLConnect(hdbc2, "DB1", SQL_NTS, "user1", SQL_NTS, "pass1",
SQL_NTS);
|
However,
if you try to use two users (user1, pass1) and (user2,
pass2) to connect to the same data source in one process, an
error will be returned:
retcode = SQLAllocConnect(henv, hdbc1);
retcode = SQLAllocConnect(henv, hdbc2);
retcode = SQLConnect(hdbc1, "DB1", SQL_NTS, "user1", SQL_NTS, "pass1",
SQL_NTS);
retcode = SQLConnect(hdbc2, "DB1", SQL_NTS, "user2", SQL_NTS, "pass2",
SQL_NTS);
|
Multiple
connections to the same data source in a process is not necessary. The
more appropriate way to handle this case is to have multiple statement
handles under one connection handle.
3.4 SQLSetConnectOption and SQLGetConnectOption
A connection
to a data source has many attributes (options) which governs the behavior
of this connection. For example one option, SQL_AUTOCOMMIT, determines
whether all database operations are automatically committed.
SQLSetConnectOption
Each option
has a default value defined by the system, but you can use SQLSetConnectOption
to specify different option values for a connection.
The prototype
of SQLSetConnectOption is:
RETCODE SQLSetConnectOption (
HDBC hdbc,
UWORD fOption,
UDWORD vParam);
|
where hdbc
is a valid connection handle, fOption is the option to be set
for the connection and vParam is the value specified for fOption.
For example,
in DBMaker the default value for auto-commit mode is on. The following
statement can be used to turn off the auto-commit mode of a connection:
retcode = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
|
The option
value SQL_AUTOCOMMIT_OFF is the new value for the option SQL_AUTOCOMMIT.
With SQL_AUTOCOMMIT set to off, an explicit SQLTransact (hdbc, COMMIT)
call is required to commit all the changes of a transaction.
Note:
You can use SQLSetConnectOption to set connection options before or
after making a connection. These options will remain in effect while
the connection handle exists. Once these options are set, they apply
to all statements that are associated with the connection. The only
exception in DBMaker is that you must set the value of option SQL_CONNECT_MODE
before you make a connection.
In addition
to the standard connection options, DBMaker provides some extended connection
options. Please refer to Appendix B of this manual for detailed
information.
SQLGetConnectOption
When you
need to know the current value of a connection option, you can use SQLGetConnectOption
to get the value.
The prototype
for SQLGetConnectOption is:
RETCODE SQLGetConnectOption (
HDBC hdbc,
UWORD fOption,
PTR vParam
);
|
where hdbc
is a valid connection handle, fOption is the option whose value
you want to retrieve and vParam points to a location to receive
the option value.
For example,
to place the value associated to SQL_AUTOCOMMIT for connection hdbc
in the variable commitval:
retcode = SQLGetConnectOption(hdbc, SQL_AUTOCOMMIT, &commitval);
|
3.5 Disconnecting and Freeing Handles
Before
terminating your application, you should free all resources allocated
by connections and the environment. For each of the functions SQLConnect,
SQLAllocConnect, and SQLAllocEnv, there is a corresponding
function associated with it. These are the functions SQLDisconnect,
SQLFreeConnect and SQLFreeEnv.
SQLDisconnect
SQLDisconnect
closes the connection associated with a specific connection handle and
frees all statement handles under the connection. (Statement handles
will be discussed in Chapter 4.)
The prototype
for SQLDisconnect is:
RETCODE SQLDisconnect(HDBC hdbc)
|
SQLFreeConnect
After closing
the connection, a program should call SQLFreeConnect to release
the connection handle and free all memory associated with the handle.
If you try to use SQLFreeConnect to free an open connection handle,
the driver will return an error. You need to close a connection (by
calling SQLDisconnect) before calling SQLFreeConnect to
free the connection handle.
The prototype
for SQLFreeConnect is:
RETCODE SQLFreeConnect(HDBC hdbc)
|
SQLFreeEnv
SQLFreeEnv
frees the environment handle and releases all memory associated with
the environment handle.
The prototype
for SQLFreeEnv is:
RETCODE SQLFreeEnv(HENV henv)
|
Before
calling SQLFreeEnv, an application must call SQLFreeConnect
to free any hdbc allocated under the henv.
3.6 Program Flow for Connecting and Disconnecting
Figure
3-4 shows the program flow in an application that uses the six
ODBC functions we discussed in this chapter.
Figure
3-4: Program flow for connecting to and disconnecting from a data source
|
