Database Connector Configuration

Use the Properties Tab within Step 2 of your Database Connector configuration.

Choose the Vendor of the Database you are connecting to:

1. Enter the Default Scheme * The GLU Variable ${GLU_schemaName}
2. Enter the UserName
3. Enter the Password
4. Choose whether Dynamic Datasource or not by selecting the tick box.

Marshalling

When using date request parameters, you can marshal the date format into the required format for the DB. The example below is marshaling into a MySQL Timestamp parameter type on a stored procedure.

Query & Stored Procedure

GLU Supports either a Query or a call to a Stored Procedure. The syntax of the call to the DB Connector is defined by the Database being called i.e. If Oracle Query is being called then the syntax will be based on SQL*Plus for the version of Oracle which the DB being connected to supports. If an Oracle stored procedure is being called then the syntax of the call will be based on Oracle PL/SQL. This syntax is specific to the different database types being used.

Query

In the query, the box defines the query which you would like to execute against the Database. Use the variable names available in the GLU.Engine directly in the query. In this example above the field id has been passed as a Request Parameter in the API.

Logs Extract – As can be seen below the extract of the logs from a DB Connector shows 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"}] 

Stored Procedure

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

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

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.

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
}

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}

It is possible with the logs to see the data results.

Unmarshalling

Response Parameters

The properties define where the values from the connector are placed in the GLU.Engine are the same for Query and Store Procedure. 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 which is returned from the Query in Attribute(s) Name. In the Object / Collection Path, enter [] to define an array where multiple values will be stored from the query.

Stored Procedure

Enter the name of the attribute which is returned from the Stored Procedure in Attribute(s) Name. Always enter the value #result-set-1[] in the Object / Collection Path. (#result-set-1[] is a Reserved Word in GLU.Ware as it will always contain the data which is returned from a stored procedure). The Attribute name must match the outbound parameters (OUT) defined in the Stored Procedure call definition.

Console Fields not used

The following fields in the Control Panel are not used for DB Connectors.

Ignore unmarshal from a Query, Header, Body, or file, this is not used for DB Connectors.

Ignore Store All Attributes as Key And value, this is not used for DB Connectors.

Delimiter – Currently this doesn’t do anything for a DB Connector and can be ignored.