At 09:02 PM 22/01/2007, you wrote:

>hello all,
>
>I have just discovered this TIB_Connection property :-) and I have a couple
>of doubts about the two possible values.
>
>- fetDomainName: Ok, it is rather intuitive but I have noticed that it
>doesn't work for aliased fields. Example:
>
>Domain INT_ID is BIGINT
>Domain CH_NAME is VARCHAR(30)
>Domain CH_SHORT_NAME is VARCHAR(2)
>Domain LOGICAL is SMALLINT
>
>Tables are short for brevity
>
>Table ANAG:
> ID INT_ID,
> NAME CH_NAME,
> PROVINCE INT_ID,
> IS_STANDARD LOGICAL;
>
>Table PROV:
> ID INT_ID
> SHORT_NAME CH_SHORT_NAME,
> LONG_NAME CH_NAME;
>
>SQL:
> SELECT A.ID, A.NAME, A.PROVINCE,
> (SELECT SHORT_NAME FROM PROV P WHERE P.ID = A.PROVINCE) AS PR
> FROM ANAG A
>
>In the TIB_Connection I have set:
>
>ColumnAttributes:
> LOGICAL=BOOLEAN=1,0
> CH_NAME=NOCASE
> CH_SHORT_NAME=NOCASE
>
>FieldsCharCase
> CH_NAME=UPPER
> CH_SHORT_NAME=UPPER
>
>Well, fetDomainName setting works just fine for all fields except for
>aliased field PR that is used in a lookup combo and also in a
>TIB_SearchPanel:
>
> ANAG.NAME and PROV.LONG_NAME are always upper cased and a search on
> these fields is case insensitive. All correct
>
> IS_STANDARD is displayed with a check box in the search panel. Correct
>
> PR is lower cased and search is case sensitive. Incorrect
>
>I'm wondering if this is normal or if there is a trick to make fetDomainName
>work with aliased fields.
>
>- fetSQLType: This is not a real problem, but I am not sure if this value is
>applied on a real SQL type, for example VARCHAR(30) or rather on a field
>name.
>
>I ask this because always referring to the example above, if I add this line
>in TIB_Connection's FieldsCharCase:
>
> PR=UPPER
>
>and this line in TIB_Connection's ColumnAttributes:
>
> PR=NOCASE
>
>two things happen:
>
>1. The aliased field is correctly upper cased now, but...
>
>2. A search on the aliased field continues to be case sensitive, very
>strange
>
>Can you shed some light?

Well, without writing a sermon....

Searching works by modifying the WHERE clause
What you refer to as "aliased fields" are always derived values that
don't have a WHERE track to any database column. So it's not
possible to construct a valid search string like

where UPPER(PR) =

which is not valid SQL. If you don't get a database exception then
it has to be because the search clause parser decides to ignore any
WHERE criterion that, if applied to a derived field, that would cause
an exception.

And derived values never have a domain associated with them. Even
something like

select first_name from employee as "First Name"

where the value isn't modified in any way is still a derived value.

Consider why.

Suppose your expression for your derived field were

select Last_Name || ', ' || First_Name from Employee as List_Name

Domain FirstName is varchar(15), domain LastName is varchar(20), so
our output is varchar(37). What domain could be applied to the output?

You would have to uppercase the derived value in your retrieval expression.

FieldEntryTypes is a set. You can have both fetDomainName and
fetSQLType if you need to. By default it is an empty set. You only
need to set it if you have global ColumnAttributes that need to refer to it.

So, suppose you have a domain named d_Boolean, that is a smallint
limited in its definition by a CHECK constraint to be only 1or 0 if
it is not null. You want to have it recognised throughout the app as
a Boolean, so you include fetDomainName in FieldEntryTypes and you
set the connection-level column attribute as

d_Boolean=BOOLEAN=1,0

Helen