Major Whinge about Fields...

Major Whinge about Fields...

Post by Paul Ingra » Tue, 16 Jan 1996 04:00:00



Well, here is a nice can of worms for your amusement. I have some code
which has no prior knowledge of a database structure, and from time
to time I may want to copy things from one table to another (for
instance, mapping foreign keys with a drop down picklist) and for this
purpose I use an InfoPower TwwDBLookupCombo.

Great.

I thought the really easy bit would be the copying (or assigning)
across of values from one table to another. I feel that I must be
missing something, since I have not yet found a way of discovering and
using the native rich Field types that Delphi makes available *if you
know what the field is at design time*. I have ended up using the
really naff construction below, making use of the DataType property
of a TField and the TField conversion properties. This must surely
rate as one of the most distasteful pieces of code anyone could ever
have lurking in their cupboard of skeletons. (FX: Sob !) All I want
to do is identify the TField descendant type and use it appropiately.

I expected there to be about one line of code to this.

I would have liked something such as:-

        GenTable.FieldByName(FKey).Field(GenTable.FieldByName(FKey).Field.Idntify).Assign :=
                        LookupTable.FieldByName(FKey).Field(LookupTable.FieldByName(FKey).Field.Idntify).Value ;

Where Delphi would work out by context that I wanted to assign a value
(of the same type) from LookupTable field to GenTable field. This ignores
the current assign/value usage. I have added and identify method, which
effectively casts the field to it's own true object type, rather than the
(seemingly useless) Field type. This might be quite mad, but would
certainly be most useful.

Anyway, here is my dirty linen...

(BTW, set tabstops to 4 to see it how it was written)

procedure TDataForm.OnComboBoxCloseUp(Sender: TObject;
        LookupTable: TwwTable; FillTable: TDataset; modified: Boolean);
var
        Current: PMyNode ;              { This is a pointer to a member of a linked list containing some odds and sods }
        z : integer ;                   { General purpose counter }
        FKey, PKey : String ;   { Somewhere to put the current Foreign and Primary keys }
begin
        Current := FindPMyNode((GenDBGrid.GetActiveField AS TField).FieldName);
        if Current = nil then
        begin
                MessageDlg('Unable to find Current for ' + LookupTable.Name, mtinformation,[mbOk], 0);
                Exit ;
        end ;
        z := 0 ;
        while z < Current^.PKeyList^.Count do
        begin
                PKey := Current^.PKeyList^.Strings[z] ;
                FKey := Current^.FKeyList^.Strings[z] ;
                case LookupTable.FieldByName(Current^.PKeyList^.Strings[z]).DataType of
                        ftBoolean       : GenTable.FieldByName(FKey).AsBoolean  := LookupTable.FieldByName(PKey).AsBoolean ;
                        ftBCD           : GenTable.FieldByName(FKey).AsInteger  := LookupTable.FieldByName(PKey).AsInteger ;
                        ftDate          : GenTable.FieldByName(FKey).AsDateTime := LookupTable.FieldByName(PKey).AsDateTime ;
                        ftDateTime      : GenTable.FieldByName(FKey).AsDateTime := LookupTable.FieldByName(PKey).AsDateTime ;
                        ftFloat         : GenTable.FieldByName(FKey).AsFloat    := LookupTable.FieldByName(PKey).AsFloat ;
                        ftSmallint      : GenTable.FieldByName(FKey).AsInteger  := LookupTable.FindField(PKey).AsInteger ;
                        ftString        : GenTable.FieldByName(FKey).AsString   := LookupTable.FieldByName(PKey).AsString ;
                        ftTime          : GenTable.FieldByName(FKey).AsDateTime := LookupTable.FieldByName(PKey).AsDateTime ;
                        ftWord          : GenTable.FieldByName(FKey).AsInteger  := LookupTable.FieldByName(PKey).AsInteger ;

{                       These types are unsupportable with the current naff method.
                        ftBlob          : (GenTable.FieldByName(FKey) AS TBlobField).Assign
                                (LookupTable.FieldByName(PKey) AS TBlobField) ;
                        ftBytes         : (GenTable.FieldByName(FKey) AS TBytesField).Assign
                                (LookupTable.FieldByName(PKey) AS TBytesField) ;
                        ftVarBytes      : (GenTable.FieldByName(FKey) AS TVarBytesField).Assign
                                (LookupTable.FieldByName(PKey) AS TVarBytesField) ;
                        ftGraphic       : (GenTable.FieldByName(FKey) AS TGraphicField).Assign
                                (LookupTable.FieldByName(PKey) AS TGraphicField) ;
                        ftMemo          : (GenTable.FieldByName(FKey) AS TMemoField).Assign
                                (LookupTable.FieldByName(PKey) AS TMemoField) ;
                        ftCurrency      : (GenTable.FieldByName(FKey) AS TCurrencyField).Value :=
                                LookupTable.FieldByName(PKey).AsInteger ; }

}

                end ;
                if LookupTable.FieldByName(Current^.PKeyList^.Strings[z]).DataType = ftUnknown then
                begin
                        MessageDlg('Could not assign ' + LookupTable.TableName +
                                '/' + PKey + ' to ' + GenTable.TableName + '/' + FKey,
                                mtinformation, [mbOk], 0) ;
                Inc(z) ;
                end ;
        end ;
