With the connection to the DB server, the user can work with objects and its data. To execute any action, the connected user must have the appropriate permissions. To work with DB objects and control server transactions, a number of properties and methods are implemented in the IDalConnection interface. To work with the database using SQL queries, a command is used that is described in the IDalCommand interface. To create a command, use the IDalConnection.CreateCommand method. In the IDalCommand.SQL property of the command, specify the SQL query to be executed. The query is executed on calling the IDalCommand.Execute, IDalCommand.ExecuteWithoutLast or IDalCommand.CreateCursor methods. Depending on which actions the SQL query executes, the following options are available:
SQL Query Extracts Data. To work with the obtained data, use cursor. To execute the command and create a cursor, use the IDalCommand.CreateCursor method.
SQL Query Changes Data. The IDalCommand.Execute or IDalCommand.ExecuteWithoutLast method returns the number of changed records.
Var
//...
ORCLConnect: IDalConnection;
Command: IDalCommand;
Cursor: IDalCursor;
//...
Begin
//...
Command := ORCLConnect.CreateCommand;
//Extract data
Command.SQL := "select * from Table1";
//Cursor for working with obtained data
Cursor := Command.CreateCursor;
While Not Cursor.Eof Do
//...
Cursor.Next;
End While;
Cursor.Close;
//Change data
Command.SQL := "insert into Table1(Code) values('123')";
i := Command.Execute;
Command.Close;
Debug.WriteLine("Records added: " + i.ToString);
To reuse the command with various sets of values, the user can specify parameters in the SQL query. The parameters have the following syntax: :<Parameter name>. To check the SQL query for parameters, call the IDalCommand.Parse method. After checking the parameters collection is available in the IDalCommand.Params property. For each parameter in this collection a value can be specified to be substituted in the SQL query on executing the command.
Var
//...
ORCLConnect: IDalConnection;
Command: IDalCommand;
//...
Begin
//...
Command := ORCLConnect.CreateCommand;
//Change data
Command.SQL := "insert into Table1(Code) values(:CodeValue)";
// SQL query analysis
Command.Parse;
//Specify found parameter value
Command.Params.Item(0).Value := 124;
//Execute command
i := Command.Execute;
Command.Close;
Debug.WriteLine("Records added: " + i.ToString);
Several sets of parameter values can be specified in the command. Specify the number of sets in the IDalCommand.MaxParamsRows property.
NOTE. Before the IDalCommand.MaxParamsRows property value is specified, specify the data type for each parameter.
Parameter values are also specified in the IDalCommand.Params collection. To move to the next set of parameters, use the IDalCommand.NextParamsRow method.
Var
//...
ORCLConnect: IDalConnection;
Command: IDalCommand;
//...
Begin
//...
Command := ORCLConnect.CreateCommand;
//Change data
Command.SQL := "insert into Table1(Code) values(:ParamArray)";
SQL query analysis
Command.Parse;
//Specify collection of parameter values
Param := Command.Params.Item(0);
Param.DataType := DbDataType.Integer;
Command.MaxParamsRows := 3;
//First value
Param.Value := 125;
Command.NextParamsRow;
//Second value
Param.Value := 126;
Command.NextParamsRow;
//Third value
Param.Value := 127;
//Execute command
i := Command.Execute;
Command.Close;
Debug.WriteLine("Records added: " + i.ToString);
See also: