mvSQL query support in Jitterbit App Builder

App Builder supports running Rdbms queries using mvSQL. This feature allows you to use mvSQL query syntax to query information and/or create Business Objects. It is available at both the Business Logic Layer as well as the Data Storage Layer. Other database Rdbms beyond mvSQL are under consideration to be supported in future releases.

For background on mvSQL:

SQL is a declarative programming language used to query relational database systems. There are many different vendor dialects of SQL. Microsoft SQL Server has Transact-SQL. Oracle has PL/SQL. App Builder has its own dialect of SQL, called mvSQL. App Builder rules, including Business Objects, CRUD rules, validation rules, etc. are all represented as mvSQL.

Video example

To access mvSQL from the business logic layer

1. Navigate to the App Builder IDE
2. Click on Build your application from the Build menu
3. Click the Business Logic Layer navigation item
4. Click the Run mvSQL icon from the Business Layer Data Source panel

To access mvSQL from the data storage layer

1. Navigate to the App Builder IDE
2. Click on Build your application from the Build menu
3. Click the Data Storage Layer navigation item
4. Locate the Data Source you're working with, and click the mvSQL icon

Run mvSQL screen

From the new Run mvSQL screen, you are able to type in mvSQL syntax into the Query field on the Run mvSQL panel. At that stage you can either click on Execute, which will run the query and return the results in the Results panel ~or~ you can click on Convert to Business Object which will create a new Business Object using the provided syntax.

When working in the Query field you have the same options and tools (logic, functions) available to you that you would if you were creating a Business Object through the standard Business SQL screen.

Supported in Query:

• mvSQL functions
• database functions
• database passthrough, ${.}
• sub-queries (select * from (select * from customer…))
• Unions
• Group by will also work like a rule, meaning, using an aggregate function like Count(1) will automatically group by the other selected columns
• List substitution. For example:

select CustomerId || '!' as Example, Right({{Example}}, 4) as Example2
from CUSTOMERS
WHERE {{Example2}} like '%R!%'

You can call or reference existing Rules in your mvSQL syntax. If, for example you have an existing Rule named Customers (Source), you can select from it:

    SELECT *
    FROM "Customers (Source)"

To use the query field

1. Enter your mvSQL query syntax. For example:

       SELECT *
       FROM Customers
       WHERE Country = 'USA'
   

2. Save the syntax (control+s) or by clicking the Execute button

3. Review the resultant information from the Results panel

   

4. If you want to convert this into a Business Object, click the Convert to Business Object button

5. Provide the required Rule Detail information:

   • Name
   • Purpose
   • Source Data Source
   • Target

6. Click Save