Build a simple web app for student math drills using the Bluemix SQLDB service

15 July 2014
PDF (467 KB)
Share:
Rodrigo Ferreira

Rodrigo Ferreira

Computer Engineering Student

Marcos Rogério De Mello

Marcos Rogério De Mello

Software Engineer

Jullyana Fialho Pinheiro

Jullyana Fialho Pinheiro

Computer Science Student

Rodrigo Ferreira

Rodrigo Ferreira

Computer Engineering Student

Marcos Rogério De Mello

Marcos Rogério De Mello

Software Engineer

Jullyana Fialho Pinheiro

Jullyana Fialho Pinheiro

Computer Science Student

Sign up for IBM Bluemix

This cloud platform is stocked with free services, runtimes, and infrastructure to help you quickly build and deploy your next mobile or web application.

In a few steps, create a simple web application for students to practice basic math skills, primarily multiplication operations.

With the integrated tools in Bluemix, we created a simple Node.js application to help students learn their multiplication tables.

To build the application shown below, use Node.js; Bluemix; and Bluemix service SQL Database (SQLDB), an on-demand relational database powered by IBM DB2®, which provides a managed database service to handle the demanding web and transactional workloads for your application.

Image shows the app

Using the application, students can track their progress and record their results.

Image shows tracking progress and recording results

What you'll need for your application

 
  • Familiarity with Node.js
  • Familiarity with the Bluemix SQLDB service

Step 1. Create a Node.js runtime and bind it to SQLDB service in Bluemix

 

The first step to recreate this application is to select a Node.js runtime in IBM Bluemix™. Bluemix offers support to several programming languages such as the Java™ programming language, JavaScript, and Ruby on Rails. For this example, a Node.js runtime is created.

After logging in, a dashboard with all the information about the user account will be shown.

  1. To create a new application, click Create an Application and select Node.js. Image shows creating a new app
  2. Click SQLDB to bind the SQLDB data management service to the Node.js application. Image shows binding the SQLDB data management service to the Node.js application

Step 2. Download the source code and push it to the Bluemix application

 
  1. Export the source code from IBM DevOps Services. Image shows exporting the source code
  2. After you download the code, specify the name of your project in the name setting in the manifest.yml file. In this case, the project I created on Bluemix is called mathapp.app.
    applications:
    - name: mathapp
      memory: 512M
      instances: 1
      domain: mybluemix.net
      path: .
      buildpack: https://github.com/ibmdb/db2nodejsbuildpack

    Reminder: Make these changes in the files you downloaded from DevOps Services. The folder where you downloaded the files is now your application's folder.

  3. After you make these changes, change to the directory for this folder and push the code. Image shows changing directory and pushing code

Step 3. Configure the communication between client and server using POST and GET methods

 

At this point, you have linked your domain on Bluemix to your SQLDB database. The next step is to connect your application to the SQLDB database. The communication between your application and the database occurs through GET and POST methods in the app.js file.

  1. In the routes folder, find the index.js file, in which you set your queries to obtain the results you need.
  2. Use the following code to call a POST function to insert the name and score into the database.
    var calcPoints = function(){
          points = totalSeconds * correctAnswers;
          $.post('/', {username: userName, points: points},function(data){
                console.log(data);
      
          });
          alert("You've got 3 wrong answers. Your score is: "  + points);
          history.back();
    }
  3. The following sample code shows the function called by that POST function in the routes/index.js file. This code is the query to store the necessary information in the database.
      exports.insert = function(ibmdb,connString) {
      return function(req, res) {
      var name = req.body.username;
      var points = req.body.points;
      ibmdb.open(connString, function(err, conn) {
                if (err ) {
                res.send("error occurred " + err.message);
                }
                else {
        
                           conn.query("insert into students (name, points) values('" + name
                           +"', " +points+");", function(err, tables, moreResultSets) {
           
                                      if ( !err ) { 
                                                res.send("successful"); 
                                      } else {
                                                res.send("error occurred " + err.message);
                                      }
                                      conn.close(function(){
                                                console.log("Connection Closed");
                                                });
                                      });
                            }
                 } );
      }
    
    }

Step 4. Manage the database through the console

 

Now that you have linked your application to the SQLDB database, explore the SQLDB console and create a table:

  1. Go to your dashboard on Bluemix and select the SQLDB service you bound to your application. Launch the console. Image shows launching the console
  2. In the console, go to Database viewer and click Run DDL. Image shows running DDL
  3. You are now able to type and load a SQL script to the console. In this example, I create the table student, with a name and the score. Load your SQL script and click Run DDL at the bottom right of the page. For more information about DB2 syntax, see the information page. Image shows typing and loading a SQL script
  4. After you run the DDL, click SCHEMA > DB2INST1. Find the student table. Image shows finding the student table

Step 5. Run queries in the application and retrieve the results to the client

 

After you create the table, the app is ready to make a query and retrieve results:

  1. Insert data into the application. For example, for the POST that sends a name and a number of points to the server using the exports.insert() function, the SELECT query can now retrieve data.
       exports.getRanking = function(ibmdb,connString) {
       return function(req, res) {
                  ibmdb.open(connString, function(err, conn) {
                            if (err ) {
                            res.send("error occurred " + err.message);
                            }
                            else {
        
                                    conn.query("SELECT NAME,POINTS FROM STUDENTS ORDER BY
                                    POINTS DESC", function(err, tables, moreResultSets) {
           
                                     if ( !err ) { 
                                     res.render('ranking', {
                                     "tablelist" : tables      
                                      });
         
                                      } else {
                                                res.send("error occurred " + err.message);
                                      }
                                      conn.close(function(){
                                               console.log("Connection Closed");
                                               });
                                      });
                            }
                    } );
      }
    }

As shown in the previous code listing, the exports.getRanking() function makes the query for all results in the student table and brings the data (the name and the number of points) into a JSON object format.

A GET linked to the page and the ranking, triggers the function getRanking() when this page is accessed.

app.post('/', routes.insert(ibmdb, connString));
app.get('/', routes.select(ibmdb,connString));
app.get('/ranking', routes.get(ibmdb,connString));

Manage the results in the rendered .ejs page that the res.render() function points to. In this example, this page is ranking.ejs as shown in the getRanking() function.

Image shows managing the results

A simple JavaScript at the rendered page accesses the data (tablelist) and iterates through it, generating a dynamic table to show the results. With this approach, every access to the page and the ranking gets results (using the GET method) from the database and exposes them, organized in a table ordered by points.

Image shows generating a dynamic table to show results

The same data can be also seen in the SQLDB console when accessing the sample data option from the table location.

Image shows same data in the SQLDB console

Conclusion

 

See how easy it is to build an application using the Bluemix SQLDB service with Node.js? This article has shown the ease in using the integrated Bluemix tools to create a simple Node.js application connected to SQLDB to store data. Although our application is quite simple, we hope you'll use it to build your own app. If you do, be sure to leave us a comment.

RELATED TOPICS:Node.jsBluemix Dev


BLUEMIX SERVICE USED IN THIS TUTORIAL:IBM Bluemix SQL Database (SQLDB) SQL Database (sqldb) adds an on-demand relational database to your application.

Add a comment

Note: HTML elements are not supported within comments.


1000 characters left

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Cloud computing
ArticleID=977455
ArticleTitle=Build a simple web app for student math drills using the Bluemix SQLDB service
publish-date=07152014