Database functions in Jitterbit Integration Studio

Database functions provide access to basic database interactions.

`CacheLookup`

Declaration

string CacheLookup(string databaseId, string sql)

Syntax

CacheLookup(<databaseId>, <sql>)

Required parameters

databaseId: A string reference path to a Database connection in the current project
sql: The SQL command to be executed against the database

Description

This function is the same as DBLookup, except that the first lookup caches the information and subsequent lookups use this cache instead of repeatedly querying the database. (An alternative to caching is to use the functions Set and Get.)

If there are no rows returned for the query specified in sql, the function returns null.

The global Jitterbit variable $jitterbit.scripting.db.rows_affected is not set by this method.

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Examples

// Looking up in a database using a SQL string
CacheLookup("<TAG>endpoint:database/My Database</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

[Back to top]

`CallStoredProcedure`

Declaration

type CallStoredProcedure(string databaseId, string spName, type resultSet[, string inputOutputVariable,...])

Syntax

CallStoredProcedure(<databaseId>, <spName>, <resultSet>[, <inputOutputVariable>,...])

Required parameters

databaseId: A string reference path to a Database connection in the current project
spName: The stored procedure to be executed on the database server
resultSet: A global variable to hold the result set returned by the database server, if applicable. (See notes below).

Optional parameters

inputOutputVariable: An input or output parameter to be passed to the stored procedure; these parameters are added as required by the signature of the stored procedure

Description

Calls the stored procedure spName using the connection information specified by the Database connection identified by databaseId.

If applicable, the returned resultSet is a two-dimensional array of strings. If the stored procedure returns no resultSet or if using an ODBC driver, this argument is ignored.

Note

With Microsoft SQL Server databases, this function calls stored procedures in the default database owner (dbo) schema only. to call stored procedures in other schemas, use the DBExecute function.

Caution

The resultSet parameter is supported only by JDBC database drivers at this time. If using ODBC, the resultSet will always return null.

The remaining optional parameters are used to pass input and output arguments to the stored procedure. The number of arguments required depends on the signature of the stored procedure.

Input arguments can be a hard-coded value, the value of a source, or the value of a calculation or formula. Output arguments (including the resultSet) are specified by reference as "$name", where "name" is the name of the global variable that will hold the output value. The return value and type of the function is the return value and type of the stored procedure.

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Examples

Example 1: Call a stored procedure with no result set

// Calls a stored procedure "MyStoredProcedure",
// which takes one input variable, one output
// variable, and ignores the result set.
// "Input" is the name of the source global
// variable that provides the input and
// "output" is the name of the global variable
// used to store the output:
CallStoredProcedure("<TAG>endpoint:database/My Oracle Database</TAG>",
    "MyStoredProcedure", 0, Input, $output);

// The value of the output parameter can be
// accessed by either $output or Get("output")

Example 2: Call a stored procedure with a result set

// Calls a stored procedure "GetValues", which
// takes two input variables and returns a
// result set.
// The result set is returned as the
// two-dimensional array $result.
// The result can be accessed by using either
// $result or Get("result"):
CallStoredProcedure("<TAG>endpoint:database/My Oracle Database</TAG>",
    "GetValues", $result, Input1, Input2);

Example 3: Call a stored procedure that accesses an Oracle object type

Use Oracle object and record types

Jitterbit supports Oracle Object Types for working with Oracle databases when using the Oracle JDBC driver. Oracle Object Types are similar to Oracle Record Types, which are not supported in Jitterbit because of a lack of support by Oracle.

Warning

To use Oracle Object Types, you must use the Oracle JDBC driver. The Oracle ODBC driver does not support either Oracle Object Types or Oracle Record Types.

To access Oracle Record Types using the Oracle JDBC driver, you can create a "wrapper" stored procedure on your Oracle database that can access and convert a Oracle Record Type. Then, use the CallStoredProcedure function in Jitterbit to call the wrapper procedure and have it perform the conversion to and from an Oracle Object Type.

Tip

More information about the differences between the Oracle Record Types and Oracle Object Types can be found within Oracle's documentation. See Record Variable Declaration and Using PL/SQL With Object Types of the Oracle Database Release 18 documentation for more information.

The example that follows describes how you can use Oracle Objects in a CallStoredProcedure function in a simplified manner.

Oracle type definitions

An Oracle Object Type definition follows this pattern:

Oracle Object Type (Supported)

CREATE OR REPLACE TYPE example_customer_details AS OBJECT
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);

