Saturday, December 1, 2007

Cfqueryparam - Matrix for MS Access

If you have ever looked at the cfqueryparam data type matrix you will notice MS Access is conspicuously absent. Yes, I know its not a supported database, but it has its place.

Anyway, I took a stab at merging the matrix from livedocs and a comparison between data types in Access and SQL Server on MS technet. I have not tested all of the types yet so no guarantees on accuracy.

Update: As mentioned in the comments below, when using the Unicode driver, the correct type for MEMO fields is CF_SQL_CLOB (not CF_SQL_LONGVARCHAR). Thanks Headsplode!
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
at

13 comments:

  1. AnonymousJuly 23, 2008 at 3:23 PM

    Nice .. but im my experiance sometimes longvarchar works for memo and sometimes it woun't.. have not figured why it is hit and miss

    ReplyDelete
  2. cfSearchingJuly 25, 2008 at 4:42 PM

    Interesting. I do not use Access that frequently, so I have not experienced that problem.

    Can you explain what you mean by does not work? Does an exception occur, is the data truncated, ... ?

    ReplyDelete
  3. AnonymousJune 29, 2009 at 4:58 PM

    I use this list for reference all the time -- it is GREAT.

    Since your title is "Matrix for Access" Can you please swap the MSSQL and JET columns so by default, we can see the (more important) Jet list?

    ReplyDelete
  4. cfSearchingJune 29, 2009 at 6:50 PM

    @Anonymous,

    Very good point! Done :)

    -Leigh

    ReplyDelete
  5. AnonymousAugust 25, 2009 at 8:00 PM

    Thank you for swapping the MSSQL and JET columns...

    Note that using cfqueryparam with an access YES/NO field can be tricky. I spent days trying to figure out the problem. The following code is needed to test for a yes / no field in a cfquery.


    For checking (if the field is 'YES') use...
    WHERE myYesNoField = <cfqueryparam value='1' cfsqltype="CF_SQL_BIT">

    For checking (if the field is 'NO') use...
    WHERE myYesNoField <> <cfqueryparam value='1' cfsqltype="CF_SQL_BIT">


    WARNING: For checking (if the field is 'NO') -- Do NOT use the following as it does not always work:
    WHERE myYesNoField = <cfqueryparam value='0' cfsqltype="CF_SQL_BIT">

    ReplyDelete
  6. cfSearchingAugust 26, 2009 at 7:15 PM

    @Anonymous,

    Weird. So if you dump the value of myYesNoField, what is it for the "no" records? Also, what driver are you using when that happens?

    -Leigh

    ReplyDelete
  7. AnonymousAugust 31, 2009 at 2:40 PM

    if you dump the value of myYesNoField, the "no" records come up as [empty string]! The "yes" records come up as "1".

    ColdFusion Server, Standard, Version= 7,0,2,142559

    JDBC Version is 3.50

    I'm not sure which driver I am actually using. Unfortunately, I do not know which driver the server is using because I do not have direct access to the server. I could not figure out any way to query the driver name. Is there some way to query this? I do have read file access to the C:\ drive.

    ReplyDelete
  8. cfSearchingAugust 31, 2009 at 3:58 PM

    @Anonymous,

    It sounds like the value in some records might be NULL? That would certainly explain the empty string.

    I _think_ dsn driver information is stored in one of the neo-****.xml files in c:\coldfusion7\lib. neo-query.xml maybe? Or if you have access to createObject('java'), you could grab the settings from the undocumented serviceFactory.

    ReplyDelete
  9. HeadsplodeOctober 19, 2010 at 2:53 AM

    I know this post is forever old, I'm sorry but I ran across it trying to TS an access problem, so... yay necro!

    Anyway, false in access it usually -1. True is a non-negative number, including 0. Unfortunately there's confusion because if 0 is stored and you use cf to output the value of 0 you get "No", which is actually "Yes" in access. HTH.

    ReplyDelete
  10. HeadsplodeOctober 20, 2010 at 3:12 AM

    "CF_SQL_LONGVARCHAR Memo"

    if Unicode support is enabled you'll have to use CF_SQL_CLOB (untested without Unicode). CF_SQL_LONGVARCHAR limits the input to ~550 chars.

    ReplyDelete
  11. cfSearchingOctober 20, 2010 at 5:20 PM

    yay necro!

    Haha. Now there is a cheer you do not hear everyday ;-)

    You are right about unicode/CF_SQL_CLOB. So thanks for the tip.

    false in access it usually -1

    Yes, I have heard that and based on previous comments was what I was actually expecting. But in my brief tests it always seemed to work correctly for me. Both for INSERT and SELECT queries. Maybe I am just lucky? ;)

    Cfqueryparam / CF8/ Unicode Driver
    1/yes/true - Equated to "yes"
    0/-1/no/false - Equated to "no"

    ReplyDelete
  12. HeadsplodeOctober 22, 2010 at 3:05 AM

    Or I could just be wrong. I can't remember the query my co-worker was using but he tried = 0 and the query returned zero results. Looking at the data Access used before it was used as a web app (data added by the built in forms), it contained -1.

    ReplyDelete
  13. cfSearchingOctober 22, 2010 at 5:28 AM

    Could be. I am also not sure what the behavior is for the non-unicode driver. So it is probably something to watch out for either way.

    ReplyDelete