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

ID: Q116235

The information in this article applies to:

Microsoft Visual FoxPro for Windows, versions 3.0, 5.0, 6.0
Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
Microsoft FoxPro Connectivity Kit, versions 2.5, 2.6

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.

MORE INFORMATION

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.<BR/>
  Core     - Most ODBC drivers meet or exceed this level.<BR/>
  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:

Q103142 SQL Server String Funcs Supported by the Connectivity Kit

Q103576 SQL Server Time/Date Func Support for Connectivity Kit

Q103578 SQL Server Numeric Funcs Supported by Connectivity Kit

Q104342 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.

REFERENCES

"Microsoft ODBC 2.0 Programmer's Reference and SDK Guide," Microsoft Press

Additional query words: CK multi-tier

Keywords : kbinterop kbDatabase kbVFp300 kbVFp500 kbVFp600 kbVFP260
Version :
Platform :
Issue type : kbinfo