Error passing null as CF_SQL_NUMERIC in CFSTOREDPROC

[rtefft]

While doing compatibility testing for ColdFusion MX, we ran across a complete showstopper. Whenever we try to pass a null value (ie variale without assigned value) via CF_SQL_NUMERIC, CF errors out with:

Error Executing Database Query.
Invalid data for CFSQLTYPE CF_SQL_NUMERIC.

The procedure call code is:

<cfset out_numeric=&quot;&quot;>
<cfstoredproc 
   procedure=&quot;MX_TEST_PKG.PASS_NUMERIC&quot;
   username=&quot;#session.username#&quot; 
   password=&quot;#session.password#&quot; 
   datasource=&quot;#session.datasource#&quot;>

 <cfprocparam type=&quot;IN&quot; cfsqltype=&quot;cf_sql_numeric&quot; 
    value=&quot;&quot; dbvarname=&quot;I_NUMERIC&quot;>

 <cfprocparam type=&quot;OUT&quot; cfsqltype=&quot;cf_sql_numeric&quot;
    dbvarname=&quot;O_NUMERIC&quot; variable=&quot;OUT_NUMERIC&quot;>	 

</cfstoredproc>

In the code above, I pass "" as I_NUMERIC and it dies. Ditto if I pass a variable with a null value.

I can email the complete CFDUMP results and test code to anyone who thinks they can help. I have verified this using a simple "numeric in, numeric out" procedure.

As we have many screens with non-required numeric fields, this has rendered our whole app useless on MX. We have no such problems on 4.5 or 5.0. In the database world, having a null value is a relatively common occurrence; why MX cannot handle this is beyond me.

Any info or help would greatly be appreicated.

Rich

____________________________
Rich Tefft
PL/SQL Programmer

 

[army001]

I am not a complete believer in MX as of yet, which is why i still use 5.0. So i do not know what CF_SQL_NUMERIC datatype actually means.

- I prefer using SQL stored procedures not CF.

- I would have to say that if the data type is numeric the MX is not allowing anything but a numerical number, not a blank or a null value.

- You could use an outrageous dummy number that can be interpreted in the procedure as a null value, but that might conflict with restraints of form validation you might have.

Sorry if this is not any help, but i didnt want your post to go un-answered.

-jason

 

[rtefft]

Thanks for the info, I appreciate hearing anything I can.

We don't use CF for database updates; we pass the user-entered values to a Stored Procedure and let it do all the work. What happens in this case is MX does an internal check of the user's value (null) against the specified dataype in CFPROCPARAM before actually calling the stored procedure. Considering how typeless (spineless?) CF 4.5 and 5.0 were with variable datatypes, this came a quite a shock. In the DB world, null is a valid value for almost any datatype.

We are changing our CF_SQL_NUMERIC types to CF_SQL_VARCHAR and making sure we do a format check on any input fields. Easy to identify them by searching for CF_SQL_NUMERIC, but duct-taping is still something we should not have to do. I will be notifying Macromedia about it, and update this post when I hear back. ____________________________
Rich Tefft
PL/SQL Programmer

 

[army001]

Yeah, that would be great to know what response Macromedia gives for that issue. I would have suggested changing the datatype but it slipped my mind, either way your right, i'd rather be healed then bandaged.

-jason