I see the AV and am fixing it.

Thanks for the feedback!

Jason

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of Svein Erling Tysvær
Sent: 07 June 2012 12:53 AM
To: IBObjects@yahoogroups.com
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
preferences.

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


------------------------------------

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