Board index » off-topic » Unable to delete record with primary key

2005-07-01 10:10:16 AM
off-topic7
Hi,
I am trying to develop a master detail datasnap application over a LAN using
Delphi 2005 professional, mysql via SOAP server. I have followed the
tutorial by Bob Swart (SOAP and Database Web Services) at
www-106.ibm.com/developerworks/db2/library/techarticle/0212swart1/.... It
has been very helpful and I was able to test deploy it. (it was successful)
However my master table has a ID which is also a primary key. Unfortunately
when I tried to remove a record from the client dbgrid, I keep getting Field
value required error. I noticed that this error persisted despite removing
the primary key but with the not null still on. Only when the ID was set at
null at mysql then the delete record is allowed. I have tried to change
farequired to false but to no effect. Any work around. Thanks in advance.
Abu
 

abu zar wrote:
DataSetProvider.DataSet.FieldByName('ID).ProviderFlags has pfRequired
and there is no way to override this in client side (business rules are
by design defined in app server). See QC 5557.
Is the ID field declared as a TAutoInc field (in MYSQL)? If that is the
case then you should use Cds.OnNewRecord to assign this value a unique
(negative) value to this field. See
bdn.borland.com/article/0,1410,20847,00.html
for how to use AutoInc fields with Datasnap.
I would also suggest to download the attatchment from QC 5603 written by
Dave Rowntree, that is an excellent example showing how PK and FK are to
be used with Datasnap (you should also be aware of the bug that Dave
reports in that case).
 

Sorry for the double posting. First time posting in google. Didn't
realise it takes 3 to 9 hours to appear. Please bear with me, I am new
to progamming, trying a DIY.
No, I did not use an autoinc. I have created a mysql database as
below
Master table: field A (not null) primary key, B, C, D, E.
Detail table 1: field A (not null), F, G, H, I.
Detail table 2: field A (not null), J, K, L, M.
..
#Field A are alphanumeric character ID inserted at the client level.
I wished to create a client that can update my database through a
network. As I had only purchased Delphi 2005 professional I could not
use datasnap server (Datasnap data module not included) but I could
get around using web based SOAP connection.
From the creation of the SOAP server, SOAP data module, till SOAP
Client. I have followed the tutorial and nested the DETAIL dataset
into the MASTER dataset and it worked perfectly (!after several bumpy
detour). I have 4 DBGrids which displayed the corresponding tables,
with a master detail relationship taking A as the link. I did not
have any problem at inserting new records in all 4 dbgrid. It updated
well. My problem begins when I tried to remove a record. I was unable
to remove records even at the DETAIL datagrid. When it comes to Field
A, after emptying the column, at update I get 'Field Value Required'
error. I notice the only way I can get around this error is to allow
NULL in the MySQL database Field A. If I could not empty my DETAIL
dataset, I suppose it would be harder for me to empty my MASTER
dataset. This would also mean in removing the primary key, which is
not an intelligent thing to do.
I suppose this is a very fundamental feature. There should surely be
a workaround to this.
As you have pointed out QC5557 the following were one of the excerpt.
<<
By default, MIDAS attempts to ensure the NOT NULL constraint is
enforce at the client. Sounds reasonable to me. As you rightly say,
you can overide this behaviour if you want to and not enforce this at
the client by setting the TField.Required flag to false on the
DSP.DataSet. The choice is yours. You can then test this field in the
DSP.BeforeUpdateRecord or DSP.OnUpdateData event and provide a value
in the DeltaDS if you want to prior to updating the DB. This provided
TField value can optionally be passed back to the CDS using the
DSP.Option poPropogateChanges.
located at the server application. 'TField.Required flag' I have
expanded all properties in the object properties for my
datasetprovider and could not find any flag for specific datafield.
Did I miss anything. The site that I could locate inregards for option
to field properties is at the cds at the client side. I don't suppose
this was what Dave meant.
I have downloaded QC5603, sorry I have ended up more confused. By the
way it also ended with similar error messages when I tried to remove
the ID's.
Thanks in advance.
 

{smallsort}

I have changed the TFields for persistent field 'A' in the SQLdataset
required property to false as well as the TField for field 'A' at the
cds in the client. Now when trying to delete the detail records I no
longer get 'Field value required' error but 'No corresponding master
record found' error instead. Any help? Thanks in advance.
 

'No corresponding master record found'
There were several discussion of this error in previous post.
Apparently I don't get this error when inserting records either in the
master or detail dbgrid. Both my primary key and foreign key have the
same name and description. I suppose that when I removed the
autogenerated ID in the detail dbgrid and updates the record master
record would not have a corresponding ID with null value to accomodate
for this change. I suppose I have to do some coding at
beforeapplyupdate, perhaps some sqlinstructions to the table to force
the removal of the record from the detail table when only the foreign
key has a value. How do I code for this. Any help would be greatly
appreciated. Thanks in advance.