Multiple Parameters in an SQL Command in Logic

[sbeadle1]

📖 Home  Back  
To enter multiple parameters in an SQL command within a Logic program, you need to use the ? character to represent the value of each parameter, and the WITH_PARAMS keyword to define the parameters.

The order of the parameters that you define after the WITH_PARAMS keyword corresponds to the order of the ? characters defined in the SQL query. You can repeat the parameters so that the same parameter can be used multiple times in the SQL.

When entering multiple parameters in an SQL command in Logic, you need to use the ? character to represent each parameter value in the query. The WITH_PARAMS keyword must be used in the RESULTSET and each parameter listed after the WITH_PARAMS keyword must correspond to the name of one of the Logic program's VAR inputs. You should separate the parameters after the WITH_PARAMS keyword with commas.

Example

In this example of an ST Program, the parameters for the SQL query are defined as VAR_INPUTs with the names objName and objFullScale. In the SQL query, the ? character is used to represent the parameters. Note that DATABASE_OBJECT is used to define the database table (in this case, CPointAlg) and the type of value for the properties defined in the SQL query (Name and FullScale).

TYPE  Point: DATABASE_OBJECT (CPointAlg)    Name:STRING;    FullScale: LREAL;  END_DATABASE_OBJECT;END_TYPEPROGRAM NameAndFullScale  VAR_INPUT    objName : STRING;    objFullScale: LREAL;  END_VAR  VAR    objList AT %S(SELECT Id, Name, FullScale FROM CPointAlg WHERE Name LIKE ? || '%' AND FullScale > ?) : RESULTSET OF Point WITH_PARAMS objName, objFullScale;  END_VAR;END_PROGRAM

So, the SQL query in the ST Program will return the Id, Name, and FullScale values from the CPointAlg database table. It will only return the values for those items that have a Name value that is like the value of the first parameter followed by any amount of characters and has a FullScale value that is greater than the value of the second parameter. The first parameter is the objName parameter as this is the first parameter listed after the WITH_PARAMS keyword. The objFullScale parameter is the second parameter as it is listed second after the WITH_PARAMS keyword. So in the SQL query, the first ? character corresponds to the objName parameter and the second ? character corresponds to the objFullScale parameter.

If the SQL query was to contain an additional ? character, an additional parameter would be required in the list of parameters after the WITH_PARAMS keyword. The additional parameter in the list could be a new parameter or could be one of the existing parameters used again, for example, it could be another use of the objFullScale parameter.

You can use a DATE_AND_TIME as a parameter, however unlike normally in the SQL you leave out the TIMESTAMP keyword as it is automatically dealt with.

Go: Home Back