Microsoft KB Archive/147731

= How to Use ODBC Functions in a Remote View =

Article ID: 147731

Article Last Modified on 8/20/1999

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition

-



This article was previously published under Q147731



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 query words: VFoxWin

Keywords: KB147731

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.