# Scripts

You can use Scripts to execute java code or to invoke a stored procedure on your target data lake e.g. Redshift or Snowflake after every flow run. You can setup different script to run based on the status of the flow run.

To obtain the connection to the target data lake instance use `db.getTargetDBConnection()` and `db.getSourceDBConnection()` to get the connection to the source instance provided you use any relational datasource as the input for the flow.

![Setup Script Page](https://272493989-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LyGZIunpED9t56ZtLzh%2F-LyKGHWcwNJmpsGymohR%2F-LyKGrKcp_CpEanEpXaK%2FScreen%20Shot%202020-01-11%20at%207.42.29%20AM.png?alt=media\&token=970a3bc8-78c1-4c38-a63e-447a2f53b4be)

1. Script Name
2. Script Status
3. Schedule Type
4. Script written in Java and/or Groovy
5. View Script Run History

### Variables Available

The following are the variables available to the script

| Variable          | Description                                                                                       |
| ----------------- | ------------------------------------------------------------------------------------------------- |
| `flowCode`        | Flow code                                                                                         |
| `outputEventType` | Table name in the target DB                                                                       |
| `inputEventType`  | Table/Object name in the source system                                                            |
| `inputType`       | Input Connector Type e.g. Oracle, MySQL                                                           |
| `outputType`      | Output Connector Type e.g. Oracle, MySQL                                                          |
| `logger`          | Log4j logger for debug purpose                                                                    |
| `db`              | An instance of [FlowScript](https://flow-docs.cloudio.io/advanced-topics/post-process#flowscript) |

### Redshift Stored Procedure Exception Handling

When calling a stored procedure on redshift the connection would become stale when an exception occurs. If you want to execute another procedure or SQL from the catch block, use `db.resetAndGetTargetDBConnection()` to close the existing stale connection and get a new connection for perform additional calls.

### Sample Script to invoke a Stored Procedure in Redshift

```groovy
    try {
      Map result = SQL.createCall(db.getTargetDBConnection(), "sp_cp_1()")
              .execute();
    } catch (Exception e) {
      try {
        String msg = "Error-" + System.currentTimeMillis() + ": " + e.getMessage();
        // call resetAndGetTargetDBConnection instead of getTargetDBConnection,
        // as the connection is getting stale in case of exception
        SQL.createCall(db.resetAndGetTargetDBConnection(), "sp_cp_y(?)")
                .bind(1, msg)
                .registerOutParameter(1, java.sql.Types.VARCHAR).execute();
      } catch (Exception exp) {
        logger.error(exp);
      }
    }
```

### Invoking REST API from Scripts

Apart from performing JDBC calls, you can use [Unirest](http://kong.github.io/unirest-java/) to make any REST web-service call from scripts

```java
Map result = Unirest.post("https://example.com")
    .basicAuth("user", "password")
    .asObject(Mapord.class)
    .getBody();
```