Database Server-Specific Notes for SQL Adapters

Scheer PAS supports the following database client libraries: DB2, Microsoft SQL Server, MySQL, MariaDB, Oracle and SQLite. If you want to use other database systems, please contact the PAS support team.

DB2

You can use any valid DB2 connection string, which is either a catalog database alias or the database name. The catalog database stores database location information in the system database directory.

MySQL

Use one of the following formats for the database connection string:

““ 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.
<server name>@<database name>: Connects to a database with the specified name on the specified server.

<server name> can have the following formats:

host name[,port]
path name of the Unix socket that is used to connect to the server

SQL Server

Use one of the following formats for the database connection string:

““ 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 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 <kloserver name>: <server name\instance name>@<database name>

SQLite

Using SQLite as DBMS, you can reference a database file located in the file system (e.g. C:\temp\db.sqlite). Holding the database in memory only, without creating a database file, is not possible.

When creating an SQL alias, you can use attribute options to set the following SQLite specific options:

BusyTimeout=<time in milliseconds>. The default is 60000 milliseconds. Used to avoid some well known problems.
SQLite PRAGMA statements: Used mainly for performance tuning. Insert PRAGMA statements as a comma-separated list. Details see below.

SQLite Pragma Statements

The SQLite PRAGMA statement is an SQL extension specific to SQLite and used to modify the behavior of the SQLite database. The syntax is sqlite_pragma_<pragma_name>=<pragma_value>.

The main use case is performance tuning. For example, the following PRAGMA options speed up inserting data (but also reduce data safety):

CODE

sqlite_pragma_synchronous=OFF, sqlite_pragma_temp_store=MEMORY, sqlite_pragma_journal_mode=TRUNCATE

sqlite_pragma_synchronous=OFF: Disable wait for writes to complete (may increase performance by factor 50). Potential of database corruption on power failure.
sqlite_pragma_temp_store=MEMORY: Store temporary tables to memory.
sqlite_pragma_journal_mode=TRUNCATE: The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it.

Known Problems Using SQLite

Using the SQL Adapter with SQLite database, you may get the following error:

[SQLSM][6][Error Message: 5 "database is locked". SQL Statement: ...]

This occurs, if multiple threads or processes want to read/write the SQLite database simultaneously. In case of concurrent writes, one write will fail. The xUML Runtime will retry to execute the write for 60 seconds. After the time-out, the message above is written to the bridgeserver log.

There are two possible approaches to solve this conflict (they may also be combined):

Add value BusyTimeout=<time in milliseconds> to attribute options of the SQL alias. Default is 60000 milliseconds. Increase this value.
Re-model the service to have short database transactions (including select) and add explicit commits to unlock the database frequently.

Related Pages:

SQL Adapter