Odd MySql CFQueryParam Truncation Issue (Help)?
Can anyone help me out with this? I am used to using Microsoft SQL Server, which I love; recently, however, I have been working on some MySQL 5.0 databases and they seem to have some odd rules. Right now, I am getting a really strange truncation error when using CFQueryParam in a calculated column. Running this code:
SELECT
<cfqueryparam
value="1234567890123456789012345678901234567890"
cfsqltype="cf_sql_varchar"
/> AS value
... returns this value in the query:
1234567890123
As you can see, only the first 13 characters get used in the SQL. I have found this error to be more or less consistent no matter what the data. I tried switching from cf_sql_varchar to cf_sql_longvarchar but there was no improvement. How can I get MySQL to stop truncating my CFQueryParam error? It works when I take out the ColdFusion CFQueryParam tag, but I always feel uncomfortable not using the CFQueryParam tag - I feel like it adds a little bit more of a security comfort blanket so that no matter what data I throw at it, I know the type will be validated.
So, how do I keep the CFQueryParam tag and lose the truncation (short of forcing all clients to use MS SQL Server)?
Want to use code from this post? Check out the license.
Reader Comments
Whats the field definition in MySQl? Just making sure that its not varchar(13) ;)
Don't think the problem is with field definition as Ben isn't querying any field there...
@Stephen,
It's a calculated column - there is no preexisting definition.
hmm well... its got to be down to the bind created by cfqueryparam and the jdbc drivers.
Though why it would be different when you used cfqueryparam as value rather than as part of a condition I'm afraid I don't know. Have to be honest, while I use cfqueryparam against MySQL 5 conditions all the time - I've never had to use it as a part of a calculated value.
@Stephen,
Maybe I just have to get over my fear of non-CFQueryParam and dynamic data and just get rid of it for those scenarios. This might just be a limitation in MySQL that doesn't exist in SQL Server... of course, I never really know which is the limitation of the server and which is a limitation of the driver.
What version of MYSQL/CF are you using? I can not replicate this on MYSQL 5.0.19 and CF 7,0,1,116466 and JConnector 3.1.12 as the driver.
@Jason,
I know we have some flavor of MySQL 5 and I think whatever the default MySQL drive in ColdFusion is. Sorry, I don't know enough about the setup. If you can't reproduce it, I guess it might just be an outdated driver on our end.
Hi Ben,
For what it's worth, I'm using mySQL 5 and CF8 and I'm not experiencing that issue either.
@Glenn,
Thanks... I guess it's just our configuration.
I'm not sure I understand where the value being passed to cfqueryparam is coming from? Is it calculated in CF or in SQL?
Either way, it raises the philosophical question: if you're not passing values directly from the user to the SQL statement, do you really need to use cfqueryparam?
The value of cfqueryparam in this situation is pretty obvious:
<cfqueryparam value="#form.value#">
But my colleagues routinely write code like this:
<cfset bit = 1>
<cfqueryparam value="#bit#">
or even this:
<cfqueryparam value="1">
IMHO, this is unnecessary. But I'd be curious to hear what other developers think about this.
@David,
The value is coming from a user submitted form. The use case is a bit kloogy though. I am, in a way, misusing the query. I am using a SQL call to create a "report criteria" query with columns Name, and Value. Something like (but not quite):
SELECT
(
'Date From' AS name,
<cfqueryparam value="#DateFormat( FORM.date_from )#" /> AS value
)
UNION ALL
(
'Date To' AS name,
<cfqueryparam value="#DateFormat( FORM.date_to )#" /> AS value
)
...etc...
This gives me the ability to pass over a query to my report rendering code which can easily loop over it and output name/value pairs.
So, looking at that, you might be wondering why I even use a SQL call? Why not just create the query manually and populate it? The reason is that with things like date to/from, the value is clear. However, what about if my report criteria is an ID? Ex. client_id? In that case, I use the SQL query to get that value:
(
'Client' AS name,
(
SELECT c.name FROM client c WHERE c.id = <cfqueryparam ... />
) AS value
)
This way, for the most part, I can group all my criteria construction into one place.
So, where does CFQueryParam come into play? I use it for the "value" bindings since I feel that to a certain degree, it is user entered. Is it truly required? Not really, but I like to use it out of habit.
As far as the use of CFQueryParam with static values like is_active = 1, I used to be guilt of that :) I have since gotten rid of that. All hard coded values are now just plainly entered into the code. Ideally, I only want to use CFQueryParam where it adds value. Maybe this example (the criteria query) is not one of those cases.
try taking out cfsqltype="cf_sql_varchar" and just use value.
Ben, have you tried using the maxlength attribute of cfqueryparam?
I am getting a lot of errors trying to use <CFQUERYPARAM> with mySQL. The error I am getting is this: "Communication link failure: Unknown command"
@Adrian,
That doesn't sound good. Does the database communication work with fewer query params?
Actually it works very weird. I think the error shows up only when using CF_SQL_CHAR... I am not sure yet. I have to do more debuging. Anyhow, for the moment I just removed <CFQUERYPARAM> tags and just use the values.
@Adrian,
I know there are some compatibility issues with some of the cfSqlType values. For instance, some databases support CF_SQL_DATE and some don't. I believe the ColdFusion documentation somewhere touches on this. Perhaps, for some reason, your driver simply doesn't translate the CHAR well. Try replacing it with VARCHAR in the CFQueryParam?