Subject RE: [IBO] Bugs: Strange query result that involve timestamp field with date params
Author IBO Support List
I see the AV and am fixing it.

Thanks for the feedback!


-----Original Message-----
From: [] On Behalf
Of Svein Erling Tysvær
Sent: 07 June 2012 12:53 AM
Subject: RE: [IBO] Bugs: Strange query result that involve timestamp field
with date params

Hi Jason!

>Ok, I tinkered around some and determined that if I coerce all input
parameters of type SQL_TYPE_DATE
>to SQL_TIMESTAMP that it will work.

First, I'm fairly content with how this works today and have no strong

>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.
>I'm wondering if I should just add it as a global flag, a session property,
a connection
>property or a statement property.

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 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


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

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 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.



IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________ - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links