Contents


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

Comments

This article was written using the Bluemix® classic interface. Given the rapid evolution of technology, some steps and illustrations may have changed.

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.

Run the appGet the code

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
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
    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
    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
    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
    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
    Image shows launching the console
  2. In the console, go to Database viewer and click Run DDL. Image shows running 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
    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
    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
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
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
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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

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