JavaScript | MacLochlainns Weblog

Archive for the ‘JavaScript’ Category

Oracle 23c MLE JavaScript

Oracle Database 23c has some really great features. One of those features is the ability to write JavaScript functions inside the database. Unfortunately, I noticed a couple omissions in Oracle’s JavaScript Developer’s Guide. Specifically, I encountered a privilege error while testing the example in the 5.1 Call Specifications for Functions example.

After having typed it all in to a simple script file, I encountered the following error message when trying to create an MLE MODULE:

CREATE OR REPLACE MLE MODULE jsmodule
*
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES
Help: https://docs.oracle.com/error-help/db/ora-06575

That was easy enough to fix. As the system user you need to grant the following two additional privileges to the user (based on my earlier sandbox pluggable user configuration user setup), which in my case is the c##student pluggable user:

GRANT CREATE MLE TO c##student;
GRANT EXECUTE ON JAVASCRIPT TO c##student;

You need to enable the SQL*PLus SERVEROUTPUT environment parameter for Oracle’s code example to work when you run the greet procedure from SQL*Plus command-line interface (CLI).

Below is the modified example file (unfortunately, the GeSHi formatting promotes log and return in the JavaScript functions to uppercase because they’re assumed as keywords in Oracle SQL):

Conditionally drop the MLE MODULE.
DROP MLE MODULE IF EXISTS jsmodule;
DROP MLE MODULE IF EXISTS jsmodule;

Create a MLE Call Specification.

CREATE OR REPLACE
  MLE MODULE jsmodule
  LANGUAGE JAVASCRIPT AS
 
  export function greet(str) {
    console.log(`Hello ${str}!`)
  }
 
  export function concat(str1, str2) {
    return str1 + " " + str2 + "!";
  }
/

The greet function doesn’t return a value and uses the Nodejs console.log() function to write a string, which means you must wrap the JavaScript function as a procedure because it returns a void type.
1 2 3 4 5
CREATE OR REPLACE PROCEDURE greet(str in VARCHAR2) AS MLE MODULE jsmodule SIGNATURE 'greet(string)'; /
CREATE OR REPLACE PROCEDURE greet(str in VARCHAR2) AS MLE MODULE jsmodule SIGNATURE 'greet(string)'; /
Wrap the concatenate function as a function because it returns a value inside the JavaScript.
1 2 3 4 5 6 7
CREATE OR REPLACE FUNCTION concatenate ( str1 VARCHAR2 , str2 VARCHAR2 ) RETURN VARCHAR2 AS MLE MODULE jsmodule SIGNATURE 'concat(string, string)'; /
CREATE OR REPLACE FUNCTION concatenate ( str1 VARCHAR2 , str2 VARCHAR2 ) RETURN VARCHAR2 AS MLE MODULE jsmodule SIGNATURE 'concat(string, string)'; /
Enable to the SERVEROUTPUT environment variable to display messages printed from inside stored procedures or other PL/SQL blocks.
SET SERVEROUTPUT ON SIZE UNLIMITED
SET SERVEROUTPUT ON SIZE UNLIMITED
Call the greet(literal|variable) function’s procedure wrapper.
CALL greet('Peter');
CALL greet('Peter');
It returns
```
Hello Peter!
```
Query the result from the concatenate() function’s function wrapper. */
SELECT concatenate('Hello','World');
SELECT concatenate('Hello','World');
It returns
```
Hello World!
```

As always, I hope this helps somebody working through the same issue.

Written by maclochlainn

December 5th, 2023 at 12:41 am

Posted in JavaScript,Oracle 23c,pl/sql

Tagged with Oracle DBA, Oracle Developer

MySQL Posts Summary

without comments

Here’s a quick catalog for my students of PowerShell, JavaScript, and Python examples connecting to MySQL:

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

Written by maclochlainn

June 16th, 2023 at 12:38 am

Posted in JavaScript,Linux,MySQL,MySQL 8,PowerShell,Python 3.x

Tagged with MySQL DBA Techniques, MySQL Developer

Is SQL Programming

with 4 comments

Is SQL, or Structured Query Language, a programming language? That’s a great question! A question that many answer with emphasis: “No, SQL is not a programming language!” There are some who answer yes; and they usually qualify that answer with something like: “SQL is a programming language designed to communicate with relational databases.”

It strikes me that those saying “yes” are saying that SQL is only a collection of interface methods to read from and write to a database engine. Those saying SQL is not a programming language often qualify that a programming language must have conditional logic and iterative structures, which don’t exist in SQL.

There’s a third group that are fence sitters. They decline to say whether SQL is a programming language, but they also say individuals who only write SQL aren’t programmers. That’s a bit harsh from my perspective.

Before determining whether SQL is a programming language let’s define a programming language. Let’s define a programming language as a collection of lexical units, or building blocks, that build program units. Lexical units are typically organized as delimiters, identifiers, literals, and comments:

Delimiters include single or double quotes to identify strings and operators that let you assign and compare values.
Identifiers are reserved words, keywords, predefined identifiers (like data type names), user-defined variables, subroutines, or types.
Literals are typically numbers and strings, where some strings qualify as dates because they implement a default format mask to convert strings to dates or date-times.
Comments are simply delimited text that the program ignores but the programmer uses.

