Microsoft KB Archive/116235

= INFO: FoxPro Can Use SQL pass-through commands and ODBC =

Article ID: 116235

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.6 Standard Edition
 * Microsoft FoxPro Connectivity Kit 2.6

-



This article was previously published under Q116235



SUMMARY
FoxPro can use SQL pass-through commands and ODBC. This ability provides a methodology for communicating with different data sources using a common syntax.



The FoxPro Connectivity Kit
FoxPro provides users with a "pass through" SQL capability. That is, you can pass SQL commands to the ODBC data source by using SQL pass-through functions. This capability is provided through 12 functions that are used to perform various ODBC-related tasks.

Single and Multitier ODBC Drivers
Current ODBC drivers fall into one of the following two categories: single- tier or multitier.


 * Single-Tier ODBC Drivers

Single-tier drivers are used when the processing of SQL statements must be done by the driver itself; that is, when there is no back-end server to process the SQL statements. Thus, single-tier drivers are used to connect to dBASE, FoxPro, Paradox, Btrieve, Microsoft Access, Microsoft Excel, and text files.

All of the Microsoft single-tier drivers use SIMBA.DLL and/or TXTISAM.DLL as the database "engine."
 * Multitier ODBC Drivers

Multitier drivers do not contain any processing capability and rely on a client-server DBMS to process SQL statements. SQL Server and ORACLE are two of the best known examples of a client-server DBMS.

ODBC Conformance Levels
There are two conformance levels for ODBC: API and SQL. The API conformance levels are Core, Level 1, and Level 2. These specify varying degrees of API support. Most current ODBC drivers are Level 1 and partially implement Level 2 features, such as scrollable cursors. (For a detailed discussion of this topic, refer to the ODBC SDK documentation listed at the end of this article.)

The ODBC SQL conformance level is related to the SQL grammar implementation, and falls into one of the following three categories:  Minimum  - All ODBC drivers should implement this level. Core    - Most ODBC drivers meet or exceed this level. Extended - Many ODBC drivers partially implement this level. The syntax for each level can be obtained from the ODBC SDK documentation, which is listed at the end of this article.

Scalar Functions
ODBC has a number of functions built-in to allow language independence between various database management systems. A list of these functions can be found in the following Microsoft Knowledge Base articles:

103576 SQL Server Time/Date Func Support for Connectivity Kit

104342 SQL Server System Funcs Supported by the Connectivity Kit

ODBC functions are implemented with an ODBC "escape shorthand" syntax. This syntax looks like:

{fn ODBCFunction([Arguments])}

The following example shows how you can use the DBExec function included in FoxPro 2.x with the SQRT ODBC function: lnResult=DBExec(dbHdle, "SELECT {fn sqrt(4)}","output") In Visual FoxPro, use the following command: lnResult=SQLExec(dbHdle, "SELECT {fn sqrt(4)}","output") To see an additional example of ODBC scalar functions, use the ClientServer Wizard in the Professional edition of FoxPro version 2.6 for Windows to create an outer join, and then examine the generated code.

