This tutorial describes how to use OraQuery component to insert data into tables by means of executing SQL queries.
This walkthrough supposes that you know how to connect to server (tutorial Logging onto the server) and that necessary objects are already created on the server (tutorial Creating database objects).
Data on server can be modified (inserted, changed or deleted) using Data Manipulation Language (DML), which is a part of SQL. The DML statements can be executed on server by 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 OraQuery. Another way is to use design-time features that provide graphical user interface to manage database. 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
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 |
To insert the first row into table dept you can use the following statement:
INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK')
The following code fragment executes the query:
[Delphi]
var
OraSession1: TOraSession;
OraQuery1: TOraQuery;
begin
OraSession1 := TOraSession.Create(nil);
OraQuery1:= TOraQuery.Create(nil);
OraSession1.ConnectString := 'SCOTT/TIGER@ORASERVER';
OraQuery1.SQL.Text := 'INSERT INTO dept (deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'')';
OraQuery1.Session := OraSession1;
OraSession1.LoginPrompt := False;
try
OraSession1.Connect;
try
OraQuery1.Execute;
ShowMessage(IntToStr(OraQuery1.RowsAffected)+' rows were affected.');
except
ShowMessage('Error encountered during INSERT operation.');
end;
finally
OraSession1.disconnect;
OraQuery1.Free;
OraSession1.Free;
end;
[C++ Builder]
TOraSession *OraSession1 = new TOraSession(NULL);
TOraQuery *OraQuery1 = new TOraQuery(NULL);
OraSession1->ConnectString = "SCOTT/TIGER@ORCL1020";
OraQuery1->SQL->Text = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK')";
OraQuery1->Session = OraSession1;
OraSession1->LoginPrompt = false;
try
{
OraSession1->Connect();
try
{
OraQuery1->Execute();
ShowMessage(IntToStr(OraQuery1->RowsAffected)+" rows were affected.");
}
catch(const Exception& e)
{
ShowMessage("Error encountered during INSERT operation.");
}
}
__finally
{
OraSession1->Disconnect();
OraQuery1->Free();
OraSession1->Free();
}
The sample first creates a connection with hardcoded connection string. Then it creates OraQuery object, assigns the query text and connection to the OraQuery instance. Connection is opened then. The Execute method of OraQuery runs SQL statement in the Text property. The RowsAffected property stores the number of rows affected by the query. This method is not intended to run SELECT statements. We will discuss retrieving data in other tutorials.
If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally
clauses to make sure the connections are closed properly.
Same operations in design time include following steps:
Place OraSession component on a designer.
INSERT INTO dept VALUES (20,'SALES','DALLAS')
and press the Execute button.
Actually there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, OraLoader is the fastest way to insert data, OraScript is designed for executing series of statements. For more information on these components refer to ODAC reference.