MacLochlainns Weblog

Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:

1. Configure your Express.js and MySQL development in a single Node.js application.
2. How to convert the list of RowDataPacket objects as elements of data, which is really just simple JavaScript knowledge.
3. How to bind variables into the query.

Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. A global install of Node.js means you need to create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory (in Linux). You can use the following Linux command from within the db directory to create the appropriate symbolic link:

ln -s /usr/local/lib/node_modules `pwd`/node_modules

or, assuming you have a /home/some_user/db directory

ln -s /usr/local/lib/node_modules /home/some_user/node_modules

After creating the node_modules symbolic link, you need to run the following two npm commands. Please note that second command holds the secret-sauce for generating a package.json file that supports Express.js and the MySQL driver:

npm init --y
sudo npm install --save express mysql

Then, you need to replace the package.json file with the contents of the package-lock.json file from your last npm command.

Here’s a small sample program that uses Express.js, converts the RowDataPackets collection, and binds local variables into the query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

// Require libraries.
const express = require('express') 
const mysql = require('mysql')
 
// Create a mysql connection. 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare two local variables.
const start_date = '2001-01-01' 
const end_date = '2003-12-31' 
 
// Connect and display results in the console log.
connection.connect((err) => { 
if (err) 
  throw err 
else { 
  console.log('Connected to MySQL Server!\n') 
  connection.query("SELECT   i.item_title " + 
                   ",        date_format(i.release_date,'%d-%M-%Y') AS release_date " + 
                   "FROM     item i JOIN common_lookup cl " + 
                   "ON       i.item_type = cl.common_lookup_id " + 
                   "WHERE    cl.common_lookup_type = 'BLU-RAY' " + 
                   "AND      i.release_date BETWEEN ? AND ? " + 
                   "ORDER BY i.release_date" 
                  ,[start_date, end_date], function (err, result) { 
    if (err) 
      throw err 
    else { 
      // Prints the index value in the RowDataPacket. 
      for(let element in result) { 
        console.log(result[element].item_title + ', ' + result[element].release_date) 
      } 
      console.log('') 
      console.log('Press Ctrl-C to terminate ...') 
    } 
  }) 
} 
})

Line 28 shows two question marks. They act as placeholders for binding variables. Then, on line 30 you see a collection of the start_date and end_date local variables, which is the second argument to the query() function.

Rather than define individual variables, you can pass them as a collection directly. For example, you replace lines 14 and 15 with this single line:

14

const dates = ['2001-01-01','2003-12-31']

Then, you can pass dates as the second argument to the query() function, like this:

30

                  ,dates, function (err, result) {

Lines 35 and 36 show you how to convert a collection of RowDataPacket objects into elements of data. The for loop assigns the index value to the element variable, which lets you address a single RowDataPacket object instance. The dot (“.“) notation lets you use the name in a name-value pair to reference its value.

It displays the following:

Connected to MySQL Server! 
 
Star Wars II, 16-May-2002 
Harry Potter and the Chamber of Secrets, 28-May-2002 
Harry Potter and the Sorcerer's Stone, 28-May-2002 
Die Another Day, 03-June-2003 
 
Press Ctrl-C to terminate ...

Naturally, I’ll get around to writing something up that shows how to leverage MySQL data into a dynamic form with Handlebars at some point in the near future.

As always, I hope this helps those looking for a solution.

These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:

1. Configure your Node.js development directory.
2. Build a small application to test a MySQL connection.
3. Build a small application that connects to the MySQL database and queries data.

This blog post assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. Then, create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory. You can use the following command from the db directory:

ln -s /usr/local/lib/node_modules `pwd`/node_modules

After creating the node_modules symbolic link, you need to run the following two npm commands:

npm init --y
npm install --save mysql

The first command sets up a generic package.json file, and the second adds the mysql package and supporting packages to the package.json file. These two steps configure the Node.js side of these examples.

They both require that you create the student user with a native password, like so:

CREATE USER 'student'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'student';
GRANT ALL PRIVILEGES ON studentdb.* TO 'student'@'localhost';
FLUSH PRIVILEGES;

The following example shows you how to check a connection to the MySQL database:

const mysql = require('mysql') 
const connection = mysql.createConnection({ 
   host: 'localhost', 
   user: 'student', 
   password: 'student', 
   database: 'studentdb' 
}) 
 
connection.connect((err) => { 
 if (err) 
   throw err 
 else 
   console.log('Connected to MySQL Server!\n') 
   console.log('User configured wiht mysql_native_password.\n'); 
   console.log('Press Ctrl-C to terminate ...') 
})

You extend the previous example by adding a query component and returning the query result value to the console’s log (leveraging the w3school’s Node.js and MySQL tutorial example):

const mysql = require('mysql') 
const connection = mysql.createConnection({ 
   host: 'localhost', 
   user: 'student', 
   password: 'student', 
   database: 'studentdb' 
}) 
 
connection.connect((err) => { 
 if (err) 
   throw err 
 else { 
   console.log('Connected to MySQL Server!\n') 
   connection.query('SELECT DISTINCT item_title FROM item', function (err, result) { 
     if (err) 
       throw err 
     else 
       console.log(result) 
       console.log('Press Ctrl-C to terminate ...') 
   }) 
 } 
})

It should display the following:

Connected to MySQL Server! 
 
[ RowDataPacket { item_title: 'The Hunt for Red October' }, 
 RowDataPacket { item_title: 'Star Wars I' }, 
 RowDataPacket { item_title: 'Star Wars II' }, 
 RowDataPacket { item_title: 'Star Wars III' }, 
 RowDataPacket { item_title: 'The Chronicles of Narnia' }, 
 RowDataPacket { item_title: 'RoboCop' }, 
 RowDataPacket { item_title: 'Pirates of the Caribbean' }, 
 RowDataPacket { item_title: 'MarioKart' }, 
 RowDataPacket { item_title: 'Splinter Cell' }, 
 RowDataPacket { item_title: 'Need for Speed' }, 
 RowDataPacket { item_title: 'The DaVinci Code' }, 
 RowDataPacket { item_title: 'Cars' }, 
 RowDataPacket { item_title: 'Beau Geste' }, 
 RowDataPacket { item_title: 'I Remember Mama' }, 
 RowDataPacket { item_title: 'Tora! Tora! Tora!' }, 
 RowDataPacket { item_title: 'A Man for All Seasons' }, 
 RowDataPacket { item_title: 'Hook' }, 
 RowDataPacket { item_title: 'Around the World in 80 Days' }, 
 RowDataPacket { item_title: 'Harry Potter and the Sorcerer\'s Stone' }, 
 RowDataPacket { item_title: 'Camelot' }, 
 RowDataPacket { item_title: 'Casino Royale' }, 
 RowDataPacket { item_title: 'Die Another Day' }, 
 RowDataPacket { item_title: 'Golden Eye' }, 
 RowDataPacket { item_title: 'Tomorrow Never Dies' }, 
 RowDataPacket { item_title: 'The World Is Not Enough' }, 
 RowDataPacket { item_title: 'Brave Heart' }, 
 RowDataPacket { item_title: 'Christmas Carol' }, 
 RowDataPacket { item_title: 'Scrooge' }, 
 RowDataPacket { item_title: 'Clear and Present Danger' }, 
 RowDataPacket { item_title: 'Harry Potter and the Chamber of Secrets' }, 
 RowDataPacket { item_title: 'Harry Potter and the Prisoner of Azkaban' }, 
 RowDataPacket { item_title: 'Harry Potter and the Goblet of Fire' }, 
 RowDataPacket { item_title: 'Harry Potter and the Order of the Phoenix' }, 
 RowDataPacket { item_title: 'Harry Potter and the Half Blood Prince' }, 
 RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 1' }, 
 RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 2' }, 
 RowDataPacket { item_title: 'Tron' }, 
 RowDataPacket { item_title: 'The Avengers' }, 
 RowDataPacket { item_title: 'Thor: The Dark World' } ] 
Press Ctrl-C to terminate ...

As always, I hope this helps those looking to learn

I liked the example used to explore basic routing in Chapter 2 of the Web Development with Node & Express book. I embellished the Node.js router example and static pages just a bit. The routing example requires you create a public subdirectory where you deploy the code and put the about.html, home.html, and 404.html files in the public subdirectory. Then, you must put a logo.png file in a tmp subdirectory inside of the public directory. The book assumes you know how to build these pages, which seems reasonable but just in case, here are the files I used to test the book’s example.

The about.html page:

<html>
<head>
<title>About Page</title>
</head>
<body>
<p>A small sample Node.js routing example.</p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The home.html page:

<html>
<head>
<title>Home Page</title>
</head>
<body>
<p style="font-size:110%">Star Trek: The Original Series - Season 1</p>
<p><img width="300" src="/img/StarTrekOriginal1.png" /></p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The 404.html page:

<html>
<head>
<title>404 Error Message</title>
</head>
<body>
<p>A 404 Error Message Page.</p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The routing example sets the path to lowercase, which is important when you type the URL to verify physical files. For example, you need to use the routing startrekoriginal1.png string value in the URL. It will fail if you use the mixed case actual file name of the StarTrekOriginal1.png file. That’s because the routing maps the lowercase string to the physical file.

While the /public directory is the home directory of the virtual map as a rule, you can’t use it in the URL (as explained in next Chapter 3). Also, the router uses /img as a virtual directory which maps to the physical /tmp subdirectory. When you want to validate a physical image file you need to know these two rules. They explain why the following URL lets you verify a physical image file found in the /public/tmp directory.

localhost:3000/img/StarTrekOriginal1.png

you will trigger the 404 Error page. Here’s the Node.js routing code:

/* Construct a web server. */
const http = require('http')
const fs = require('fs')
const port = process.env.PORT || 3000
 
/* Function uses the fs package to read files. */
function serveStaticFile(res, path, contentType, responseCode = 200) {
  fs.readFile(__dirname + path, (err, data) => {
    if (err) {
      res.writeHead(500, { 'Content-Type': 'text/plain' })
      return res.end('500 - Internal Error')
    }
    res.writeHead(responseCode, { 'Content-Type': contentType })
    res.end(data)
  })
}
 
/* Create the Node.js server. */
const server = http.createServer((req, res) => {
  // Normalize URL by removing query string, optional
  // trailing slash, and making it lowercase.
  const path= req.url.replace(/\/?(?:\?.*)?$/,'').toLowerCase()
 
  switch(path) {
    case '':
      serveStaticFile(res, '/public/home.html', 'text/html' )
      break
    case '/about':
      serveStaticFile(res, '/public/about.html', 'text/html' )
      break
    case '/img/startrekoriginal1.png':
      serveStaticFile(res, '/public/tmp/StarTrekOriginal1.png', 'image/png' )
      break
    case '/img/logo.png':
      serveStaticFile(res, '/public/tmp/logo.png', 'image/png' )
      break
    default:
      serveStaticFile(res, '/public/404.html', 'text/html', 404 )
      break
  }
})
 
server.listen(port, () => console.log(`server started on port ${port}; ` +
                                      'press Ctrl-C to terminate...'))

Assuming you name the Node.js routing example helloworld3.js, you would start the router with the following command:

node helloworld3.js

It should start the router. Enter the following URL:

http://localhost:3000

You should see a page rendered like the following:

As always, I hope this helps those trying to use this technology.

I want to add the MEAN (MongoDB, Express.js, Angular.js, and Node.js) stack to my backend server development course. This post documents the installation and configuration of components on Fedora 30.

The first step requires installing the Node package. The Node package also contains the Node package manager (npm). You install the Node packages as the root user or as a sudoer user with the following command.

yum install -y npm

It should produce the following installation log:

Last metadata expiration check: 1:10:42 ago on Wed 08 Jul 2020 06:57:52 PM MDT.
Dependencies resolved.
================================================================================================================================
 Package                         Architecture          Version                                     Repository              Size
================================================================================================================================
Installing:
 npm                             x86_64                1:6.13.4-1.10.19.0.1.fc30                   updates                3.8 M
Installing dependencies:
 nodejs                          x86_64                1:10.19.0-1.fc30                            updates                 88 k
 nodejs-libs                     x86_64                1:10.19.0-1.fc30                            updates                9.1 M
Installing weak dependencies:
 nodejs-full-i18n                x86_64                1:10.19.0-1.fc30                            updates                7.3 M
 
Transaction Summary
================================================================================================================================
Install  4 Packages
 
Total download size: 20 M
Installed size: 91 M
Downloading Packages:
(1/4): nodejs-10.19.0-1.fc30.x86_64.rpm                                                         173 kB/s |  88 kB     00:00    
(2/4): nodejs-full-i18n-10.19.0-1.fc30.x86_64.rpm                                               2.8 MB/s | 7.3 MB     00:02    
(3/4): nodejs-libs-10.19.0-1.fc30.x86_64.rpm                                                    2.7 MB/s | 9.1 MB     00:03    
(4/4): npm-6.13.4-1.10.19.0.1.fc30.x86_64.rpm                                                   1.3 MB/s | 3.8 MB     00:02    
--------------------------------------------------------------------------------------------------------------------------------
Total                                                                                           4.9 MB/s |  20 MB     00:04     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Running scriptlet: npm-1:6.13.4-1.10.19.0.1.fc30.x86_64                                                                   1/1 
  Preparing        :                                                                                                        1/1 
  Installing       : nodejs-libs-1:10.19.0-1.fc30.x86_64                                                                    1/4 
  Installing       : nodejs-full-i18n-1:10.19.0-1.fc30.x86_64                                                               2/4 
  Installing       : npm-1:6.13.4-1.10.19.0.1.fc30.x86_64                                                                   3/4 
  Installing       : nodejs-1:10.19.0-1.fc30.x86_64                                                                         4/4 
  Running scriptlet: nodejs-1:10.19.0-1.fc30.x86_64                                                                         4/4 
  Verifying        : nodejs-1:10.19.0-1.fc30.x86_64                                                                         1/4 
  Verifying        : nodejs-full-i18n-1:10.19.0-1.fc30.x86_64                                                               2/4 
  Verifying        : nodejs-libs-1:10.19.0-1.fc30.x86_64                                                                    3/4 
  Verifying        : npm-1:6.13.4-1.10.19.0.1.fc30.x86_64                                                                   4/4 
 
Installed:
  nodejs-1:10.19.0-1.fc30.x86_64           nodejs-full-i18n-1:10.19.0-1.fc30.x86_64     nodejs-libs-1:10.19.0-1.fc30.x86_64    
  npm-1:6.13.4-1.10.19.0.1.fc30.x86_64    
 
Complete!

After installing the Node package, you should use the Node package manager (npm) to install the Node Monitor nodemon. nodemon is a popular utility that automatically lets you restart Node programs when you make changes to the source code.

While npm is installed as part of the Node package, you must use npm to install the Node Monitor. The following command installs the nodemon globally on your Fedora system. The -g flag lets you install it globally, which is important when you manage package.json files.

npm install -g nodemon

You install nodemon globally but most of your web app or project files will be installed locally. Node is a different paradigm than building an Apache or IIS web application because Node provides a framework for you to build a web server.

Here’s a quick Hello World! example that I borrowed a JavaScript helloworld.js file from an excellent Web Development with Node & Express: Leveraging the JavaScript Stack by Ethan Brown. For those who haven’t worked with JavaScript in years, semicolons are optional now.

/* Construct a web server. */
const http = require('http')
const port = process.env.PORT || 3000
 
const server = http.createServer((req, res) => {
  res.writeHead(200, { 'Content-Type': 'text/plain' })
  res.end('Hello world!')
})
 
server.listen(port, () => console.log(`server started on port ${port}); ` +
                                      'press Ctrl-C to terminate...'))

I put this in /var/www/html/node directory, which is owned by the superuser, root. You need to start the server before accessing it from a browser. You can start the program with the following syntax as a privileged user:

node /var/www/html/node/helloworld.js

Then, you can use the localhost to access it with the following URL:

http://localhost:3000

It will display the following:

Next, you need to use the Node Package Manager (npm) to install the Express.js packages. You do that with the following syntax:

npm install -g express express-generator

It should produce a console out put like the following:

npm WARN deprecated mkdirp@0.5.1: Legacy versions of mkdirp are no longer supported. Please update to mkdirp 1.x. (Note that the API surface has changed to use Promises in 1.x.)
/usr/local/bin/express -> /usr/local/lib/node_modules/express-generator/bin/express-cli.js
+ express@4.17.1
+ express-generator@4.16.1
added 60 packages from 42 contributors in 4.798s

After you install all the packages, you can inspect them with the following command. The packages are found in the /usr/local/lib/node_modules/express directory. The listing is generated from the package.json file on Fedora and Ubuntu Linux.

npm list -g

It should display something like this:

/usr/local/lib
├─┬ express@4.17.1
│ ├─┬ accepts@1.3.7
│ │ ├─┬ mime-types@2.1.27
│ │ │ └── mime-db@1.44.0
│ │ └── negotiator@0.6.2
│ ├── array-flatten@1.1.1
│ ├─┬ body-parser@1.19.0
│ │ ├── bytes@3.1.0
│ │ ├── content-type@1.0.4 deduped
│ │ ├── debug@2.6.9 deduped
│ │ ├── depd@1.1.2 deduped
│ │ ├─┬ http-errors@1.7.2
│ │ │ ├── depd@1.1.2 deduped
│ │ │ ├── inherits@2.0.3
│ │ │ ├── setprototypeof@1.1.1 deduped
│ │ │ ├── statuses@1.5.0 deduped
│ │ │ └── toidentifier@1.0.0
│ │ ├─┬ iconv-lite@0.4.24
│ │ │ └── safer-buffer@2.1.2
│ │ ├── on-finished@2.3.0 deduped
│ │ ├── qs@6.7.0 deduped
│ │ ├─┬ raw-body@2.4.0
│ │ │ ├── bytes@3.1.0 deduped
│ │ │ ├── http-errors@1.7.2 deduped
│ │ │ ├── iconv-lite@0.4.24 deduped
│ │ │ └── unpipe@1.0.0 deduped
│ │ └── type-is@1.6.18 deduped
│ ├─┬ content-disposition@0.5.3
│ │ └── safe-buffer@5.1.2 deduped
│ ├── content-type@1.0.4
│ ├── cookie@0.4.0
│ ├── cookie-signature@1.0.6
│ ├─┬ debug@2.6.9
│ │ └── ms@2.0.0
│ ├── depd@1.1.2
│ ├── encodeurl@1.0.2
│ ├── escape-html@1.0.3
│ ├── etag@1.8.1
│ ├─┬ finalhandler@1.1.2
│ │ ├── debug@2.6.9 deduped
│ │ ├── encodeurl@1.0.2 deduped
│ │ ├── escape-html@1.0.3 deduped
│ │ ├── on-finished@2.3.0 deduped
│ │ ├── parseurl@1.3.3 deduped
│ │ ├── statuses@1.5.0 deduped
│ │ └── unpipe@1.0.0
│ ├── fresh@0.5.2
│ ├── merge-descriptors@1.0.1
│ ├── methods@1.1.2
│ ├─┬ on-finished@2.3.0
│ │ └── ee-first@1.1.1
│ ├── parseurl@1.3.3
│ ├── path-to-regexp@0.1.7
│ ├─┬ proxy-addr@2.0.6
│ │ ├── forwarded@0.1.2
│ │ └── ipaddr.js@1.9.1
│ ├── qs@6.7.0
│ ├── range-parser@1.2.1
│ ├── safe-buffer@5.1.2
│ ├─┬ send@0.17.1
│ │ ├── debug@2.6.9 deduped
│ │ ├── depd@1.1.2 deduped
│ │ ├── destroy@1.0.4
│ │ ├── encodeurl@1.0.2 deduped
│ │ ├── escape-html@1.0.3 deduped
│ │ ├── etag@1.8.1 deduped
│ │ ├── fresh@0.5.2 deduped
│ │ ├── http-errors@1.7.2 deduped
│ │ ├── mime@1.6.0
│ │ ├── ms@2.1.1
│ │ ├── on-finished@2.3.0 deduped
│ │ ├── range-parser@1.2.1 deduped
│ │ └── statuses@1.5.0 deduped
│ ├─┬ serve-static@1.14.1
│ │ ├── encodeurl@1.0.2 deduped
│ │ ├── escape-html@1.0.3 deduped
│ │ ├── parseurl@1.3.3 deduped
│ │ └── send@0.17.1 deduped
│ ├── setprototypeof@1.1.1
│ ├── statuses@1.5.0
│ ├─┬ type-is@1.6.18
│ │ ├── media-typer@0.3.0
│ │ └── mime-types@2.1.27 deduped
│ ├── utils-merge@1.0.1
│ └── vary@1.1.2
└─┬ express-generator@4.16.1
  ├── commander@2.15.1
  ├── ejs@2.6.1
  ├─┬ minimatch@3.0.4
  │ └─┬ brace-expansion@1.1.11
  │   ├── balanced-match@1.0.0
  │   └── concat-map@0.0.1
  ├─┬ mkdirp@0.5.1
  │ └── minimist@0.0.8
  └── sorted-object@2.0.1

You can also create a secure node site (HTTPS) with the following additional steps. They include creating a self-signed secure public and private key. This creates the public key:

openssl genrsa -out key.pem

The openssl command will generate a private key key.pem file. It generates something like the following text message to console:

Generating RSA private key, 2048 bit long modulus (2 primes)
...........+++++
.............................................+++++
e is 65537 (0x010001)

Next, you need to generate a self-signed certificate. You do this in two steps.

1. Create a Distinguished Name (DN) file. The csr.pem file is the DN file. You need it to create a self-signed certificate:

   openssl req -new -key key.pem -out csr.pem

   openssl req -new -key key.pem -out csr.pem

   It will prompt you for values, like the following:

   You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []:MiddleEarth Locality Name (eg, city) [Default City]:Rivendell Organization Name (eg, company) [Default Company Ltd]:Fellowship Organizational Unit Name (eg, section) []:Self Common Name (eg, your name or your server's hostname) []:Localhost Email Address []:bilbo@loth.org   Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:friend An optional company name []:Bilbo

   You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []:MiddleEarth Locality Name (eg, city) [Default City]:Rivendell Organization Name (eg, company) [Default Company Ltd]:Fellowship Organizational Unit Name (eg, section) []:Self Common Name (eg, your name or your server's hostname) []:Localhost Email Address []:bilbo@loth.org Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:friend An optional company name []:Bilbo

2. Use the DN file to create your secure certificate. The following openssl command creates the certificate file by using your private key key.pem and DN csr.pem files.

   openssl x509 -req -days 9999 -in csr.pem -signkey key.pem -out cert.pem

   openssl x509 -req -days 9999 -in csr.pem -signkey key.pem -out cert.pem

   It should generate a Secure certificate cert.pem file and return something like the following to the console.

   Signature ok subject=C = XX, ST = MiddleEarth, L = Rivendell, O = Fellowship, OU = Self, CN = Localhost, emailAddress = bilbo@loth.org Getting Private key

   Signature ok subject=C = XX, ST = MiddleEarth, L = Rivendell, O = Fellowship, OU = Self, CN = Localhost, emailAddress = bilbo@loth.org Getting Private key

You can put these private key (key.pem) and certificate (cert.pem) files in an ssl subdirectory of the directory where you put the JavaScript program. The following creates a secure server page with the following code.

/* Construct a secure web server. */ 
const https = require('https') 
const fs = require('fs') 
const port = process.env.PORT || 3000 
 
const options = { 
 key: fs.readFileSync('ssl/key.pem'), 
 cert: fs.readFileSync('ssl/cert.pem') 
} 
 
const server = https.createServer((options, res) => { 
 res.writeHead(200, { 'Content-Type': 'text/plain' }) 
 res.end('Hello world!') 
}) 
 
server.listen(port, () => console.log(`server started on port ${port}); ` + 
                                     'press Ctrl-C to terminate...'))

If you try launch your browser using the localhost instead of a DNS or file resolved network name on the designated port, it will raise the following security error:

This site can't be reached
https's server IP address could not be found.
DNS_PROBE_FINISHED_NXDOMAIN

An alternate approach to writing a secure server includes using Express.js library. The syntax changes somewhat and you include two new libraries, as shown below:

/* Construct a secure web server. */ 
const https = require('https') 
const express = require('express') 
const fs = require('fs') 
const app = express() 
const port = process.env.PORT || 3001 
 
const options = { 
 key:  fs.readFileSync('ssl/key.pem'), 
 cert: fs.readFileSync('ssl/cert.pem') 
} 
 
https.createServer(options, app).listen(port, () => { 
 console.log(`Express started in ${app.get('env')} mode ` + 
             `on port + ${port}.`) 
})

This will fail with the following error message if you’re running it with a global installation unless you set the $NODE_PATH environment variable correctly. Without setting the variable you may get the following error message:

internal/modules/cjs/loader.js:638 
   throw err; 
   ^ 
 
Error: Cannot find module 'express' 
   at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15) 
   at Function.Module._load (internal/modules/cjs/loader.js:562:25) 
   at Module.require (internal/modules/cjs/loader.js:692:17) 
   at require (internal/modules/cjs/helpers.js:25:18) 
   at Object.<anonymous> (/var/www/html/node/helloworldsecure.js:3:17) 
   at Module._compile (internal/modules/cjs/loader.js:778:30) 
   at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10) 
   at Module.load (internal/modules/cjs/loader.js:653:32) 
   at tryModuleLoad (internal/modules/cjs/loader.js:593:12) 
   at Function.Module._load (internal/modules/cjs/loader.js:585:3)

Sometimes they’ll advise you to do an individual user installation of Express.js to get past this error but that’s not necessary. You just need to set the $NODE_PATH environment variable as follows:

export NODE_PATH=/usr/local/lib/node_modules

This will enable the JavaScript to work without error and without a specific user installation. Assuming you name either of these programs as helloworldsecure.js, you run them with the following command:

node helloworldsecure.js

You can terminate the program with a Ctrl+c or if use the kill -15 pid command if you started it as a background process. You can find the process ID (pid) with the jobs command.

As always, I hope this is helpful to those starting out with this cool technology stack.

During the holidays, I check salaries for my students and the IT industry overall. I’m never surprised by the reality, after all salaries pay for return on skills and effort. Here’s my annual look, which some may find unkind but reality is seldom kind.

Before looking at IT salaries, it seems like a good opportunity to first look at the overall job market for Millennials in the United States. AOL provides a great graphic of the median income for Millennials (those born between 1981 and 1997), which is $18,000 to $43,000 a year:

That’s a stark contrast to Forbes’ statistics on the top college baccalaureate degrees. In fact, the top five with the highest salary are between $58 to $67 thousand a year. They are:

1. Computer Science ………… $66,800
2. Engineering ………………… $65,000
3. Mathematics & Statistics … $60,300
4. Economics ………………….. $58,600
5. Finance ……………………… $58,000

Computer science, applied computer science, and information technology are probably lumped into the first category. Information systems, exposure without real skills, is a management degree and probably opens positions equivalent to the business degree at $50 thousand a year. More or less, that’s a nine thousand dollar difference between having real skills and being able to talk the game and supervise technical resources. (The 10 hottest IT skills for 2015 are listed in Computerworld.)

There’s no surprise that Ruby, Objective C (iPhone, iPad, Mac OS X), Python, Java, C++ are at the top of the pyramid. Starting salaries in the Salt Lake area are higher for programmers college than they are for other computer science skill sets. In fact, my informal contacts peg them as starting at $70+ thousand. That’s higher than Forbes average for computer science. Here’s a visual on experienced programmers by language:

It seems fair to say that a computer science, applied computer science, and information technology degree with an emphasis in real programming skills is the best bet to pay off student loans. However, some will wait for politicians to do that for them, but really that’s quite unlikely, isn’t it?

Reality is always blunt. Reality also seems to frequently differs from what politicians say. After all, politicians pander to audiences, which generally means they say a great deal of nonsense. Nonsense like economics doesn’t matter, everyone should earn the same regardless of their education, skills, or work ethic. Aldous Huxley said it more elegantly when he said, “That all men are equal is a proposition to which, at ordinary times, no sane human being has ever given his assent.”

What the MySQL team is doing with JSON (JavaScript Object Notation) in MySQL 5.7 is great! The MySQL Server Blog (Rick Hillegas and Dag Wanvik) published two key articles about new JSON functions. If you don’t follow these, let me highlight them as a set:

• JSON Functions, Part 1 – Manipulation JSON Data
• JSON Functions, Part 2 – Querying JSON Data

Most folks know how important JSON is to web development. I like the following visual that highlights it. It was provided as a comment to this earlier Popular Programming Language post by Michael Farmer. Clearly, JavaScript is popular because it’s critical to effective web development. If you’re new to JSON, check out Adam Khoury’s JSON tutorial set on YouTube.

If you want the original graphic, you can find it here. It’s always to hard to keep up with the technology, isn’t it? 🙂

First of all, Happy New Year!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

I couldn’t resist including Objective-C because it shows how the iPhone, iPad, and Mac OS impact our daily lives. At the same time, Assembly [#15] is actually more popular than Objective-C. Shell [#17] follows Objective-C. While the Visual Basic [#14] programming language still remains very popular.

There are many “why” questions raised by this list of popular programming languages. The “why” from my perspective deals with what are the market drivers for their popularity. The money drivers I see are as follows:

• Business software: Java, C++, C#, and AIDE – Android IDE (works with Java and C++ source code)
• OS X and iOS Development: Objective-C
• Development Tools: Java, C, C++, and Python
• System Admin/Utilities Tools: C, Perl, and Shell
• Web Development: Python, PHP, Ruby, and Perl
• Data Analysis: SQL, MATLAB, and R

Business Intelligence (BI) software manages most high-level data analysis tools and they’ll continue to get better over time. However, if SQL has shown us anything over 30 years it’s that ultimately we revert to it to solve problems. The conclusion from the reality of BI probably means the programming languages that develop those tools will continue to rise and so will the underlying data languages.

It’s also interesting to note that nine out of ten of the popular programming languages work with databases, like Oracle, MySQL, PostgreSQL, or SQL Server. While JavaScript doesn’t access the database typically, it’s JSON (JavaScript Object Notation) is supported in all the databases.

Fifteen years ago when I dove into JavaScript friends called me a hobbyist and said I was wasting my time. They had some basis in what they said because most of my work is server side with the database or operating system. However, they haven’t said that I wasted my time with the advent of NoSQL solutions, like Cassandra and MongoDB; or frameworks like node.js. Now writing JavaScript is mainstream and an invaluable skill set, and they’re trying to catch up. While David Flanagan’s JavaScript: The Definitive Guide is comprehensive, I recommend my students start with David McFarland’s JavaScript & jQuery: The Missing Manual. I also strongly suggest they use jQuery first and write JavaScript when jQuery isn’t suitable.

I really liked David McFarland’s CSS: The Missing Manual when it came out in 2009, and I’ve recommended it for several years. However, he’s got a new version – CSS3: The Missing Manual that came out in late January 2014. It’s an improvement over his first volume and I’d recommend you upgrade if you’re writing, modifying, or maintaining Cascading Style Sheet or if you just want to learn more about CSS.

Fortunately for me, CSS3: The Missing Manual is available through iTunes for Apple users, Naturally, it’s also available on Safari and Kindle formats. As an Apple user, I opted for the iBook format for my iPad Air. Unfortunately, it’s $27.99 as an iBook compared to $15.49 on Kindle, and that almost makes me opt to use the Kindle App. 😉

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7^th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …

The topic of WebSockets came up today, and it seems a good idea to qualify WebSockets in the blog beyond simply pointing folks to Kaazing. HTML5’s WebSockets are a great fix for the half-duplex world of AJAX. They certainly have the potential to be more scalable than current Comet solutions, and present a real-time communication alternative. The latest draft of The WebSocket API was published earlier this month.

If you’re new to WebSockets, you may want to review Peter Lubbers’ PowerPoint presentation from the International Conference on Java Technology, 2010 (or the updated version he provided via a comment to this post). That is probably shorter than watching the multiple parts posted in 10-minute segments on YouTube.

More or less the PDF file of the presentation covers how HTTP is half-duplex not full duplex, and why it doesn’t support real-time data in a browser. It points to AJAX (Asynchronous JavaScript and XML) as a programming nightmare – or more precisely a workaround to a limitation of HTTP/HTTPS. It also covers the idea of COMET programming or frameworks as complex and incomplete attempts to simulate full duplex or bi-directional communication on the web.

As Peter Lubbers qualifies, AJAX and COMET solutions don’t scale against high transaction volumes or concurrency because their header traffic overwhelms the actual data transfers. This reality occurs more or less because browsers only implement unidirectional communication through a request and acknowledgement model and send large header sequences compared to small data footprints.

Peter Lubbers presents three types of HTTP solutions in the presentation:

• Polling involves periodic requests to the server for updated information, and it is the backbone of many Ajax applications that simulate real-time communication. The HTTP message headers involved in polling are frequently larger than the transmitted data; and while polling works well in low-message rate situations it doesn’t scale well. It also involves opening and closing many connections to the server and hence database needlessly in some cases.
• Long Polling or asynchronous-polling requests the server to keep the connection open for a set period of time. It doesn’t solve problems with high-message rates situations of polling in general because it creates a continuous loop of immediate polls and each poll, like ordinary polling messages, sends mostly HTTP headers not data.
• Streaming architecture opens a socket but can cause problems with proxy and firewall servers, create cross-domain issues due to browser connection limits, and periodically pose overhead to flush streams that have built up in the communication channel. Streaming solutions reduce HTTP header traffic but at a cost in overhead to the server.

Websockets are designed to fix these issues. The most interesting thing about polling, long polling, streaming, or Websockets is they require the same careful attention to how databases validate user access and serve up content. When the HTML5 standard nears completion, they’ll be a great need for database connection solutions, like Oracle’s Data Resident Connection Pooling.

By the way, here are some great video links for learning HTML5.