DB Connectors

Database Connector Configuration


To configure the Database Connector in GLU, follow these steps using the Properties Tab, shown in Step 2:


Choose the Vendor of the Database you are connecting to:

  1. Enter Default Schema: Input the default schema for the database. Utilise the GLU Variable ${GLU_schemaName} to dynamically retrieve the schema name.
  2. Enter Username: Provide the username required for authenticating the database connection.
  3. Enter Password: Enter the password associated with the provided username for authentication purposes.
  4. Choose Dynamic Datasource: Decide whether to use a dynamic datasource by selecting the tick box accordingly.

By completing these configurations within the Properties Tab of the Database Connector, you can establish a connection to your desired database, ensuring seamless data access and management within the GLU environment.

Query & Stored Procedure

GLU supports either a query or a call to a stored procedure. The syntax of the call to the DB (Database) Connector is determined by the database being accessed. For example, if an Oracle query is being executed, the syntax will be based on SQL*Plus for the version of Oracle that the connected database supports. Similarly, if an Oracle stored procedure is being called, the syntax will be based on Oracle PL/SQL. It’s important to note that this syntax varies depending on the specific database type being used.


Query

In the query, you define the SQL statement you want to execute against the database. You can use the variable names available in the GLU.Engine directly within the query. For example, in the query above, the field “id” has been passed as a request parameter in the API.



Logs Extract

Below is an extract of the logs from a DB Connector showing how the results of multiple rows are presented in JSON:

h2tm 2022-03-05 | 07:44:54.867 | qtp1572820532-68 | DEBUG | g.g.w.i.h.HttpMappingUtils | [TRX_ID : 6cfe81e7-a7ad-4678-936e-ab51d80e691d] RESPONSE PAYLOAD Orchestration : [mysql Select][{“lastName”:”Cluck Norris”},{“lastName”:”Dixie Chick”},{“lastName”:”Egghead”},{“lastName”:”Foghorn Leghorn”}]

This log entry indicates that the database query was executed successfully, and the results are presented in JSON format. Each row of the result set is represented as a JSON object, with the “lastName” field containing the corresponding value from the database.

Note the section on unmarshalling for how to unmarshall from a query.


Stored Procedure

To call a database stored procedure, select “Stored Procedure” and provide the syntax for the stored procedure name.



In the example, the following stored procedure is being called in the schema “gludb”. Note that the schema “gludb” could be replaced by the GLU variable ${GLU_schemaName}.

The format is made of:

DB/Schema name.SPname(input variables in order ,OUT output variables)

  • Input variables must each be defined by their datatype. e.g. VARCHAR ${name}, INT ${amount}, DATE ${timestamp}
  • Output variables aren’t always expected in an stored procedure, only add them if expected.

Example of MySQL Stored Procedure:

DELIMITER ;;
CREATE DEFINER=admin@% PROCEDURE testParam(IN chickens VARCHAR(30), OUT lastName VARCHAR(30))
BEGIN

if chickens = 5780

then
SELECT surname as lastName
FROM pet
WHERE postalCode=chickens;
else
SELECT surname as lastName
FROM pet
WHERE ID=chickens
LIMIT 1;

end if;

END;;
DELIMITER ;

As with Query DB Connectors for Stored Procedure, it is not necessary to define Request Parameters for the variables referred to in the query.

Some other examples of a stored procedure input:

${GLU_schemaName}.dbo.usp_addWeather(VARCHAR ${todaytemp}, DATE ${dateDefault}, VARCHAR ${abovemax}, VARCHAR ${belowmax}, OUT VARCHAR response)

OR

${GLU_schemaName}.dbo.sp_TestSQLReturn(INTEGER ${channelId}, INTEGER ${vid}, OUT VARCHAR response )


Logs Extract

Below is a JSON example of the structure of the results payload. The array with data is “#result-set-1”:

{

“#result-set-1”: [
{
“lastName”: “Cluck Norris”
},
{
“lastName”: “Dixie Chick”
},
{
“lastName”: “Egghead”
},
{
“lastName”: “Foghorn Leghorn”
}
],
“#update-count-1”: 0,
“surname”: null
}

Log extract example:

udc0m 2022-03-06 | 11:08:06.843 | qtp589311025-63 | DEBUG | g.g.w.i.h.HttpMappingUtils | [TRX_ID : a4cdc5a6-2f80-4619-931a-f565f674bccf] RESPONSE PAYLOAD Orchestration : [mysql SP]{"#result-set-1":[{"lastName":"Cluck Norris"},{"lastName":"Dixie Chick"},{"lastName":"Egghead"},{"lastName":"Foghorn Leghorn"}],"#update-count-1":0,"surname":null}

Additionally, the log extract provides insight into the response payload received from the database stored procedure call. It includes the transaction ID, timestamp, and debug information indicating the response payload type (in this case, “mysql SP”) and the actual response data.

Marshalling

When using date request parameters, you can marshal the date format into the required format for the database. This ensures compatibility and consistency when interacting with the database. The example below is marshaling into a MySQL Timestamp parameter type on a stored procedure:

e.g. For a MS SQL database, the date marshalling format to use is: yyyy-MM-dd HH:mm:ss

Unmarshalling

Unmarshalling is a important step in processing responses received from connectors, and it’s essential to configure response parameters correctly for both Query and Stored Procedure responses.

Response Parameters

For both Query and Stored Procedure responses, the properties defining where the connector values are stored in the GLU.Engine are the same. As multiple rows of values within arrays will be returned from the DB connector it will be necessary to define a Parameter Name and an Object / Collection Path as an array to store the values.

Query

  • Enter the name of the attribute returned from the Query in the “Attribute(s) Name” field.
  • In the “Object/Collection Path,” use [] to designate an array where multiple values will be stored from the query.
  • As per regular array unmarshalling, if you are only taking the first response record, or if there is only ONE response record, you would unmarshall from that record in the array: [0]

Stored Procedure

  • Enter the name of the attribute returned from the Stored Procedure in the “Attribute(s) Name” field.
  • In the “Object/Collection Path,” use #result-set-1[]. This is a reserved word in GLU.Ware and will always contain the data returned from a stored procedure. Ensure that the Attribute name matches the outbound parameters (OUT) defined in the Stored Procedure call definition.
  • It can be helpful to check your log to see how the response payload is converted to json – it is here that you will see whether you need to add #result-set-1[] or not.

Console Fields not used

When configuring DB Connectors, certain fields in the Control Panel are not utilized and can be disregarded:

  1. Ignore unmarshalling from a Query, Header, Body, or file: This option is not applicable for DB Connectors. It is used for other types of connectors to specify whether to ignore unmarshalling from various sources such as queries, headers, bodies, or files.
  2. Ignore Store All Attributes as Key And value: Similarly, this option is not used for DB Connectors. It is typically employed for other types of connectors to determine whether to ignore storing all attributes as key-value pairs.
  3. Delimiter: This field currently has no functionality for a DB Connector. You can safely ignore it during configuration.

By recognising that these fields are not relevant for DB Connectors, you can focus on configuring the parameters that are essential for the proper functioning of your database integration.

Was this article helpful?

Related Articles

Fill the form and we’ll contact you shortly

    I agree with

    cookies
    We uses cookies to make your experience on this website better. Learn more
    Accept cookies