Hi,

I have this table:

create table Table1
(
   ID  int  not null AUTO_INCREMENT,
   Data    varchar(150),
   primary key (ID)
);

1>If I use the SQLClientDataSet I must everytime specify the value for ID
field
even I set the Required property of this persistent field to false. Why?

2>If I specify the value for ID Field as 0 (due to 1st problem solving) i
never get the correct value
from database which was generated as auto_increment. In DB is the correct
value :-(.
I set the Options of the SQLClientDataSet to AutoRefresh=true.
The persistent field ID has property AutoGenerateValue set to arAutoInc or
arDefault.

3> I tried to set the value ID field in AfterUpdateRecord event. But it is
good
but not when i want to insert more than 1 row befor Apply updates. in this
case
raises error 'Key violation'. I thing I need to solve the 1st problem (with
required property).

I'm using Delphi 6, mySQL 3.22 (running on MS 2000 Server)

Can anybody help me? Some Ideas how should I get around this problems?

thanx
Jiri

Hi Jiri

I've experienced this as well, but have a workaround that requires a
little bit more work.

1. Use persistent fields. Change the auto increment field to a plain old
integer.
2. Create following stored procedure

create procedure Table1Insert (
   @Id int output,
   @Data varchar(150)
)
as
begin
   insert into Table1 (Data) values (@Data)

   set @Id = scope_identity()
end
go

3. Add TSQLStoredProcedure for inserting (say stpTableInsert). Point it
at Table1Insert, don't forget to set the @Id parameter to ptOutput
4. In BeforeUpdateRecord event for provider add code

if UpdateKind = ukInsert then
begin
   with stpTableInsert do
   begin
     Params.ParamByName('@Data').Value :=
       DeltaDS.FieldByName('@Data').NewValue;
     Execute;
     DeltaDS.FieldByName('Id').NewValue :=
       Params.ParamByName('@Id').Value;
   end;
   Applied := true;
end;

5. Include poPropogateChanges in the Options for the SQLClientDataSet

HTH
Regards
Clint.

--
insert puter between com & fleet in my address.

The original request sais:

Are you sure that MySQL supports STORED PROCEDURES?

Best regards,
Patricio.

D'oh. Standard MYSQL/MSSQL confusion.