Stored procedures with FB_DBStoredProceduresRecordArray

The function block FB_DBStoredProceduresRecordArray can be used to declare parameters as INPUT, OUTPUT or INOUT and transfer them to the stored procedures. In this way complex SQL commands can be preprogrammed in the database server and then triggered by the TwinCAT Database Server. In contrast to the function block FB_DBStoredProceduresRecordReturn, this function block can be used to return several records with a single call.

Download: TcDBSrv_InfoSysSamples.zip

Stored procedures with FB_DBStoredProceduresRecordArray 1:

Database type used

MS SQL (MS SQL Server 2008)

Compatible database types

MS SQL, MySQL, Oracle

Function blocks used

FB_DBStoredProceduresRecordArray

Libraries to be integrated

Tc2_Database, Tc2_System, Tc2_Base, Tc2_Utilities

Download file list

TcDBSrv_InfoSysSamples.tszip, CurrentConfigDataBase.xml

The following sample illustrates the call in a simple stored procedure with an input parameter and return record. The procedure was created on a Microsoft SQL Server 2008.

Code der Stored Procedure SP_GetAddressByCustomerID

CREATE PROCEDURE [SP_GetAddressByCustomerID] 
    @Customer_ID bigint
AS
BEGIN
    SELECT tbl_Customer.ID, tbl_Customer.Name, tbl_Customer.Customer, tbl_Products.SerNum, 
     tbl_Products.Product, tbl_Products.Info, tbl_Pos.Timestamp 
    FROM 
        tbl_Pos JOIN tbl_Customer ON tbl_Pos.CustomerNum = tbl_Customer.ID 
        JOIN tbl_Products ON tbl_Pos.ProductNum = tbl_Products.SerNum
    WHERE
        tbl_Pos.CustomerNum = @Customer_ID;
END

Variable declaration in the PLC

PROGRAM MAIN
VAR
    R_TRIG1          : R_TRIG;
    bREAD            : BOOL := FALSE;

    nState           : BYTE;

    arrParaList      : ARRAY [0..0] OF ST_DBParameter;

    nCustomerID      : DINT := 12345;

    FB_DBStoredProceduresRecordArray1: FB_DBStoredProceduresRecordArray;

    nCustomerID: DINT:= 12345;
    nRecordStartIndex: UDINT;
    stRecordArr      : ARRAY [1..25] OF ST_Record;
    nRecs            : UDINT;

    bBusy            : BOOL;
    bErr             : BOOL;
    nErrid           : UDINT;
    stSqlstate       : ST_DBSQLError;
END_VAR 

Record structure in the PLC (ST_Record)

TYPE ST_Record :
STRUCT
    nID         : T_ULARGE_INTEGER;
    sCustomer   : STRING(50);
    sName       : STRING(50);
    nProductNum : DINT;
    sProductName: STRING(50);
    sProductInfo: T_MaxString;
    tTimestamp  : DT;
END_STRUCT
END_TYPE

PLC program

R_TRIG1(CLK:=bREAD);
IF R_TRIG1.Q AND NOT bBusy THEN
    nState := 1;
END_IF

CASE nState OF
 0:
     ;
 1:(*Init of the parameters*)
     arrParaList[0].sParameterName    := '@Customer_ID';
     arrParaList[0].eParameterDataType:= eDBColumn_Integer;
     arrParaList[0].eParameterType    := eDBParameter_Input;
     arrParaList[0].cbParameterValue  := SIZEOF(nCustomerID);
     arrParaList[0].pParameterValue   := ADR(nCustomerID);

     nState := 2;
 2:(*Start the stored procedure "SP_GetCustomerPosition"*)
     FB_DBStoredProceduresRecordArray1(
         sNetID:= ,
         hDBID:= 1,
         sProcedureName   := 'SP_GetCustomerPositions',
         cbParameterList  := SIZEOF(arrParaList),
         pParameterList   := ADR(arrParaList),
         nStartIndex      := nRecordStartIndex,
         nRecordCount     := 25,
         cbRecordArraySize:= SIZEOF(stRecordArr),
         pDestAddr        := ADR(stRecordArr),
         bExecute         := TRUE,
         tTimeout         := T#15s,
         bBusy             => bBusy,
         bError            => bErr,
         nErrID            => nErrid,
         sSQLState         => stSqlstate,
         nRecords          => nRecs);
        
     IF NOT bBusy THEN
         FB_DBStoredProceduresRecordReturn1(bExecute:= FALSE);
         nState := 0;
     END_IF
END_CASE

Visualization

Stored procedures with FB_DBStoredProceduresRecordArray 2:

Requirements

Development environment

Target platform

PLC libraries to be linked

TwinCAT v3.0.0

PC or CX (x86)

Tc2_Database