1 2 Previous Next 16 Replies Latest reply on Jun 14, 2018 6:50 AM by blessed DBA

    Creating a REST API with Node.js and Oracle Database

    danmcghan-Oracle

      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!

        • 1. Re: Creating a REST API with Node.js and Oracle Database
          blessed DBA

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

          • 2. Re: Creating a REST API with Node.js and Oracle Database
            danmcghan-Oracle

            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.

            • 3. Re: Creating a REST API with Node.js and Oracle Database
              cj

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

              • 4. Re: Creating a REST API with Node.js and Oracle Database
                3422961

                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.

                 

                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"

                }

                • 5. Re: Creating a REST API with Node.js and Oracle Database
                  cj

                  @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.

                  • 6. Re: Creating a REST API with Node.js and Oracle Database
                    3422961

                    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.

                     

                    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) )

                    • 7. Re: Creating a REST API with Node.js and Oracle Database
                      3422961

                      Hi CJ,

                      Please help me to resolve above query.

                      • 8. Re: Creating a REST API with Node.js and Oracle Database
                        3422961

                        Hi all,

                        Anyone please help me to resolve my above mentioned issue.

                        Appreciate your response.

                        • 9. Re: Creating a REST API with Node.js and Oracle Database
                          3422961

                          Hi all,

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

                          • 10. Re: Creating a REST API with Node.js and Oracle Database
                            blessed DBA

                            Change the port and try ..

                            • 11. Re: Creating a REST API with Node.js and Oracle Database
                              dc56ee50-792a-4644-b673-6321432beb0c

                              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.

                              • 12. Re: Creating a REST API with Node.js and Oracle Database
                                3422961

                                Hi,

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

                                • 13. Re: Creating a REST API with Node.js and Oracle Database
                                  blessed DBA

                                  Can u Copy paste the error ????

                                  • 14. Re: Creating a REST API with Node.js and Oracle Database
                                    3422961

                                    Hi,

                                    This what the error i got.

                                    1 2 Previous Next