Connect Oracle Digital Assistant to Oracle ATP Database

From my previous post, i showed you how to connect to Oracle ATP database using Node.js.

We shall take advantage of this capability and build a custom  component to connect a digital assistant skill to the ATP database.

Prerequisites:

Use Case

I created a simple Event booking skill which run queries on ATP database.

If you managed to connect to ATP database using Node.js then you are up to this task also!

My pseudo persona, Jamie Foxx, will use the Oracle digital assistant to book seats on the events near him. This will update the Bookings column on the EVENTS table in ATP. It will also add a new record on the RESERVATIONS table.

On the bot:

oda4

On the ATP database an update is done on the Bookings column of EVENTS table (viewing using SQL Developer):

oda5

A new record is added on the RESERVATIONS table:

oda6

Validation:

You cannot book more than the seats available.

Ex. Jamie Foxx has booked two seats at Jazz Festival, so 198 seats are remaining now, lets try and book 201 seats for the same event  🙂

oda7

How was this done?

Interested? Let’s walk through the simple architecture: 🙂

IMG_4152

Create a Event Skill bot in ODA

My skill has 3 intents; Bookings, Greetings, SearchEvents.

I have implemented a resolveEntities system component to extract event name and number of seats entities from the user input in a composite bag.

metadata:
  platformVersion: "1.0"
main: true
name: LN_Events
#context: Define the variables which will used throughout the dialog flow here.
context:
  variables:
    iResult: "nlpresult"
    variableMakeReservations: "MakeReservations" #Composite bag
    name : "string"
    eventid : "string"
    seatstoReserve : "string"
    paid : "boolean"
    bookings : "string"
    eventname : "string"
    remainingSeats : "string"
    events: "Events"

states:

 ############## Intents #######################

  intent:
    component: "System.Intent"
    properties: 
      variable: "iResult"
    transitions:
      actions:
        Greetings: "startGreeting"
        Bookings: "startBookings"
        SearchEvents: "searchEvents"
        unresolvedIntent: "unresolvedState"
        

#########################################################
        
  startGreeting:
    component: "System.Output"
    properties:
      text: "Hi Jamie Foxx, I am your online Events Booking Bot, i can help you reserve seats on the available events, also trust me, I can facilitate your payments!"
    transitions:
      return: "startGreeting"

#########################################################

  searchEvents:
    component: "System.List"
    properties:
      prompt: "Hi Jamie Foxx, here are the events near your location. Which one would you wish to book?"
      options: "${events.type.enumValues}"
      variable: "events"        
    transitions: 
      next: "setEventName"
 
 ################################
  setEventName:
    component: "System.SetVariable"
    properties:
      variable: "variableMakeReservations.BagEvent"
      value: "${events.value}"

#########################################################      
 
  startBookings:
    component: "System.SetVariable"
    properties:
      variable: "name"
      value: "Jamie Foxx"
    transitions:
      next: "resolveEntities"
        
############# Resolve entities ############################

  resolveEntities:
    component: "System.ResolveEntities"
    properties:
      #transitionAfterMatch: true
      variable: "variableMakeReservations"
      nlpResultVariable: "iResult"      
      maxPrompts: 2
      cancelPolicy: "immediate" 
    transitions:
      actions:
        #match: "" 
        cancel: "maxError"
        next: "submitBooking"      
      

 ######################################################  
 
  submitBooking: #This is the custom component
    component: "makeReservation" 
    properties:
     name : "${name}"
     eventname : "${variableMakeReservations.value.BagEvent}"
     seatstoReserve : "${variableMakeReservations.value.BagNumber.number}"
    transitions:
      next: "endsubmitBooking"      

##########################################################


  endsubmitBooking:
    component: "System.ResetVariables"
    properties:      
      variableList: "iResult,variableMakeReservations,name,eventid,seatstoReserve,eventname,remainingSeats,Events"
    transitions:
      return: "done"


##################################################

  output: #For debuging purposes
    component: "System.Output"
    properties:
      text: "You have reserved ${variableMakeReservations.value.BagNumber.number} seats at ${variableMakeReservations.value.BagEvent}"
    transitions:
      #next: "savetoATP"
      next: "endsubmitBooking"
      
