tracker issue : CF-4200012

select a category, or use search below
(searches all categories and all time range)

CF2016 -- value of dbVarName is ignored

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/CannotReproduce

Reporter/Name(from Bugbase): Michael Ladakos / Michael Ladakos ()

Created: 10/12/2017

Components: Database

Versions: 2016

Failure Type: Others

Found In Build/Fixed In Build: CF2016 - Update 5 /

Priority/Frequency: Normal /

Locale/System: / Windows 10

Vote Count: 0

Problem Description: Recently upgraded from CF10 to CF2016. The stored procedures broke on lines that contained dbvarname. I would get an error that a databse procedure has failed to execute and the code would error out on the line containing the dbvarname without the prepended colon I inserted the colons and everything worked. 

About 1 month later (a few days ago)

I am given a new section of the website to test for upgrade to CF2016. The code still breaks. However, now the code breaks AFTER the stored procedure. When we try to use variables output from storedprocedures, they are null and errors occur. This is fixed by prepending the dbvarname value with a colon. However, the code will work regardless of the value of dbvarname is, and dbvarname functionality is not working. It is maintaining positional referencing for the stored procedure. 

Also, my debug output doesn't have values in the storedprocedure tables for dbvarname. Unsure if this is the expected behavior for it since dbvarname was disabled for so long. Maybe it wasn't fixed/updated. 

Steps to Reproduce:
In CF2016, any stored procedure utilizing dbvarname without a prepended symbol. Example follows:

<cfstoredproc procedure="find_passmod_age" datasource="#request.dsn#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname=":p_version" value="1A" maxlength="5" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname=":p_user_logon" value="#variables.username#" maxlength="30" null="No">
<cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname=":p_days_ago" variablE="variables.p_days_ago" maxlength="4" null="No">

This is the current code, but I've been changing the values of dbvarname. examples of tests below:


and I also interposed the two IN cfprocparam lines and got different results. 

Actual Result:

If the colon is missing, it will error out. Instead of erroring out on the cfprocparam line with the dbvarname missing the colon, it errors out after the stored procedure when the out variable is used with a null value. 
If the colon is not missing, the procedure is run with positional referencing regardless of the value of dbvarname and is successful even if the value is null or has a value that doesn't match the database .

Expected Result:

If the colon is missing, it used to error out on the storedprocedure line. Unsure if this is intended. 
If the colon is not missing, it should use dbvarname to reference the variables used in the storedprocedure on the database. If the values don't match, it should error out. If the values do match, positional referencing shouldn't be required and the cfprocparam lines should be interchangeable without affecting the stored procedure operation. 

Any Workarounds:

My page can work by sticking to positional referencing, but the functionality of dbvarname does not appear to be working.



Hi Michael, DBVarname is supported with CF2016 release. However, if the backend database does not support stored procedures with the named parameter, then it may not work. Please provide the information mentioned below: 1. Have you verified it on the latest update of CF2016? 2. Database server name 3. Repro case. I have tried with the basic example and it works fine.
Comment by Nimit S.
232 | October 20, 2017 11:58:29 AM GMT
Given the nature of our project, I don't want to reveal any unnecessary information. How can we verify our database supports this feature?
Comment by Michael L.
233 | October 30, 2017 07:33:43 PM GMT
Hi Michael, Can you please tell me the Database server which you are using along with the version number? If you do not feel comfortable sharing the details on the bug tracker, then you can send the information at -Nimit
Comment by Nimit S.
234 | October 31, 2017 02:33:57 PM GMT
Oracle 12C Release 1
Comment by Michael L.
235 | November 07, 2017 01:45:11 PM GMT
Hi Michael, Oracle 12c Release 1 do support stored procedures with named parameter. Can you please confirm if you are still facing this issue with the stored procedure? -Nimit
Comment by Nimit S.
29584 | August 22, 2018 04:08:19 AM GMT
Hi Michael, Can you please confirm if you are still facing this issue with the stored procedure? -Nimit
Comment by Nimit S.
29637 | August 29, 2018 05:54:14 PM GMT