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

• MariaDB
• PostgreSQL
• Microsoft SQL Server

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:

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:

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:

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:

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:

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:

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:

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:

MariaDB configuration parameters

The configuration parameters for the MariaDB connector are:

MariaDB errors

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

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:

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:

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:

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:

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:

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:

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:

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:

PostgreSQL configuration parameters

The configuration parameters for the PostgreSQL connector are:

PostgreSQL errors

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

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:

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:

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:

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:

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:

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:

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:

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:

SQL Server configuration parameters

The configuration parameters for the SQL Server connector are:

SQL Server errors

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