Skip to main content
S
SeemusDesignAndWeb
Participating Frequently
January 22, 2015
Answered

Stored Procedures stop working after Coldfusion 11 install (OS X 10.10.1)

  • January 22, 2015
  • 1 reply
  • 3457 views

I recently installed a fresh install of CF 11 on Mac OS X Yosemite. All works fine except my stored procedures now no longer work. I am using SQL Server 2008 on Win XP using VMWare Fusion 7.

The Stored procedure executes perfectly on the database and, when testing, all the paramaters are outputting OK on my CF page. But I get an error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.

The SP code is below:

<cfstoredproc datasource="#dsn#" procedure="clw_logThisPage">

  <cfprocparam cfsqltype="CF_SQL_NUMERIC" dbvarname="logid" value="#session.logid#">

  <cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="memberid" value="#session.member_id#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="pagename" value="#launch#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="clwref" value="#ref#" >

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="casesletter" value="#letter#">

  <cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="statinstview" value="#view#">

  <cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="year" value="#year#">

  <cfprocparam cfsqltype="CF_SQL_NUMERIC" dbvarname="id" value="#id#">

  </cfstoredproc>

It looks as though the values are not being passed correctly to MS SQL. But I cannot find why this is happening. Any help would be much appreciated.

With thanks

John

This topic has been closed for replies.
Correct answer Carl Von Stetten

Sorry, somehow I got Oracle stuck in my head.  The symbol for SQL Server is "@".

-Carl V.

1 reply

Carl Von Stetten
Carl Von Stetten
Legend
January 22, 2015

By chance, did you download the CF11 installers within the past few week or so?  If that is the case, it has update 3 already integrated.  Update 3 changed the way the dbvarname attribute works for stored procedures - it restored some earlier functionality removed from CF back in version 7.  Prior to Update 2, the dbvarname attribute was ignored entirely and parameters were passed in the order they were coded in your cfstoredproc tag.  With Update 2, a JVM flag was added to enable CF to honor the dbvarname attribute.  In Update 3, the attribute is honored no matter what.  There is some debate right now in the bugbase as to whether this functionality was implemented well to support backward compatibility.

Anyway, you need to make sure the values in the dbvarname attributes match **exactly** the names of the parameters in the stored procedures, and they must be prefixed with the appropriate Oracle parameter symbol (I believe it is the colon ":"). 

-Carl V.

    S
    SeemusDesignAndWebAuthor
    Participating Frequently
    January 22, 2015

    Thanks for you reply. Yes I downloaded it yesterday and I imagined it was something to do with Update 3. However, I am accessing SQL Server. So I have checked the Stored Procedure on MS SQL and the variables are the same.  As I am using SQL Server should I add a prefix, if so what is it? As the colon causes an error too.

    Many thanks

    John

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    January 22, 2015

    Sorry, somehow I got Oracle stuck in my head.  The symbol for SQL Server is "@".

    -Carl V.

    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

    Explore now
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices