2

I have a conversation in watson assistant and i want be able to call an action from IBM cloud function to make some query on my database (Azure). I'm not familiar with cloud function so this may be a dumb question, i don't understand how to make a connection with the database, i tried write some nodejs code but of course i'm wrong couse it return an "internal error". I also tried to write some code in python instead of nodejs. Again this is a dumb question so forgive me. Thank you!

    var mysql = require('mysql');

    var connection = mysql.createConnection({
      host: 'my_host',
      user: 'my_user',
      password: 'my_psw',
      database: 'my_db'
    });

    connection.connect();

    rows = connection.query('my_query')
    if (!err) {
      console.log(typeof(rows));
      console.log('The solution is: ', rows);
    } else {
      console.log(typeof(rows));
      console.log('Error while performing Query.');

    }


    connection.end();

{
  "error": "Internal error."
}

import pyodbc as pyodbc

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=my_server;DATABASE=my_db;UID=my_user;PWD=my_pwd')
cursor = conn.cursor()
sql = "my_sql" 
cursor.execute(sql)
result = cursor.fetchall()
print(result)


csr = conn.cursor()
csr.close()
del csr
conn.close()
    

{
  "error": "The action did not return a dictionary."
}
data_henrik
  • 16,724
  • 2
  • 28
  • 49
Alessandro Saletti
  • 23
  • 5
  • Both these tutorials use Cloud Functions to connect to a database: https://console.bluemix.net/docs/tutorials/slack-chatbot-database-watson.html and https://console.bluemix.net/docs/tutorials/serverless-github-traffic-analytics.html The code is available on GitHub – data_henrik Oct 16 '18 at 11:36

1 Answers1

2

The error comes from how you return the result. You need to pack the result into a valid JSON structure.

return {"sqlresult": myresult}

I referenced two tutorials in the comment above. The chatbot tutorial uses Node.js to implement Cloud Functions. Those functions are called from Watson Assistant. Take a look at this action that fetches records from a Db2 database. It opens a database connection, fetches the record(s) and packs them into a JSON structure. That JSON object is then returned to Watson Assistant.

The tutorial also shows how to pass the database credentials into the Cloud Function.

data_henrik
  • 16,724
  • 2
  • 28
  • 49