Beckhoff - Build query from results of another query

Question

I want to build a SELECT statement from the results of query within the Twicat3 PLC environment.

Query executes properly when variable is hard coded, but fails to execute when variable is passed from the results of another query. The defined datatypes are the same. Error in InformationLog View indicates "The given key was not present in the dictionary." when the query is executed using the results of the first query.

PROGRAM MAIN
VAR
conv_QueryDB    : Query_tblConveyorQueue;
conv_queryRes   : ARRAY [0..9] OF tblConveyorQueue;
conv_str_query  : STRING(2000);
conv_query      : WSTRING(2000);
conv_query1     : WSTRING(255) := "SELECT TOP 1 LEFT(AreaID,5) FROM ConveyorQueue WHERE CartonID = '";
conv_barcode    : WSTRING(255) := "900981-1";
conv_query2 : WSTRING(255) := "' ORDER BY AreaID";

zone_QueryDB    : Query_tblZoneMap;
zone_queryRes   : ARRAY [0..18] OF tblZoneMap;
zone_str_query  : STRING(2000);
zone_query  : WSTRING(2000);
zone_query1 : WSTRING(255) := "SELECT transfer, zone, direction, neighborZone, deliveryLoc FROM tblZoneMap WHERE transfer = 1 AND zone = 0 AND deliveryLoc = '";
zone_dest       : WSTRING(255);
zone_query2 : WSTRING(255) := "' ";
END_VAR

conv_query := WCONCAT(conv_query1, WCONCAT(conv_barcode, conv_query2));
conv_str_query := WSTRING_TO_STRING(conv_query);
conv_QueryDB(queryString := conv_str_query, dbConn := 2, bConnect := 1);
conv_queryRes := conv_QueryDB.queryResults;

zone_dest := STRING_TO_WSTRING(conv_queryRes[0].AreaID); 
//zone_dest := "P1030"; //This works when uncommented??!!??
zone_query := WCONCAT(zone_query1, WCONCAT(zone_dest, zone_query2));
zone_str_query := WSTRING_TO_STRING(zone_query);
zone_QueryDB(queryString := zone_str_query, dbConn := 1, bConnect := 1);
zone_queryRes := zone_QueryDB.queryResults;

I would expect the zone_queryRes to be the same when executing zone_dest as a hard coded value vs. when it's extracted from the conv_queryRes array. I'm not sure if this is a datatype issue. When executed, the query strings appear to have the correct syntax.

--------------EDIT-----------------------------------------------------------------

Including SQL Function Block.

FUNCTION_BLOCK Query_tblZoneMap
VAR_INPUT 
    queryString     : STRING(2000); //Select statement
    dbConn          : UDINT; //Int of connection configured in TF6420
    bCONNECT        : BOOL; //Rising trigger to connect to db. Need to make this re-fireable

END_VAR
VAR_OUTPUT
    queryResults    : ARRAY [0..18] OF tblZoneMap;
    nError          : INT;

