Forum Stats

  • 3,733,825 Users
  • 2,246,827 Discussions
  • 7,856,887 Comments

Discussions

Creating a REST API with Node.js and Oracle Database

danmcghan-Oracle
danmcghan-Oracle Member Posts: 43
edited June 2018 in Node.js

I've started a new series: Creating a REST API with Node.js and Oracle Database

The current plan is to touch on the following core topics before going into details on specific features and techniques:

  • Web Server Basics
  • Database Basics
  • Handling GET requests
  • Handling PUT, POST, and DELETE requests
  • Adding pagination, sorting, and filtering to GET request

I'm making the code available in our oracle-db-examples repo.

Your questions and comments are welcome!

3422961

Comments

  • blessed DBA
    blessed DBA Member Posts: 218
    edited April 2018

    How can we handle the Large data using this this will make any slowness

  • danmcghan-Oracle
    danmcghan-Oracle Member Posts: 43
    edited April 2018

    There are a number of variables that would come into play (result set size, number of concurrent users, available mid-tier memory, etc.) but typically streams are used to deal with very large result sets. I plan to cover streams at some point in the future. The next post will cover pagination, which can also help if the client doesn't need the entire result set.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,630 Employee
    edited April 2018

    Do you mean data with long values, e.g. LOBs?  Or do you mean a large number of data items / rows?

  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi all,

    I try to make API with node js, express, and OracleDB. My sample code in server.js is below.I got an error while create an api in command line interface which is Connection.connect is not a function. Please help me to resolve this one.

    pastedImage_0.png

    server.js

    var express = require('express');

    var oracledb = require('oracledb');

    var app = express();

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

    // Get a non-pooled connection

    var connection = oracledb.getConnection(

      {

        user          : dbConfig.user,

        password      : dbConfig.password,

        connectString : dbConfig.connectString

      })

     

      connection.connect(function(err){

    if(!err) {

        console.log("Database is connected ... nn");   

    } else {

        console.log("Error connecting database ... nn");   

    }

    });

    app.get("/",function(req,res){

    connection.query('SELECT * from employees', function(err, rows, fields) {

    connection.end();

      if (!err)

        console.log('The solution is: ', rows);

      else

        console.log('Error while performing Query.');

      });

    });

    app.listen(3000);

    ----------------------------------------------------------

    dbconfig.js 

    module.exports = {

      user          : "xxx",

      password      : "xxxx",

      connectString : "localhost/XE"

    }

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,630 Employee
    edited June 2018

    @3422961 check the examples on using node-oracledb at https://github.com/oracle/node-oracledb/tree/master/examples particularly the webapp.js and webapppromises.js files. Note how they create and use the connection pool.

    If you want another example of a web service, look at https://blogs.oracle.com/opal/a-node-oracledb-web-service-in-docker  The main server.js file is here and the complete set of files is here.

    3422961
  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi CJ,

    Thanks for your response.It really helpful to me.

    But am getting stuck in one point.

    I received following error while create a record using POST method.Please help me to resolve this.

    pastedImage_0.png

    Here is my script;

    var express = require('express');

    var oracledb = require('oracledb');

    var app = express();

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

    var bodyParser = require('body-parser');

    var port = 3000;

    app.use(bodyParser.json()); // Use body parser to parse JSON body

    oracledb.outFormat = oracledb.OBJECT;

    // Get a non-pooled connection

    function run() {

    oracledb.createPool({

          user          : dbConfig.user,

        password      : dbConfig.password,

        connectString : dbConfig.connectString

        },

        function(err) {

          if (err)

            console.error("createPool() error: " + err.message);

          else

            var server = app.listen(port,

              function () {

                console.log('Server is listening on port ' + server.address().port);

              });

        });

    }

    function doGetConnection(res, cb) {

      oracledb.getConnection(function (err, connection) {

        if (err) {

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

          res.status(500).send(JSON.stringify({

            status: 500,

            message: "Error getting DB connection",

            detailed_message: err.message

          }));

        } else {

          cb(err, connection);

      console.log("  Connection is connected");

        }

      });

    }

    app.get('/index.htm', function (req, res) {

       res.sendFile( __dirname + "/" + "index.htm" );

    })

    app.post('/process_post', function (req, res) {

    console.log("contenttype"+req.get('Content-Type'))

     

      doGetConnection(res, function(err, connection) {

        if (err)

          return;

        connection.execute(

          "INSERT INTO TEST_TABLE VALUES (:s,:p)",

     

          { s: JSON.stringify(req.body) },

      { p: JSON.stringify(req.body) },

      console.log("check1"),

          { autoCommit: true },

       console.log("check2"),

          function (err) {

      console.log("check3");

            if (err) {

    console.log("check4");

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

              res.status(400).send(JSON.stringify({

                status: 400,

                message: "Input Error",

                detailed_message: err.message

              }));

            } else {

              // Successfully created the resource

              res.status(201).set('Location', '/process_post/' + req.body.FIRST_NAME+req.body.LAST_NAME).end();

     

            }

            doRelease(connection, "POST /process_post");

          });

      });

    });

    function doRelease(connection, message) {

      connection.close(

        function(err) {

          if (err)

            console.error(err);

          else

            console.log(message + " : Connection released");

        });

    }

    run();

    --------------------------------------------------------------

    TABLE SCRIPT

    CREATE TABLE TEST_TABLE ( "FIRST_NAME" VARCHAR2(200 BYTE), "LAST_NAME" VARCHAR2(200 BYTE) )

  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi CJ,

    Please help me to resolve above query.

  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi all,

    Anyone please help me to resolve my above mentioned issue.

    Appreciate your response.

  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi all,

    Still didnt find solution for this.Please help me to resolve this.

  • blessed DBA
    blessed DBA Member Posts: 218
    edited June 2018

    Change the port and try ..

    3422961
  • 3725182
    3725182 Member Posts: 1
    edited June 2018

    @3422961 Unless I'm misreading your code, you are calling connection.execute and passing 7 parameters:

    1. "INSERT INTO TEST_TABLE VALUES (:s,:p)"
    2. { s: JSON.stringify(req.body) }
    3. { p: JSON.stringify(req.body) }
    4. console.log("check1")
    5. { autoCommit: true }
    6. console.log("check2")
    7. function (err) {...

    Please review the API doc to understand the correct number and types of parameters to pass. Also, please review the link in the start of this post which contains details on how to create such a REST API correctly.

    3422961
  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi,

    I changed the port and tried. still am getting the same error.

  • blessed DBA
    blessed DBA Member Posts: 218
    edited June 2018

    Can u Copy paste the error ????

    3422961
  • 3422961
    3422961 Member Posts: 185
    edited June 2018

    Hi,

    This what the error i got.

    pastedImage_0.png

  • blessed DBA
    blessed DBA Member Posts: 218
    edited June 2018

    Can u please share the script code ????

  • blessed DBA
    blessed DBA Member Posts: 218
    edited June 2018

    connection.query('SELECT * from employees', function(err, rows, fields)

    use this  change

    connection.query('SELECT * from employees where column_name in ', function(err, rows, fields)

    3422961
Sign In or Register to comment.