##################################################

  maxError:
    component: "System.Output"
    properties:
      text: "Sorry the input is invalid"
    transitions:
      return: "maxError"

######################################################

  unresolvedState:
    component: "System.Output"
    properties:
      text: "Sorry,i didn't quite get that, could you try again?"
      keepTurn: false
    transitions:
      return: "unresolvedState"     

You can download the skill here.

Create a Custom Component

From the YAML file above, we can see that we have a custom component called “makeReservation

I scripted an ODA custom component  running on a Node.js server to get the inputs from the skill and perform CRUD operations on the ATP database.

Here is my custom component, named makeReservation.js I have tried to comment on my code to make it easier to understand 🙂

The inputs from the Event skill custom component includes; name, eventname, seatstoReserve.

After the logic the response is sent back to the bot using the “sdk.reply(message);”

require ('custom-env').env('stagging'); //find the .env.stagging file and place the right location of your wallet

var async = require('async');

var oracledb = require('oracledb');

var dbConfig = require('./../dbconfig.js');

//variables

let name;

let eventid;

let seatstoReserve;

let paid = 'No';

let bookings;

let eventname;

let remainingSeats;

let message;

module.exports = {

metadata: function metadata() {

return {

"name": "makeReservation",

"channels": {

"facebook": "1.0",

"webhook": "1.0"

},

"properties": {

"name": {"type":"string", "required": true},

"eventname": {"type":"string", "required": true},

"seatstoReserve": {"type":"string", "required": true}

},

"supportedActions": []

};

},

invoke: function invoke(sdk, done) {

    

//console.log('Check User Payload: ' + JSON.stringify(sdk.payload()));

name = sdk.properties().name;

eventname = sdk.properties().eventname;

seatstoReserve = parseInt(sdk.properties().seatstoReserve);

console.log("DATA: " + eventname +": "+ seatstoReserve);




////Start Functions

var doconnect = function(cb) {

oracledb.getConnection({

user: dbConfig.dbuser,

password: dbConfig.dbpassword,

connectString: dbConfig.connectString

},

cb);

};

var dorelease = function(conn) {

conn.close(function (err) {

if (err)

console.error(err.message);

});

};

var doCheckAvailability = function(conn, cb){

console.log(`check availability at ${eventname}`);

conn.execute(

`SELECT EVENTID, BOOKINGS, (SEATS - BOOKINGS) AS REMAININGSEATS FROM EVENTS WHERE eventname = :v`,

[eventname],

function(err, result)

{

if (err) { console.error(err); return cb(err, conn); }

console.log(JSON.stringify(result.rows));




eventid = JSON.stringify(result.rows[0][0]);

remainingSeats = JSON.stringify(result.rows[0][2]);




bookings= JSON.stringify(result.rows[0][1]);

console.log('Event ID: '+ eventid);

console.log('Event Name: '+ eventname);

console.log('Bookings: '+ bookings);

console.log('Remaining Seats: '+ remainingSeats);

return cb(null, conn);




});

}

var doinsert = function (conn, cb) {




//before you add a reservation, check if there are seats available

if (parseInt(seatstoReserve) > parseInt(remainingSeats)){

message = `Sorry, you can't book ${seatstoReserve} seats at ${eventname}. Unfortunately we do not have enough seats available... the available seats remaining are ${remainingSeats}`;

console.log(`From console: log Sorry, you can't book ${seatstoReserve} seats at ${eventname}. No enough seats available... the available seats are ${remainingSeats}`);




dorelease(conn);

//return false;

}else{

var data = [name,eventid,paid,seatstoReserve]

conn.executeMany(

"INSERT INTO RESERVATIONS VALUES (:1, :2, :3, :4)",

[data], // bind the JSON string for inserting into the JSON column.

{ autoCommit: true },

function(err) {

if (err) {

return cb(err, conn);

} else {

//console.log("Data Inserted");

message ="Reservation done succesifully for: "+ eventname +": "+ seatstoReserve+" seat(s)";

//return cb(null, conn);

}

});




}

return cb(null, conn);

};

var doUpdateBooking = function(conn, cb){

bookings = parseInt(bookings) + parseInt(seatstoReserve);

var Updatedata = [bookings, eventid]

console.log(Updatedata);

conn.executeMany(

`UPDATE EVENTS SET BOOKINGS = :1 WHERE EVENTID = :2`, // WHERE eventid =21

[Updatedata],

{ autoCommit: true },

function(err) {

if (err) {

return cb(err, conn);

} else {

console.log("Data Updated!");

return cb(null, conn);

}

} );

};

////Run the logic here using waterfall

async.waterfall(

[

doconnect,// Does the connection -Very key!

doCheckAvailability, //Check if seats are available

doinsert, //Add reservation

doUpdateBooking //Update the bookings in Events table

],

function (err, conn) {

if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }

if (conn)

dorelease(conn);




//reply to the bot!

sdk.reply(message);

sdk.transition("endsubmitBooking");

sdk.keepTurn(true);

done();

});




    }

};

From the code above, you need to define dbCofig.js -where you store you connection details (Just like from my previous post) and then define environment variables on a .env file.

The dbconfig.js file:
module.exports= {

dbuser: 'admin',

dbpassword: 'Your Password',

connectString: 'YourDatabaseName_TP'

}

On my .env.stagging file indicates where you have stored your ATP connection wallet.

TNS_ADMIN='c:\wallets'

On my main file which is event_api.js you need to spin up the Node server using express() Node library:

require ('custom-env').env('stagging') //either stagging or production

const apiURL = '/mobile/atp/components';

// Reference component shell

var shell = require('./shell')();

const express = require('express')

const bodyParser = require('body-parser')

const service = express()

const request = require('request')

service.set('port', (process.env.PORT || 5002))

// Process application/x-www-form-urlencoded

service.use(bodyParser.urlencoded({extended: false}))

// Process application/json

service.use(bodyParser.json())

/**

* Mobile Cloud custom code service entry point.

* @param {external:ExpressApplicationObject}

* service

*/

//module.exports = function(service) {

/**

* Retrieves metadata for components implemented by this service.

*/

service.get(apiURL, function(req,res) {

res.set('Content-Type', 'application/json')

.status(200)

.json(shell.getAllComponentMetadata());

});

/**

* Invoke the named component

*/

service.post(apiURL+'/:componentName', function(req,res) {

console.log("HELLO");

const sdkMixin = { oracleMobile: req.oracleMobile };

console.log(req.params.componentName);

shell.invokeComponentByName(req.params.componentName, req.body, sdkMixin, function(err, data) {

if (!err) {

res.status(200).json(data);

}

else {

switch (err.name) {

case'unknownComponent':

res.status(404).send(err.message);

break;

case'badRequest':

res.status(400).json(err.message);

break;

default:

res.status(500).json(err.message);

break;

}

}

});

});

//};

//

// Spin up the server

service.listen(service.get('port'), function() {

console.log('running on port', service.get('port'))

})

The package.json:

{

"name" : "event_api",

"version" : "1.0.0",

"description" : "API for Event bot connecting to ATP",

"main" : "event_api.js",

"oracleMobile" : {

"dependencies" : {

"apis" : { },

"connectors" : { }

},

"configuration": {

"node": "6.10"

}

}

}

If you have packaged the custom component with all the other javascript files required ie. Shell.js, sdk.js, registry.js, MessageModel.js , you can spin up the server with:

>node event_api.js

Clone the Component on GitHub

However if you want to keep you server live you can run event_api.js using the opensource tools like pm2 running on Oracle Compute cloud.

You need to connect your skill to your custom component service running on the Node server on the components tab.

oda1

All done now! Run the code!

On my ATP database tables RESERVATIONS, i have 0 entries. Let’s reserve two tickets at Jazz festival.

oda2

oda3

On the events skill bot:

oda4

The data is updated on table EVENTS – for jazz festival : 2 seats on Bookings column.oda5

Also added a record on the RESERVATIONS table.

oda6

I played around with validations too- you cannot over book 🙂

oda7

Final Thoughts

Node-orcledb can be used to create a middleware layer which will present REST APIs and websockets interfaces for Oracle ATP database. The middleware logic can be hosted on any Node.js server or even run in a container such as OKE.

References:

 

Thank you and Happy coding! If you liked it, share it!

This blog reflects  my own thoughts and doesn’t reflect the thoughts of my employer.

 

Connect to Oracle ATP database using Node.js

In this week series I will explore the capabilities of Oracle Autonomous Transaction Processing (ATP) database beyond connecting to SQL developer.

There are several ways to achieve this, however this is the simplest way to show the capabilities/possibilities that can be attained. 

Prerequisites:

  • Node.js installed in your computer
  • node-oracledb library
  • Oracle Instant Client
  • Oracle ATP database

Installing the Oracle Instant Client in Windows OS

We need the Oracle Instant client to connect and run remote Oracle databases in Node.js.

Download and install the Oracle Instant Client.

Unzip the package into a single directory ie. C:\oracle\instantclient_18_5

Set the environment variable PATH to include the path that you created.

Download the ATP Database connection Wallet.

Log in to your ATP database and download your credential wallet. This contains your connection information to your Oracle ATP database.

ATP1

Extract the wallet files in a given folder. Mine are in:

C:\wallets

We need to update the sqlnet.ora & ojdbc.properties  files in the wallet folder to reflect the location of the wallet.

In ojdbc.properties file:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="c:\wallets")))

sqlnet.ora:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="c:\wallets")))
SSL_SERVER_DN_MATCH=yes

Set the TNS_ADIMN variable:

Since we are using Node.js we can define the Environment variables in an .env configurations file many thanks to the custom-env node library.

ATP2

Install these Node.js libraries to help you run the connection. (Each of the libraries below has their specific functions- We shall see on a later post)

npm install oracledb
npm install async
npm install app
npm install express

You can create a simple testconnection.js file to confirm that the connection to ATP database is working. This file requires an .env configurations file and the dbconfig.js file.

require ('custom-env').env('stagging') //find the .env.stagging file and place the right location of your wallet

console.log(process.env.TNS_ADMIN)

var oracledb = require('oracledb');

var dbConfig = require('./dbconfig.js');

let error;

let user;

oracledb.getConnection({

user: dbConfig.dbuser,

password: dbConfig.dbpassword,

connectString: dbConfig.connectString

},

function(err, connection) {

if (err) {

error = err;

return;

}

connection.execute('select user from dual', [], function(err, result) {

if (err) {

error = err;

return;

}

user = result.rows[0][0];

console.log('Connection test succeeded. You connected to ATP as ' + user + '!');

error = null;

connection.close(function(err) {

if (err) {

console.log(err);

}

});

})

}

);
The dbconfig.js file:
 
module.exports= {

dbuser: 'admin',

dbpassword: 'Your Password',

connectString: 'YourDatabaseName_TP'

}

The .env configurations file helps you load the Node.js app environment variable configurations on different environments. ie. on my .env.stagging file i have:

TNS_ADMIN='c:\wallets' 

Run the testconnection.js file:

node testconnection.js 

ATP3

We succeeded in connecting to the ATP database using Node.js.

Installing the Oracle Instant Client in Oracle Linux

Download a “Basic” or “Basic Light” zip file matching your architecture.

Unzip the package in the folder that is accessible to your application

mkdir -p /opt/oracle
cd /opt/oracle
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip

Install the libaio package as root.

sudo yum install libaio

If there is no other Oracle software on the machine that will be impacted, permanently add Instant Client to the run time link path

sudo sh -c "echo /opt/oracle/instantclient_18_3 > /etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig

Else set an environment variable LD_LIBRARY_PATH   to the directory of the Instant Client.

export LD_LIBRARY_PATH=/opt/oracle/instantclient_18_3:$LD_LIBRARY_PATH

Co locate the ATP connection wallet with the Instant client, create a network/admin subdirectory if it does not exist.

mkdir -p /opt/oracle/instantclient_12_2/network/admin

Edit the sql.ora file to reflect the directory “?/network/admin”

linux1

Load your Node.js files as shown previously. Test to see if your connection worked!

linux2

On the next post we shall explore how we can use this ability to connect an Oracle Digital Assistant with ATP database using a custom component.

References:

 

Thank you and Happy coding! If you liked it, share it!

This blog reflects  my own thoughts and doesn’t reflect the thoughts of my employer.