end;

What I am looking for is either what I am missing, or some
creative way of doing the above, preferably using the recommended
TField descendants without hideous switches (cases) etc.,

OnLine Help says of TField:-
A TField component is an abstract object. The Fields property of
a dataset is always one of the following TField descendants:

TStringField    Fixed length text data up to 255 characters
TIntegerField   Whole numbers in the range -2,147,483,648 to 2,147,483,647
TSmallintField  Whole numbers in the range -32768 to 32767
TWordField      Whole numbers in the range 0 to 65535
TFloatField     Real numbers with absolute magnitudes from 5.0*10-324 to 1.7*10308 accurate to 15-16 digits
TCurrencyField  Currency values. The range and accuracy is the same as TFloatField
TBCDField       Real numbers with a fixed number of digits after the decimal point. Accurate to 18 digits. Range
depends on the number of digits after the decimal point. [Paradox only]
TBooleanField   True or False values
TDateTimeField  Date and time value

TDateField      Date value
TTimeField      Time value
TBlobField      Arbitrary data field without a size limit
TBytesField     Arbitrary data field without a size limit
TVarBytesField  Arbitrary data field up to 65535 characters, with the actual length stored in the first two bytes
TMemoField      Arbitrary length text
TGraphicField   Arbitrary length graphic, such as a bitmap

And it is these I want to get at, but how ? remember that
I have *no prior knowledge* of the database structure.

Thanks for your help - if nothing else, that appalling code
should give you a laugh. I personally think it's rather sad.

Paul.

--
Paul Ingram Group Ltd
140A High Street               email: firstname.lastn...@ig.co.uk
Godalming                      voice: +44 1483 424424
Surrey, UK                       fax: +44 1483 419419

 
 
 

1. Major major MAJOR bug in BDE

I have (unfortunately) discovered a rather severe bug in the Borland Database
Engine.  To be brief: passthrough (RequestLive = TRUE) SQL queries cause a memory
leak in IDQRY01.DLL.

It may be specific to my SQL-LINK (ORACLE) but every TQuery.Open and every Master
Query DataChange in a Master-Detail Query causes a leak of about 5k from the
IDQRY01.DLL memory allocation (for EVERY TQUERY).  After 1700 of these leaks,
IDQRY01.DLL reports an EOutOfMemory exception, and must be unloaded and reloaded
to continue.

1700 sound like a lot?  Not if you have 10 detail queries, like I do.  That's
right kiddies, hit the (Master query TDBNavigator) Prior or Next buttons 170 times
and your program will crash.  And so will Paradox and every other BDE program you
have running.

What did Borland say when I reported it?  "Call our 1-900 line for help."  I am
going to pay Borland to let me help them debug a program I just got done paying
them $1700 for?  How nice of me!

I have been able to reproduce it on every machine I have tried (rather, I haven't
been able to not produce it, which really was the effect I was after.)  You can
watch it too: grab HeapWalker or whatever memory leak debugger you like to use and
watch IDQRY01.DLL's private allocations: when they reach 8.5 megs watch what
happens.  Neeto!  This is an especially nifty side effect when your project is
due, and Borland won't return your calls.

This can be demonstrated without a single line of code, just slap down two
TQuery's in a Master-Detail relationship, hook a TDBNavigator up to the master
DataSource, make sure the Detail TQuery is set up with RequestLive = TRUE.  Run.  
Hit the Navigator buttons about 1700 times.  (The preferred demo uses a TTimer to
automate the process, but that would require a single line of code ;-)

It does not happen with TTables or Local-SQL TQuerys.  (But I NEED to use
passthrough TQuerys!)

Disgruntled,

        Brian Badger
        MCI Telecommunications

--
Brian Badger, Registered Telepath; P -3 (I can't even read my own thoughts...)
My opinions are my own, and do not reflect the views of the Psi Cop who
implanted my personality.

2. using mysql in java

3. SQL OLEDB whinge

4. Edit Mode - What am I doing wrong?

5. Major Bug using MEMO fields in Reports

6. Interesting query

7. MAJOR PROBLEM: Saving Recordsets containing adBinary fields in XML format

8. re-re-post: Distributing the Internet Control Pack

9. Major Problem with Form and Update...HELP!!!

10. Attention MS: major bug in SQL 7.0

11. Major performance problem using IIS/ADO/SQL2000

12. Major SQLServer reference site needed

13. Help - major problem