Bug TMySCRIPT & Params (v6.10.0.7) ?

swierzbicki · Post by **swierzbicki** » Fri 03 Jun 2011 14:57

Hello,

Looks like a bug in TMyScript.
I'm not able to use parameters like in TMyQuery component !

Here is my SQL Script

Code: Select all

Update
ClientsSIA
Set
IdVRP = :prIdVRP,
IdVRPInitial = If(IfNull(IdVRPInitial,'') = '', :prIdVRP, IdVRPInitial)
Where
IdClientSIA in
(&ListeClients)

This code gives an error out of index (when calling Params[0] or Params[1] :

Code: Select all

    scMAJIdVRPClientSIA.Connection.StartTransaction;
    try

    scMAJIdVRPClientSIA.Params[0].AsString :=   qrVRP.FieldByName('IdVRP').AsString;
    scMAJIdVRPClientSIA.Params[1].AsString :=   qrVRP.FieldByName('IdVRP').AsString;
    scMAJIdVRPClientSIA.Macros.MacroByName('ListeClients').Value := ListeClients;
    scMAJIdVRPClientSIA.Execute;
    scMAJIdVRPClientSIA.Connection.commit;
    except
    scMAJIdVRPClientSIA.Connection.Rollback;
    raise;
    end;

This code gives an error out unknown parameter (when calling ParambyName(pridvrp).Astring :

Code: Select all

    scMAJIdVRPClientSIA.Connection.StartTransaction;
    try

    scMAJIdVRPClientSIA.Params.ParambyName('prIdVRP').AsString :=   qrVRP.FieldByName('IdVRP').AsString;
    scMAJIdVRPClientSIA.Macros.MacroByName('ListeClients').Value := ListeClients;
    scMAJIdVRPClientSIA.Execute;
    scMAJIdVRPClientSIA.Connection.commit;
    except
    scMAJIdVRPClientSIA.Connection.Rollback;
    raise;
    end;

[/code]

AndreyZ · Post by **AndreyZ** » Mon 06 Jun 2011 08:05

Hello,

If you want to use parameters in the TMyScript component, you should create them dynamically. Here is an example:

Code: Select all

scMAJIdVRPClientSIA.Params.CreateParam(ftString, 'prIdVRP', ptInput);
scMAJIdVRPClientSIA.Params[0].AsString := qrVRP.FieldByName('IdVRP').AsString;

swierzbicki · Post by **swierzbicki** » Mon 06 Jun 2011 14:21

thank you for your answer.
Is their a raison to do this by code ? Isn't it possible to make the TMyScript component behave like a TMyquery component (Macros already works the same way) ?

BR

AndreyZ · Post by **AndreyZ** » Tue 07 Jun 2011 08:43

We didn't implement automatic parameters creating in the TMyScript component, because it can contain a lot of SQL code where several queries can contain the same parameter name. It means that you will not be able to set the value only for one of such parameters. However, you can avoid creating parameters manually by using the TMyScript.DataSet property. Here is a code example:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
  scMAJIdVRPClientSIA.DataSet := MyQuery1;
  scMAJIdVRPClientSIA.SQL.Text := 'Update ClientsSIA Set IdVRP = :prIdVRP, IdVRPInitial = If(IfNull(IdVRPInitial,'') = '', :prIdVRP, IdVRPInitial) Where IdClientSIA in (&ListeClients)';
  scMAJIdVRPClientSIA.Connection.StartTransaction; 
  try 
    scMAJIdVRPClientSIA.Macros.MacroByName('ListeClients').Value := ListeClients; 
    scMAJIdVRPClientSIA.Execute; 
    scMAJIdVRPClientSIA.Connection.commit; 
  except 
    scMAJIdVRPClientSIA.Connection.Rollback; 
    raise; 
  end;
end;

procedure TMainForm.MyQuery1BeforeExecute(Sender: TObject);
begin
  MyQuery1.Params[0].AsString := qrVRP.FieldByName('IdVRP').AsString; 
  MyQuery1.Params[1].AsString := qrVRP.FieldByName('IdVRP').AsString;
end;