Board index » delphi » Delphi's DB Engine and Server Data Validation

Delphi's DB Engine and Server Data Validation

It seems that Delphi (or Borland's Data Engine) is a bit too smart for
its own good.

I created a database using personal InterBase and one table is giving
me some problems (on the Delphi side). Below is my problem:

1. I used the attached SQL script to create a database on my local
interbase server. It's purpose was to play with declarative
referential integrity and triggers, specifically the automatic
generation of unique primary keys, as was mentioned in the manuals.

2. After running the script, I added a couple of records to the
TypeCat table using ISQL INSERT statements.

3. I then used ISQL once again to add records to the ChkBook table.

4. Up to this point, I had experience NO problems whatsoever.

---- From this point forward I am using Delphi (not ISQL)
5. Now, I wanted to use a grid control to enter data into the table,
so added a database object, a table object, a dataset object, a grid
object and a navigator object, all bound to the objects you expect
them to be bound to. I don't believe ANY code was added to any of
these controls except the necessary connection parameters for the
database object. (The table object was bound to ChkBook)

6. The only REAL customization I did, was that I went in and modified
the columns formatting in the Columns Editor for the Grid. I wanted to
hide the TransIDVal column.
6. Run the form, right? No problem running the form. I tried to add a
record. Given that I had a trigger on the server which would fill in
the NULL value for the primary key, I assumed that the transaction
would go through just fine.

7. It didn't. It seems that the BDE or Delphi itself is being TOO
intelligent, but not quite intelligent enough, in that it sees the
declared referential integrity and validates it before it sends the
data to the server. It forgets that a TRIGGER might be filling in the
disallowed NULL value for me.

8. How do I get around this?

I've attached a uuencoded zip-file containing my SQL script and the
project which I was playing with. There doesn't seem to be any
true-to-life documentation of this behavior of Delphi or the BDE
anywhere in what I currently have.

QUESTION #2:

I discovered by chance that the ||-operator in iSQL means concatenation; however, the InterBase documentation
seems to be pretty lacking in documentation of operators and allowed operations. Can you point me to an
appropriate reference?

--- Begin attachment of silly play code.
table
`!"#$%&'()*+,-./0123456789:;<=>?
@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_
begin 600 DPFIN.ZIP
<uuencoded_portion_removed>
<<F<N9&-U4$L%!@`````/``\`L@,``+$F````````
`
end
size 10873
--
Gregory Deatz                         e-mail: de...@cs.rutgers.edu
Computer Science Department            phone: (908) 932-0050
Rutgers University                    office: CoRE 340
Hill Center
Piscataway, NJ 08855

 

Re:Delphi's DB Engine and Server Data Validation


In article <4p4phr$...@aramis.rutgers.edu>
           de...@aramis.rutgers.edu "Gregory Deatz" writes:

Quote

>It seems that Delphi (or Borland's Data Engine) is a bit too smart for
>its own good.

[snip]

Quote
>6. Run the form, right? No problem running the form. I tried to add a
>record. Given that I had a trigger on the server which would fill in
>the NULL value for the primary key, I assumed that the transaction
>would go through just fine.

>7. It didn't. It seems that the BDE or Delphi itself is being TOO
>intelligent, but not quite intelligent enough, in that it sees the
>declared referential integrity and validates it before it sends the
>data to the server. It forgets that a TRIGGER might be filling in the
>disallowed NULL value for me.

>8. How do I get around this?

Thats a fair description of Delphi's actions. Each TField object has
a Required property which defaults to True by default for these fields.
It should be set to false, and then Delphi will allow the record to
be posted. In order to bring the value back into Delphi, you then need
to do a TTable.Refresh.

Quote

>I've attached a uuencoded zip-file containing my SQL script and the
>project which I was playing with. There doesn't seem to be any
>true-to-life documentation of this behavior of Delphi or the BDE
>anywhere in what I currently have.

>QUESTION #2:

>I discovered by chance that the ||-operator in iSQL means concatenation;
> however, the InterBase documentation
>seems to be pretty lacking in documentation of operators and allowed
> operations. Can you point me to an
>appropriate reference?

Most of it seems to be in the Manual or the Help files, it's very
difficult to find though.

--
Donald Oddy
Grove Systems Ltd.                                    0161-224 4465

Re:Delphi's DB Engine and Server Data Validation


Quote
>>6. Run the form, right? No problem running the form. I tried to add a
>>record. Given that I had a trigger on the server which would fill in
>>the NULL value for the primary key, I assumed that the transaction
>>would go through just fine.

>>7. It didn't. It seems that the BDE or Delphi itself is being TOO
>>intelligent, but not quite intelligent enough, in that it sees the
>>declared referential integrity and validates it before it sends the
>>data to the server. It forgets that a TRIGGER might be filling in the
>>disallowed NULL value for me.

>>8. How do I get around this?
>Thats a fair description of Delphi's actions. Each TField object has
>a Required property which defaults to True by default for these fields.
>It should be set to false, and then Delphi will allow the record to
>be posted. In order to bring the value back into Delphi, you then need
>to do a TTable.Refresh.

A TField object is created automatically for each field at run time if
you don't create any yourself.  In this case you can't control the
"Required" property.  To resolve this, you must go into the field
editor and add the field objects yourself and on fields that are set
by your trigger change the required property to false.

With regard to TTable.Refresh, if your trigger changes the key value
(as is done with a blind key), TTable.Refresh will fail to locate the
correct row reliably.  This is most noticable with multiple users.

Wade

Other Threads