Database Functions

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");

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.

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: Calling 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: Calling 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: Calling a stored procedure that accesses an Oracle Object Type

Using 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.

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.

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:

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:

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

<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>

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>");

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.
• 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: Executing and retrieving 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: Executing and retrieving 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: Executing and retrieving 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

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.

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);

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");

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");

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>");

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.

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();

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();

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)));

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);

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

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

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

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>)