Versions Compared

Old Version 7

changes.mady.by.user Chee Hove Teng

Saved on

compared with

New Version Current

changes.mady.by.user Felicity Borrelli

Saved on

Key

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

...

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.

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

Note
title

Note!

Failovers can be triggered by all errors that occur while connecting to the database or executing commands, not exclusively due to connection problems.

Please refer to Notes section on the Database Functions page  page for details on allowed database data type.

The following functions for Database Prepared Statements described here are:toc

...

...

sqlPrepSelect

The sqlPrepSelect function is used when selecting data from database tables.

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

 

Parameter

Description

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

from MZ 9.2.2.0

 

)

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, the system has the disableCommit set to true unless changed by 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.

 

Info
title

Example - Using sqlPrepSelect

 

Code Block
final string sql = "select col2 from tab1 where col1=?"; 
consume {
    table tab = sqlPrepSelect(sql, myUdr.aValue, 
    "Default.myPrimaryDB", "Default.mySecondaryDB");
           
    if ( tab != null ) {
       if ( tableRowCount(tab) > 0 ) {
       	debug("Found following value in col2:" 
       	+ tableGet(tab, 0, 0));
       }
    } 
    else {
      debug("Both primary and secondary database nodes 
             are unavailable");
    }
}

...

 

...

sqlPrepDynamicSelect

The sqlPrepDynamicSelect function is used when selecting data from database tables with dynamically created SQL statements.

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

 

Parameter

Description

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
title

Note!

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

from MZ 9.2.2.0

 

)

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, the system has 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

 

title
Info

Example - Using sqpPrepDynamicSelect

 

Code Block
final string EXT_REF_CONFIG = "Default.extrefprop";
string TABLE_KEY = externalReferenceGet(EXT_REF_CONFIG, "mytable");
consume {
        string sql = "select col2 from " + TABLE_KEY + " where col1=?";
        list <any> parameters = listCreate(any, myUDR.aValue);
        table tab = sqlPrepDynamicSelect(sql, parameters, "Default.myPrimaryDB", "Default.mySecondaryDB");
        if ( tab != null ) {
           if ( tableRowCount(tab) > 0 ) {
              debug("Found following value in col2:" + tableGet(tab, 0, 0));
           }
        }
        else {
           abort("Both primary and secondary database nodes are unavailable");
         }
}

 

sqlPrepUpdate

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

title
Note

Note!

This function do not work with Postgresql numeric column data type.

 

Code Block
int sqlPrepUpdate(
    string sqlStatement,
    any parameter [,...],
    string DBProfile [,...],
    )

 

Parameter

Description

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

 

title
Info

Example - Using sqpPrepUpdate

 

Code Block
final string sql = "update table1 set name = ? where id = ?";  
consume {
   int cnt = sqlPrepUpdate(sql, aNewName, anIdent, 
    "Default.myPrimaryDB", "Default.mySecondaryDB");
   
   if ( cnt == -1 ) {
       debug("Both primary and secondary database nodes 
    are unavailable");
   }    
}