Connection pooling gives an ODBC application the ability to reuse an existing connection from a pool, so that the application doesn’t have to go through the complete connection process for any subsequent connection.
In the database program you are using, follow the steps for importing data. Database programs vary widely, so refer to the Help documentation for each program to find detailed instructions about importing data.
Database programs often come with their own drivers built in, and many ODBC drivers are available in the list of drivers in the ODBC Data Source Administrator. You can also try searching the Internet or you can contact the manufacturer of the database program that you want to use.
1. Open ODBC data source administrator by clicking the Start button Picture of the Start button, clicking Control Panel, clicking Additional Options, and then clicking Data Sources (ODBC).
2. Click Add, choose the appropriate driver for the data source you’re adding, and then click Finish.
3. In the Data Source Name box, type a name for the data source (for example, you could type FileMaker). You can also type a description to help you remember why you are using this data source.
Obtain the following information about your ODBC database.
* Database Name
* Display name for the database (as it will appear in the GoodContacts list of databases)
* Data Source Name (as listed in the ODBC Manager when you added the database
* User Name and Password settings (only required if your database requires a login)
* Name of the Table containing your data (Remember that GoodContacts currently supports a single table.)
* List of fields in this table
The configuration tool is available from the Windows Control Panel, but the method varies depending on the version of Windows.
If you do not see your data source listed when you open the admin tool, you may need an additional driver or plug-in from the database supplier.
The DSN is a way of referring to a particular driver and database by any name you wish. The DSN is usually a key to a list of attributes the ODBC driver needs to connect to the database (e.g. ip address and port) but there is always a key which names the driver so the driver manager knows which driver to use with which data source. Do not confuse DSNs with ODBC connection strings or DBI’s “$data_source” (the first argument to “connect” in DBI.
The $data_source argument to DBI is composed of ‘dbi:DRIVER:something_else’ where DRIVER is the name of the DBD driver you want to use (ODBC of course for DBD::ODBC). The “something_else” for DBD::ODBC can be a DSN name or it can be a normal ODBC connection string.
An ODBC connection string consists of attribute/value pairs separated with semicolons (;). You can replace “something_else” above with a normal ODBC connection string but as a special case for DBD::ODBC you can just use the DSN name without the usual ODBC connection string prefix of “DSN=dsn_name”.
e.g.
dbi:ODBC:DSN=fred
ODBC connection string using fred DSN
dbi:ODBC:fred
Same as above (a special case).
dbi:ODBC:Driver={blah blah driver};Host=1.2.3.4;Port=1000;
This is known as a DSN-less connection string for obvious reasons.
The ODBC driver manager is the interface between an ODBC application (DBD::ODBC in this case) and the ODBC driver. The driver manager principally provides the ODBC API so ODBC applications may link with a single shared object (or dll) and be able to talk to a range of ODBC drivers. At run time the application provides a connection string which defines the ODBC data source it wants to connect to and this in turn defines the ODBC driver which will handle this data source. The driver manager loads the requested ODBC driver and passes all ODBC API calls on to the driver. In this way, an ODBC application can be built and distributed without knowing which ODBC driver it will be using.
However, this is a rather simplistic description of what the driver manager does. The ODBC driver manager also:
* Controls a repository of installed ODBC drivers (on UNIX this is the file odbcinst.ini).
* Controls a repository of defined ODBC data sources (on UNIX these are the files odbc.ini and .odbc.ini).
* Provides the ODBC driver APIs (SQLGetPrivateProfileString and SQLWritePrivateProfileString) to read and write ODBC data source attributes.
* Handles ConfigDSN which the driver exports to configure data sources.
* Provides APIs to install and uninstall drivers (SQLInstallDriver).
* Maps ODBC versions e.g. so an ODBC 2.0 application can work with an ODBC 3.0 driver and vice versa.
* Maps ODBC states between different versions of ODBC.
* Provides a cursor library for drivers which only support forward-only cursors.
* Provides SQLDataSources and SQLDrivers so an application can find out what ODBC drivers are installed and what ODBC data sources are defined.
* Provides an ODBC administrator which driver writers can use to install ODBC drivers and users can use to define ODBC data sources.
The ODBC Driver Manager is the piece of software which interacts with the drivers for the application. It “hides” some of the differences between the drivers (i.e. if a function call is not supported by a driver, it ‘hides’ that and informs the application that the call is not supported. DBD::ODBC needs this to talk to drivers.
Under Win32, you usually get the ODBC Driver Manager as part of the OS.
The following table lists ODBC functions, grouped by type of task, and includes the conformance designation and a brief description of the purpose of each function.
Connecting to a data source
SQLAllocHandle: Obtains an environment, connection, statement, or descriptor handle.
SQLConnect: Connects to a specific driver by data source name, user ID, and password.
SQLDriverConnect: Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialog boxes for the user.
SQLBrowseConnect: Returns successive levels of connection attributes and valid attribute values. When a value has been specified for each connection attribute, connects to the data source.
Obtaining information about a driver and data source
SQLDataSources: Returns the list of available data sources.
SQLDrivers: Returns the list of installed drivers and their attributes.
SQLGetInfo: Returns information about a specific driver and data source.
SQLGetFunctions: Returns supported driver functions.
SQLGetTypeInfo: Returns information about supported data types.
Setting and retrieving driver attributes
SQLSetConnectAttr: Sets a connection attribute.
SQLGetConnectAttr: Returns the value of a connection attribute.
SQLSetEnvAttr: Sets an environment attribute.
SQLGetEnvAttr: Returns the value of an environment attribute.
SQLSetStmtAttr: Sets a statement attribute.
SQLGetStmtAttr: Returns the value of a statement attribute.
Setting and retrieving descriptor fields
SQLGetDescField: Returns the value of a single descriptor field.
SQLGetDescRec: Returns the values of multiple descriptor fields.
SQLSetDescField: Sets a single descriptor field.
SQLSetDescRec: Sets multiple descriptor fields.
Preparing SQL requests
SQLPrepare: Prepares an SQL statement for later execution.
SQLBindParameter: Assigns storage for a parameter in an SQL statement.
SQLGetCursorName: Returns the cursor name associated with a statement handle.
SQLSetCursorName: Specifies a cursor name.
SQLSetScrollOptions: Sets options that control cursor behavior.
Submitting requests
SQLExecute: Executes a prepared statement.
SQLExecDirect: Executes a statement.
SQLNativeSql: Returns the text of an SQL statement as translated by the driver.
SQLDescribeParam: Returns the description for a specific parameter in a statement.
SQLNumParams: Returns the number of parameters in a statement.
SQLParamData: Used in conjunction with SQLPutData to supply parameter data at execution time. (Useful for long data values.)
SQLPutData: Sends part or all of a data value for a parameter. (Useful for long data values.)
Retrieving results and information about results
SQLRowCount: Returns the number of rows affected by an insert, update, or delete request.
SQLNumResultCols: Returns the number of columns in the result set.
SQLDescribeCol: Describes a column in the result set.
SQLColAttribute: Describes attributes of a column in the result set.
SQLBindCol: Assigns storage for a result column and specifies the data type.
SQLFetch: Returns multiple result rows.
SQLFetchScroll: Returns scrollable result rows.
SQLGetData: Returns part or all of one column of one row of a result set. (Useful for long data values.)
SQLSetPos: Positions a cursor within a fetched block of data and allows an application to refresh data in the rowset or to update or delete data in the result set.
SQLBulkOperations: Performs bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark.
SQLMoreResults: Determines whether there are more result sets available and, if so, initializes processing for the next result set.
SQLGetDiagField: Returns additional diagnostic information (a single field of the diagnostic data structure).
SQLGetDiagRec: Returns additional diagnostic information (multiple fields of the diagnostic data structure).
Obtaining information about the data source’s system tables (catalog functions)
SQLColumnPrivileges: Returns a list of columns and associated privileges for one or more tables.
SQLColumns: Returns the list of column names in specified tables.
SQLForeignKeys: Returns a list of column names that make up foreign keys, if they exist for a specified table.
SQLPrimaryKeys: Returns the list of column names that make up the primary key for a table.
SQLProcedureColumns: Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures.
SQLProcedures: Returns the list of procedure names stored in a specific data source.
SQLSpecialColumns: Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction.
SQLStatistics: Returns statistics about a single table and the list of indexes associated with the table.
SQLTablePrivileges: Returns a list of tables and the privileges associated with each table.
SQLTables: Returns the list of table names stored in a specific data source.
Terminating a statement
SQLFreeStmt: Ends statement processing, discards pending results, and, optionally, frees all resources associated with the statement handle.
SQLCloseCursor: Closes a cursor that has been opened on a statement handle.
SQLCancel: Cancels an SQL statement.
SQLEndTran: Commits or rolls back a transaction.
Terminating a connection
SQLDisconnect: Closes the connection.
SQLFreeHandle: Releases an environment, connection, statement, or descriptor handle.
SQLBrowseConnect supports an iterative method of discovering and enumerating the attributes and attribute values required to connect to a data source. Each call to SQLBrowseConnect returns successive levels of attributes and attribute values. When all levels have been enumerated, a connection to the data source is completed and a complete connection string is returned by SQLBrowseConnect. A return code of SQL_SUCCESS or SQL_SUCCESS_WITH_INFO indicates that all connection information has been specified and the application is now connected to the data source.
SQLRETURN SQLBrowseConnect(
SQLHDBC ConnectionHandle,
SQLCHAR * InConnectionString,
SQLSMALLINT StringLength1,
SQLCHAR * OutConnectionString,
SQLSMALLINT BufferLength,
SQLSMALLINT * StringLength2Ptr);
ConnectionHandle
[Input] Connection handle.
InConnectionString
[Input] Browse request connection string (see “InConnectionString Argument” in “Comments”).
StringLength1
[Input] Length of *InConnectionString in characters.
OutConnectionString
[Output] Pointer to a character buffer in which to return the browse result connection string (see “OutConnectionString Argument” in “Comments”).
BufferLength
[Input] Length, in characters, of the *OutConnectionString buffer.
StringLength2Ptr
[Output] The total number of characters (excluding null-termination) available to return in *OutConnectionString. If the number of characters available to return is greater than or equal to BufferLength, the connection string in *OutConnectionString is truncated to BufferLength minus the length of a null-termination character.
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_ERROR, or SQL_INVALID_HANDLE.
InConnectionString Argument
A browse request connection string has the following syntax:
connection-string ::= attribute[;] | attribute; connection-stringattribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]attribute-keyword ::= DSN | UID | PWD | driver-defined-attribute-keywordattribute-value ::= character-stringdriver-defined-attribute-keyword ::= identifier
where character-string has zero or more characters; identifier has one or more characters; attribute-keyword is not case-sensitive; attribute-value may be case-sensitive; and the value of the DSN keyword does not consist solely of blanks. Because of connection string and initialization file grammar, keywords and attribute values that contain the characters []{}(),;?*=!@ should be avoided. Because of the grammar in the system information, keywords and data source names cannot contain the backslash (\) character. For an ODBC 2.x driver, braces are required around the attribute value for the DRIVER keyword.
If any keywords are repeated in the browse request connection string, the driver uses the value associated with the first occurrence of the keyword. If the DSN and DRIVER keywords are included in the same browse request connection string, the Driver Manager and driver use whichever keyword appears first.
OutConnectionString Argument
The browse result connection string is a list of connection attributes. A connection attribute consists of an attribute keyword and a corresponding attribute value. The browse result connection string has the following syntax:
connection-string ::= attribute[;] | attribute; connection-stringattribute ::= [*]attribute-keyword=attribute-valueattribute-keyword ::= ODBC-attribute-keyword | driver-defined-attribute-keywordODBC-attribute-keyword = {UID | PWD}[:localized-identifier]driver-defined-attribute-keyword ::= identifier[:localized-identifier]attribute-value ::= {attribute-value-list} | ? (The braces are literal; they are returned by the driver.)attribute-value-list ::= character-string [:localized-character string] | character-string [:localized-character string], attribute-value-list
where character-string and localized-character string have zero or more characters; identifier and localized-identifier have one or more characters; attribute-keyword is not case-sensitive; and attribute-value may be case-sensitive. Because of connection string and initialization file grammar, keywords, localized identifiers, and attribute values that contain the characters []{}(),;?*=!@ should be avoided. Because of the grammar in the system information, keywords and data source names cannot contain the backslash (\) character.
The browse result connection string syntax is used according to the following semantic rules:
* If an asterisk (*) precedes an attribute-keyword, the attribute is optional and can be omitted in the next call to SQLBrowseConnect.
* The attribute keywords UID and PWD have the same meaning as defined in SQLDriverConnect.
* A driver-defined-attribute-keyword names the kind of attribute for which an attribute value may be supplied. For example, it might be SERVER, DATABASE, HOST, or DBMS.
* ODBC-attribute-keywords and driver-defined-attribute-keywords include a localized or user-friendly version of the keyword. This might be used by applications as a label in a dialog box. However, UID, PWD, or the identifier alone must be used when passing a browse request string to the driver.
* The {attribute-value-list} is an enumeration of actual values valid for the corresponding attribute-keyword. Note that the braces ({}) do not indicate a list of choices; they are returned by the driver. For example, it might be a list of server names or a list of database names.
* If the attribute-value is a single question mark (?), a single value corresponds to the attribute-keyword. For example, UID=JohnS; PWD=Sesame.
* Each call to SQLBrowseConnect returns only the information required to satisfy the next level of the connection process. The driver associates state information with the connection handle so that the context can always be determined on each call.
Using SQLBrowseConnect
SQLBrowseConnect requires an allocated connection. The Driver Manager loads the driver that was specified in or that corresponds to the data source name specified in the initial browse request connection string; for information about when this occurs, see the “Comments” section in SQLConnect Function. The driver may establish a connection with the data source during the browsing process. If SQLBrowseConnect returns SQL_ERROR, outstanding connections are terminated and the connection is returned to an unconnected state.
SQLBrowseConnect does not support connection pooling. If SQLBrowseConnect is called while connection pooling is enabled, SQLSTATE HY000 (General error) will be returned.
When SQLBrowseConnect is called for the first time on a connection, the browse request connection string must contain the DSN keyword or the DRIVER keyword. If the browse request connection string contains the DSN keyword, the Driver Manager locates a corresponding data source specification in the system information:
* If the Driver Manager finds the corresponding data source specification, it loads the associated driver DLL; the driver can retrieve information about the data source from the system information.
* If the Driver Manager cannot find the corresponding data source specification, it locates the default data source specification and loads the associated driver DLL; the driver can retrieve information about the default data source from the system information. “DEFAULT” is passed to the driver for the DSN.
* If the Driver Manager cannot find the corresponding data source specification and there is no default data source specification, it returns SQL_ERROR with SQLSTATE IM002 (Data source not found and no default driver specified).
If the browse request connection string contains the DRIVER keyword, the Driver Manager loads the specified driver; it does not attempt to locate a data source in the system information. Because the DRIVER keyword does not use information from the system information, the driver must define enough keywords so that a driver can connect to a data source using only the information in the browse request connection strings.
On each call to SQLBrowseConnect, the application specifies the connection attribute values in the browse request connection string. The driver returns successive levels of attributes and attribute values in the browse result connection string; it returns SQL_NEED_DATA as long as there are connection attributes that have not yet been enumerated in the browse request connection string. The application uses the contents of the browse result connection string to build the browse request connection string for the next call to SQLBrowseConnect. All mandatory attributes (those not preceded by an asterisk in the OutConnectionString argument) must be included in the next call to SQLBrowseConnect. Note that the application cannot use the contents of previous browse result connection strings when building the current browse request connection string; that is, it cannot specify different values for attributes set in previous levels.
When all levels of connection and their associated attributes have been enumerated, the driver returns SQL_SUCCESS, the connection to the data source is complete, and a complete connection string is returned to the application. The connection string is suitable to use, in conjunction with SQLDriverConnect, with the SQL_DRIVER_NOPROMPT option to establish another connection. The complete connection string cannot be used in another call to SQLBrowseConnect, however; if SQLBrowseConnect were called again, the entire sequence of calls would have to be repeated.
SQLBrowseConnect also returns SQL_NEED_DATA if there are recoverable, nonfatal errors during the browse process; for example, an invalid password or attribute keyword supplied by the application. When SQL_NEED_DATA is returned and the browse result connection string is unchanged, an error has occurred and the application can call SQLGetDiagRec to return the SQLSTATE for browse-time errors. This permits the application to correct the attribute and continue the browse.
An application can terminate the browse process at any time by calling SQLDisconnect. The driver will terminate any outstanding connections and return the connection to an unconnected state.
If a driver supports SQLBrowseConnect, the driver keyword section in the system information for the driver must contain the ConnectFunctions keyword with the third character set to “Y.”
If you are connecting to a data source provider that supports Windows authentication, you should specify Trusted_Connection=yes instead of user ID and password information in the connection string.
In the following example, an application calls SQLBrowseConnect repeatedly. Each time SQLBrowseConnect returns SQL_NEED_DATA, it passes back information about the data it needs in *OutConnectionString. The application passes OutConnectionString to its routine GetUserInput (not shown). GetUserInput parses the information, builds and displays a dialog box, and returns the information entered by the user in *InConnectionString. The application passes the user’s information to the driver in the next call to SQLBrowseConnect. After the application has provided all necessary information for the driver to connect to the data source, SQLBrowseConnect returns SQL_SUCCESS and the application proceeds.