How to Use ODBC Functions in a Remote View (147731)

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.