BorlandTalk.com

Gerald A. Ebert · Guest

I am having a problem using the TSQLClientDataSet component in Delphi (V6
Enterprise) to access array fields in Interbase.

I can use IBConsole to see that the field "language_req" in the Interbase
"job" table is defined as [1:5] VarChar (15).

The Interbase SQL statement to get the data is simply
select job_code,
job_grade,
job_country,
language_req[1],
language_req[2],
language_req[3],
language_req[4],
language_req[5]
from job.

But when I examine the "job" field definitions using TSQLClientDataSet, it
sees the array field as ftBytes.

How can I get Delphi to tell me what IBConsole knows about array field
definitions?

Thanks

Gerald A. Ebert · Guest

I figured out not only how to determine if a field is an array but also if
it is a "computed by" field.

Example code:

//
===========================================================================

function xxx.is_array_field (const table_name,
field_name : String;
var lower_bound,
upper_bound,
field_length,
character_Length : Integer;
var type_name : String) : Boolean;

var query : TSQLQuery;

s : String;

begin
result := False;
SQLConnection.TableScope := SQLConnection.TableScope + [tsSysTable];
query := TSQLQuery.Create (nil);

s := 'select b.rdb$lower_bound, '
+ 'b.rdb$upper_bound, '
+ 'c.rdb$field_length, '
+ 'c.rdb$character_Length, '
+ 'd.rdb$type_name'
+ ' from rdb$relation_fields a, '
+ 'rdb$field_dimensions b, '
+ 'rdb$fields c, '
+ 'rdb$types d'
+ ' where ((a.rdb$relation_name = "' + table_name + '") and '
+ '(a.rdb$field_name = "' + field_name + '") and '
+ '(a.rdb$field_source = b.rdb$field_name) and '
+ '(a.rdb$field_source = c.rdb$field_name) and '
+ '(c.rdb$field_type = d.rdb$type))';

try
try
query.SQLConnection := DM_dbExpress.SQLConnection;
query.SQL.Add (s);
query.Open;
query.First;

if (not query.Eof) then
begin
lower_bound := query.FieldByName
('rdb$lower_bound').AsInteger;
upper_bound := query.FieldByName
('rdb$upper_bound').AsInteger;
field_length := query.FieldByName
('rdb$field_length').AsInteger;
character_Length := query.FieldByName
('rdb$character_Length').AsInteger;
type_name := query.FieldByName
('rdb$type_name').AsString;

result := True;
end;

query.Close;
except
on e : Exception
do FMemo.log_error ('is_array_field',
s
+ #13
+ e.Message);
end;
finally
Query.Free;
SQLConnection.TableScope := SQLConnection.TableScope - [tsSysTable];
end;
end;

//
===========================================================================

function xxx.is_calculated_field (const Table_Name,
field_name : String) :
Boolean;

var query : TSQLQuery;

s : String;

begin
result := False;
SQLConnection.TableScope := SQLConnection.TableScope + [tsSysTable];
query := TSQLQuery.Create (nil);

s := 'select a.rdb$field_name '
+ ' from rdb$relation_fields a, '
+ 'rdb$dependencies b'
+ ' where ((a.rdb$relation_name = "' + Table_Name + '") and '
+ '(b.rdb$depended_on_name = "' + Table_Name + '") and '
+ '(a.rdb$field_source = b.rdb$dependent_name) and '
+ '(b.rdb$dependent_type = 3))';

try
try
query.SQLConnection := DM_dbExpress.SQLConnection;
query.SQL.Add (s);
query.Open;
query.First;

while ((not query.Eof) and (not result)) do
if AnsiSameText (Trim (query.Fields.Fields [0].AsString),
field_name)
then result := True
else query.Next;

query.Close;
except
on e : Exception
do FMemo.log_error ('is_calculated_field',
s
+ #13
+ e.Message);
end;
finally
Query.Free;
SQLConnection.TableScope := SQLConnection.TableScope - [tsSysTable];
end;
end;

//
===========================================================================

"Gerald A. Ebert" <GeraldEbert (AT) rcn (DOT) com> wrote