Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: update params syntax
Scripting_function


Panel
titleDescription

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.
Panel
titleClient 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.

Panel
titleSyntax

system.db.runSFPrepUpdate(query, args, datasources)

  • Parameters

String query - A query (typically an UPDATE, INSERT, or DELETE) to run as a prepared statement, with placeholders (?) denoting where the arguments go.

ObjectList[Any] args - A list of arguments. Will be used in order to match each placeholder (?) found in the query.

List[String] datasources - List of datasources to run the query through.

  • Returns

Boolean - Returns true if successfully sent to Store and Forward system.

  • Scope

Gateway, Vision Client, Perspective Session

Panel
titleCode Examples
Code Block
languagepy
titleCode Snippet
# 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"])
Code Block
languagepy
titleCode Snippet
# 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"])


Panel
titleKeywords

system db runSFPrepUpdate, db.runSFPrepUpdate