An Oracle Record Type definition follows this pattern:

Oracle Record Type (Not Supported)

CREATE TYPE example_customer_details IS RECORD
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);

Example steps

Step 1: Create the Object To use Oracle Object Types, first create the object in the Oracle database:

Create Object

CREATE OR REPLACE TYPE example_customer_details AS OBJECT
(status                    NUMBER
,party_id                  NUMBER
,account_id                VARCHAR
);

Step 2: Create the Package Next, create the package as a function in the Oracle database:

Create Package

CREATE OR REPLACE PACKAGE example AS
  FUNCTION processcustomer(custin IN example_customer_details, new_account_number IN VARCHAR) RETURN example_customer_details;
END example;

Step 3: Create the Package Body Next, create the package body as a function in the Oracle database:

Create Package Body

CREATE OR REPLACE PACKAGE BODY example AS
FUNCTION processcustomer(custin IN example_customer_details, new_account_number IN varchar) RETURN example_customer_details
  IS
  custout example_customer_details;
  BEGIN
     custout := example_customer_details(
     custin.status + 1,
     custin.party_id,
     new_account_number
     );
     return custout;
END;
END example;

Step 4: Call the Stored Procedure in Jitterbit Now you are ready to call the stored procedure processcustomer from Jitterbit using the CallStoredProcedure function. This example script shows how to pass an object to the CallStoredProcedure function. You can also pass objects from a stored procedure as return or output parameters in a similar manner.

Create Jitterbit Script

<trans>
$cust = dict();
$cust["status"] = 1;
$cust["party_id"] = 10;
$cust["account_id"] = "2341";
db = "<TAG>endpoint:database/My Oracle Database</TAG>";
$custout = CallStoredProcedure(db, "EXAMPLE.PROCESSCUSTOMER", "", $cust, "NA0233");
r = "Status: " + $custout["STATUS"] +
" Party ID: " + $custout["PARTY_ID"] +
" Account ID: " + $custout["ACCOUNT_ID"];
WriteToOperationLog("Resulting object: " + r);
</trans>

Note

In the example, the processcustomer function in Oracle expects two parameters: the custom object (example_customer_details) and a VARCHAR (new_account_number). In the above example, the dictionary $cust represents the custom object, and NA0233 represents the VARCHAR.

Caution

On output, the data type property names are case-sensitive and are thus uppercase. For input objects, the property names are not case-sensitive.

[Back to top]

`DBCloseConnection`

Declaration

void DBCloseConnection(string databaseId)

Syntax

DBCloseConnection(<databaseId>)

Required parameters

databaseId: A string reference path to a Database connection in the current project

Description

Commits the current transaction and closes the Database connection.

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Examples

// Closing a Database connection
DBCloseConnection("<TAG>endpoint:database/My Database</TAG>");

[Back to top]

`DBExecute`

Declaration

array DBExecute(string databaseId, string sql)

int DBExecute(string databaseId, string sql, string outputVariable,...)

Syntax

DBExecute(<databaseId>, <sql>)

DBExecute(<databaseId>, <sql>, <outputVariable>,...)

Required parameters

databaseId: A string reference path to a Database connection in the current project
sql: The SQL command to be executed against the database
outputVariable: (Second form) An output parameter that is matched to fields returned in the SQL command. Additional arguments can be specified as required.

Description

Executes a SQL statement on a database and returns the results.

If the SQL statement produces a result set, there are two ways to retrieve the data:

If you specify only the two required parameters (first form), the function will return the full record set as an array of rows.

You can then use a While() loop to iterate over the rows and use Get() to retrieve the data. If no rows are returned, the method returns an empty array (Length($arr) == 0).
If you specify output variables in addition to the two required parameters (second form), the values of the fields of the first row are returned.

