🚀 go-pugleaf

> I am using Microsoft SQL Server 2000 and Oracle 8i (8.1.6). For SQL server
I
> am using the OLEDB driver that came with MDAC2.6 and for Oracle I am using
> OLEDB driver provided by Oracle.

Since you are using OLEDB in both cases (guess I could have deduced that
from the fact that you are posting in the .ado group), then maybe you could
go directly to the OLEDB driver and ask it? I think this is a dead end, but
it might be worth investigating.

> Do I have to use the system tables specific to the respective database, to
> get this info ?
> In that case, what is the meaning of the following two properties ??
> (1) TDataSet->FieldDefList->FieldDefs[i]->Required
> (2) TDataSet->FieldDefList->FieldDefs[i]->Attributes.Contains(faRequired)

To simplify, you are really asking what is the purpose of
TFieldDef::Required (and also TField::Required) and why aren't they being
set correctly based on the schema of your tables. I will hazard a guess (but
it is only a guess). FieldDef's are typically used to create new persistent
fields in code, or to add fields to a table or clientdatset. Here, you would
set Required from your code, as opposed to having it read in for you.

Now to answer the question of why Required isn't being set for you based on
the table schema. My guess is that when you open and ADO dataset, the
information needed to determine the value of required is not available, so
the VCL sets it to false.

Think about the flow of control in calculating the required property. First
you have the generic VCL dataset code (TDataSet's, TField's, and
TFieldDef's). These database classes are generic, and are used for ADO, the
BDE, interbase express classes, and midas (TClientDataset). Then you have
the VCL ADO classes that talk directly to Microsoft's ADO COM objects.
Microsoft's ADO objects talk to the OLEDB provider you have chosen, and the
OLEDB provider talks directly (usually) to the backend database server. In
all, it looks like this

VCL(generic)->VCL(ado specific)->ADO COM->OLEDB->database server

So why isn't Required being set? Where is the weak link in the chain? Let's
start at the far right with the database server. Do we know that both Oracle
and SQL Server allow you to determine whether a field is required? I am
pretty confident that SQL server does, because you could query the system
tables if you had to. I don't know about oracle, but I will assume that it
does.

Now you have to look at the OLEDB provider. OLEDB is a complicated mess of
hundreds of different COM interfaces. Does one of those interfaces allow you
to inspect whether a field is required or not? This I don't know. If such an
interface does exist, do both the Oracle and the SQL server OLEDB providers
implement this interface correctly? Again I don't know. Some pretty big
unknowns.

Now we get to the ADO layer. Assuming that the OLEDB layer allows you to
figure out whether a field is required or not, does ADO provide a way to
propagate this information up when you perform a query? Again I don't know
(hmmm, I seem to be saying that a lot, maybe you should take my advice with
a grain of salt).

Finally, we arrive at the VCL. Since the VCL field and fielddef classes are
generic to many database engines(ADO, BDE, etc), it is conceivable that some
features may be available to one engine that is not available to another.
For example, the BDE may allow you to read whether a field is required, but
ado may not. In this case, TFieldDef::Required would work for the BDE, but
be meanlingless for ADO.

So where are we know? My best guess is that based on the database engine
that you have chosen (ADO) and the type of query you are running, the VCL is
unable to figure out what Required should be. FWIW, I tested a TTable with a
oracle table through the BDE SQL links driver, and the required property of
the field defs seemed to be set correctly!

Sorry if this isn't of much use to you.

Harold Howe [TeamB]
http://www.bcbdev.com