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