Pass names of global variables within quotes as parameters after the first two parameters. The value of the first field of the first row will be written to the global variable passed as the third parameter, the second field of the first row to the fourth parameter, and so on. Alternatively, the global variables can be passed by reference by preceding them with a $ sign, such as $output.

The return value in this case is the number of records returned; either 1 (if records were found) or 0 (if none were returned).

The returned data values are always strings. Binary data is returned as its hex-string representation.

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Related Jitterbit Variables

If this method completes successfully, $jitterbit.scripting.db.rows_affected will contain the number of rows affected by the query.
If using a JDBC driver to connect to a database, set jitterbit.scripting.db.search.rowset to true upstream of the function to make any calls to a stored procedure that returns multiple results to return the first non-empty record set instead of returning an empty set.
To run the statement in a transaction, set the variables $jitterbit.scripting.db.auto_commit=false and $jitterbit.scripting.db.transaction=true in a script prior to the call. The transaction will be committed at the end of a successful transformation. Setting both variables (auto_commit and transaction) to true will result in an error.
Set $jitterbit.scripting.db.max_rows to limit the number of records to return. The default is 10,000 rows.

Examples

Example 1: Execute and retrieve values in an array

// Results of the SQL select as an array

t = "<TAG>endpoint:database/My Database</TAG>";

rows = DBExecute(t, "SELECT ORDER_TYPE, ORDER_AMOUNT FROM PO_HEADER WHERE PO_NUMBER = 1");

// The value of the database column ORDER_TYPE
// can then be accessed with
// Get($rows, $i, 0)
// where $i is the 0-based count of the row you
// want retrieved..

Example 2: Execute and retrieve values in passed referenced global variables

// Results of the SQL select will be in the
// $custName and $custAddr global variables:

t = "<TAG>endpoint:database/My Database</TAG>";

DBExecute(t,
  "SELECT CustomerName, CustomerAddress FROM Customers WHERE CustomerId = " + $custId,
  $custName, $custAddr);

// The value of the database column CustomerName
// can then be accessed with either
// Get("custName")
// or
// $custName

Example 3: Execute and retrieve values in passed named global variables

// Results of the SQL select will be in the
// OrderType and OrderAmount global variables:

t = "<TAG>endpoint:database/My Database</TAG>";

DBExecute(t, "SELECT ORDER_TYPE, ORDER_AMOUNT FROM PO_HEADER WHERE PO_NUMBER = 1",
  "OrderType", "OrderAmount");

// The value of the database column ORDER_TYPE
// can then be accessed with either
// Get("OrderType")
// or
// $OrderType

[Back to top]

`DBLoad`

Declaration

void DBLoad(string source, string target, int mode, string tablename, string columnNames[, string columnKeynames, int skipLines, string dateFormat, string datetimeFormat])

Syntax

DBLoad(<source>, <target>, <mode>, <tablename>, <columnNames>[, <columnKeynames>, <skipLines>, <dateFormat>, <datetimeFormat>])

Required parameters

source: A string reference path to an activity associated with a file-type endpoint in the current project that is a single file in CSV format
target: A string reference path to a Database activity associated with a Database endpoint in the current project
mode: An integer; one of 1 (upsert), 2 (insert), or 3 (update)
tablename: The table in the target database
columnNames: A comma-delimited list of column names
columnKeynames: A comma-delimited list of column names that form the update key. Required if mode is not 2.

Optional parameters

skipLines: Number of lines to ignore at the beginning of the file (used to skip headers)
dateFormat: Specifies the format of date fields, such as "Date" in Oracle databases
datetimeFormat: Specifies the format of datetime fields, such as "TimeStamp" in Oracle databases

Description

Takes a source (a single file in CSV format) and loads the data into a specified table in a target database.

The parameter columnKeynames is not used when only inserting (mode=2) and may be omitted in that case.

Source and target

