MyDAC Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TMyQuery and TMyTable components.

1. Requirements
2. General information
3. Design Time
4. Run Time
   • 4.1 Using DataSet Functionality
   • 4.2 Building DML Statements Manually
     • 4.2.1 DML Statements With Parameters
     • 4.2.2 DML Statements As Plain Text
5. Additional Information

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To MySQL" and "Connecting To MySQL Embedded") and that necessary objects are already created on the server (tutorial "Creating Database Objects").

General information

Data on server can be inserted using Data Manipulation Language (DML), which is a part of SQL. DML statements can be executed on server by an account that has necessary privileges. There are two ways to manipulate a database. You can build DML statements manually and run them within some component like TMyQuery. Another way is to use the dataset functionality (the Insert, Append, and Post methods) of the TMyQuery and TMyTable components. We will discuss both ways.

The goal of this tutorial is to insert the following data into tables dept and emp:
Table dept

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

Table emp

ename	job	mgr	hiredate	sal	comm	deptno
SMITH	CLERK	7902	17.12.1980	800	NULL	20
ALLEN	SALESMAN	7698	20.02.1981	1600	300	30
WARD	SALESMAN	7698	22.02.1981	1250	500	30
JONES	MANAGER	7839	02.04.1981	2975	NULL	20
MARTIN	SALESMAN	7698	28.09.1981	1250	1400	30
BLAKE	MANAGER	7839	01.05.1981	2850	NULL	30
CLARK	MANAGER	7839	09.06.1981	2450	NULL	10
SCOTT	ANALYST	7566	13.07.1987	3000	NULL	20
KING	PRESIDENT	NULL	17.11.1981	5000	NULL	10
TURNER	SALESMAN	7698	08.09.1981	1500	0	30
ADAMS	CLERK	7788	13.07.1987	1100	NULL	20
JAMES	CLERK	7698	03.12.1981	950	NULL	30
FORD	ANALYST	7566	03.12.1981	3000	NULL	20
MILLER	CLERK	7782	23.01.1982	1300	NULL	10

Note: The empno field of the emp table is an IDENTITY(1,1) (i.e. autoincrement) field, so its value is filled automatically by the server.

Design time

• Open the Component palette and find the TMyQuery component in the MyDAC category.
• Double-click on the component. Note that a new object appears on the form. If this is the first time you create TMyQuery in this application, it is named MSQuery1. Note that the MSQuery1.Connection property is already set to an existent (on the form) connection.
• Double-click on the MSQuery1 object.
• Type the following lines:
  

  INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

• Press the Execute button.

Performing these steps adds a new record to the dept table.

Run time

Using DataSet Functionality

The Insert, Append, and Post methods of the TMyQuery and TMyTable components allow inserting data not using DML statements. DML statements are generated by MyDAC components internally. The difference between the Append and Insert methods is that Append creates a new empty record in the end of a dataset, when Insert creates it in the position of the current record of a dataset. The code below demonstrates using these methods:

[Delphi]

var
  q: TMyQuery;
begin
  q := TMyQuery.Create(nil);
  try
    q.Connection := con; // con is either TMyConnection or TMyEmbConnection already set up
    q.SQL.Text := 'SELECT * FROM dept';
    q.Open;
    q.Append;
    q.FieldByName('deptno').AsInteger := 10;
    q.FieldByName('dname').AsString := 'ACCOUNTING';
    q.FieldByName('loc').AsString := 'NEW YORK';
    q.Post;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMyQuery* q = new TMyQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMyConnection or TMyEmbConnection already set up
    q->SQL->Text = "SELECT * FROM dept";
    q->Open();
    q->Append();
    q->FieldByName("deptno")->AsInteger = 10;
    q->FieldByName("dname")->AsString = "ACCOUNTING";
    q->FieldByName("loc")->AsString = "NEW YORK";
    q->Post();
  }
  __finally
  {
    q->Free();
  }
}

Building DML Statements Manually

DML Statements can contain plain text and text with parameters. This section describes both ways.

DML Statements With Parameters

[Delphi]

var
  q: TMyQuery;
begin
  q := TMyQuery.Create(nil);
  try
    q.Connection := con; // con is either TMyConnection or TMyEmbConnection already set up
    q.SQL.Clear;
    q.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    q.ParamByName('deptno').AsInteger := 10;
    q.ParamByName('dname').AsString := 'ACCOUNTING';
    q.ParamByName('loc').AsString := 'NEW YORK';
    q.Execute;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMyQuery* q = new TMyQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMyConnection or TMyEmbConnection already set up
    q->SQL->Clear();
    q->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
    q->ParamByName("deptno")->AsInteger = 10;
    q->ParamByName("dname")->AsString = "ACCOUNTING";
    q->ParamByName("loc")->AsString = "NEW YORK";
    q->Execute();
  }
  __finally
  {
    q->Free();
  }
}

DML Statements As Plain Text

[Delphi]

var
  q: TMyQuery;
begin
  q := TMyQuery.Create(nil);
  try
    q.Connection := con; // con is either TMyConnection or TMyEmbConnection already set up
    q.SQL.Clear;
    q.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
    q.Execute;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMyQuery* q = new TMyQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMyConnection or TMyEmbConnection already set up
    q->SQL->Clear();
    q->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
    q->Execute();
  }
  __finally
  {
    q->Free();
  }
}

Additional Information

Actually, there are lots of ways to insert data into tables. Any tool or component capable of running a SQL query can be used to manage data. Some components are better for performing certain tasks. For example, TMyLoader is the fastest way to insert data, TMyScript is designed for executing series of statements one by one.

It is also possible to use stored procedures for inserting data. In this case, all data manipulation logic is defined on the server. You can find more about using stored procedures in the tutorial "Stored Procedures".