Stored Procedures with FB_DBStoredProcedureRecordArray
With the help of the function block FB_DBStoredProceduresRecordArray parameters can be declared as INPUT, OUTPUT or INOUT and can be passed over to the stored procedures. So complex SQL-commands can be pre-programmed at the database server and only need to be triggered by the TwinCAT Database Server. The difference to the FB_DBStoredProceduresRecordReturn function block is, you get a number of records with only one function call.
Download "Example with stored procedures" sample9.zip
Used database type | MS SQL (MS SQL Server 2008) |
Compatible database types | MS SQL |
Used function blocks | FB_DBStoredProceduresRecordArray |
Integrated libraries | "TcDatabase.lib", "TcSystem.lib","TcBase.lib","TcStandard.lib" |
Download data list | FB_DBStoredProceduresRecordArray_Sample.pro, CurrentConfigDataBase.xml, SQLQuery2.sql |
The following example shows the call of a stored procedure with an input parameter and a return data set for a customer and production database sample. The procedure was generated at a Microsoft SQL Server 2008.
Code of the 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
Data set 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_IFCASE 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_IFEND_CASE
Visualization
