Forum Stats

  • 3,839,011 Users
  • 2,262,438 Discussions
  • 7,900,835 Comments

Discussions

Creating a REST API with Node.js and Oracle Database

danmcghan-Oracle
danmcghan-Oracle Member Posts: 43 Employee
edited Jun 14, 2018 2:50AM 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
«1

Comments

  • blessed DBA
    blessed DBA Member Posts: 218
    edited Apr 20, 2018 6:12AM

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

  • danmcghan-Oracle
    danmcghan-Oracle Member Posts: 43 Employee
    edited Apr 20, 2018 8:57AM

    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,652 Employee
    edited Apr 23, 2018 1:29AM

    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 Jun 8, 2018 3:35AM

    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,652 Employee
    edited Jun 8, 2018 8:24PM

    @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 Jun 11, 2018 2:47AM

    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 Jun 12, 2018 1:45AM

    Hi CJ,

    Please help me to resolve above query.

  • 3422961
    3422961 Member Posts: 185
    edited Jun 12, 2018 3:53AM

    Hi all,

    Anyone please help me to resolve my above mentioned issue.

    Appreciate your response.

  • 3422961
    3422961 Member Posts: 185
    edited Jun 12, 2018 8:24AM

    Hi all,

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

  • blessed DBA
    blessed DBA Member Posts: 218
    edited Jun 12, 2018 11:23PM

    Change the port and try ..

    3422961