DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Because the DevOps movement has redefined engineering responsibilities, SREs now have to become stewards of observability strategy.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Related

  • Fixing Common Oracle Database Problems
  • Nginx + Node.JS: Perform Identification and Authentication
  • Building a Database Written in Node.js From the Ground Up
  • 10 Node.js Security Practices

Trending

  • Building a Real-Time Audio Transcription System With OpenAI’s Realtime API
  • Analyzing Techniques to Provision Access via IDAM Models During Emergency and Disaster Response
  • How to Merge HTML Documents in Java
  • Can You Run a MariaDB Cluster on a $150 Kubernetes Lab? I Gave It a Shot
  1. DZone
  2. Data Engineering
  3. Databases
  4. Real-Time Data With Node.js, Socket.IO, and Oracle Database

Real-Time Data With Node.js, Socket.IO, and Oracle Database

Originally, the only way to simulate real-time data in web apps was via some sort of polling from the browser to the web server. Learn how to push changes from Oracle Database to the web server.

By 
Dan McGhan user avatar
Dan McGhan
·
Nov. 13, 17 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
10.5K Views

Join the DZone community and get the full member experience.

Join For Free

Real-time data is an increasingly popular topic these days. The basic idea is simple: When data in the database changes, any clients viewing the data should see the changes immediately, or in real-time. Gmail and Twitter are great examples of applications that make use of real-time data. Users of these applications don't need to refresh the page to see new email or tweets; instead, new data is pushed to the clients when it becomes available.

Originally, the only way to simulate real-time data in web applications was via some sort of polling from the browser to the web server. This was often complex and difficult to scale. Enter WebSocket, a protocol that provides two-way communication between the browser and web server in a highly efficient manner. The most common way to work with WebSocket is via Socket.IO, a JavaScript library that abstracts away some of the underlying complexity and provides better cross-browser support than native APIs.

So, with Node.js and Socket.IO, we can push changes in data from the web server to the client, but how can we push changes from Oracle Database to the web server? There are really two parts to the problem: detecting changes and then alerting the web server. While I wasn't quite sure how I'd go about implementing the change detection, I had a pretty good idea of how I could alert the Web server of changes: UTL_HTTP.

UTL_HTTP is one of several PL/SQL packages that allow Oracle Database to communicate over a network (others include UTL_TCP, UTL_SMTP, and UTL_MAIL). Since the requirement was to talk to a web server, UTL_HTTP was a natural fit.

That just left the small problem of detecting changes to data. At first I thought about creating DML triggers combined with Advanced Queuing but that seemed overly complex. Luckily, while searching for a solution, I stumbled upon Oracle Database's Continuous Query Notification (CQN). As I read the description in the documentation, I knew I'd hit the jackpot:

Continuous Query Notification (CQN) lets an application register queries with the database for either object change notification (the default) or query result change notification. . . . If a query is registered for query result change notification (QRCN), the database notifies the application whenever a transaction changes the result of the query and commits.

Query Result Change Notification, yes, please! How had I not heard of this before? At any rate, the feature completed the list of ingredients I needed to create an end-to-end, real-time data proof of concept — I just needed to get coding!

I ended up doing two basic implementations. The first was the simplest, just to demonstrate the moving pieces in as simple a manner as possible. After that I did a more "fully-baked" implementation using AngularJS in the front-end. The prerequisites for both are the same:

  • Oracle Database 11g or 12c. If using Oracle 12c the database cannot use the multitenant architecture, as QRCN is not yet supported. I plan to create some content on installing Oracle Enterprise Linux 7 and Oracle XE in VirtualBox, but if you'd like to get an XE database up now for this POC then check out OraOpenSource's Oracle XE & APEX project on GitHub.
  • Node.js and the Oracle Database Node.js Driver installed globally. Check out Up and Running With Node.js and Oracle for instructions on how to set up Node.js and the Oracle driver. Unfortunately, the database included with the VM cannot be used in this POC but the VM could be used as the web server.
  • Bower installed globally in Node.js. Bower is similar to NPM only it was made for front-end dependencies.

Update: Versions 0.5.0 and higher of the driver changed the execute option "isAutoCommit" to "autoCommit". If you're testing with one of these later versions (highly likely), please update the code to reflect the change.

Ready to get started??? Have at it!

Implementation 1: Old School, DOM Scripting Goodness

In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:

curl --remote-name https://jsao.io/super-cities-cqn-demo-0.1.0.zip
unzip super-cities-cqn-demo-0.1.0.zip
cd super-cities-cqn-demo-0.1.0/
npm install
bower install

Use your favorite text editor to modify the file named dbconfig.js so that it has the correct information to connect to your database. If you are not going to use the HR schema then you’ll need to modify the files in the sql-scripts directory to reflect the correct schema before executing them in the next step.

Open your favorite tool to work with Oracle Database (I recommend SQL Developer) and run the scripts found in super-cities-cqn-demo-0.1.0/sql-scripts. First, run 1-run-as-sys.sql and then run 2-run-as-hr.sql. Be sure to update the IP address in 2-run-as-hr.sql to the correct IP address for the Node.js server.

Return to the terminal in the super-cities-cqn-demo-0.1.0 directory and use Node.js to start the server:

node server.js

Implementation 2: AngularJS, for the MVC Folks

In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:

curl --remote-name https://jsao.io/employees-cqn-demo-0.1.0.zip
unzip employees-cqn-demo-0.1.0.zip
cd employees-cqn-demo-0.1.0/
npm install
bower install

Use your favorite text editor to modify the file named dbconfig.js so that it has the correct information to connect to your database. If you are not going to use the HR schema then you’ll need to modify the files in the sql-scripts directory to reflect the correct schema before executing them in the next step.

Open your favorite tool to work with Oracle Database (I recommend SQL Developer) and run the scripts found in employees-cqn-demo-0.1.0/sql-scripts. First, run 1-run-as-sys.sql and then run 2-run-as-hr.sql. Be sure to update the IP address in 2-run-as-hr.sql to the correct IP address for the Node.js server.

Return to the terminal in the employees-cqn-demo-0.1.0 directory and use Node.js to start the server:

node server.js

This is really only the tip of the iceberg. A lot of thought would need to be put into any real implementation to figure out the best implementation, which would vary depending on the exact use case. Is it feasible to push data to the web server rather than just alerting it about a change? How much data could realistically be cached in web server and what's the best way to do it? There are lots of questions to ask and research.

I hope this gets the wheels turning for some folks out there. I'd love to hear from you if you put this technology to use. I plan to do more research on my end as well and in the future, I hope to produce more content that explores Oracle's Continuous Query Notification.

Database Oracle Database Node.js Data (computing) Socket.IO

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Fixing Common Oracle Database Problems
  • Nginx + Node.JS: Perform Identification and Authentication
  • Building a Database Written in Node.js From the Ground Up
  • 10 Node.js Security Practices

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!