PgDAC Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TPgQuery and TPgTable components.

1. Requirements
2. General Information
3. Design-Time
4. Runtime
   • 4.1 Using the DataSet Functionality
   • 4.2 Building DML Statements Manually
     • 4.2.1 Parameterized DML Statements
     • 4.2.2 Non-Parameterized DML Statements
5. Additional Information

Requirements

This tutorial assumes that you already know how to connect to the server (see Connecting to PostgreSQL) and that the necessary objects have already been created on the server (see Creating Database Objects). To insert data at runtime, add the PgAccess unit to the uses clause for Delphi or include the PgAccess.hpp header file for C++ Builder.

General Information

You can insert data into a table using the Data Manipulation Language (DML), which is part of SQL. The user must have the appropriate privileges to execute DML statements on the server. There are two ways to manipulate data in a table: you can build DML statements manually and run them with a component like TPgQuery, or you can use the dataset functionality (the Insert, Append, and Post methods) of the TPgQuery and TPgTable components. This tutorial shows you how to insert data into the dept and emp tables.

The dept table definition:

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

The emp table definition:

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	17-12-1980	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-02-1981	1600	300	30
7521	WARD	SALESMAN	7698	22-02-1981	1250	500	30
7566	JONES	MANAGER	7839	02-04-1981	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-09-1981	1250	1400	30
7698	BLAKE	MANAGER	7839	01-05-1981	2850	NULL	30
7782	CLARK	MANAGER	7839	09-06-1981	2450	NULL	10
7788	SCOTT	ANALYST	7566	13-07-1987	3000	NULL	20
7839	KING	PRESIDENT	NULL	17-11-1981	5000	NULL	10
7844	TURNER	SALESMAN	7698	08-09-1981	1500	0	30
7876	ADAMS	CLERK	7788	13-07-1987	1100	NULL	20
7900	JAMES	CLERK	7698	03-12-1981	950	NULL	30
7902	FORD	ANALYST	7566	03-12-1981	3000	NULL	20
7934	MILLER	CLERK	7782	23-01-1982	1300	NULL	10

Inserting at Design-Time

• Find the TPgQuery component in the PgDAC category on the Tool Palette.
• Double-click the component. A new object will appear on the form. If this is the first time that you create a TPgQuery object in this application, the object will be named PgQuery1. Note that the PgQuery1.Connection property is automatically set to an existing connection.
• Double-click the PgQuery1 object.
• Enter the following statement:
  

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

• Click the Execute button to add a new record to the dept table.

Inserting at Runtime

Using the DataSet Functionality

The Insert, Append, and Post methods of the TPgQuery and TPgTable components allow you to insert data without having to manually construct a DML statement — it is generated by PgDAC components internally. Insert adds a new empty record in the current cursor position, while Append adds a new empty record at the end of the dataset. The code below demonstrates the use of these methods:

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1; 
    // adds a statement to retrieve data
    PgQuery1.SQL.Text := 'SELECT * FROM dept';
    // opens the dataset
    PgQuery1.Open;
    // adds a new empty record at the end of the dataset
    PgQuery1.Append;
    // searches fields by their names and assigns new values
    PgQuery1.FieldByName('deptno').AsInteger := 10;
    PgQuery1.FieldByName('dname').AsString := 'ACCOUNTING';
    PgQuery1.FieldByName('loc').AsString := 'NEW YORK';
    // writes the modified record
    PgQuery1.Post;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    // adds a statement to retrieve data
    PgQuery1->SQL->Text = "SELECT * FROM dept";
    // opens the dataset
    PgQuery1->Open();
    // adds a new empty record at the end of the dataset
    PgQuery1->Append();
    // searches fields by their names and assigns new values
    PgQuery1->FieldByName("deptno")->AsInteger = 10;
    PgQuery1->FieldByName("dname")->AsString = "ACCOUNTING";
    PgQuery1->FieldByName("loc")->AsString = "NEW YORK";
    // writes the modified record
    PgQuery1->Post();
}
__finally {
    PgQuery1->Free();
}

Building DML Statements Manually

DML statements can be constucted with or without parameters. The code below demonstrates both ways.

Parameterized DML Statements

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1;
    // adds a parameterized statement to insert data
    PgQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    // searches parameters by their names and assigns new values
    PgQuery1.ParamByName('deptno').AsInteger := 10;
    PgQuery1.ParamByName('dname').AsString := 'ACCOUNTING';
    PgQuery1.ParamByName('loc').AsString := 'NEW YORK';
    // executes the statement
    PgQuery1.Execute;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    // adds a parameterized statement to insert data 
    PgQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
    // searches parameters by their names and assigns new values
    PgQuery1->ParamByName("deptno")->AsInteger = 10;
    PgQuery1->ParamByName("dname")->AsString = "ACCOUNTING";
    PgQuery1->ParamByName("loc")->AsString = "NEW YORK";
    // executes the statement
    PgQuery1->Execute();
}
__finally {
    PgQuery1->Free();
}

Non-Parameterized DML Statements

Delphi

var
  PgQuery1: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1; 
    // adds a statement to insert a record
    PgQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
    // executes the statement
    PgQuery1.Execute;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
    // PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    //adds the statement to insert a record 
    PgQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
    // executes the statement
    PgQuery1->Execute();
}
__finally {
    PgQuery1->Free();
}

Additional Information

There are many ways to insert data into tables. Any tool or component that is capable of running an SQL query can be used to manage data. For example, TPgSQL can be used to insert records one by one, while TPgScript is designed to execute multiple DDL/DML statements as a single SQL script. TPgLoader is the fastest way to insert data into PostgreSQL tables.

It is also possible to use stored procedures to insert data, in which case all data manipulation logic is defined on the server. See Using Stored Procedures for more information.