TIBCO EBX®
Documentation > Developer Guide > SQL in EBX®
Navigation modeDocumentation > Developer Guide > SQL in EBX®

Introduction

Overview

This documentation covers Structured Query Language (SQL) queries and expressions in EBX®. EBX® supports standard SQL queries to retrieve rows selected from one or more tables. Some EBX® SQL language features are extensions to the standard. Supported EBX® SQL syntax includes: table expressions (SELECT, FROM, WHERE, GROUP BY and HAVING clauses), DISTINCT, ORDER BY, LIMIT and OFFSET, combining queries (UNION [ALL]), and WITH (except RECURSIVE modifier).

The goal of this API is to provide to developers the ability to retrieve data from EBX® using a well-known standard.

EBX® SQL is accessible through Java APIs, especially from the class Query . The queries also support parameters. See Query.setParameter.

Mapping data model entities

The following section provides a detailed explanation about the mapping of the EBX® concepts into SQL.

Table (in data model)

EBX® tables are mapped naturally to SQL tables. In the data model, there can be more than one EBX® table with the same name. This ambiguity can occur when tables are in groups. To remove the ambiguity, use the full path of the table surrounded by double quotes (for example, "my_group/my_table" no longer conflicts with "other_group/my_table"). You can also use the entity name of the table, which is unique inside the data model. You can use the table name only if it does not collide with an entity name or another table name.

Fields

In SQL Standard, the structure of a table consists of one or more columns. Every element (including fields) whose parent is an EBX® table, is mapped to a column.

Groups

In SQL Standard, the structure of a table consists of one or more columns. Every element whose parent is an EBX® table is mapped to a column. This includes groups that are mapped to SQL columns as SQL structure types.

Associations

In SQL Standard, querying data among multiple tables is based on foreign keys and primary keys. These concepts in EBX® are similar to those in SQL. Therefore, joins between tables in SQL can also be done using EBX® foreign and primary keys.

Mapping data types

Handling data through SQL is highly dependent on its data type. For example, in predicates, columns can be compared only if they have the same SQL data type. The SQL data types are types according to the type in the data model.

Simple data types

Supported standard SQL data types

This table lists all of the simple types defined in the XML Schema that are supported by EBX®, along with their corresponding standard SQL types.

XML Schema type

SQL type

Java type

Notes

xs:string

VARCHAR

java.lang.String

xs:boolean

BOOLEAN

java.lang.Boolean

Values: TRUE, FALSE, UNKNOWN

xs:decimal

DECIMAL

java.math.BigDecimal

xs:dateTime

TIMESTAMP

java.lang.Date

xs:time

TIME

java.lang.Date

The date portion of the returned Date is always set to '1970/01/01'.

xs:date

DATE

java.lang.Date

The time portion of the returned Date is always set to the beginning of the day (that is, '00:00:00').

xs:anyURI

VARCHAR

java.net.URI

xs:Name (xs:string restriction)

VARCHAR

java.lang.String

xs:int

INT

java.lang.Integer

Extended simple types defined by EBX®

EBX® provides pre-defined simple data types. These types are defined by the internal schema common-1.0.xsd. Their definition is detailed in the section Extended simple types defined by EBX®

XML Schema type

SQL type

Java class

osd:text (xs:string restriction)

VARCHAR

java.lang.String

osd:html (xs:string restriction)

VARCHAR

java.lang.String

osd:email (xs:string restriction)

VARCHAR

java.lang.String

osd:password (xs:string restriction)

VARCHAR

java.lang.String

osd:color (xs:string restriction)

VARCHAR

java.lang.String

osd:resource (xs:anyURI restriction)

internal type

internal class

osd:locale (xs:string restriction)

VARCHAR

java.util.Locale

osd:dataspaceKey (xs:string restriction)

VARCHAR

java.lang.String

osd:datasetName (xs:string restriction)

VARCHAR

java.lang.String

List (multi-valued) types

Lists are handled as SQL Arrays. Their corresponding Java class is java.util.List.

Complex types

Complex types are handled as SQL Structured types. Their corresponding Java class is Object[]. This applies to foreign keys (see below) and groups, because they are defined through complex types. Use the dot operator to access fields inside the SQL Structure types. For example, use address.street to access the field street of the field address, if it is a complex type. When you reference a sub-field of a complex type in a query, you must always use the table name or alias:

TableRef types

In EBX®, a table can have a primary key composed of multiple fields. Foreign keys are defined by a single field with the osd:tableRef declaration. The standard SQL syntax has been extended to extract the value of any targeted primary key field. In the Extraction of foreign keys example, the following SQL expressions are valid:

Note

Even if the primary key is composed of only one field, the name of the field must be specified to access the value. For example, if the primary key is composed of a single id, fkb.id must be used to access the value, as in tableA.fkb.id = 123

A typical example of tableRef usage would be SQL joins:

SELECT * FROM employee JOIN department ON employee.fkDept.id = department.id

Or in case the referenced table has a composite primary key:

SELECT * FROM tableA JOIN tableB ON tableA.fkB.id1 = tableB.id1 AND tableA.fkB.id2 = tableB.id2

System columns

Apart from the fields present in a table, EBX® SQL provides some extra system columns. These columns are not returned by a SQL statement, unless they are explicitly referenced. For example, "SELECT * FROM ..." does not return systems columns, but "SELECT systemColumnName FROM ..." does.

Name

Description

SQL type

Java class

Examples

$adaptation

The Adaptation representing the table record.

internal type

com.onwbp.adaptation.Adaptation

SELECT t."$adaptation" FROM myTable t WHERE t.value>100

$pk

String representation of the primary key of the record (see PrimaryKey.syntax).

VARCHAR

java.lang.String

SELECT t.* FROM myTable1 t WHERE t."$pk"='123'

SELECT t.* FROM myTable2 t WHERE t."$pk"='123|abc'

SELECT t."$pk" FROM myTable3 t WHERE t.value>100

SELECT t.value FROM myTable3 t ORDER BY t."$pk"

SQL syntax

Supported standard operators and functions

An operator is a reserved word or a character used primarily in a SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in a SQL statement, and to serve as conjunctions for multiple conditions in a statement. EBX® supports most of the SQL standard operators and functions. Some functions and operators can have optional parameters: they are surrounded by square brackets in the documentation. Generally, there are five types of operators and functions:

The following table lists all of the operators' associativity and precedence, highest to lowest.

Operator

Associativity

.

left

[ ] (array element)

left

+ - (unary plus, minus)

right

* / %

left

+ -

left

BETWEEN, IN, LIKE, CONTAINS, and so on

-

< > = <= >= <> !=

left

IS NULL, IS FALSE, IS NOT TRUE, and so on

-

NOT

right

AND

left

OR

left

Escaping identifiers

In the following cases, the identifier must be escaped by using double quotes:

The following example shows a query to illustrate all cases:

SELECT t."user", t."$pk" FROM "/root/myTable" t WHERE t."order" = 1

Explain plan

EBX® SQL supports EXPLAIN PLAN FOR ... syntax to get the plan information of a query. The result is similar to Query.explain.

Example: EXPLAIN PLAN FOR SELECT id FROM myTable

Limitations and performance guidelines

Documentation > Developer Guide > SQL in EBX®