View Jitterbit Developer Portal
Database functions provide access to basic database interactions. This function is the same as If there are no rows returned for the query specified in The global Jitterbit variable 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.
Calls the stored procedure If applicable, the returned 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 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. 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 The example that follows describes how you can use Oracle Objects in a An Oracle Object Type definition follows this pattern: An Oracle Record Type definition follows this pattern: Step 1: Create the Object Step 2: Create the Package Step 3: Create the Package Body Step 4: Call the Stored Procedure in Jitterbit NOTE: In the example, the
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.
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 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 The return value in this case is the number of records returned; either 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
Takes a source (a single file in CSV format) and loads the data into a specified table in a target database. The parameter 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
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 For more advanced queries, where you want to retrieve more than one value or row, use the functions 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.
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 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
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.
An alias for the function
Overrides the current setting of the insert/update mode to "insert" for the current record. The return value is null.
Overrides the current setting of the insert/update mode to "update" for the current record. The return value is null.
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 (
For use in mappings, this function sets a database target field to be treated as unmapped. The return value is null.
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 NOTE: In the syntax for this function, the less-than ("
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 NOTE: In the syntax for this function, the less-than ("
Adds a user-defined database function to the beginning of a formula. The NOTE: In the syntax for this function, the less-than ("
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 projectsql
: The SQL command to be executed against the databaseDescription
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
.)sql
, the function returns null.$jitterbit.scripting.db.rows_affected
is not set by this method.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 projectspName
: The stored procedure to be executed on the database serverresultSet
: 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 procedureDescription
spName
using the connection information specified by the Database connection identified by databaseId
.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.resultSet
parameter is supported only by JDBC database drivers at this time. If using ODBC, the resultSet
will always return null.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.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
CallStoredProcedure
function in Jitterbit to call the wrapper procedure and have it perform the conversion to and from an Oracle Object Type.CallStoredProcedure
function in a simplified manner.Oracle Type Definitions
CREATE OR REPLACE TYPE example_customer_details AS OBJECT
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);
CREATE TYPE example_customer_details IS RECORD
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);
Example Steps
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
);
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;
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;
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>
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.DBCloseConnection
Declaration
void DBCloseConnection(string databaseId)
Syntax
DBCloseConnection(<databaseId>)
Required Parameters
databaseId
: A string reference path to a Database connection in the current projectDescription
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 projectsql
: The SQL command to be executed against the databaseoutputVariable
: (Second form) An output parameter that is matched to fields returned in the SQL command. Additional arguments can be specified as required.Description
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)
.$output
.1
(if records were found) or 0
(if none were returned).$jitterbit.scripting.db.rows_affected
will contain the number of rows affected by the query.$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.$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 formattarget
: A string reference path to a Database activity associated with a Database endpoint in the current projectmode
: An integer; one of 1
(upsert), 2
(insert), or 3
(update)tablename
: The table in the target databasecolumnNames
: A comma-delimited list of column namescolumnKeynames
: 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 databasesdatetimeFormat
: Specifies the format of datetime fields, such as "TimeStamp
" in Oracle databasesDescription
columnKeynames
is not used when only inserting (mode=2
) and may be omitted in that case.Source and Target
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);
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 projectsql
: The SQL command to be executed against the databaseDescription
$jitterbit.scripting.db.rows_affected
is not set by this method.DBLookupAll
or DBExecute
.Database ID
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 projectsql
: The SQL command to be executed against the databaseDescription
$jitterbit.scripting.db.rows_affected
is not set by this method.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 projectDescription
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
DBLoad
. See DBLoad
for details.SetDBInsert
Declaration
void SetDBInsert()
Syntax
SetDBInsert()
Description
Examples
// Sets the insert/update mode to "insert"
// for the current record
SetDBInsert();
SetDBUpdate
Declaration
void SetDBUpdate()
Syntax
SetDBUpdate()
Description
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 escapedOptional Parameters
escapeBackslash
: Boolean flag that indicates if backslashes ("\
") should be escaped by being doubled; default is false
Description
'
) 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
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><SEQUENCE>
Declaration
<SEQUENCE>
Syntax
<SEQUENCE>
Description
<SQLIDENTITY>
instead.<
") and greater-than (">
") symbols are part of the function syntax. <SQLIDENTITY><SQLIDENTITY>
Declaration
<SQLIDENTITY>
Syntax
<SQLIDENTITY>
Description
Identity
in SQL Server or Serial
in PostgreSQL. For Oracle databases, use the function <SEQUENCE>
instead.<
") and greater-than (">
") symbols are part of the function syntax. <UDF><UDF>
Declaration
<UDF>string userDefinedFunction
Syntax
<UDF><userDefinedFunction>
Required Parameters
userDefinedFunction
: A string defining a user-defined function callDescription
<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.<
") 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>)