That means a programming language must let you define a variable, assign a value to a variable, iterate across a set of values, and make conditional statements. SQL meets these four conditions, but it does, as a set-programming language, qualify all variables as lists of tuples. Though it is possible to have variables with zero to many elements and one to many members in any given tuple. That means you can assign a literal value to to a one-element list with a single-member tuple, like you would a string or integer to a variable of that type.

As Kris Köhntopp commented, computer science defines a programming language as Turing Complete. As his comment qualifies and the Wikipedia page explains: “Turing completeness in declarative SQL is implemented through recursive common table expressions. Unsurprisingly, procedural extensions to SQL (PLSQL, etc.) are also Turing-complete.” While PostgreSQL introduces recursive query syntax through CTEs, it recently added the search and cycle feature in PostgreSQL 14. The recursive query feature has existed in the Oracle database since Oracle 8, but their documentation calls them hierarchical queries. I wrote a quick a tutorial on hierarchical queries in 2008.

For clarity, define and declare are two words that give grief to some newbies. Let’s qualify what they mean. Declare means to give a variable a name and data type. Define means to declare a variable and assign it a value. Another word for assigning a variable is initializing it. Unassigned variables are automatically assigned a default value or a null dependent on the programming language.

Let’s first declare a local variable, assign it to variable, and display the variable. The following example uses Node.js to define the input variable, assign the input variable to the display variable, and then print the display variable to console. Node.js requires that you assign an empty string to the display variable to define it as a string otherwise its type would be undefined, which is common behavior in dynamically typed languages.

/* Declare the display variable as a string. */
var display = ""
 
/* Define the input variable. */
var input = "Hello World!"
 
/* Assign the input variable contents to the display variable. */
display = input
 
/* Print the display variable contents to console. */
console.log(display)

It prints:

Hello World!

Let’s write the same type of program in MySQL. Like the Node.js, there are implementation differences. The biggest difference in MySQL or other relational databases occurs because SQL is a declarative set-based language. That means every variable is a collection of a record structure . You can only mimic a scalar or primitive data type variable by creating a record structure with a single member.

In the case below, there are four processing steps:

The ‘Hello World!’ literal value is assigned to an input variable.
The SELECT-list (or comma-delimited set of values in the SELECT clause) is assigned like a tuple to the struct collection variable by treating the query of the literal value as an expression.
The FROM clause returns the struct collection as the data set or as a derived table.
The topmost SELECT clause evaluates the struct collection row-by-row, like a loop, and assigns the input member to a display variable.

The query is:

SELECT  struct.input AS display
FROM   (SELECT 'Hello World!' AS input) struct;

Since the struct collection contains only one element, it displays the original literal value one time, like

+--------------+
| display      |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

Let’s update the SQL syntax to the more readable, ANSI 1999 and forward, syntax with a Common Table Expression (CTE). CTEs are implemented by the WITH clause.

WITH struct AS
 (SELECT 'Hello World!' AS input)
SELECT struct.input AS display
FROM   struct;

The best thing about CTE values they run one-time and are subsequently available anywhere in your query, subqueries, or correlated subqueries. In short, there’s never an excuse to write a subquery twice in the same query.

Let’s look at loops and if-statements. Having established that we can assign a literal to a variable, re-assign the value from one variable to another, and then display the new variable, let’s assign a set of literal values to an array variable. As before, let’s use Node.js to structure the initial problem.

The program now assigns an array of strings to the input variable, uses a for-loop to read the values from the input array, and uses an if-statement with a regular expression evaluation. The if-statement determines which of the array value meets the condition by using a negating logical expression. That’s because the search() function returns a 0 or greater value when the needle value is found in the string and a -1 when not found. After validating that the needle variable value is found in an input string, the input value is assigned to the display variable.

/* Declare the display variable as a string. */
var display = ""
 
/* Declare a lookup variable. */
var needle = "Goodbye"
 
/* Define the input variable as an array of strings. */
var input = ["Hello World!"
            ,"Goodbye, Cruel World!"
            ,"Good morning, too early ..."]
 
/* Read through an array and assign the value that meets
 * the condition to the display variable. */
for (i = 0; i < input.length; i++)
  if (!(input[i].search(needle) < 0))
    display = input[i]
 
/* Print the display variable contents to console. */
console.log(display)

Then, it prints the display value:

Goodbye, Cruel World!

To replicate the coding approach in a query, there must be two CTEs. The needle CTE assigns a literal value of ‘goodbye’ to a one-element collection of a single-member tuple variable. The struct CTE creates a collection of strings by using the UNION ALL operator to append three unique tuples instead of one tuple as found in the early example.

The needle CTE returns a one-element collection of a single-member tuple variable. The struct CTE returns a three-element collection of a single-member tuple, which mimics an array of strings. The needle and struct CTEs return distinct variables with different data types. A cross join operation between the two CTEs puts their results together into the same context. It returns a Cartesian product that:

Adds a single-row tuples to each row of the query’s result set or derived table.
Adds a multiple-tuples to each row of the query’s result set or derived table by creating copies of each row (following the Cartesian set theory which multiplies rows and adds columns).

In this case, the Cartesian join adds a one-element needle CTE value to each element, or row, returned by the multiple-element struct CTE and produces the following derived table:

+-----------------------------+---------+
| display                     | lookup  |
+-----------------------------+---------+
| Hello World!                | goodbye |
| Goodbye, cruel world!       | goodbye |
| Good morning, too early ... | goodbye |
+-----------------------------+---------+
3 rows in set (0.00 sec)

The following query reads through the CTE collection like a loop and filters out any invalid input values. It uses the MySQL regular expression like function in the WHERE clause, which acts as a conditional or if-statement.

WITH needle AS
 (SELECT 'goodbye' AS lookup)
, struct AS
 (SELECT 'Hello World!' AS input
  UNION ALL
  SELECT 'Goodbye, cruel world!' AS input
  UNION ALL
  SELECT 'Good morning, too early ...' AS input)
 SELECT struct.input AS display
 FROM   struct CROSS JOIN needle
 WHERE  REGEXP_LIKE(struct.input, CONCAT('^.*',needle.lookup,'.*$'),'i');

It returns the one display value that meets the criteria:

+-----------------------+
| display               |
+-----------------------+
| Goodbye, cruel world! |
+-----------------------+
1 row in set (0.00 sec)

The comparisons of the imperative programming approach in Node.js and declarative programming approach should have established that SQL has all the elements of a programming language. That is, SQL has variable declaration and assignment and both iterative and conditional statements. SQL also has different styles for implementing variable declaration and the examples covered subqueries and CTEs with cross joins placing variables in common scope.

Comparative Approaches:

Next, let’s examine a problem that a programmer might encounter when they think SQL only queries or inserts, updates, or deletes single rows. With that perspective of SQL there’s often a limited perspective on how to write queries. Developers with this skill set level typically write only basic queries, which may include inner and outer joins and some aggregation statements.

Let’s assume the following for this programming assignment:

A sale table as your data source, and
A requirement to display the type, number, pre-tax sale amount, and percentage by type.

The sale table definition:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| sale_id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| item_desc  | varchar(20)  | YES  |     | NULL    |                |
| unit_price | decimal(8,2) | YES  |     | NULL    |                |
| serial_no  | varchar(10)  | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

A basic Node.js program may contain a SQL query that returns the item_desc and unit_price columns while counting the number of serial_no rows and summing the unit_price amounts (that assumes no discount sales, after all its Apple). That type of query leaves calculating the total amount of sales and percentage by type to the Node.js program.

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   s.item_desc " +
                    ",        s.unit_price " +
                    ",        COUNT(s.serial_no) AS quantity_sold " +
                    ",        SUM(s.unit_price) AS sales " +
                    "FROM     sale s " +
                    "GROUP BY s.item_desc " +
                    ",        s.unit_price", function (err, result) { 
     if (err) 
       throw err 
     else { 
       // Prints the index value in the RowDataPacket. 
       console.log(result)
       connection.end()
   }})} 
})

This program would return a JSON structure, like:

[ RowDataPacket {
    item_desc: 'MacBook Pro 16',
    unit_price: 2499,
    quantity_sold: 16,
    sales: 39984 },
  ...
  RowDataPacket {
    item_desc: 'MacBook Air M1',
    unit_price: 999,
    quantity_sold: 22,
    sales: 21978 } ]

While the remaining JavaScript code isn’t difficult to write, it’s unnecessary effort if the developer knew SQL well enough to program in it. The developer could simply re-write the query like the following and return the percentage by type value in the base JSON structure.

WITH sales AS
 (SELECT SUM(unit_price) AS total
  FROM   sale)
SELECT   s.item_desc
,        s.unit_price
,        COUNT(s.serial_no) AS quantity_sold
,        SUM(s.unit_price) AS sales
,        CONCAT(FORMAT((s.unit_price * COUNT(s.serial_no))/sales.total * 100,2),'%') AS percentage
FROM     sale s CROSS JOIN sales
GROUP BY s.item_desc
,        s.unit_price
,        sales.total;

The query uses the sales CTE to calculate and define a tuple with the total sales and adds a derived column calculating the percentage by type of device. It’s probably important to note that aggregation rules require you add the sales.total CTE tuple to the group by clause.

The new query returns this JSON list:

[ RowDataPacket {
    item_desc: 'MacBook Pro 16',
    unit_price: 2499,
    quantity_sold: 16,
    sales: 39984,
    percentage: '17.70%' },
  ...
  RowDataPacket {
    item_desc: 'MacBook Air M1',
    unit_price: 999,
    quantity_sold: 22,
    sales: 21978,
    percentage: '9.73%' } ]

The developer would get a complete JSON list when the new query replaces the old. It also would eliminate the need to write additional JavaScript to calculate the percentage by type of device.

Conclusions:

Leveraging the programming power of SQL is frequently possible in many frontend and backend programming solutions. However, the programming power of SQL is infrequently found in programming solutions. That leaves me to ask: “Is it possible that the almost systemic failure to leverage the programming capabilities of SQL is a result of biases by instructors and mentors to their own limited skill sets?” That likely might be true if their instructors and mentors held the belief that: “No, SQL is not a programming language!”

Candidly, folks that write SQL at the programming level almost always have concurrent mastery in two or more imperative programming languages. They’re probably the ones who say, “SQL is a programming language designed to communicate with relational databases.”

