DB Commands (Magic xpa 2.x)

When processing a large number of records in a Magic xpa task, such as in a Batch task that scans a file, it is possible to change the default setting by changing the Cursor property in the Data Source Properties dialog box to No. This will cause the gateway to use DB commands instead of cursors, requiring separate connections for each result set. The maximum number of connections is user-defined, and the default setting for the maximum number of connections is 3.

The gateway can send only one DB command in the current connection (in addition to numerous cursors). If another DB command should be issued, a new connection will be opened. DB commands are used for commands that execute a single record or command, such as fetching a linked record, CREATE TABLE command, or a executing a Direct SQL task. Generally speaking, the greater the number of connections the gateway uses, the better the client performance. However, at the same time memory requirements increase while server performance decreases. If all of the existing connections are already used by a pending command and a new connection is needed, an existing connection will be freed for your use according to the LRU algorithm. This can affect performance adversely, because the released command will eventually be reissued.

If a Direct SQL Batch task is used (Stored Procedure or Select statement), the gateway is unable to reuse this connection until all the results have been fetched. If nested direct SQL Batch tasks are used, and the maximum number of connections the gateway can use is not enough, the following error message will appear:

MS-SQL Gateway: No more connections available.

If this happens, try increasing Max Connections in the DBMS properties. If this error message still appears, either increase the value of the parameter noted in the error message or modify your project, so that it does not use the nested Direct SQL tasks.