Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Colleen Lanham and Demian Holmberg and Robert Dix and Paul Carney

Odd MySql CFQueryParam Truncation Issue (Help)?

By Ben Nadel on
Tags: ColdFusion, SQL

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)?


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

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.

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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"

Reply to this Comment

@Adrian,

That doesn't sound good. Does the database communication work with fewer query params?

Reply to this Comment

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.

Reply to this Comment

@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?

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.