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
Posts
13 comments:
Nice .. but im my experiance sometimes longvarchar works for memo and sometimes it woun't.. have not figured why it is hit and miss
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, ... ?
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?
@Anonymous,
Very good point! Done :)
-Leigh
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">
@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
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.
@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.
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.
"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.
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"
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.
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.
Post a Comment