Who are those pesky fence sitters? You remember those, don’t you. They’re the ones who declined to take a position on whether SQL is a programming language. Are they the developers who are still learning, and those without an entrenched, preconceived, or learned bias? Or, do they wonder if SQL is Turing complete?

Written by maclochlainn

June 12th, 2022 at 7:36 pm

Posted in Database,Database Design,DBA,JavaScript,MySQL,MySQL 8,NodeJS

Tagged with MySQL DBA, MySQL Developer

Node.js MySQL Error

without comments

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql')

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^
 
Error: Cannot find module 'mysql'
    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> (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    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)

I explained they could fix the problem with the following two Node.js Package Manager (NPM) commands:

npm init --y 
npm install --save mysql

The student was able to retest the code with success. The issue was simply that the Node.js couldn’t find the NPM MySQL module.

Written by maclochlainn

June 12th, 2022 at 1:58 pm

Posted in Database,DBA,JavaScript,Linux,Microsoft Windows 10,MySQL,MySQL 8,NodeJS,sql,Unix,Windows OS

Tagged with MySQL DBA, MySQL Developer

MySQL and JavaScript

without comments

Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?

// Declare a string and substring.
var myStr = 'Get me from the string.'
var mySubstr = 'me'
 
// Assign the substring to variable by rescuing it from the larger string.
var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length)
 
// Print the result.
print(rescued)

tested with MongoDB, like

mongo --nodb --norc < parsing.js

returning:

MongoDB shell version v4.0.20
me
bye

They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.

They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).

The MySQL steps are:

-- Stable testing scenario with table values requires a test table.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample
( text  VARCHAR(100) );
 
-- Insert the literal string into the testing table.
INSERT INTO sample
( text )
VALUES
('Get me from the string.');
 
-- Test using a WITH clause to place a variable in context for use
-- in the query, relying on the fact that a Cartesian set of one
-- column and row becomes a new column in all rows of the other
-- table's set.
WITH struct AS
(SELECT 'me' AS result)
 SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued
 FROM   sample CROSS JOIN struct;

It returns the following:

+---------+
| rescued |
+---------+
| me      |
+---------+
1 row in set (0.00 sec)

Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.

Written by maclochlainn

October 24th, 2021 at 10:48 am

Posted in JavaScript,Linux,MySQL,MySQL 8,MySQL Client,MySQL Shell,sql

Tagged with MySQL DBA, MySQL Developer, MySQL Techniques

Fun JavaScript Q?

without comments

I got sent a fun JavaScript question from a student. It was fun for two reasons. They were clearly engaged and trying to figure stuff out on their own and they were just beyond basic syntax issues.

The student wanted to know why this code worked:

// Populate an object with one property.
var notempty = {test:"me"}
 
// Test for and enumerate object properties.
for (var property in notempty) {
  if (!notempty.hasOwnProperty(property)) {
    print("No properties")
  }
  else {
    print("[" + property + "]")
  }
}
print("done")

and returned:

[test]
done

but the behavior changed when the initialized object didn’t have any properties. It failed to print the No properties message:

// Populate an object with one property.
var empty = {}
 
// Test for and enumerate object properties.
for (var property in empty) {
  if (!empty.hasOwnProperty(property)) {
    print("No properties")
  }
  else {
    print("[" + property + "]")
  }
} print("done")

it printed:

done

The answer was easy, there were no properties to enumerate, which meant the loop never executes. However, that’s only part of the answer. The complete answer is that the empty object and the default Object.prototype have no properties to enumerate. You should perform a different test before the loop, like:

// Populate an object without properties.
var empty = {}
 
// Test for and enumerate object properties.
if (Object.keys(empty).length !== 0) {
  for (var property in empty) {
    if (empty.hasOwnProperty(property)) {
      print("[" + property + "]")
    }
  }
} else {
  print("No properties")
}
 
// Print complete message.
print("done")

If you run this from the mongo shell, like this

mongo --nodb --norc < object_properties.js

It prints:

MongoDB shell version v4.0.19
No properties
done
bye

If you run this inside the mongo shell with the load function, like

load("object_properties.js")

It prints:

No properties
done
true

However, you can generate a parsing error when you rewrite line 11 in the if-else statement, like so

if (Object.keys(empty).length !== 0) {
  ...
}
else {
  ...
}

The reason for the error is that JavaScript appends a semicolon (;) at the end of each line. Therefore, the if-else-statement appears as an if-only-statement. which raises the syntax error.

It prints:

MongoDB shell version v4.0.19
2021-08-02T13:38:34.672-0600 E QUERY    [js] SyntaxError: expected expression, got keyword 'else' @(shell):1:0
done
bye

It appears the parsing error is a warning message because the code completes. Personally, I would prefer that they not raise a warning message because it actually implies a style preference.

There’s a next level problem you may run into while trying to implement reflection, or the discovery of existing properties and their value types, of JavaScript objects. Here’s that code, which seems to be missing from many tutorials and articles:

// Create an object with functions.
// Populate an object without properties.
var notEmpty = { first_name: "Clark"
               , last_name: "Kent"
               , getName: function() { return this.first_name + " " + this.last_name }
               }
 
