ColdFusion cfqueryparam MS Access Data Types

Once you get comfortable with it, ColdFusion's cfqueryparam is an excellent tool. It can enhance security, tighten up code, and conveniently ensure the correct data types are being used in your database statements. If you have looked in cfqueryparam at all, you've surely noticed that Adobe provides an excellent cfqueryparam cross-reference of data types for all sorts of databases... except MS Access.

I inevitably ran across the need to use cfqueryparam with a MS Access database and was struggling to match the data types. Access data types can be obnoxious, especially when the db designer has taken it upon himself to establish an obscure Access data type for every column. Thankfully, cfSearching provided a helpful table cross-referencing Access data types with cfqueryparam data type attribute values. I can vouch for the note that "when using the Unicode driver, the correct type for MEMO fields is CF_SQL_CLOB (not CF_SQL_LONGVARCHAR). "

Credit goes to cfSearching for the cross-reference, but here it is in case their page is down or goes away for whatever reason... it would be a shame to lose this information!

ColdFusion                  Jet                     MSSQL
CF_SQL_BIT                  Yes/No                  bit
CF_SQL_DECIMAL              Decimal, Currency       decimal, money, small money
CF_SQL_FLOAT                Double                  float
CF_SQL_INTEGER              Long Integer            int
CF_SQL_LONGVARBINARY        OLE Object              image
CF_SQL_LONGVARCHAR          Memo                    text, ntext
CF_SQL_MONEY                                        Double                      
CF_SQL_REAL                 Single                  real
CF_SQL_SMALLINT             Integer                 smallint
CF_SQL_TIMESTAMP            Date/Time               datetime, smalldate time
CF_SQL_TINYINT              Byte                    tinyint
CF_SQL_VARCHAR              Text                    varchar, nvarchar, sysname