Close
Alfresco Process Automation

Database connectors

There are three database connectors that can be used to execute queries against:

Note: The Oracle connector has been removed due to issues with its licensing.

All database connectors are displayed on the process diagram with their respective logos.

Important: All databases should be hosted outside of the Alfresco hosted environment and should be created and managed by customers.

Note: All queries are sent as prepared statements using parameters. SQL injection is not possible.

MariaDB

The MariaDB connector has four actions it can execute: INSERT, UPDATE, DELETE and SELECT.

MariaDB insert

The MariaDB INSERT action is used to execute an insert statement against a MariaDB database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

INSERT INTO flavors (flavor)
VALUES ({type});

Note: The { } are declared without quotations.

The output parameters from an insert statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

MariaDB update

The MariaDB UPDATE action is used to execute an update statement against a MariaDB database.

The input parameters for an update statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

UPDATE flavors
SET flavor = {type}
WHERE flavor = "mint-choc";

Note: The { } are declared without quotations.

The output parameters from an update statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

MariaDB delete

The MariaDB DELETE action is used to execute a delete statement against a MariaDB database.

The input parameters for a delete statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

DELETE FROM flavors
WHERE flavor = {type};

Note: The { } are declared without quotations.

The output parameters from a delete statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

MariaDB select

The MariaDB SELECT action is used to execute a select query against a MariaDB database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

SELECT TOP 10 * FROM flavors
WHERE flavor = {type}
ORDER BY ingredients DESC;

Note: The { } are declared without quotations.

The output parameters from a select query are:

Parameter Type Description
result Integer Optional. A JSON object containing the selected data.

MariaDB configuration parameters

The configuration parameters for the MariaDB connector are:

Parameter Description
DB_USERNAME Required. The database user to execute the statement as, for example connector-user.
DB_PASSWORD Required. The password for the database user executing the statement.
MARIADB_HOST Optional. The host address of the database, for example mariadb.example.com.
MARIADB_PORT Optional. The port of the hosted database, for example 421.
DB_NAME Optional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCE Required. The database datasource, the default value is a concatenation of MARIADB_HOST, MARIADB_PORT and DB_NAME: jdbc:mysql://${MARIADB_HOST}:${MARIADB_PORT}/${DB_NAME}.
DB_DRIVER_CLASS_NAME Optional. The database driver to use. The default value is org.mariadb.jdbc.Driver.

MariaDB errors

The possible errors that can be handled by the MariaDB connector are:

Error Description
MISSING_INPUT A mandatory input variable was not provided.
INVALID_INPUT The input variable has an invalid type.
DATA_ACCESS_ERROR Unable to access data.
DATA_INTEGRITY_VIOLATION_ERROR Data integrity violation error occurs when performing database operation.
CONNECTION_ERROR Cannot connect to a database instance.
SQL_GRAMMAR_ERROR Invalid syntax error.
DUPLICATE_KEY_ERROR Duplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROR Optimistic error occurs when performing database operation.
DEAD_LOCK_ERROR Deadlock error occurs when performing database operation.
PERMISSION_DENIED_ERROR Lack of permission to the resource and method requested.
UNKNOWN_ERROR Unexpected runtime error.

PostgreSQL

The PostgreSQL connector has four actions it can execute: INSERT, UPDATE, DELETE and SELECT.

PostgreSQL insert

The PostgreSQL INSERT action is used to execute an insert statement against a PostgreSQL database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

INSERT INTO flavors (flavor)
VALUES ({type});

Note: The { } are declared without quotations.

The output parameters from an insert statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

PostgreSQL update

The PostgreSQL UPDATE action is used to execute an update statement against a PostgreSQL database.

The input parameters for an update statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

UPDATE flavors
SET flavor = {type}
WHERE flavor = "mint-choc";

Note: The { } are declared without quotations.

The output parameters from an update statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

PostgreSQL delete

The PostgreSQL DELETE action is used to execute a delete statement against a PostgreSQL database.

The input parameters for a delete statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

DELETE FROM flavors
WHERE flavor = {type};

Note: The { } are declared without quotations.

The output parameters from a delete statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

PostgreSQL select

The PostgreSQL SELECT action is used to execute a select query against a PostgreSQL database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

SELECT TOP 10 * FROM flavors
WHERE flavor = {type}
ORDER BY ingredients DESC;

Note: The { } are declared without quotations.

The output parameters from a select query are:

Parameter Type Description
result Integer Optional. A JSON object containing the selected data.

PostgreSQL configuration parameters

The configuration parameters for the PostgreSQL connector are:

Parameter Description
DB_USERNAME Required. The database user to execute the statement as, for example connector-user.
DB_PASSWORD Required. The password for the database user executing the statement.
POSTGRES_HOST Optional. The host address of the database, for example postgres.example.com.
POSTGRES_PORT Optional. The port of the hosted database, for example 421.
DB_NAME Optional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCE Required. The database datasource, the default value is a concatenation of POSTGRES_HOST, POSTGRES_PORT and DB_NAME: jdbc:postgresql://${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}.
DB_DRIVER_CLASS_NAME Optional. The database driver to use. The default value is org.postgresql.Driver.

PostgreSQL errors

The possible errors that can be handled by the PostgreSQL connector are:

Error Description
MISSING_INPUT A mandatory input variable was not provided.
INVALID_INPUT The input variable has an invalid type.
DATA_ACCESS_ERROR Unable to access data.
DATA_INTEGRITY_VIOLATION_ERROR Data integrity violation error occurs when performing database operation.
CONNECTION_ERROR Cannot connect to a database instance.
SQL_GRAMMAR_ERROR Invalid syntax error.
DUPLICATE_KEY_ERROR Duplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROR Optimistic error occurs when performing database operation.
DEAD_LOCK_ERROR Deadlock error occurs when performing database operation.
PERMISSION_DENIED_ERROR Lack of permission to the resource and method requested.
UNKNOWN_ERROR Unexpected runtime error.

Microsoft SQL Server

The Microsoft SQL Server connector has four actions it can execute: INSERT, UPDATE, DELETE and SELECT.

SQL Server insert

The SQL Server INSERT action is used to execute an insert statement against a SQL Server database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

INSERT INTO flavors (flavor)
VALUES ({type});

Note: The { } are declared without quotations.

The output parameters from an insert statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

SQL Server update

The SQL Server UPDATE action is used to execute an update statement against a SQL Server database.

The input parameters for an update statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

UPDATE flavors
SET flavor = {type}
WHERE flavor = "mint-choc";

Note: The { } are declared without quotations.

The output parameters from an update statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

SQL Server delete

The SQL Server DELETE action is used to execute a delete statement against a SQL Server database.

The input parameters for a delete statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

DELETE FROM flavors
WHERE flavor = {type};

Note: The { } are declared without quotations.

The output parameters from a delete statement are:

Parameter Type Description
result Integer Optional. The number of rows in the database that were affected by the statement.

SQL Server select

The SQL Server SELECT action is used to execute a select query against a SQL Server database.

The input parameters for an insert statement are:

Parameter Type Description
query String Required. The query to execute against the database.
metadata JSON Optional. The list of parameters to pass to the query.

The metadata parameter can be used to pass variables into the query parameter.

For example, if the contents of metadata are:

{
"type" : "mint"
}

This can be used in the query parameter as:

SELECT TOP 10 * FROM flavors
WHERE flavor = {type}
ORDER BY ingredients DESC;

Note: The { } are declared without quotations.

The output parameters from a select query are:

Parameter Type Description
result Integer Optional. A JSON object containing the selected data.

SQL Server configuration parameters

The configuration parameters for the SQL Server connector are:

Parameter Description
DB_USERNAME Required. The database user to execute the statement as, for example connector-user.
DB_PASSWORD Required. The password for the database user executing the statement.
SQLSERVER_HOST Optional. The host address of the database, for example sqlserver.example.com.
SQLSERVER_PORT Optional. The port of the hosted database, for example 421.
DB_NAME Optional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCE Required. The database datasource, the default value is a concatenation of SQLSERVER_HOST, SQLSERVER_PORT and DB_NAME: jdbc:sqlserver://${SQLSERVER_HOST}:${SQLSERVER_PORT}/${DB_NAME}.
DB_DRIVER_CLASS_NAME Optional. The database driver to use. The default value is com.microsoft.sqlserver.jdbc.SQLServerDriver.

SQL Server errors

The possible errors that can be handled by the SQL Server connector are:

Error Description
MISSING_INPUT A mandatory input variable was not provided.
INVALID_INPUT The input variable has an invalid type.
DATA_ACCESS_ERROR Unable to access data.
DATA_INTEGRITY_VIOLATION_ERROR Data integrity violation error occurs when performing database operation.
CONNECTION_ERROR Cannot connect to a database instance.
SQL_GRAMMAR_ERROR Invalid syntax error.
DUPLICATE_KEY_ERROR Duplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROR Optimistic error occurs when performing database operation.
DEAD_LOCK_ERROR Deadlock error occurs when performing database operation.
PERMISSION_DENIED_ERROR Lack of permission to the resource and method requested.
UNKNOWN_ERROR Unexpected runtime error.

Edit this page

Suggest an edit on GitHub
By clicking "Accept Cookies", you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View Cookie Policy.