PAS 23.1.1 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.	Querying SQL Databases
Transaction handling	execute sql = commit or rollback	Commit or rollback an SQL transaction.	SQL Transaction Handling
Bulk fetch data	getHandle	Get a connection handle for subsequent fetchNext actions.	Handling Big Data Sets
	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.

Tip
The Designer also supports MongoDB as a document-oriented database with a dedicated adapter. See 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

MySQL

Oracle

SQLite

SQLServer

Any valid DB2 connection string, which is either catalog database alias or the database name.
The catalog database stores database location information in the system database directory.

DBTypeVariable For further information refer to SQL Adapter Reference.

Informix

Info
This database has to be configured first. To use it, please contact our Scheer PAS support team.

InterBase

Info
This database has to be configured first. To use it, please contact our Scheer PAS support team.

MariaDB
PAS 24.0

One of the following formats:

Code Block
[<server_name>@][<database_name>]

"" or "@"
Empty string or '@' character, connects to a local server.
<database_name> or @<database_name>
Connects to a database with the specified name on local server.
<server_name>@
Connects to the specified server. It can have the following formats:
- host name[,port]
- path name of the Unix socket that is used to connect to the server
<server_name>@<database_name>
Connects to a database with the specified name on the specified server.

mariadb.local@acme_db

MYSQL

One of the following formats:

Code Block
[<server_name>@][<database_name>]

"" or "@"
Empty string or '@' character, connects to a local server.
<database_name> or @<database_name>
Connects to a database with the specified name on local server.
<server_name>@
Connects to the specified server. It can have the following formats:
- host name[,port]
- path name of the Unix socket that is used to connect to the server
<server_name>@<database_name>
Connects to a database with the specified name on the specified server.

mysql.local@acme_db

ODBC
PAS 24.0

Any valid ODBC connection string.

Oracle

Any valid Oracle connection string, e.g. a database alias name as specified in TNSNAMES.ORA file or at <hostname>[:<port>][/<service_name>] .

PostgreSQL
PAS 24.0

One of the following formats:

Code Block
[<server_name>@][<database_name>][;<options>]

"" or "@"
Empty string or '@' character: Connects to a local server.
<database_name> or @<database_name>
Connects to a database with the specified name on local server.
<server_name>@
Connects to the specified server. It can have the following formats:
- host name[,port]
- path name of the Unix socket that is used to connect to the server
<server_name>@<database_name>
Connects to a database with the specified name on the specified server.
<options>
string used for PQsetdbLogin function pgoptions parameter (these are the server process parameters).

postgresql.local@acme_db;connect_timeout=10

SQLBase

Info
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 Block
[<server_name>@][<database_name>]

"" or "@"
Empty string or '@' character: Connects to a default database on a local server.
<database_name> or @ <database_name>
Connects to a database with the specified name on your local server.
<server_name>@
Connects to a default database on the specified server.
<server_name>@<database_name>
Connects to a database with the specified name on the specified server.

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

Info
This database has to be configured first. To use it, please contact our Scheer PAS support team.

Tip

title	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 on the left 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. Depending on the diagram type you can see the following information (example BPMN execution diagram):

Attribute	Description	Allowed Values / Example
Name	The name of the SQL adapter operation.	getHandle
Symbol Type	Operations added to an execution diagram are execution steps.	Execution Step

All this is predefined and cannot be changed.

Click Add Stereotype to define the selected operation as to bean SQL adapter.

Select SQL Adapter from the list of available adapter stereotypes.
Click Save.

The Attributes panel shows the added adapter stereotype. Now you still need to configure the adapter.

Expand the stereotype by clicking the arrow on the right.

An SQL adapter is configured via its alias (see Aliases for more information on aliases).

Additionally, you can insert

sql
dbType

See further information below and on page URL. The adapter option action derives from the used operation. Do not configure this.

You can select an existing alias from a drop-down list by clicking the text Select alias.

If you want to remove an added alias, select None from the drop-down list:

You can also create and add a new alias by clicking the corresponding icon.

Refer to Aliases for more information on how to create a new alias.

A dialog opens where you can name and configure the new alias. Refer to SQL for more information on the configuration options of this adapter.

Click Save to create and add the new alias.

Anchor

	sql_adapter_options
	sql_adapter_options

To add a static sql statement, click on the corresponding icon and enter a valid statement.

Refer to Querying SQL Databases for more hints on SQL statements.

The dbType is defined in the alias, but you can overwrite it if you select a different type here. To select a dbType , click icon and select a database type from the list.

If the attribute dbType is set to DBTypeValue, the dbTypeVariable attribute is used to define the type of the database. The dbType then can be defined by a setting variable.

Refer to SQL for more details.

Panel

title	On this Page:

Table of Contents

Multiexcerpt include

SpaceWithExcerpt	INTERNAL
MultiExcerptName	SQLAdapter_Example
PageWithExcerpt	INTERNAL:_designer_examples

Panel

title	Related Pages:

Otp

Floating	false

rprp

Children Display

Aliases

Panel

title	Related Documentation:

MongoDB Adapter

Page tree

Versions Compared

Old Version 1

New Version Current

Key

Supported Databases

Adding an SQL Adapter Operation to a Diagram

Configuring the SQL Adapter Operation

Page tree

Page History

Versions Compared

Old Version 1

New Version Current

Key

Supported Databases

Adding an SQL Adapter Operation to a Diagram

Configuring the SQL Adapter Operation