1

I have a simple Query of Queries statement like this:

<cfquery name="rsProductTypeID" dbtype="query">
SELECT
 ProductTypeID
FROM
 rsProductTypes
WHERE
 ProductType = <cfqueryparam value="Computers" cfsqltype="cf_sql_nvarchar"/>;
</cfquery>

All it is doing is asking for the primary key value (ProductTypeID) from the recordset rsProductType depending on which ProductType we are asking for.

For some reason the above query gives me this error:

PreparedStatement.setNString(int,java.lang.String)

But if I change the Query of Queries and remove the <cfqueryparam> part and replace it with normal text, then it works fine. Here is the working code:

<cfquery name="rsProductTypeID" dbtype="query">
    SELECT
     ProductTypeID
    FROM
     rsProductTypes
    WHERE
     ProductType = 'Computers';
 </cfquery>

I can't tell what the difference is between the code that is causing it to fail when using <cfqueryparam> and not.

volume one
  • 6,800
  • 13
  • 67
  • 146

2 Answers2

4

I think I figured it out... when ColdFusion returns a recordset, it doesn't maintain the original datatype from the database. In my database ProductType is defined as nvarchar(50) but ColdFusion returns it as varchar format. So the cfsqltype attribute was causing the error. I changed the cfsqltype attribute to cf_sql_varchar and it works fine. Very annoying but what can you do.

I posted it as a self-answer in case anyone else gets this error. Not sure if that's against the rules or not.

volume one
  • 6,800
  • 13
  • 67
  • 146
  • What did you change it from? Your question shows cf_sql_varchar as the cfsqltype. Semi-colons in cfquerys sometimes cause problems. I suggest that you get in the habit of not using them. – Dan Bracuk Aug 15 '14 at 17:35
  • I've also just learnt that the WHERE statement in a Query of Queries is case-sensitive. So `ProductType = 'computers'` matches nothing, but `ProductType = 'Computers'` returns a record. – volume one Aug 15 '14 at 17:43
  • 2
    where clauses are case sensitive almost everywhere except for sql server. – Dan Bracuk Aug 15 '14 at 17:57
  • "Very annoying but what can you do." raise a bug with Adobe. And post the bug ref back here. – Adam Cameron Aug 15 '14 at 20:15
  • @AdamCameron not a bad idea but easier said than done. just been to http://cfbugs.adobe.com/ and it says site unavailable. not sure if its a permanent thing because it doesn't say when it will be back up. Oh how I wish Macromedia still existed. – volume one Aug 15 '14 at 22:52
2

cf_sql_nvarchar is not a valid value for the cfsqltype attribute of cfqueryparam

Docs: https://wikidocs.adobe.com/wiki/display/coldfusionen/cfqueryparam

Sean Coyne
  • 3,864
  • 21
  • 24
  • 1
    Sean - That is a mistake in the documentation. It was added in CF10. The new type is a [more granular alternative to the old "String Format" setting](http://stackoverflow.com/questions/10802388/what-are-the-details-for-using-cf-sql-nvarchar-in-coldfusion-10/10848136). That said, I do not know how it is handled with QoQ's. – Leigh Aug 15 '14 at 19:42
  • Too bad we cannot edit the CF docs the way we can S.O. questions and answers ;-) – Leigh Aug 15 '14 at 19:45
  • @AdamCameron - Not just leave comments like with the old docs? Huh, if so .. learn something new everyday. – Leigh Aug 15 '14 at 20:37
  • You need to apply for a login, but if they gave one to me, they'll give one to anyone. – Adam Cameron Aug 15 '14 at 21:11