CODEWITHSUNDEEP
sql-servercoldfusion
Scott Jibben
Scott Jibben

Reputation: 2287

CFQueryParam/cfsqltype for MS-SQL varchar(max) datatype

I'm in the process of updating MS-SQL table structures to use nvarchar(max) instead of the text datatype.

Previously, for my ColdFusion queries using the text datatype, I used cfsqltype="CF_SQL_LONGVARCHAR":

<cfquery name="local.qryUpdateByPK" datasource="#local.dsn#">
UPDATE
    [JobPosting]
SET
    [JobDescription] = <cfqueryparam value="#arguments.JobDescription#" cfsqltype="CF_SQL_LONGVARCHAR" null="#YesNoFormat(NOT Len(Trim(arguments.JobDescription)))#">
WHERE
    [JobPostingID] = <cfqueryparam value="#arguments.JobPostingID#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

I'm wondering what should be used for the cfsqltype attribute when using nvarchar(max) or varchar(max).

Upvotes: 3

Views: 1031

Answers (1)

Leigh
Leigh

Reputation: 28873

If memory serves, information on the nvarchar support was a bit sketchy in the main Adobe documentation last I checked. However, the correct sqltype for nvarchar(max) is CF_SQL_LONGNVARCHAR.

Update:

The new types are mentioned briefly in Database Enhancements for CF10

New data type support for CFSQLType

The cfqueryparam and cfprocparam tags support the following SQL types:

  • CF_SQL_NCHAR
  • CF_SQL_NVARCHAR
  • CF_SQL_LONGNVARCHAR
  • CF_SQL_NCLOB
  • CF_SQL_SQLXML

Upvotes: 4

Related Questions