Database connectors

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

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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. A JSON object containing the selected data.

MariaDB configuration parameters

The configuration parameters for the MariaDB connector are:

ParameterDescription
DB_USERNAMERequired. The database user to execute the statement as, for example connector-user.
DB_PASSWORDRequired. The password for the database user executing the statement.
MARIADB_HOSTOptional. The host address of the database, for example mariadb.example.com.
MARIADB_PORTOptional. The port of the hosted database, for example 421.
DB_NAMEOptional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCERequired. 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_NAMEOptional. 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:

ErrorDescription
MISSING_INPUTA mandatory input variable was not provided.
INVALID_INPUTThe input variable has an invalid type.
DATA_ACCESS_ERRORUnable to access data.
DATA_INTEGRITY_VIOLATION_ERRORData integrity violation error occurs when performing database operation.
CONNECTION_ERRORCannot connect to a database instance.
SQL_GRAMMAR_ERRORInvalid syntax error.
DUPLICATE_KEY_ERRORDuplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROROptimistic error occurs when performing database operation.
DEAD_LOCK_ERRORDeadlock error occurs when performing database operation.
PERMISSION_DENIED_ERRORLack of permission to the resource and method requested.
UNKNOWN_ERRORUnexpected runtime error.

Oracle

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

Oracle insert

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

The input parameters for an insert statement are:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. The number of rows in the database that were affected by the statement.

Oracle update

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

The input parameters for an update statement are:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. The number of rows in the database that were affected by the statement.

Oracle delete

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

The input parameters for a delete statement are:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. The number of rows in the database that were affected by the statement.

Oracle select

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

The input parameters for an insert statement are:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. A JSON object containing the selected data.

Oracle configuration parameters

The configuration parameters for the Oracle connector are:

ParameterDescription
DB_USERNAMERequired. The database user to execute the statement as, for example connector-user.
DB_PASSWORDRequired. The password for the database user executing the statement.
ORACLE_HOSTOptional. The host address of the database, for example oracle.example.com.
ORACLE_PORTOptional. The port of the hosted database, for example 421.
DB_NAMEOptional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCERequired. The database datasource, the default value is a concatenation of ORACLE_HOST, ORACLE_PORT and DB_NAME: jdbc:oracle:thin:@//${ORACLE_HOST}:${ORACLE_PORT}/${DB_NAME}.
DB_DRIVER_CLASS_NAMEOptional. The database driver to use. The default value is oracle.jdbc.OracleDriver.

Oracle errors

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

ErrorDescription
MISSING_INPUTA mandatory input variable was not provided.
INVALID_INPUTThe input variable has an invalid type.
DATA_ACCESS_ERRORUnable to access data.
DATA_INTEGRITY_VIOLATION_ERRORData integrity violation error occurs when performing database operation.
CONNECTION_ERRORCannot connect to a database instance.
SQL_GRAMMAR_ERRORInvalid syntax error.
DUPLICATE_KEY_ERRORDuplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROROptimistic error occurs when performing database operation.
DEAD_LOCK_ERRORDeadlock error occurs when performing database operation.
PERMISSION_DENIED_ERRORLack of permission to the resource and method requested.
UNKNOWN_ERRORUnexpected 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. 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:

ParameterTypeDescription
queryStringRequired. The query to execute against the database.
metadataJSONOptional. 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:

ParameterTypeDescription
resultIntegerOptional. A JSON object containing the selected data.

PostgreSQL configuration parameters

The configuration parameters for the PostgreSQL connector are:

ParameterDescription
DB_USERNAMERequired. The database user to execute the statement as, for example connector-user.
DB_PASSWORDRequired. The password for the database user executing the statement.
POSTGRES_HOSTOptional. The host address of the database, for example postgres.example.com.
POSTGRES_PORTOptional. The port of the hosted database, for example 421.
DB_NAMEOptional. The name of the database to execute the statement against, for example inventory.
DB_DATASOURCERequired. 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_NAMEOptional. 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:

ErrorDescription
MISSING_INPUTA mandatory input variable was not provided.
INVALID_INPUTThe input variable has an invalid type.
DATA_ACCESS_ERRORUnable to access data.
DATA_INTEGRITY_VIOLATION_ERRORData integrity violation error occurs when performing database operation.
CONNECTION_ERRORCannot connect to a database instance.
SQL_GRAMMAR_ERRORInvalid syntax error.
DUPLICATE_KEY_ERRORDuplicate key error occurs when performing database operation.
OPTIMISTIC_LOCK_ERROROptimistic error occurs when performing database operation.
DEAD_LOCK_ERRORDeadlock error occurs when performing database operation.
PERMISSION_DENIED_ERRORLack of permission to the resource and method requested.
UNKNOWN_ERRORUnexpected runtime error.

© 2023 Alfresco Software, Inc. All Rights Reserved.