How to Use ODBC Functions in a Remote View

Last reviewed: February 29, 1996
Article ID: Q147731

The information in this article applies to:

Microsoft Visual FoxPro for Windows, version 3.0

SUMMARY

When creating a remote view, you must ensure that the selection criteria are valid functions that will be understood by the server. For example, with SQL Server, there are some ODBC and Visual FoxPro functions that do not exist in the SQL Server syntax.

To use an ODBC function, you can declare it as a scalar function to be passed to the SQL Server thus:

   ?SQLEXEC('SELECT {fn MONTH(date)} FROM sales WHERE {fn MONTH(date)}=9')

This SQL statement will return only the month value of the date field where the month is September. A more realistic SQL statement would be:

   ?SQLEXEC('SELECT * FROM sales WHERE {fn MONTH(date)}=?cMonth')

MORE INFORMATION

The following code illustrates how to call an ODBC function. SQL Server will not understand ODBC commands unless ODBC scalar functions are used as in this example:

   nHandle = SQLCONNECT('DataSourceName','sa','')
   cSQL = "SELECT {fn MONTH(date)} FROM sales WHERE {fn MONTH(date)}=9"
   result = SQLEXEC(nHandle, cSQL)

This SQL statement will return only the month value of the date field where the month is September. A more realistic SQL statement would be:

   cSQL= 'SELECT * FROM sales WHERE {fn MONTH(date)}=?cMonth'
   result = SQLEXEC(nHandle, cSQL)

The ODBC driver will see '{fn MONTH(data)}' as an ODBC function to be passed to the SQL Server. You can use scalar functions on the columns of the result set or the columns that restrict rows of a result set.

For more information on Scalar functions with ODBC, please see the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide published by Microsoft Press.

Additional reference words: 3.00 VFoxWin
KBCategory: kbinterop kbhowto
KBSubcategory: FxinteropGeneral

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 29, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.