Understanding the ODBC Extensions

Open database connectivity (ODBC) is an application programming interface that uses structured query language (SQL) to access and manipulate data in database management systems such as dBASE®, Paradox®, and Microsoft Access.

SQL is a widely accepted industry standard for storing, maintaining, and retrieving information from a DBMS. A particular DBMS may support different SQL functions and grammar to take advantage of unique, proprietary features. For that reason, if SQL statements are used to access data directly, an application would require separate programs, each targeted to a specific DBMS, to work with external data.

Using ODBC API functions, however, a single application can access data in diverse DBMSs without supporting multiple implementations of SQL. To access
a particular DBMS, an application supporting ODBC uses software components called drivers, which perform all interaction between an application and a specific DBMS. The following illustration shows the relationship between the dBASE ODBC driver and an application accessing dBASE database files.

To provide the same interoperability for WordBasic macros, the WBODBC.WLL add-in provides functions you can call from WordBasic that mirror ODBC functions. With these functions, you can create a macro that can access data in any ODBC-supporting DBMS for which you have an ODBC driver.

The ODBC Extensions and SQL

After establishing a data source connection through a call to SQLOpen, you issue SQL queries to an ODBC-supporting DBMS by calls to the WordBasic ODBC extensions SQLExecQuery or SQLQueryExec. The SQL statements in the query should conform to SQL grammar. If the SQL grammar is supported by the ODBC API, the query is interpreted by the ODBC driver. The ODBC Driver Manager (ODBC.DLL) then calls the appropriate ODBC API functions to execute the query.

If the query contains SQL grammar that is not supported by the ODBC driver, the query is passed directly to the DBMS without further processing by ODBC API functions. In this way, a driver can support a superset of SQL, including vendor-specific grammar.

You can see some examples of SQL queries in the sample macros in the WBODBC.DOT, which is included on the Microsoft Word Developer's Kit disk. When you run the Exec macro, you can compose and issue queries interactively; you can also use this macro to test queries you create before including the query strings in other macros.

To learn more about the SQL grammar supported by a specific ODBC driver, see the online Help file provided with each driver. For more information about SQL, the following standards are available:

• Database Language – SQL with Integrity Enhancement, ANSI, 1989 ANSI X3.135-1989.
• X/Open and SQL Access Group SQL CAE draft specification (1991).
• Database Language SQL: ANSI X3H2 and ISO/IEC JTC1,SC21,WG3 (draft international standard).

In addition to standards and the SQL documentation provided with the DBMS you are using, there are many books that describe SQL, including:

• Microsoft ODBC Programmer's Reference, provided with the Microsoft ODBC Software Development Kit version 2.10.
• Date, C. J.: A Guide to the SQL Standard (Addison-Wesley, 1989).
• Emerson, Sandra L., Darnovsky, Marcy, and Bowman, Judith S.: The Practical SQL Handbook (Addison-Wesley, 1989).
• Groff, James R. and Weinberg, Paul N.: Using SQL (Osborne McGraw-Hill, 1990).
• Gruber, Martin: Understanding SQL (Sybex, 1990).
• Hursch, Jack L. and Carolyn J.: SQL, The Structured Query Language
  (TAB Books, 1988).
• Pascal, Fabian: SQL and Relational Basics (M & T Books, 1990).
• Trimble, J. Harvey, Jr. and Chappell, David: A Visual Introduction to SQL (Wiley, 1989).
• Van der Lans, Rick F.: Introduction to SQL (Addison-Wesley, 1988).
• Vang, Soren: SQL and Relational Databases (Microtrend Books, 1990).
• Viescas, John: Quick Reference Guide to SQL (Microsoft Corp., 1989).

Tip

If you use Microsoft Query, you can create a query in the Query window and then copy the equivalent SQL statements to your WordBasic macro.

ODBC SQL Data Types

Each ODBC driver has different naming syntax for its own SQL data types. A given ODBC driver maps its native SQL data types to ODBC SQL data types. Information about data-type mapping for a specific ODBC driver is provided in the online Help file for each installed driver. For example, the following table shows how the ODBC driver for Paradox maps native Paradox data types.

A given driver and ODBC data source do not necessarily support all ODBC SQL data types. For complete information about ODBC SQL data types, see the Microsoft ODBC Programmer's Reference in the Microsoft ODBC Software Development Kit version 2.10.

The extensions in WBODBC.WLL allow you to create and delete ODBC tables, as well as to examine and manipulate fields within ODBC tables. When using the SQL statements CREATE TABLE and ALTER TABLE, you must specify the data types native to that particular DBMS. For example, if you are adding a column to a table in Microsoft FoxPro and the data type of the column is "Date," you must specify "Date" as the column data type in the ALTER TABLE statement, not the ODBC SQL data type "SQL_DATE." To determine the appropriate data type for the DBMS you are using, use the WordBasic ODBC function SQLGetTypeInfo$. For any valid SQL data type, this function returns the corresponding data type for the DBMS of the current data source.

Note that the ODBC extensions for Word cannot directly manipulate ODBC SQL data types; the ODBC extensions use only string data types. Numbers are formatted and manipulated as strings.