END_VAR
VAR
    fbSQLDatabase   : FB_SQLDatabase(sNetID:='', tTimeout:=T#500MS);
    fbSqlCommand    : FB_SQLCommand(sNetID := '', tTimeout := T#500MS);     
    fbSQLResult     : FB_SQLResult(sNetID:='', tTimeout:=T#500MS);
    nState          : INT;
    R_TRIG1         : R_TRIG;
END_VAR


(*
    Welcome to the QueryDB function block!
    This function exists to query a database that is configured using the TF6420 Connectivity module. 
    When executed, it establishes a connection with the database, creates an instance of the FB_SQLCommand,
    executes the instance of FB_SQLCommand, then reads the cached results using FB_SQLResult into the 
    queryResults array.
    When results are received successfully, the database connection is closed.

*)
R_TRIG1(CLK:=bCONNECT);
IF R_TRIG1.Q AND nState = 0 THEN
    nState := 1;

END_IF
CASE nState OF
    0:(*Idle*)
    IF bConnect THEN
        bCONNECT := 0;
    END_IF  
    1: // Connect to database 
    IF fbSqlDatabase.Connect(dbConn) THEN
        IF fbSqlDatabase.bError THEN
            nState := 255;
        ELSE
            nState := nState+1;
        END_IF
    END_IF  

    2: // Create a command reference
    IF fbSqlDatabase.CreateCmd(ADR(fbSqlCommand)) THEN
        IF fbSqlDatabase.bError THEN
            nState := 255;
        ELSE
            nState := nState+1;
        END_IF
    END_IF

    3://Execute SELECT Statement FB_SQLCommand.ExecuteDataReturn(pSQLCmd:= , cbSQLCmd:= , pSQLDBResult:= )
    IF fbSQLCommand.ExecuteDataReturn(pSQLCmd:= ADR(queryString)
                                    , cbSQLCmd:= SIZEOF(queryString)
                                    , pSQLDBResult:= ADR(fbSqlResult)) THEN
        IF fbSQLCommand.bError THEN
            nState := 255;
            ELSE
            nState := nState + 1;
        END_IF
    END_IF

    4://Read Results 
    IF fbSqlResult.Read(nStartIndex:= 0
                        ,nRecordCount:= 19
                        ,pData:= ADR(queryResults)
                        ,cbData:= SIZEOF(queryResults)
                        ,bWithVerifying:= FALSE
                        ,bDataRelease:= FALSE) THEN
        IF fbSqlResult.bError THEN
            nState := 255;
        ELSE
            nState := nState+1;
        END_IF
    END_IF  
    5:// disconnect from database
    IF fbSqlDatabase.Disconnect() THEN
        IF fbSqlDatabase.bError THEN
            nState := 255;
        ELSE
            nState := 0;
            bConnect := 0;
        END_IF
    END_IF
    255:
        nError  := 1; // If errored, check SQL logs.
        bConnect := 0;
        nState := 0;
END_CASE

Filippo Boido · Answer 1 · 2019-04-12T16:55:56.460

why do you use WSTRING in the first place? Use normal strings so you don't have to convert back and forth and your code is more readable.

If you concat many Strings together, build a function that is capable of it. Something that looks like this when you call it:

F_CONCAT3(
    zone_query1,
    zone_dest,
    zone_query2,
);

I advise you to use the Beckhoff coding convention so that also other developers can read your code:

https://infosys.beckhoff.com/english.php?content=../content/1033/tc3_plc_intro/45035999420423563.html&id=

Your code could then look like this:

sConvQuery := F_CONCAT3(sConvQuery1, sConvBarcode, sConvQuery2);

//Use Enums instead of numbers 
fbQueryDB(queryString := sConvQuery , dbConn := 2, bConnect := TRUE);
aConvQueryRes := fbQueryDB.queryResults;

sZoneDest := aConvQueryRes[0].AreaID; 
sZoneQuery := F_CONCAT3(sZoneQuery1,sZoneDest,sZoneQuery2);

fbQueryDB(queryString := sZoneQuery, dbConn := 1, bConnect := TRUE);
aZoneQueryRes := fbQueryDB.queryResults;

Can you please post the code of the zone_QueryDB Function Block?

Thanks for the reply! Code convention was set by someone else. I dont have control on that one. I used WSTRING to clean up the query string building and include single quotes within the built string ex `"SELECT * FROM tblZoneMap WHERE col = 'sVar' "`. Going to edit main post to include the QueryDB function block. — Ray Crowder, Apr 12 '19 at 16:01
You can use $' for single quotation marks:https://infosys.beckhoff.de/english.php?content=../content/1033/tcplccontrol/html/tcplcctrl_constant_string.htm&id= — Filippo Boido, Apr 12 '19 at 16:57

Filippo Boido · Answer 2 · 2019-04-13T06:15:31.063

I also think you should put your queries in a step sequence to avoid any conflict in the Query_tblZoneMap Function Block:

CASE nStep OF

    eFIRST_QUERY: 

        sConvQuery := F_CONCAT3(sConvQuery1, sConvBarcode, sConvQuery2);
        //Use Enums instead of numbers 
        fbQueryDB(queryString := sConvQuery , dbConn := 2, bConnect := TRUE);
        nStep := eWAIT_FOR_FIRST_RESULT;

    eWAIT_FOR_FIRST_RESULT:     

        IF NOT fbQueryDB.bBusy THEN
            aConvQueryRes := fbQueryDB.queryResults;
            nStep := eSECOND_QUERY;
        END_IF

        //Error handling 
        //ELSIF fbQueryDB.bError ...

    eSECOND_QUERY:

        sZoneDest := aConvQueryRes[0].AreaID; 
        sZoneQuery := F_CONCAT3(sZoneQuery1,sZoneDest,sZoneQuery2);
        fbQueryDB(queryString := sZoneQuery, dbConn := 1, bConnect := TRUE);
        nStep := eWAIT_FOR_SECOND_RESULT;

    eWAIT_FOR_SECOND_RESULT:    

        IF NOT fbQueryDB.bBusy THEN
            aZoneQueryRes := fbQueryDB.queryResults;
            nStep := eCHECK_RESULT;
        END_IF

        //Error handling
        //ELSIF fbQueryDB.bError ...

    eCHECK_RESULT: //...    

END_CASE

This is great advice, thank you. The queries execute consistently. The issue I am struggling with here involves passing the results from one query into another query. I can query a MSSQL table and return a char(20) string ex _'Zone14'_. When I hardcode _'Zone14'_ the second query returns the desired results. When I pass the results from MSSQL to the SQLite query, it fails to return anything. Do you have any thoughts on what the discrepancy is? The variable in question is ```zone_dest```. — Ray Crowder, Apr 12 '19 at 21:45
If you refactored the code as I showed you and are still having issues, then toggle a breakpoint at sZoneQuery := F_CONCAT3(sZoneQuery1,sZoneDest,sZoneQuery2); and take a look at the second query string. — Filippo Boido, Apr 13 '19 at 06:19

Beckhoff - Build query from results of another query

2 Answers2