thanx Mark
>> because the ODBC connection will use the JET OLE-DB Provider
that's the MS OLE-DB driver, yes?
OLE-DB is a set of COM interfaces to data sources. It's not ODBC. However, some ODBC drivers use OLE-DB providers internally to interact with data. As far as I know, the MS Access driver shipped with Win2K does this and as the Jet OLE-DB Provider does allow % to be used in place of *, the MS Access driver which uses this provider also allows % to be used in place of *
but hang on... % and _ are ANSI SQL-92 and -99 standards, yes?
from http://archive.devx.com/upload/free/features/tgvp/1999/fa99/rjfa99/rjfa99.as p "Jet SQL-formerly called Access SQL-isn't famous for ANSI SQL-92 compliance. For example, Jet SQL departs from the ANSI standards by using # instead of ' as a date delimiter, substituting * and ? wildcards for ANSI % and _, respectively, and commits other minor syntax heresies."
% is the match any wildcard in ANSI 92/99 SQL, whereas the Jet database engine uses * as the match any wildcard. Some database drivers like the MS Access Driver above make allowances for non ANSI compliance in the database by parsing SQL commands from the client app and for example replacing the % wildcard with * before executing the command on the database.
so CFQUERY uses OLE-DB and is ANSI SQL compliant for % and _ wildcards no matter whether it's JET or SQLServer
The database driver is parsing your SQL, replacing the % wildcard with * before executing the SQL on the database.
and CFSTOREDPROC uses ODBC drivers and uses whatever the database uses (* for JET and % for SQLServer)
The database driver is accepting an instruction to execute a stored procedure. The SQL containing the wildcard is in a stored procedure and the database driver cannot parse the stored procedure before executing - it just tells the database, execute this stored procedure for me. Your stored procedure must use the standard match any wildcard for the database in question, * for Jet.
BTW: I thought they were all JDBC drivers?
If you're using CFMX and are not using an ODBC Socket datasource, then AFAIK, they are JDBC drivers. Beats me how you can use % with your MS Access JDBC driver 'cos I can't (CFMX, updater3)
Mark
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
