ExecuteDataReturn

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. A specified number of records can be read.

Syntax

METHOD ExecuteDataReturn : 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; 
    nStartIndex: UDINT;
    nRecordCount: UDINT;
    pReturnData: POINTER TO BYTE;
    cbReturnData: UDINT;
    pRecords: POINTER TO UDINT;
END_VAR

Inputs

Name	Type	Description
hDBID	UDINT	Indicates the ID of the database to be used.
pExpression	POINTER TO BYTE	Address of the string variable with the SQL command
cbExpression	UDINT	Length of the string variable with the SQL command
pData	POINTER TO BYTE	Address of the structure with the parameter values
cbData	UDINT	Length of the structure with the parameter values
pParameter	POINTER TO ARRAY[0..MAX_DBCOLUMNS] OF ST_ExpParameter	Address of the structure array with the parameter information
cbParameter	UDINT	Length of the structure array with the parameter information
nStartIndex	UDINT	Indicates the index of the first record to be read.
nRecordCount	UDINT	Indicates the number of records to be read.
pReturnData	POINTER TO BYTE	Address of the structure array into which the records are to be written.
cbReturnData	UDINT	Indicates the size of the structure array in bytes.
pRecords	POINTER TO BYTE	Number of read records.

Return value

Name	Type	Description
ExecuteDataReturn	BOOL	Displays the status of the method. Returns TRUE as soon as the method execution is finished, even in the event of an error.

	Parameterizing the command The column names for the individual parameters are specified in curly brackets in the SQL command. Sample: ‚SELECT * FROM MyHouse_Temperatures WHERE Room = {SelectedRoom}’. Accordingly, SelectedRoom has to be specified as parameter name in the structure ST_ExpParameter. Some databases do not support the parameterization of SQL clauses. (TOP/LIMIT/ROWNUM/...) Parameterizable table names are not usually supported.

Sample

VAR
    fbPLCDBCmd       : FB_PLCDBCmdEvt(sNetID := '', tTimeout := T#5S);
    sCmd             : STRING (1000); 
    stPara           : ST_ExpParameter;
    RecordAmt        : ULINT := 3; 
    ReturnDataStruct : ARRAY [0..9] OF ST_DataAll; 
    nRecords         : UDINT;
    tcMessage        : I_TcMessage;
END_VAR

// set Parameter configuration
stPara.eParaType := E_ExpParameterType.Int64;
stPara.nParaSize := 8;
stPara.sParaName := 'RecordAmt';

// set command with placeholder
sCmd := 'SELECT TOP ({RecordAmt}) * FROM MyTableName';

// call functionblock
IF fbPLCDBCmd.ExecuteDataReturn(
    hDBID:= 1, 
    pExpression:= ADR(sCmd), 
    cbExpression:= SIZEOF(sCmd), 
    pData:= ADR(RecordAmt), 
    cbData:= SIZEOF(RecordAmt), 
    pParameter:= ADR(stPara), 
    cbParameter:= SIZEOF(stPara),
    nStartIndex:= 0, 
    nRecordCount:= 10, 
    pReturnData:= ADR(ReturnDataStruct), 
    cbReturnData:= SIZEOF(ReturnDataStruct), 
    pRecords:= ADR(nRecords)) 
THEN
    IF fbPLCDBCmd.bError THEN
        tcMessage := fbPLCDBCmd.ipTcResult;
        nState := 255; 
    ELSE
        nState := 0;
    END_IF
END_IF