CODEWITHSUNDEEP
mysqldelphistored-proceduresado
NiMuSi
NiMuSi

Reputation: 412

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;

Upvotes: 0

Views: 202

Answers (2)

NiMuSi
NiMuSi

Reputation: 412

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;

Upvotes: 0

Artem Konstantinov
Artem Konstantinov

Reputation: 11

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

Upvotes: 0

Related Questions