Execute

This method can be used to send SQL commands to the database. The database connection is opened with each call and then closed again. It is possible to define placeholders in the command, which are replaced by the TwinCAT Database Server with the corresponding values before the execution. Returned records cannot be read.

Syntax

METHOD Execute : BOOL
VAR_INPUT
    hDBID: UDINT;
    pExpression: POINTER TO BYTE;
    cbExpression: UDINT;
    pData: POINTER TO BYTE;
    cbData: UDINT;
    pParameter: POINTER TO ARRAY[0..MAX_DBCOLUMNS] OF ST_ExpParameter;
    cbParameter: UDINT; 
END_VAR

Sample

VAR
    fbPLCDBCmd    : FB_PLCDBCmdEvt(sNetID := '', tTimeout := T#5S);
    sCmd          : STRING (1000); 
    myStruct      : ST_DataAll;
    aPara         : ARRAY[0..14] OF ST_ExpParameter;
    tcMessage     : I_TcMessage;
END_VAR

TYPE ST_DataAll :
STRUCT
    colBigInt: LINT;
    colInteger: DINT;
    colSmallInt: INT;
    colTinyInt: BYTE;
    colBit: BOOL;
    colMoney: LREAL;
    colFloat: LREAL;
    colReal: REAL;
    colDateTime: DT;
    colNText: STRING(255);
    colNChar: STRING(10);
    colImage: ARRAY[0..255] OF BYTE;
    colNVarChar: STRING(50);
    colBinary: ARRAY[0..29] OF BYTE;
    colVarBinary: ARRAY[0..19] OF BYTE;
END_STRUCT
END_TYPE

// set Parameter configuration
aPara[0].sParaName := 'colBigInt';    aPara[0].eParaType := E_ExpParameterType.Int64;      aPara[0].nParaSize := 8;
aPara[1].sParaName := 'colInteger';   aPara[1].eParaType := E_ExpParameterType.Int32;      aPara[1].nParaSize := 4;
aPara[2].sParaName := 'colSmallInt';  aPara[2].eParaType := E_ExpParameterType.Int16;      aPara[2].nParaSize := 2;
aPara[3].sParaName := 'colTinyInt';   aPara[3].eParaType := E_ExpParameterType.Byte_;      aPara[3].nParaSize := 1;
aPara[4].sParaName := 'colBit';       aPara[4].eParaType := E_ExpParameterType.Boolean;    aPara[4].nParaSize := 1;
aPara[5].sParaName := 'colMoney';     aPara[5].eParaType := E_ExpParameterType.Double64;   aPara[5].nParaSize := 8;
aPara[6].sParaName := 'colFloat';     aPara[6].eParaType := E_ExpParameterType.Double64;   aPara[6].nParaSize := 8;
aPara[7].sParaName := 'colReal';      aPara[7].eParaType := E_ExpParameterType.Float32;    aPara[7].nParaSize := 4;
aPara[8].sParaName := 'colDateTime';  aPara[8].eParaType := E_ExpParameterType.DateTime;   aPara[8].nParaSize := 4;
aPara[9].sParaName := 'colNText';     aPara[9].eParaType := E_ExpParameterType.STRING_;    aPara[9].nParaSize := 256;
aPara[10].sParaName:= 'colNChar';     aPara[10].eParaType := E_ExpParameterType.STRING_;   aPara[10].nParaSize := 10;
aPara[11].sParaName:= 'colImage';     aPara[11].eParaType := E_ExpParameterType.ByteArray; aPara[11].nParaSize := 256;
aPara[12].sParaName:= 'colNVarChar';  aPara[12].eParaType := E_ExpParameterType.STRING_;   aPara[12].nParaSize := 50;
aPara[13].sParaName:= 'colBinary';    aPara[13].eParaType := E_ExpParameterType.ByteArray; aPara[13].nParaSize := 30;
aPara[14].sParaName:= 'colVarBinary'; aPara[14].eParaType := E_ExpParameterType.ByteArray; aPara[14].nParaSize := 20;

// set command
sCmd := 'INSERT INTO MyTableName (colInteger, colSmallInt, colTinyInt, colBit, colMoney, colFloat, colReal, colDateTime, colNText, colNChar, colImage, colNVarChar, colBinary, colVarBinary) VALUES ({colInteger}, {colSmallInt}, {colTinyInt}, {colBit}, {colMoney}, {colFloat}, {colReal}, {colDateTime}, {colNText}, {colNChar}, {colImage}, {colNVarChar}, {colBinary}, {colVarBinary})';

// call functionblock
IF fbPLCDBCmd.Execute(
    hDBID:= 1, 
    pExpression:= ADR(sCmd), 
    cbExpression:= SIZEOF(sCmd), 
    pData:= ADR(myStruct), 
    cbData:= SIZEOF(myStruct), 
    pParameter:= ADR(aPara), 
    cbParameter:= SIZEOF(aPara)) 
THEN
    IF fbPLCDBCmd.bError THEN
        tcMessage := fbPLCDBCmd.ipTcResult;
        nState := 255; 
    ELSE
        nState := 0;
    END_IF
END_IF