Working With Result Sets Using Stored Procedures

This tutorial describes how to retrieve and modify result sets obtained from stored procedures using the TMyStoredProc component.

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To MySQL" and "Connecting To MySQL Embedded"), how to create necessary objects on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

Besides scalar variables, stored procedures can return result sets, i.e. the results of SELECT statements. Data can be inserted or modified in obtained result sets using the dataset functionality of the TMyStoredProc component.

The goal of this tutorial is to retrieve and modify data from the dept table using the TMyStoredProc component. The following stored procedure will be used to retrieve data:

CREATE PROCEDURE SelectDept()
BEGIN
  SELECT * FROM dept;
END

The Insert, Append, Edit, and Post methods of the TMyStoredProc component can be used to insert and modify data in obtained result sets. DML statements are generated by TMyStoredProc internally. The code below demonstrates using these methods:

[Delphi]

var
  sp: TMyStoredProc;
begin
  sp := TMyStoredProc.Create(nil);
  try
    // con is either TMyConnection or TMyEmbConnection already set up
    sp.Connection := con;

    // choose a stored procedure name
    sp.StoredProcName := 'SelectDept';

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // retrieve data
    sp.Open;

    // append record
    sp.Append;
    sp.FieldByName('deptno').AsInteger := 50;
    sp.FieldByName('dname').AsString := 'SALES';
    sp.FieldByName('loc').AsString := 'NEW YORK';
    sp.Post;

    // insert record
    sp.Insert;
    sp.FieldByName('deptno').AsInteger := 60;
    sp.FieldByName('dname').AsString := 'ACCOUNTING';
    sp.FieldByName('loc').AsString := 'LOS ANGELES';
    sp.Post;

    // to make the record with deptno=10 the current record
    sp.FindKey([10]);

    // modify record
    sp.Edit;
    sp.FieldByName('dname').AsString := 'RESEARCH';
    sp.FieldByName('loc').AsString := 'LOS ANGELES';
    sp.Post;
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMyStoredProc* sp = new TMyStoredProc(NULL);
  try
  {
    // con is either TMyConnection or TMyEmbConnection already set up
    sp->Connection = con;

    // choose a stored procedure name
    sp->StoredProcName = "SelectDept";

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // retrieve data
    sp->Open();

    // append record
    sp->Append();
    sp->FieldByName("deptno")->AsInteger = 50;
    sp->FieldByName("dname")->AsString = "SALES";
    sp->FieldByName("loc")->AsString = "NEW YORK";
    sp->Post();

    // insert record
    sp->Insert();
    sp->FieldByName("deptno")->AsInteger = 60;
    sp->FieldByName("dname")->AsString = "ACCOUNTING";
    sp->FieldByName("loc")->AsString = "LOS ANGELES";
    sp->Post();

    // to make the record with deptno=10 the current record
    sp->FindKey(ARRAYOFCONST((10)));

    // modify record
    sp->Edit();
    sp->FieldByName("dname")->AsString = "RESEARCH";
    sp->FieldByName("loc")->AsString = "LOS ANGELES";
    sp->Post();
  }
  __finally
  {
    sp->Free();
  }
}