The source used in this function call must be defined as an activity associated with a file-type endpoint in the current project. These include configured File Share, FTP, HTTP, Local Storage, and Temporary Storage activities. The first file returned from that source will be used.

The target used in this function call must be defined as a Database activity associated with a Database endpoint in the current project.

For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Warning

The DBLoad() function works only on Database activities associated with a Database endpoint using a JDBC driver.

Examples

// Using the file returned from the source
// "FTP Files", this example upserts (mode=1)
// into the table "MyTable" on the database
// target "myDatabase". "FTP Files" is
// expected to be a CSV file that contains data
// for the columns "ID,Col1,Col2,Col3".
// The update key (used to decide whether to
// update or insert) will be on the column "ID".
// The first line of the CSV file will be
// ignored as it is a header:

DBLoad("<TAG>activity:ftp/FTP Endpoint/ftp_read/FTP Files</TAG>",
    "<TAG>activity:database/Database Endpoint/database_insert/myDatabase</TAG>",
    1, "MyTable", "ID,Col1,Col2,Col3", "ID", 1);

[Back to top]

`DBLookup`

Declaration

string DBLookup(string databaseId, string sql)

Syntax

DBLookup(<databaseId>, <sql>)

Required parameters

databaseId: A string reference path to a Database connection in the current project
sql: The SQL command to be executed against the database

Description

Executes a SQL statement on a database and returns the first field of the first result matching the specified criteria.

The returned data value is always a string. Binary data is returned as its hex-string representation. If there are no rows returned for the specified query, the function returns null.

The global Jitterbit variable $jitterbit.scripting.db.rows_affected is not set by this method.

For more advanced queries, where you want to retrieve more than one value or row, use the functions DBLookupAll or DBExecute.

Database ID

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Examples

