Versions Compared

Version Old Version 1 New Version Current
Changes made by

Jenni Wallin

Chee Hove Teng

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

A prepared statement is used to efficiently execute a statement multiple times. The Prepared Statement related functions support database failover, which is useful in a replicated database environment, by receiving multiple database profiles.

All functions without the parameter disableCommit=false will not be commit after the function has executed.

If a workflow failure occurs using one database profile a failover process starts to replace that profile with the next available one. If none of the supplied profiles could be used a null value will be returned.

...

Code Block
table sqlPrepSelect
    (string sqlStatement,
     any parameter [,...],
     string DBProfile [,...],
	 @Deprecated boolean disableCommit,  //Optional)

...

ParameterDescription

sqlStatement

SQL string containing one or more parameter placeholders ('?').

parameter(s)

Value(s) to bind against parameter placeholders.

DBProfile/s)

Name of the database profile(s) to use for access.

disableCommit

(Deprecated since PE 4.3)

An optional parameter to disable the commit statement from being performed at the end of every SQL transaction for this particular function. Setting this parameter to false will result in the commit statement to be performed at the end of every SQL transaction for this particular function. By default,  will   will have the disableCommit set to true unless otherwise changed via this parameter.

Info

It should be noted that on recent Oracle versions, the DBLink SQL transaction behaviour has changed, where every single SQL statement for remote database transaction requires a commit or rollback statement in order to close a connection.

In addition, PostgreSQL users should set this to false as every statement needs to be committed. 


Returns

Null in case of failure the error details can be found in System Log otherwise a table, holding the SQL select result.

...

Code Block
table sqlPrepDynamicSelect
    (string sqlStatement,
     list<any> parameters,
     string DBProfile [,...],
	 @Deprecated boolean disableCommit,  //Optional)

...

ParameterDescription

sqlStatement

SQL string containing one or more parameter placeholders ('?').

The string may also contain variables that substitute any part of the statement such as table- or column names.

Note
titleNote!

Validation of sqlStatement is performed in runtime and not during configuration. Make sure that number of placeholders matches the actual number of parameters.


parameter(s)

Value(s) to bind against parameter placeholders

DBProfile/s)

Name of the database profile(s) to use for access

disableCommit

(Deprecated since PE 4.3)

An optional parameter to disable the commit statement from being performed at the end of every SQL transaction for this particular function. Setting this parameter to false will result in the commit statement to be performed at the end of every SQL transaction for this particular function. By default,  will   will have the disableCommit set to true unless otherwise changed via this parameter.

Info

It should be noted that on recent Oracle versions, the DBLink SQL transaction behaviour has changed, where every single SQL statement for remote database transaction requires a commit or rollback statement in order to close a connection.


Returns

Null in case of failure the error details can be found in System Log otherwise a table, holding the SQL select result

...

The sqlPrepUpdate function is used when updating, inserting or inserting deleting data into a database table.

...

ParameterDescription

sqlStatement

SQL string containing one or more parameter placeholders ('?')

parameter(s)

Value(s) to bind against parameter placeholders

DBProfile/s)

Name of the database profile(s) to use for access

Returns

-1 in case of failure the error details can be found in System Log, otherwise an integer equal to the number of rows updated or inserted

...