SQL Query Execution Command

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:

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);

Using Parameters in SQL Queries

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:

Introduction