Hi, Helen,

thank you very much for your very elaborate explanations.
Much of what you wrote is, what I already had learned during the database
conversion (thanks to your book too) and my evaluation of IBO.
So, don't necessarily bother to read all of the following, during writing it
and crosschecking with your explanations, I realized, a lot of my writing
just implements your help.
As it seems, most of this is caused by the use of the TDataSet compatible
components, but currently I don't have the option to switch to the native
IBO components. I'm converting several applications from BDE to IB - of
course with the least possible amount of work, and I believe, using the
TDataSet compatible components is the best way.

Sorry, I think I didn't explain the situation or query exactly enough.
In my DB I use two domains for Booleans:
DM_BoolT: SmallInt not NULL, check value in (0,1) default 1
DM_BoolF: SmallInt not NULL, check value in (0,1) default 0
All Tables with boolean fields use these types.
The IB_Connection has these properties set:
FieldEntryTypes := [fetDomainName]
DefaultValues = DM_BoolF=0<CRLF>DM_BoolT=1
ColumnAttributes = DM_BoolF=BOOLEAN=1,0<CRLF>DM_BoolT=BOOLEAN=1,0

Soe IBO can handle all my "boolean" fields correctly - and it does.

Table:
create MyTable (
Field1 OfSomeDomain,
BooleanField DM_BoolF,
OtherFields OtherDomains
);

Query:
select Field1, OtherFields, BooleanField
from MyTable
where BooleanField = :BoolParam

TIBOQuery property ColumnAttributes = BoolParam=BOOLEAN=1,0

When opening this query, the BooleanField is correctly recognized as
boolean, and I can read and write it as boolean (so the "boolean" parameters
of the insert and update query are ok).
The BoolParam is also correctly recognized as a boolean parameter
(Params[0].DataType=ftBoolean).
I just can't set it's value ".asBoolean", because the TParamClass'es
property write method SetAsBoolean (unit DB) converts it to a variant of
type varBoolean / vt_bool, which according to Win32SDK is represented by
0=False, -1=True.
I solved this by writing a simple procedure IBBoolPar(Par: TParam; Value:
Boolean), that converts the boolean value to the integers I want. I was just
hoping, that (for ease of conversion of other old BDE-Apps) I could find a
way to define this conversion for IBO at connection level.

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <IBObjects@yahoogroups.com>
Sent: Tuesday, February 28, 2006 12:54 AM
Subject: Re: [IBO] Boolean params in TIBOQuery


> At 07:33 AM 28/02/2006, you wrote:
>>Hi,
>>Thanks for your help. This works fine for fields in the resulting dataset,
>>but I tried it for parameters, it doesn't work. Setting a parameter to
>>true,
>>where the corresponding IB/FB data type is SmallInt or Integer causes the
>>value to be converted to -1.
>>It seems the problem arises in TParam.SetAsBoolean, where the boolean
>>value
>>is converted to a variant, which again uses -1 as the TRUE value.
>
> That is just a default, because the correct string or int equivalents
> are not known.
>
>>I had hoped, that something in IBO's conversion from TParam to the true
>>FB-Parameter would offer the possibility to configure "set any parameter
>>value thas is not 0 to 1".
>
> No, that is not the logic. IBO's handling of Booleans reflects the
> rules of the database engine, which allow *anything* to be used to
> emulate Booleans.
>
> However ---
>
> By using the AsBoolean method for *writing*, you are asking Delphi to
> convert a variant to a Boolean. That is not going to work for IB/Fb,
> since they don't support a Boolean type. When writing, you need to
> the conversion to be the other way: you read the value from a
> TIB_Column or a non-linked control AsBoolean, and, if you don't have
> the conditions where IBO can figure out that the param is to be
> treated as Boolean, you assign to either the Value property or you
> assign it by the SQL type specifically (AsInteger, in your case).
>
> You fix up your ColumnAttributes settings globally, so that IBO can
> properly interpret what it has to pass in the request. Because you
> haven't done that, you are getting the default conversion
> (0=True, -1=False).
>
> Apparently, there is more for you to understand regarding the
> ColumnAttributes settings.
>
> By setting the attribute of the field at statement level, you are
> overriding the default only for *that* field in *that* dataset.
>
> If your database is consistent about how it handles Booleans, e.g.,
> in your case you always use a smallint (1=true, 0=false) then set the
> ColumnAttribute in the IB_Connection object, as follows:
>
> Set the FieldEntryTypes property to include [fetSQLType].
>
> Add the details of the Boolean mask to the ColumnAttributes
> property. At this level, you must include the table name:
>
> MyTable.MyBoolColumn=BOOLEAN=1,0
>
> Now, when any statement is prepared and the server returns the
> expected attributes of any fields and parameters to the application,
> IBO will always interpret that field or parameter as a Boolean, if
> the type is consistent with the attribute definition.
>
> You can step up to another level if you actually have your Boolean
> usage defined in the database as a domain, e.g.
>
> CREATE DOMAIN D_BOOLEAN
> AS SMALLINT NOT NULL
> CHECK (VALUE IN (1,0))
>
> In that case, add fetDomainName to your FieldEntryTypes, and add the
> domain's Boolean interpretation to the IB_Connection's ColumnAttributes:
>
> D_BOOLEAN=BOOLEAN=1,0
>
> Again, if your database is consistent in using the domain, then IBO
> will do the right thing each time it encounters a table column that
> has been defined for this domain, and will do the right thing with
> prepared params.
>
> The only thing you must keep in mind is that you can't pre-assign the
> attributes of fields (output arguments) and params (input arguments)
> for stored procedures via the domain. Domains are not recognised in
> SPs, so the database engine never returns such attributes for them,
> that could be picked up and used by fetDomainName. For these, you
> will always have to assign the ColumnAttributes for both input and
> output params at statement level.
>
> Another thing to keep in mind is that a TIB_Column is not a TField or
> a TParam. Both the Fields[] and Params[] in native IBO are TIB_Row
> objects. The attributes of columns and parameters are not known
> until after the statement is prepared. If you use
> ParamByName.Property or ParamByName.Method, IBO will prepare the
> statement if it is not already prepared. If you use Params.Property
> or Params.Method, you will get an AV. There is no persistent field
> sitting there waiting for unbound assignments.
>
> In the case of the TDataset-compatible components, you won't always
> get an AV, because that object is a TDataset that has an internal
> TIB_Dataset object. If you have instantiated the TField and TParam
> objects, then the TIBODataset has access to those objects while the
> statement is unprepared, and assignments to these objects won't cause
> an AV. However, by doing that, you run the risk of having your
> external and internal datasets out of synch when the statement is
> eventually prepared.
>
> Helen
>
>
>
> ___________________________________________________________________________
> 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
>
>
>
>
>
>
>
>