# ADO Database Engine (Engine `ado`)

Via the ADO database engine, the possibility is provided to access the database within the 3-tier architecture.
This is done by directing the client's SQL query to the application server, which executes the query and sends the result as an ADO recordset (Active Data Object) in XML representation to the client.

This functionality is particularly used for SQL queries to formulate requests beyond the normal search capabilities of the client.

Only data manipulations (INSERT, UPDATE, DELETE) can be performed via this interface if this is enabled in the registry of the application server.

* [ado.ExecuteSQL](ado.md#ado.ExecuteSQL)

## Undocumented Endpoints

The following jobs are offered by the enaio® server but are not currently described in this documentation:

* `ado.ExecuteClientQuery`

<a id="ado.ExecuteSQL"></a>

## ado.ExecuteSQL

This job executes an SQL statement on the database.
As a result, an XML file with the response to the query is created and written to the OSTEMP directory.

If the job is executed in a Unicode system (database migrated to Unicode), missing `N` before texts are automatically added; already written `N` are ignored.
To completely disable this behavior, `Flags=2` must be passed.

**Example of automatic Unicode supplementation**

```sql
SELECT 'Horst' FROM test WHERE feld='Paul'
=>
SELECT N'Horst' FROM test WHERE feld=N'Paul'
```

### Input Parameters

| Name | Type | Required | Description |
|---|---|---|---|
| `Flags` | LONG | Yes | `0` = texts are automatically supplemented with `N`; `2` = no automatic replacements |
| `CursorType` | INT | Yes | `-1` = cursor type as specified in the registry (default); `0`, `1`, `2`, `3` = corresponding to ADO constants for cursor types. Other values result in error messages. |
| `Command` | STRING | Yes | SQL command to execute |

### Output Files

| Name | Description |
|---|---|
| `[File List]` | Path and name of the result file in XML format |

### Return Value

`(INT)`: `0` = Job successful, otherwise error code.

**Returned XML file for `SELECT * FROM osorganisations`**

```xml
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
     xmlns:rs="urn:schemas-microsoft-com:rowset"
     xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30" rs:updatable="true">
      <s:AttributeType name="id" rs:number="1" rs:nullable="true" rs:writeunknown="true"
                       rs:basecatalog="as_test" rs:basetable="osorganisations" rs:basecolumn="id">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="32"/>
      </s:AttributeType>
      <s:AttributeType name="name" rs:number="2" rs:nullable="true" rs:writeunknown="true"
                       rs:basecatalog="as_test" rs:basetable="osorganisations" rs:basecolumn="name">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
      </s:AttributeType>
      <s:AttributeType name="layout" rs:number="3" rs:nullable="true" rs:maydefer="true"
                       rs:writeunknown="true" rs:basecatalog="as_test"
                       rs:basetable="osorganisations" rs:basecolumn="layout">
        <s:datatype dt:type="bin.hex" dt:maxLength="2147483647" rs:long="true"/>
      </s:AttributeType>
      <s:AttributeType name="active" rs:number="4" rs:nullable="true" rs:writeunknown="true"
                       rs:basecatalog="as_test" rs:basetable="osorganisations" rs:basecolumn="active">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
      </s:AttributeType>
      <s:extends type="rs:rowbase"/>
    </s:ElementType>
  </s:Schema>
  <rs:data>
    <z:row id="45808CE977334AB88C5A8EFF467689A8" name="Test" active="1"/>
  </rs:data>
</xml>
```

### Required Roles

The caller needs the following [system role](mng.md):

* `88` — Server: Execute Ado jobs