Skip to main content
Version: 7.9

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

TypeParameterDescription
StringqueryA query (typically an UPDATE, INSERT, or DELETE) to run as a prepared statement, with placeholders (?) denoting where the arguments go.
Object[]argsA list of arguments. Will be used in order to match each placeholder (?) found in the query.
String[]datasourcesList 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
# 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
# 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"])