SQL Adapter
The Designer supports relational databases via an SQL adapter. SQL support is native and you do not need to install any client tools or drivers for the supported databases.
Using the SQL adapter, you can
Task | Adapter Action | Description | Documentation Reference |
|---|---|---|---|
Execute SQL statements | execute | Execute an SQL statement. | |
Transaction handling | execute | Commit or rollback an SQL transaction. | |
Bulk fetch data | getHandle | Get a connection handle for subsequent fetchNext actions. | |
fetchNext | Fetch next record. | ||
closeHandle | Close the connection handle. If all records have been fetched, the handle is closed automatically. |
To use the SQL adapter it is helpful if you are familiar with the concepts of SQL.
The Designer also supports MongoDB as a document-oriented database with a dedicated adapter. Refer to MongoDB Adapter for more details.
Supported Databases
The xUML Runtime uses a generic SQL Database Management System (DBMS) adapter that works with the following DBMSs:
Database | Database Connection String | Example |
|---|---|---|
DB2 | Any valid DB2 connection string, which is either catalog database alias or the database name. | |
DBTypeVariable | For further information refer to SQL Adapter Reference. | |
Informix |
This database has to be configured first. To use it, please contact our Scheer PAS support team. | |
InterBase |
This database has to be configured first. To use it, please contact our Scheer PAS support team. | |
MariaDB | One of the following formats:
CODE
| mariadb.local@acme_db |
MYSQL | One of the following formats:
CODE
| mysql.local@acme_db |
ODBC | Any valid ODBC connection string. | |
Oracle | Any valid Oracle connection string, e.g. a database alias name as specified in | |
PostgreSQL | One of the following formats:
CODE
| postgresql.local@acme_db;connect_timeout=10 |
SQLBase |
This database has to be configured first. To use it, please contact our Scheer PAS support team. | |
SQLite | A string containing a valid SQLite database file path. | |
SQLServer | One of the following formats:
CODE
To connect to a named instance of SQL Server 2000 use <server_name\instance_name> instead of <server_name>: <server_name\instance_name>@<database_name>. | sqlserver.local@acme_db |
Sybase |
This database has to be configured first. To use it, please contact our Scheer PAS support team. |
Database Interchangeability
In order to have the option to switch between DBMSs smoothly, it is strongly recommended to only use ANSI SQL in database queries. If using proper ANSI SQL, all databases served by the SQL adapter are able to communicate with your service. However, if you introduce special commands (like Oracle SQL dialects) into your queries, you are bound to the Oracle DBMS and cannot switch freely.
Adding an SQL Adapter Operation to a Diagram
Expand the path to the SQL adapter in the service panel (Base Types/Bridge Base/Base Components/Add Ons/SQL):
You can drag out operations from the data model to any diagram:
The example shows how to add an SQL adapter operation to a BPMN execution diagram:
Configuring the SQL Adapter Operation
Once an operation has been added to a diagram, it needs to be configured as an SQL adapter.
Select the newly added SQL adapter operation and switch to the Attributes panel:
The following attributes are predefined and cannot be changed:
Attribute | Description | Allowed Values / Example |
|---|---|---|
Name | The name of the SQL adapter operation. | execute |
Description | If provided, you can find a description of the operation in this field. | |
Is static | Indicates that the operation is static. |
Click Add in the Extensions line to define the selected operation as to be an SQL adapter:
Search for the SQL Adapter and select it from the list of available adapter extensions. Click Next:
In the next window, you can configure the adapter. You have the following options:
Alias: An SQL adapter is configured via its alias (see Aliases for more information).
SQL Statement: You can enter a static SQL statement in this field. Refer to Querying SQL Databases for more hints on SQL statements.
DB Type: The database type is defined in the alias, but you can overwrite it if you select a different type here. If you want to do this, select a database type from the drop-down list.
If the attribute DB Type is set to Configured in DB type variable attribute, the dbType can be defined by a setting variable. Refer to SQL Adapter Reference for more details.
Regarding the alias, you have two options:
Use the drop-down list to select an available alias.
Use the + to create a new alias.
If you create a new alias, you can configure it in the following dialog:
Refer to SQL Adapter Reference for more information on the configuration options of this adapter.
Click Save to create the alias and add it to the extension.
Refer to Aliases for more information on how to create a new alias.
Save the adapter configuration. The Attributes panel now shows the added adapter extension.
If you want to remove an alias from an extension, select None from the drop-down list:
Deleting a database type works the same way: Select None from the selection list in field DB Type.
To remove an SQL Statement, click the corresponding Minus:
SQLAdapter_CustomerData_Example
Click here to download a simple example model that shows the usage of the SQL adapter in Scheer PAS Designer.