// Test for and enumerate object properties.
if (Object.keys(notEmpty).length !== 0) {
  for (var property in notEmpty) {
    if (notEmpty.hasOwnProperty(property)) {
      print("[" + property + "] value is a [" + typeof(notEmpty[property]) + "]")
    }
  }
} else {
  print("No properties")
}
 
print("done")

This code on line 12 lets you view a property’s value type:

1	typeof(notEmpty[property])

Only the object[property] subscript notation method works when the property’s value is a variable. The other dot notation syntax to access a property’s value in JavaScript, object.property, doesn’t work without a literal value. Actually, it would work if you embed it inside an eval() function call, but the eval() function is bad juju (black magic) in JavaScript coding.

It prints the following:

MongoDB shell version v4.0.19
[first_name] value is a [string]
[last_name] value is a [string]
[getName] value is a [function]
done
bye

While this shows the basic functionality, it would be more effective to create a function and extend it while adding some output formatting. The next example wouldn’t be found in a JSON (JavaScript Object Notation) collection element but would be found in a NodeJS application managing JSON structures. You would define the constructor to match the data properties of the JSON structure.

The nice thing about a JavaScript function is that “It can specify a list of parameters that will act as variables initialized by the invocation arguments. The body of the function includes variable definitions and statement. (JavaScript: The Good Parts, 2008)” Newer approaches allow you to pass an object as a parameter with named notation inside the object.

// Create an object with functions.
function Person(first_name, last_name) {
             this.first_name = first_name
           , this.last_name  = last_name
           , this.setFirstName =
               function(first_name) {
                 this.first_name = first_name }
           , this.getName =
               function() {
                 return this.first_name + " " + this.last_name }
         }
 
// Create an instance of a variable and print results.
var myPerson = new Person("Clark","Kent")
print("Initial Name: " + myPerson.getName())
 
// Call the added function and print the new value.
myPerson.setFirstName("Lois")
print("Reset Name:   " + myPerson.getName())
print()
 
// Test for and enumerate object properties.
print("Variable Property Names & Values")
print("====================================")
if (Object.keys(myPerson).length !== 0) {
  for (var property in myPerson) {
    if (myPerson.hasOwnProperty(property)) {
      print("[" + property + "] value is a [" + typeof(myPerson[property]) + "]")
    }
  }
} else {
  print("No properties")
}
print("====================================")
print("done")

It prints the following:

MongoDB shell version v4.0.19
Initial Name: Clark Kent
Reset Name:   Lois Kent
 
Variable Property Names & Values
====================================
[first_name] value is a [string]
[last_name] value is a [string]
[setFirstName] value is a [function]
[getName] value is a [function]
====================================
done
bye

While the prior example shows object construction, this one introduces one variable inheriting the Person function’s properties and another inheriting the same properties before adding a new one to only the variable. The inherited properties, which are functions, are methods of the variable by delegation.

Below is the modified code. It changes the variable from myPerson to myPerson1 and lines 22 through 28 add the new myPerson2 variable and setLastName function. Lines 47 to 58 enumerate across the new second variable, myPerson2 to show you that it has a new setLastName function.

// Create an object with functions.
function Person(first_name, last_name) {
             this.first_name = first_name
           , this.last_name  = last_name
           , this.setFirstName =
               function(first_name) {
                 this.first_name = first_name }
           , this.getName =
               function() {
                 return this.first_name + " " + this.last_name }
         }
 
// Create an instance of a variable and print results.
var myPerson1 = new Person("Clark","Kent")
print("Initial Name: " + myPerson1.getName())
 
// Call the added function and print the new value.
myPerson1.setFirstName("Lois")
print("Reset Name:   " + myPerson1.getName())
print()
 
// Create an instance of a variable and print results.
var myPerson2 = new Person("Bruce","Wayne")
print("Initial Name: " + myPerson2.getName())
 
// Add a function to the local variable.
function setLastName(last_name) { this.last_name = last_name }
myPerson2.setLastName = setLastName
print()
 
// Test for and enumerate object properties.
print("Variable Property Names & Values")
print("====================================")
if (Object.keys(myPerson1).length !== 0) {
  for (var property in myPerson1) {
    if (myPerson1.hasOwnProperty(property)) {
      print("[" + property + "] value is a [" + typeof(myPerson1[property]) + "]")
    }
  }
} else {
  print("No properties")
}
print("====================================")
print()
 
// Test for and enumerate object properties.
print("Variable Property Names & Values")
print("====================================")
if (Object.keys(myPerson2).length !== 0) {
  for (var property in myPerson2) {
    if (myPerson2.hasOwnProperty(property)) {
      print("[" + property + "] value is a [" + typeof(myPerson2[property]) + "]")
    }
  }
} else {
  print("No properties")
}
print("====================================")
print("done")

The program shows you the following output:

MongoDB shell version v4.0.19
Initial Name: Clark Kent
Reset Name:   Lois Kent
 
Initial Name: Bruce Wayne
 
Variable Property Names & Values
====================================
[first_name] value is a [string]
[last_name] value is a [string]
[setFirstName] value is a [function]
[getName] value is a [function]
====================================
 
Variable Property Names & Values
====================================
[first_name] value is a [string]
[last_name] value is a [string]
[setFirstName] value is a [function]
[getName] value is a [function]
[setLastName] value is a [function]
====================================
done
bye

As always, I hope this helps those working through a similar issue.

Written by maclochlainn

