Mysql Question posted in
The official MySQL documentation can be found here.

insert into mySQL using Delphi ADOStoredProc

I have a mySQL with the following two tables:

person keyed on ID

staff keyed on ID and StaffID (used to link managers and their staff)

The following stored procedure should insert a row into the staff table provided both ID and StaffID exist in the person table

    IF EXISTS (SELECT * FROM person WHERE ID = pStaffID) THEN
      INSERT INTO staff VALUES(pID, pStaffID, pDateOfEffect, pRemarks);
    END IF

The following code in Delphi should do the insert

procedure TfrmStaff.btnOKClick(Sender: TObject);
begin
  if (txtStaffID.Text <> '') then with DataModule1.ADOStoredProc1 do begin
    NullStrictConvert := false;
    ProcedureName:='Insert_Staff';
    Parameters.Clear;
    Parameters.CreateParameter('pID', ftInteger, pdInput, 50, ID);
    Parameters.CreateParameter('pStaffID', ftInteger, pdInput, 50, txtStaffID.Text);
    Parameters.CreateParameter('pDateOfEffect', ftDate, pdInput, 50, txtDateOfEffect.Text);
    Parameters.CreateParameter('pRemarks', ftString, pdInput, 100, txtRemarks.Text);
    ExecProc;
  end;
end;

I am using 43 and 45 as the two IDS, which both exist in the person table. For test purposes I am leaving both txtDateOfEffect and txtRemarks empty.

This fails with the following error message:

check the manual for the right syntax to use near ‘{ call
Insert_Staff(?, ?, ?, ?)} at line 1;

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In the end I've decided to go with an ADOQuery instead of ADOStoredProc, using ExecSQL rather than ExecProc. The following code works as expected (qAdd is an ADOQuery). This allows me to continue using the stored proc on the server.

    procedure TfrmStaff.btnOKClick(Sender: TObject);
begin
  if (txtStaffID.Text <> '') then with DataModule1.qAdd do begin
    Close;
    SQL.Text := 'Call Insert_Spouse(:pID, :pStaffID, :pDateOfEffect, :pRemarks)';
    Parameters.ParseSQL(SQL.Text, true);
    Parameters[0].Value := ID;
    Parameters[1].Value := txtStaffID.Text;
    Parameters[2].Value := 'null';
    Parameters[3].Value := 'null';
    ExecSQL;
  end;
end;

  2. 0

    Try to put TAdoStoredProc on form or on data module, setup connection and proc name and call ExecProc method. If you get success, use parameters’ settings from this component

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search