system.db.runSFPrepUpdate
This function is used in Python Scripting.
Description
Runs a prepared statement query through the store and forward system and to multiple datasources at the same time. Prepared statements differ from regular queries in that they can use a special placeholder, the question-mark character (?) in the query where any dynamic arguments would go, and then use an array of values to provide real information for those arguments. Make sure that the length of your argument array matches the number of question-mark placeholders in your query. This call should be used for UPDATE, INSERT, and DELETE queries.
This is extremely useful for two purposes:
- This method avoids the problematic technique of concatenating user input inside of a query, which can lead to syntax errors, or worse, a nasty security problem called a SQL injection attack. For example, if you have a user-supplied string that is used in a WHERE clause, you use single-quotes to enclose the string to make the query valid. What happens in the user has a single-quote in their text? Your query will fail. Prepared statements are immune to this problem.
- This is the only way to write an INSERT or UPDATE query that has binary or BLOB data. Using BLOBs can be very handy for storing images or reports in the database, where all clients have access to them.
Client Permission Restrictions
Permission Type: Legacy Database Access
Client access to this scripting function is blocked to users that do not meet the role/zone requirements for the above permission type. This function is unaffected when run in the Gateway scope.
Syntax
system.db.runSFPrepUpdate(query, args, datasources)
Parameters
| Type | Parameter | Description |
|---|---|---|
| String | query | A query (typically an UPDATE, INSERT, or DELETE) to run as a prepared statement, with placeholders (?) denoting where the arguments go. |
| Object[] | args | A list of arguments. Will be used in order to match each placeholder (?) found in the query. |
| String[] | datasources | List of datasources to run the query through. |
Returns
Boolean - Returns true if successfully sent to store-and-forward system.
Scope
All
Code Examples
# Example 1: Run through single datasource
print system.db.runSFPrepUpdate("INSERT INTO recipes (name, sp1, sp2, sp3) VALUES (?,?,?,?)", ['A Name', 1032, 234, 1], datasources=["MySQLDatasource"])
# Example 2: Run through two datasources
print system.db.runSFPrepUpdate("INSERT INTO recipes (name, sp1, sp2, sp3) VALUES (?,?,?,?)", ['A Name', 1032, 234, 1], datasources=["MySQLDatasource", "SQLServerDatasource"])