August 2nd, 2021 at 5:21 pm

Posted in JavaScript,JSON,MongoDB,NodeJS

Tagged with MongoDB DBA, MongoDB Developer

MongoDB Duplicate Key

without comments

How do you avoid encountering a “duplicate key on update” in MongoDB? Well, that’s an interesting question if you’re coming at it from a relational perspective. The answer is understanding that while there may be a natural key, the update needs to use the unique identifier (or, _id). The unique identifier is essentially a primary key based on a system generated surrogate key, assigned by the insertOne() or insertMany() methods. As you’ll notice in the example, it’s not just a numeric value.

The following builds you a test case and is designed to run from the interactive mongo shell. It assumes you understand enough JavaScript to read the code dealing with the JSON (JavaScript Object Notation) structure.

Insert three rows in a people collection with the insertMany() method:

> db.people.insertMany([
...  {"name" : "joe", "age" : 65}
... ,{"name" : "joe", "age" : 20}
... ,{"name" : "joe", "age" : 49}])

it returns the following unique identifiers:

{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("60a4a67f8d03d82365e994ab"),
                ObjectId("60a4a67f8d03d82365e994ac"),
                ObjectId("60a4a67f8d03d82365e994ad")
        ]
}

Assign the return value from a findOne() method against the people collection to a joe variable:
> joe = db.people.findOne({"name" : "joe", "age" : 20})
> joe = db.people.findOne({"name" : "joe", "age" : 20})
Typically, you can print local variables in the mongo shell with the print command, like:
print(joe)
print(joe)
it returns the following:
[object BSON]
[object BSON]
The object is a Binary JSON object, which requires you use the following command:
printjson(joe)
printjson(joe)
It prints the following JSON element:
{ "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 20 }
{ "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 20 }
Increment the age value from 20 to 21:
> joe.age++
> joe.age++
You can show the change in the age member’s value by printing the JSON element with the printjson() function:
{ "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 }
{ "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 }

If you attempt to replace the JSON structure in the MongoDB collection with anything other than the following, you’ll raise a duplicate key error. You must reference the _id value to update the correct element of the collection. The following syntax works:

db.people.replaceOne({"_id" : joe._id}, joe)

it should return the following:

{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

You can verify that the change only affected the originally queried row with the following command:

db.people.find()

it should return the following:

{ "_id" : ObjectId("60a4a67f8d03d82365e994ab"), "name" : "joe", "age" : 65 }
{ "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 }
{ "_id" : ObjectId("60a4a67f8d03d82365e994ad"), "name" : "joe", "age" : 49 }

I thought this example may help others because the O’Reily’s MongoDB: The Definitive Guide example (on pages 35-37) uses a manually typed string, which doesn’t lend itself to a programmatic solutions. It also failed to clarify that value returned from the findOne() method and assigned to the local joe variable would be a BSON (Binary JSON) object; and it didn’t cover any means to easily convert the BSON to a JSON text. The reality is there’s no necessity to cast the BSON into a JSON object, also left out of the text. All that said, it’s still an excellent, if not essential, book for your technical library if you’re working with the MongoDB database.

Written by maclochlainn

May 22nd, 2021 at 11:09 pm

Posted in JavaScript,MongoDB

Tagged with MongoDB DBA, MongoDB Developer

MongoDB Script Test

without comments

There are many ways to test and edit files. A lot of developers only use their favorite Integrated Developer Environment (IDE) but I find testing script files within the scope of a pipelined set of scripts much faster.

The ability to edit a JavaScript file from within the mongo Shell would be nice but unfortunately, it doesn’t exist. You are able to edit a complex variable with a mechanism quite like the Oracle Database. Rather than leave you hanging on how to edit a complex variable in the mongo shell, here are the steps before launching into how to test your JavaScript files:

You can enter this manually during any connection to the mongo shell or put it in your .mongorc.js configuration file, like this in Fedora with the fully qualified filename:
EDITOR="/usr/bin/vim"
EDITOR="/usr/bin/vim"
Let’s say you have a stooges array variable that contains “Moe”, “Curly”, and “Larry” and you want to add “Shemp” to the variable. You can edit the stooges array variable with vi and add “Shemp” to it by typing edit and the stooges variable name.
edit stooges
edit stooges
Then, you can test the stooges array variable’s new values:
stooges
stooges
It returns the following:
[ "Moe", "Curly", "Shemp", "Larry" ]
[ "Moe", "Curly", "Shemp", "Larry" ]

Unfortunately, these changes to the demo array variable will be lost after you break the connection. While it does afford a quick test case, you should make the changes in the external JavaScript file. Then, the change is there the the next time you access the resource file.

Here’s my quick edit and test script technique for MongoDB from your present working directory:

Assume you create a compliment.js test file, like:

/* Declare an array variable and random index value. */
var compliment = ["Bashful","Doc","Dopey","Grumpy","Happy","Sleepy","Sneezy"]
var index = Math.floor(Math.random()*7)
 
/* Print a welcome message. */
print("Hello, " + compliment[index] + ".")

You can edit the compliment.js file with vi and test the script interactively from the present working directory.
- You can edit the file with the following syntax:
  vi compliment.js
  vi compliment.js
- then, you can test the task.sql file:
  mongo --nodb --norc < compliment.js
  mongo --nodb --norc < compliment.js
- The --nodb option instructs MongoDB to not connect to a database and the --norc option instructs MongoDB to not load your .mongorc.js file. Effectively, disabling the database connection and loading of the MongoDB resource file (.mongorc.js) lets you test your code in MongoDB’s Javascript shell unencumbered by any overhead from the MongoDB server.
  The foregoing script returns the following:
  MongoDB shell version v4.0.19 Hello, Sneezy. bye
  MongoDB shell version v4.0.19 Hello, Sneezy. bye
- If you have the desired outcome, you’re done. However, if you need further change you repeat the process.

As always, I hope this helps those looking for a quick way to accomplish a task.

Written by maclochlainn

April 11th, 2021 at 1:45 pm

Posted in JavaScript,JSON,Linux,MongoDB

Tagged with MongoDB DBA, MongoDB Developer

JavaScript Streams?

without comments

A quick followup to my post on how to write a server-side shell component in JavaScript. Naturally, it’s based on a question posed to me about the original article. It asked, “Why didn’t I use JavaScript’s streams instead of synchronized files?”

Moreover, the question asks why I wrote logic (lines 69 thru 105) that wrote to local files rather than a stream. While they didn’t provide an example, here’s a rewritten solution that uses a stream.

  else {
    // Returns RowDataPacket from query.
    for(let element in result) {
      data += result[element].item_title + ', ' + result[element].release_date = '\n'
    }
 
    // Write file when data string is not empty.
    if (data.length > 0) {
      buffer = Buffer.alloc(data.length, data)
 
      // Check for data from database query and write file.
      if (path.length > 0) { 
        let writeStream = fs.createWriteStream(path)
        writeStream.write(buffer) 
        writeStream.on(buffer) 
      } 
      // Set standard out (stdout) and exit program.
      console.log(data)
      process.exit(0)
    }
    else {
      console.error('Query returned no rows.')
    }
  }

The Node.js stream replacement code has a significant problem when the target file can’t be read and written to by a Node.js application. This could be an ownership- or permission-driven problem coupled with the lazy file opening behavior of a stream in JavaScript. The lazy open is a hidden behavior of the createWriteStream() method, which actually calls the fs.open() method. It may raise the following type of error:

events.js:174 
     throw er; // Unhandled 'error' event 
     ^ 
 
Error: EACCES: permission denied, open 'output.csv' 
Emitted 'error' event at: 
   at lazyFs.open (internal/fs/streams.js:277:12) 
   at FSReqWrap.args [as oncomplete] (fs.js:140:20)

You can prevent this type of unhandled exception by putting this type of block right after you verify the target file name in the script. It ensures that your program raises a handled exception before your code tries to access a target file as a stream.

// Verify access to the file. 
try { 
  fs.accessSync(path, (fs.constants.R_OK && fs.constants.W_OK)) 
  access = true 
} 
catch { 
  console.error("Error accessing [%s] file.", path) 
}

Naturally, you also need to define the access variable at the top of your script. The preceding block lets you set the access variable to true on line 72 when you have permissions to the file used by the stream. It also lets you replace line 76 (from the prior example code) with the following statement that effectively blocks any attempt to use a stream that will fail because of the lazy file opening process:

76	if (access && (path.length > 0)) {

Adding the extra block does lengthen the program, and change line numbers. I hope I’ve adjusted in a way that makes sense by referencing the old numbers for the change of the decision making if-statement.

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

Written by maclochlainn

August 4th, 2020 at 1:47 am

Posted in JavaScript,Linux,NodeJS

Tagged with Linux Developer, Linux Development

MySQL JSON Server

with one comment

A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.

I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.

The Node.js solution is completely portable between Windows and Linux. You can deploy it to either platform without any edits for Windows case insensitive Command-Line Interface (CLI). Clearly, Node.js offers a replacement for direct interaction with the .NET components in PowerShell. This appears to mean basic Linux shell or PowerShell knowledge is all that’s required to write and deploy JavaScript programs as server-side programming solutions. It means anything that you would have done with the .NET you can do with JavaScript. Likewise, you can replace PHP, C#, Python, or Ruby server-side scripts with JavaScript programs.

// Declare constants. 
const fs = require('fs') 
const util = require('util') 
const express = require('express') 
const mysql = require('mysql') 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare local variables for case insensitive use. 
var data = '' 
var buffer = Buffer.alloc(0) 
var path = '' 
 
// Declare default query variables dates. 
var startDate = new Date('1980-01-01') 
var endDate = new Date() 
 
// Set default endDate value as tomorrow. 
endDate.setDate(new Date().getDate() + 1) 
 
// Define a regular expression for valid file names. 
var regexp = /^([0-9a-zA-Z]+|[0-9a-zA-Z]+\.+[0-9a-zA-Z]{3})$/ 
 
// Assign dynamic variables from arguments. 
var argv = process.argv.slice(2) 
 
// Check for paired values, evaluate and assign them to local variables. 
if ((argv.length % 2) == 0) { 
 for (let i = 0; i < argv.length; i += 2) { 
   // Assign a file name to write to the output path. 
   if ((argv[i].toLowerCase() == '-f') && (regexp.test(argv[i+1]))) { 
     // Assign present working for Windows or Linux. 
     if (process.platform == 'win32') 
       path = '.\\' + argv[1] 
     else 
       path = './' + argv[1] 
   } 
   // Assign a start date from the input string. 
   else if (argv[i].toLowerCase() == '-b') { 
     startDate = new Date(argv[i+1]) 
   } 
   // Assign a end date from the input string. 
   else if (argv[i].toLowerCase() == '-e') { 
     endDate = new Date(argv[i+1]) 
   } 
 } 
} 
else { 
 console.error('Arguments must be in pairs: flag and value.') 
} 
 
// Define and run MySQL query. 
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" 
                ,[startDate, endDate], function (err, result) { 
 if (err) { 
   console.error('Query contains error ...') 
   console.error('-> ' + err) 
 } 
 else { 
   // Prints the index value in the RowDataPacket. 
   for(let element in result) { 
     data += result[element].item_title + ', ' + result[element].release_date + '\n' 
   } 
   // Write file when data string is not empty. 
   if (data.length > 0 ) { 
     buffer = Buffer.alloc(data.length,data) 
 
     // Check for a defined path before writing a file. 
     if (path.length > 0) { 
       // Open the file. 
       fs.open(path, 'w', function(err, fd) { 
         if (err) { 
           console.error('Could not open [' + path + '] file [' + err + ']') 
         } 
         else { 
           // Write the file. 
           fs.write(fd, buffer, 0, buffer.length, null, function(err) { 
             if (err) 
               console.error('Error writing [' + path + '] file [' + err + ']') 
             fs.close(fd, function() { 
               if (fs.existsSync(path)) { 
                 process.exit(0) 
               } 
             }) 
           }) 
         }   
       }) 
     } 
     // Set standard out (stdout). 
     console.log(data) 
   } 
   else { 
     console.error('Query returned no rows.') 
   } 
 } 
}) 
 
// Close MySQL connection. 
connection.end()

// Declare constants. const fs = require('fs') const util = require('util') const express = require('express') const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) // Declare local variables for case insensitive use. var data = '' var buffer = Buffer.alloc(0) var path = '' // Declare default query variables dates. var startDate = new Date('1980-01-01') var endDate = new Date() // Set default endDate value as tomorrow. endDate.setDate(new Date().getDate() + 1) // Define a regular expression for valid file names. var regexp = /^([0-9a-zA-Z]+|[0-9a-zA-Z]+\.+[0-9a-zA-Z]{3})$/ // Assign dynamic variables from arguments. var argv = process.argv.slice(2) // Check for paired values, evaluate and assign them to local variables. if ((argv.length % 2) == 0) { for (let i = 0; i < argv.length; i += 2) { // Assign a file name to write to the output path. if ((argv[i].toLowerCase() == '-f') && (regexp.test(argv[i+1]))) { // Assign present working for Windows or Linux. if (process.platform == 'win32') path = '.\\' + argv[1] else path = './' + argv[1] } // Assign a start date from the input string. else if (argv[i].toLowerCase() == '-b') { startDate = new Date(argv[i+1]) } // Assign a end date from the input string. else if (argv[i].toLowerCase() == '-e') { endDate = new Date(argv[i+1]) } } } else { console.error('Arguments must be in pairs: flag and value.') } // Define and run MySQL query. 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" ,[startDate, endDate], function (err, result) { if (err) { console.error('Query contains error ...') console.error('-> ' + err) } else { // Prints the index value in the RowDataPacket. for(let element in result) { data += result[element].item_title + ', ' + result[element].release_date + '\n' } // Write file when data string is not empty. if (data.length > 0 ) { buffer = Buffer.alloc(data.length,data) // Check for a defined path before writing a file. if (path.length > 0) { // Open the file. fs.open(path, 'w', function(err, fd) { if (err) { console.error('Could not open [' + path + '] file [' + err + ']') } else { // Write the file. fs.write(fd, buffer, 0, buffer.length, null, function(err) { if (err) console.error('Error writing [' + path + '] file [' + err + ']') fs.close(fd, function() { if (fs.existsSync(path)) { process.exit(0) } }) }) } }) } // Set standard out (stdout). console.log(data) } else { console.error('Query returned no rows.') } } }) // Close MySQL connection. connection.end()

You can call this code with the default values, like

node app.js

You can call this code with a user defined file name, and a custom start and end date values, like

node app.js -f output.csv -b '2001-01-01' -e '2004-12-31'

The latter command returns the following by querying my MySQL studentdb video store:

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 
Harry Potter and the Prisoner of Azkaban, 23-October-2004

As always, I hope this helps somebody trying to sort it out.

Written by maclochlainn

August 1st, 2020 at 1:05 am

Posted in bash,ExpressJS,Fedora,JavaScript,Linux,MySQL,MySQL 8,NodeJS

Tagged with MySQL DBA, MySQL Developer, MySQL Development, use

MacLochlainns Weblog

Archive for the ‘JavaScript’ Category

Oracle 23c MLE JavaScript

MySQL Posts Summary

Is SQL Programming

Node.js MySQL Error

MySQL and JavaScript

Fun JavaScript Q?

MongoDB Duplicate Key

MongoDB Script Test

JavaScript Streams?

MySQL JSON Server

Recent Posts

Things Written About

Pages

Blogroll

Archives