// Returns the first field of the first result
// from running the SQL query
result = DBLookup("<TAG>endpoint:database/My Database</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

[Back to top]

`DBLookupAll`

Declaration

array DBLookupAll(string databaseId, string sql)

Syntax

DBLookupAll(<databaseId>, <sql>)

Required parameters

databaseId: A string reference path to a Database connection in the current project
sql: The SQL command to be executed against the database

Description

Executes a SQL statement on a database and returns the results matching the specified criteria.

The returned data is always returned as a two-dimensional array of strings. Binary data is returned as its hex-string representation. If there are no rows returned for the specified query, the function returns an empty array.

The global Jitterbit variable $jitterbit.scripting.db.rows_affected is not set by this method.

The database used in this function call must be defined as a Database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

For more advanced queries, where you want to retrieve directly into global variables, use the function DBExecute.

Examples

// Returns the result from running the SQL query
result = DBLookupAll("<TAG>endpoint:database/My Database</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

[Back to top]

`DBRollbackTransaction`

Declaration

void DBRollbackTransaction(string databaseId)

Syntax

DBRollbackTransaction(<databaseId>)

Required parameters

databaseId: A string reference path to a Database connection in the current project

Description

Rolls back the current transaction and closes the Database connection.

The database used in this function call must be defined as a database connection in the current project. For more information, see the instructions on inserting endpoints under the Endpoints section in Jitterbit Script.

Examples

// Rolls back the current transaction
DBRollbackTransaction("<TAG>endpoint:database/My Database</TAG>");

[Back to top]

`DBWrite`

Declaration

void DBWrite(string source, string target, int mode, string tablename, string columnNames[, string columnKeynames, int skipLines, string dateFormat, string datetimeFormat])

Syntax

DBWrite(<source>, <target>, <mode>, <tablename>, <columnNames>[, <columnKeynames>, <skipLines>, <dateFormat>, <datetimeFormat>])

Description

An alias for the function DBLoad. See DBLoad for details.

[Back to top]

`SetDBInsert`

Declaration

void SetDBInsert()

Syntax

SetDBInsert()

Description

Overrides the current setting of the insert/update mode to "insert" for the current record. The return value is null.

Examples

// Sets the insert/update mode to "insert"
// for the current record
SetDBInsert();

[Back to top]

`SetDBUpdate`

Declaration

void SetDBUpdate()

Syntax

SetDBUpdate()

Description

Overrides the current setting of the insert/update mode to "update" for the current record. The return value is null.

Examples

// Sets the insert/update mode to "update"
// for the current record
SetDBUpdate();

[Back to top]

`SQLEscape`

Declaration

string SQLEscape(string unescapedSQL[, bool escapeBackslash])

Syntax

SQLEscape(<unescapedSQL>[, <escapeBackslash>])

Required parameters

unescapedSQL: A string of SQL that is to be escaped

Optional parameters

escapeBackslash: Boolean flag that indicates if backslashes ("\") should be escaped by being doubled; default is false

Description

Performs the necessary escaping of literal strings used in a SQL statement.

Strings used as character constants in a SQL statement uses a single quote (') as a delimiter; if the actual data contains single quotes, they need to be escaped by specifying them twice. This method escapes single quotes following the SQL standard by replacing each single quote (') with two single quotes (''). If backslash characters should also be escaped, provide and set the second parameter to true.

Examples

// In this example, the variable GUID needs to
// have any single quotes in it escaped
// (doubled); the resulting string is then
// enclosed in single quotes by the Quote
// function before being used in a DBLookup
// function:


DBLookup("<TAG>endpoint:database/My Database</TAG>",
    "SELECT ORDER FROM PO_HEADER WHERE PO_ID=" + Quote(SQLEscape(GUID)));

[Back to top]

`Unmap`

Declaration

void Unmap()

Syntax

Unmap()

Description

For use in mappings, this function sets a database target field to be treated as unmapped. The return value is null.

Examples

valueToInsert = DBLookup(....);
// If valueToInsert returned by a DBLookup is null, we want to treat
// this field as unmapped and we do not want to include it in the INSERT statement
// that is being generated for the DB target for this record:
If (valueToInsert == Null(), Unmap(), valueToInsert);

[Back to top]

`<SEQUENCE\>`

Declaration

<SEQUENCE>

Syntax

<SEQUENCE>

Description

For use in mappings with Oracle databases, this function is used when the target contains tables that are linked with a primary key/foreign key relationship. In that case, map this to the primary keys that are generated by the Oracle database.

For databases other than Oracle, use the function <SQLIDENTITY> instead.

Note

In the syntax for this function, the less-than ("<") and greater-than (">") symbols are part of the function syntax.

Examples

If <trans> tags are present, <SEQUENCE> should be placed outside of them like so:

<trans>
</trans>
<SEQUENCE>

[Back to top]

`<SQLIDENTITY\>`

Declaration

<SQLIDENTITY>

Syntax

<SQLIDENTITY>

Description

For use in mappings with non-Oracle databases, this function is used when the target contains tables that are linked with a primary key/foreign key relationship. In that case, map this to the primary keys that are generated by the database, such as Identity in SQL Server or Serial in PostgreSQL. For Oracle databases, use the function <SEQUENCE> instead.

Note

In the syntax for this function, the less-than ("<") and greater-than (">") symbols are part of the function syntax.

Examples

If <trans> tags are present, <SQLIDENTITY> should be placed outside of them like so:

<trans>
</trans>
<SQLIDENTITY>

[Back to top]

`<UDF\>`

Declaration

<UDF>string userDefinedFunction

Syntax

<UDF><userDefinedFunction>

Required Parameters

userDefinedFunction: A string defining a user-defined function call

Description

Adds a user-defined database function to the beginning of a formula. The <UDF> prefix is stripped off the expression before being passed on. Note that opening and closing <trans> tags can be used to indicate parts of the function call that are to be evaluated by Jitterbit before the expression is passed to a database.

Note

In the syntax for this function, the less-than ("<") and greater-than (">") symbols around <UDF> are part of the function syntax.

Examples

// The user-defined function geography::Point()
// is being called with parameters created by evaluating
// the Jitterbit Script enclosed by <trans> tags:

<UDF>geography::Point(<trans>json$Incidents$item.Latitude$ + ","
  + json$Incidents$item.Longitude$ + ",4326";</trans>)

[Back to top]