Ticket #29 (closed defect: fixed)
Maxlength for Oracle Date Types Causes Oracle Error with CFQUERYPARM Tag
| Reported by: | matthew.darby@… | Owned by: | dhughes |
|---|---|---|---|
| Type: | defect | Priority: | normal |
| Milestone: | Beta | Component: | Reactor - DBMS Interfaces |
| Version: | 1.0 | Severity: | major |
| Keywords: | Cc: |
Description
One of the latest versions of Reactor changed the attributes set for the cfqueryparam tag for table DAO objects. Previously, the tag was set using maxlength set to the attribute scale. In an update, this was changed to correctly set the maxlength attribute to be the maxlength accepted for a certain table column. This change is correct, however, in Oracle the max length for a date object is set to 7. For date formatted MM/DD/YYYY upon insert or update the length of the string passed exceeds the maxlength set by the cfqueryparam tag. This causes a ColdFusion error.
There are two problems I see and it is possible that one change will correct both. First, even though Oracle passed back the value of 7 as the maxlength for all Oracle date types, it appears there is a need to set this value a little higher for the purposed of inserts and update if we continue to use the maxlength attribute within the cfqueryparam tag. Without making this change we will always have an error.
The second problem I see is that though we validate using CF's IsDate() method with the performing the insert and update bypasses checks to see if the string submitted for date are greater than the maxlength. This is why the string I passed to Reactor appeared to be valid and it is, but threw a CF error upon running the query.
So sum things up we can't use what Oracle passes back as a maxlength value to perform inserts or updates using the cfqueryparam tag and the maxlength attribute. Instead the value needs to be raised to a greater value or the maxlength attribute not used at all. I am not sure if other databases have this problem or other data types.