| Subject | RE: [IBO] Bugs: Strange query result that involve timestamp field with date params |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2012-06-07T06:52:41Z |
Hi Jason!
Another thing is that when I program and don't remember the name of a property, I typically type what I believe to be the start of the name. So maybe ParamsCoerce defined as SET OF [DateTimestamp, CharVarchar, Numbers] rather than CoerceDateParamsToTimestamp?
PS! On IB_SQL (IBO version 4.9.14.48) the following SQL produces an access violation upon prepare:
select *
from rdb$database
where cast(rdb$relation_id as integer) = :Param1
or cast(rdb$relation_id as decimal(9,2)) = :Param1
Small changes, like using decimal(18,2) or using SmallInt rather than Integer works.
Set
>Ok, I tinkered around some and determined that if I coerce all input parameters of type SQL_TYPE_DATEFirst, I'm fairly content with how this works today and have no strong preferences.
>to SQL_TIMESTAMP that it will work.
>Can anyone think of any reason why a DATE only field couldn't be handled as a regular TIMESTAMP field?No, not as long as BETWEEN and equality comparisons will work (I don't know whether TIMESTAMP is treated as a decimal or floating point value and 2012-06-07 > 2012-06-06 23:59:59.99999999999987).
>I'm thinking of adding a property called CoerceDateParamsToTimestamp to handle this.How do things function today? Let's say you have the above case with MyParam referring to both a timestamp and a date field. Will ParamByName('MyParam').AsDate refer to the date field and ParamByName('MyParam').AsDateTime refer to the other?
>I'm wondering if I should just add it as a global flag, a session property, a connection
>property or a statement property.
>I'm also wondering what the default value should be.If the answer to my above question is NO, then I'd say the default value ought to be TRUE, it is unlikely that people use two identical names to refer to two separate parameters when ParamByName cannot be used. Possibly excepting such overloading, I even find it hard to think of a case when not converting makes sense, so I'd even say that this property could be PROTECTED rather than PUBLIC or PUBLISHED.
>Thoughts?Why only Date and DateTime? When I've been bitten by something like this, it has typically been when I have one CHAR and one VARCHAR field, and I can imagine similar things happening with BLOB subtype text and e.g. integer vs decimal.
Another thing is that when I program and don't remember the name of a property, I typically type what I believe to be the start of the name. So maybe ParamsCoerce defined as SET OF [DateTimestamp, CharVarchar, Numbers] rather than CoerceDateParamsToTimestamp?
PS! On IB_SQL (IBO version 4.9.14.48) the following SQL produces an access violation upon prepare:
select *
from rdb$database
where cast(rdb$relation_id as integer) = :Param1
or cast(rdb$relation_id as decimal(9,2)) = :Param1
Small changes, like using decimal(18,2) or using SmallInt rather than Integer works.
Set