tracker issue : CF-3808734

Hi Himavanth and Nimit, I've also verified this in CF11 Update 3 (11,0,03,292024(PreRelease)). Thanks!, -Aaron

Comment by External U.

Actually, what was the fix? B/c it looks like dbvarname was already supported by default in CF Final (11,0,0,289822), as the following runs fine (inserts rows w/o error) w/ MSSQL: <cfquery> CREATE TABLE ticket_CF-3808734 ( id int IDENTITY(1,1) PRIMARY KEY, foo varchar(50) NOT NULL ) </cfquery> <cfquery> CREATE PROCEDURE InsertIntoTicketCF-3808734 (@foo VARCHAR(255), @id INT OUTPUT) AS BEGIN INSERT INTO ticket_CF-3808734 (foo) VALUES (@foo); SELECT @id = @@IDENTITY; END; </cfquery> <cfstoredproc procedure="InsertIntoTicketCF-3808734"> <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@foo" value="bar"> <cfprocparam cfsqltype="cf_sql_integer" dbvarname="@id" type="out" variable="id"> </cfstoredproc> <cfdump var="#variables.id#" /> Thanks!, -Aaron

Comment by External U.

Hi Aaron, By default, we used to ignore dbvarname attribute in cfprocparam tag. In ColdFusion 11, you can use dbvarname, by enabling JVM flag "-Dcoldfusion.dbvarDependants={DSN}. But now with ColdFusion 11 Update 3, dbvarname attribute is enabled by default. We need not use JVM flag to enable it. In this testcase, it is treating it as positional parameter and ignoring dbvarname attribute. If you switch the order of parameter, then it should not work by default in CF Final (11,0,0,289822). Regards, Nimit

Comment by Nimit S.

Hi Nimit, Aha! Okay, just verified (by switching the order of the parameters) you are correct - it is fixed in CF11 Update 3 =) Thank you for the explanation! BTW, I did not get any email when you added your comment here. I just now saw your comment for the 1st time. That is why my reply was delayed. Thanks!, -Aaron

Comment by External U.

While testing our app for update 3 I found The old stored procedure that is working on cf9 now doesn't work on cf 11 with update 3. I guess that's related to this bug. I am getting below error while all my input data looks fine. And it still works on CF9. character to number conversion error ORA-06512. I have few numeric inputs as well as strings to the stored proc. Am I missing anything ?

Comment by External U.

CF9 ignores dbvarname attribute even if specified. CF11 sends them to the database. It is possible that your dbvarname attribute is set to something wrong. We can help if you send us the storedproc call code and some environment details like the database/driver being used.

Comment by Himavanth R.

Hi Himavanth, I have attached the code and stored proc parameter list. I tried with oracle as well as jdbc driver. it still doesn't work. this issue happened only after applying update 3. Thanks

Comment by External U.

Could you try appending : to your dbvarname attributes. :P_USER_NAME in place of P_USER_NAME Since CF11 Update 3, we are passing on the dbvarname attributes to the driver without any modifications.

Comment by Himavanth R.

Yes It works now after appending : to dbvarname. Is there going to be a fix for this ? otherwise this is gonna be a huge change in the code throughout the application !

Comment by External U.

The plan was to pass on the dbvarname attributes as specified to the driver. But you can log a bug for this. We will have to do this based on the database being used because in case of sqlserver, @ needs to prepend the dbvarname.

Comment by Himavanth R.

That is true and Thanks for the input. will create a bug for this.

Comment by External U.

The documentation at https://wikidocs.adobe.com/wiki/display/coldfusionen/cfprocparam says that the dbvarname can be enabled with a JVM property of -Dcoldfusion.dbvarDependants={dsn1, dsn2} As of Patch 3, there doesn't appear to be a way to revert to the prior behavior (which basically ignored the dbvarname property), so our application breaks. We either have to remove all of the dbvarname references, or go through every cfprocparam and validate that it is using the correct name as found in the package being called.

Comment by External U.

@TheraRob, Yup, you'll have to tweak your code. It's was working in versions prior to CF11 Update really by accident (because CF was ignoring the dbvarname attribute). You're not alone in this situation, but the change was a long-needed fix to the loss of this functionality over 10 years ago. Admittedly, having to fix lots of code isn't pleasant.

Comment by External U.