sqlCommit
Used to explicitly write pending changes to the database. This does not reset the statement count for the CommitFrequency. Once this statement is executed, the changes can no longer be rolled back.
Only used in dbExecute nodes. Shares a single session with all sqlSelect and sqlNonSelect statements in the node.
Used in the following format:
sqlCommit()
Examples
# for Oracle (OCI)
given a database with values 1, 2, 3, and 4 in table1sqlNonSelect("insert into table1 values (5)")
sqlNonSelect("insert into table1 values (6)")
sqlNonSelect("insert into table1 values (7)")
sqlNonSelect("insert into table1 values (8)")select * from table1 #will retrieve values: 1, 2, 3, 4
sqlCommit()
select * from table1 # will retrieve values:1, 2, 3, 4, 5, 6, 7, 8
sqlNonSelect
Used to execute a statement on a database. The statement may contain positional bind specifiers, which are bound to the bind expressions. If the node has no inputs, the statement is bound and executed once. If the node has inputs, the query is bound and executed on each iteration. The format of the statement, including bind specifications, is database-dependent. The value types of the bind expressions may have to match the bind specifications syntactically, depending on the database.
Used in the following format, where sql-stmt-expr must be a string-valued expression and bind-expr must be one or more expressions:
sqlNonSelect(sql-stmt-expr, {bind-expr}*)
Must be a top-level statement; only used in sqlrunner nodes. All sqlNonSelect and Select statement in a single node share the same session.
Examples
# for Oracle (OCI)
sqlNonSelect("update results set flag = :1 where id = :2",
'flag',
'id')
sqlRollBack
Used to erase all pending changes made to the database in the node. This does not reset the statement count for the CommitFrequency. Used in the following format:
sqlRollback()
Only used in dbExecute nodes. Shares a single session with all sqlCommit, sqlSelect and sqlNonSelect statements in the node.
Examples
# for Oracle (OCI)
given a database with values 1, 2, 3, and 4 in table1sqlNonSelect("insert into table1 values (5)")select * from table1 #will retrieve values: 1, 2, 3, 4 sqlCommit()
select * from table1 #will retrieve values: 1, 2, 3, 4, 5sqlNonSelect("insert into table1 values (6)")
sqlNonSelect("insert into table1 values (7)")
select * from table1 #will retrieve values: 1, 2, 3, 4, 5sqlRollback()
sqlNonSelect("insert into table1 values (8)")
select * from table1 #will retrieve values: 1, 2, 3, 4, 5
sqlCommit
select * from table1 # will retrieve values:1, 2, 3, 4, 5, 8
# for Oracle (OCI)
given a database with values 1, 2, 3, and 4 in table1sqlNonSelect("insert into table1 values (5)")select * from table1 #will retrieve values: 1, 2, 3, 4
sqlCommit()
select * from table1 #will retrieve values: 1, 2, 3, 4, 5sqlNonSelect("insert into table1 values (6)")
sqlNonSelect("insert into table1 values (7)")
sqlCommit()
sqlRollback() #this rollback has no effect,
# the pending changes have already been committedselect * from table1 #will retrieve values: 1, 2, 3, 4, 5, 6, 7
sqlSelect
Used to select records from a database and send them to an output. The output may be specified either by name or by number (one-indexed). The query may contain positional bind specifiers, which are bound to the bind expressions. If the node has no inputs, the query is bound and executed once, and the records are output to the designated output. If the node has inputs, the query is bound and executed on each iteration, and the records from each query are output sequentially to the designated output. The format of the query, including bind specifications, is database-dependent. The value types of the bind expressions may have to match the bind specifications syntactically, depending on the database. The mapping from database types to Data360 Analyze Script types is also database dependent, and not all types may be supported.
Used in the following format, where output-name must be a string literal, output-num must be an integer literal, query-expr is a string-valued expression, and bind-expr is one or more expressions:
sqlSelect({"output-name"|output-num}, query-expr, {bind-expr}*)
Must be a top-level statement; only used in sqlrunner nodes. All sqlSelect and sqlNonSelect statements in a single node share the same session.
Examples
# for Oracle (OCI)
sqlSelect(1, # write to output #1
"select * from billing_data where state = :1", # execute query
'state') # for each record in input (evaluates to :1