Odd MySql CFQueryParam Truncation Issue (Help)?
Posted February 27, 2008 at 11:49 AM
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:
Launch code in new window » Download code as text file »
- 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)?
Download Code Snippet ZIP File
Comments (13) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Dig Deep Fitness iPhone Fitness Application Beta
Trimming An Image Canvas With ImageUtils.cfc ColdFusion Image Component
Reader Comments
Whats the field definition in MySQl? Just making sure that its not varchar(13) ;)
Posted by Stephen Moretti on Feb 27, 2008 at 11:54 AM
Don't think the problem is with field definition as Ben isn't querying any field there...
Posted by Radek on Feb 27, 2008 at 11:57 AM
@Stephen,
It's a calculated column - there is no preexisting definition.
Posted by Ben Nadel on Feb 27, 2008 at 11:57 AM
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.
Posted by Stephen Moretti on Feb 27, 2008 at 12:37 PM
@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.
Posted by Ben Nadel on Feb 27, 2008 at 12:40 PM
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.
Posted by Jason Morgan on Feb 27, 2008 at 1:53 PM
@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.
Posted by Ben Nadel on Feb 27, 2008 at 2:41 PM
Hi Ben,
For what it's worth, I'm using mySQL 5 and CF8 and I'm not experiencing that issue either.
Posted by Glenn Gervais on Feb 27, 2008 at 2:51 PM
@Glenn,
Thanks... I guess it's just our configuration.
Posted by Ben Nadel on Feb 27, 2008 at 3:15 PM
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.
Posted by David Stamm on Feb 28, 2008 at 10:48 AM
@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.
Posted by Ben Nadel on Feb 28, 2008 at 10:56 AM
try taking out cfsqltype="cf_sql_varchar" and just use value.
Posted by d on Feb 28, 2008 at 9:11 PM
Ben, have you tried using the maxlength attribute of cfqueryparam?
Posted by Tom Mollerus on Feb 28, 2008 at 9:47 PM
