Troubleshooting the SQL Adapter

Read of BLOB fails with error " Can not convert output type (0) of database column '<name of the column>' to internal type 'Blob'".

DB2 with ODBC

The xUML Runtime cannot convert the database type to the xUML base type.

Add the setting LONGDATACOMPAT=1 to your your DB2 ODBC settings.

Query results have wrong encoding.

MySQL

Old versions of MySQL may not respect the codepage setting (e.g. UTF-8).

• Set option CharacterSet=<my codepage setting> on the SQL alias.

• Update your MySQL database to a newer version.

Error in bridgeserver log:
[... 2006: MySQL server has gone away ...]

MySQL

MySQL cannot work on data (commit, execute) because the data exceeds the maximum allowed package size (max_allowed_packet).

Increase max_allowed_packet (e.g. to 128 M) on your installation of MySQL.

CLOB is being cut off after 32767 chars when accessing it with the E2E Bridge.

Oracle

The xUML Runtime cannot convert the database type CLOB to the xUML base type.

• Set tagged value nativeType=CLOB on the parameter of the stored procedure.

• Specify the database type for selects and inserts, e.g.
  INSERT INTO TEST_CLOB
(ID, CDATA)
VALUES (IN::name, IN:{CLOB}:content )

Error when using Oracle and trying to access a CLOB parameter returned by a stored procedure, e.g. "ORA-24508: Buffer is not aligned correctly."

Oracle

Error in bridgeserverlog when requesting database:

• Read timed out (SocketTimeoutException [...]

• Level: Error
Type: E2ERuntime
Code: C02
[...]
Description: Service has a session with an adapter call which runs longer than 30 minutes.

Oracle

Faulty connection string: use of backslash instead of forward slash.

Check the Oracle connection string.

Performance problems: some SQL requests with a WHERE clause are slow. 

Oracle

An existing index that corresponds to the WHERE clause is not used.

This may be a character encoding issue. Please find a more detailed explanation on Oracle: Performance Problems Due to Index Not Used.

When trying to call a stored procedure, you get an error like "HY004 [Microsoft] [ODBC Driver Manager] SQL data type out of range"

Oracle
Caché

Database access encoding problem.

Try accessing the database in Unicode/non-Unicode mode (see SQL Adapter Reference).

Encoding problems with special characters, e.g. the German umlauts, using Oracle on a Unix operating system.

Oracle on Unix

Different encoding on database client and database server is used.

Set NLS_LANG for your Bridge installation on Unix, see Installing and Configuring Database Access for Oracle in our installation guide.

Error when trying to insert a record containing a datetime field, e.g. "22007 The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.".

SQL Server

SQL Server datetime only supports zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

Truncate the microseconds to three digits.

Error while fetching results: After a commit in a bulk fetch loop the cursor disappears, error Abort was called, and object is in a zombie state is thrown.

SQL Server

The default setting in MS SQL is "Set cursor close on commit on".

Create one database session for the bulk fetch and a second database session for the other SQL calls including the commit. Create two aliases, one for each database session.

The SQL handler throws the following error upon executing a database insert:

Bridge error: SQLSM/6
HY104 [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value

SQL Server

A TEXT or [N]VARCHAR(MAX) database field contains character data that is too long.
This may occur when using SQLNCLI/ODBC API for SQL Adapter with MS SQL Server.

Define the related fields to be of type CLOB (instead of String) in your service model.

SQL rollback does not work. Error message is something like
6401 "Cannot roll back [...]. No transaction or savepoint of that name was found."

SQL Server

Improper database settings and rollback statement.

Change the following in your database setup:

• SET XACT_ABORT OFF

Add the following to your rollback statement:

• IF @@TRANCOUNT > 0

Error when using SQL Adapter with SQLite: "[SQLSM][6][Error Message: 5 "database is locked". SQL Statement: ...]"

SQLite

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, see Database Server-Specific Notes for SQL Adapters):

• 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.