Showing entries 1 to 10 of 43
10 Older Entries »
Displaying posts with tag: unix (reset)
Node.js MySQL Error

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. (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile …
[Read more]
MySQL RegExp Default

We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.

For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:

CREATE TABLE movie
( movie_id     int unsigned primary key auto_increment
, movie_title  varchar(60)) auto_increment=1001;

Then, I inserted the following rows:

INSERT INTO movie 
( movie_title )
VALUES
 ('The King and I')
,('I')
,('The I Inside')
,('I am Legend');

Querying all results with this query:

SELECT * FROM movie;

It returns the following results:

+----------+----------------+
| movie_id | movie_title    |
+----------+----------------+
|     1001 | The King and I |
|     1002 | I              |
|     1003 | The I Inside   |
|     1004 | I am Legend …
[Read more]
MySQL 5-Table Procedure

A student wanted a better example of writing a MySQL Persistent Stored Module (PSM) that maintains transactional scope across a couple tables. Here’s the one I wrote about ten years ago to build the MySQL Video Store model. It looks I neglected to put it out there before, so here it is for reference.

-- Conditionally drop procedure if it exists.
DROP PROCEDURE IF EXISTS contact_insert;

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$

SELECT 'CREATE PROCEDURE contact_insert' AS "Statement";
CREATE PROCEDURE contact_insert
( pv_member_type         CHAR(12)
, pv_account_number      CHAR(19)
, pv_credit_card_number  CHAR(19)
, pv_credit_card_type    CHAR(12)
, pv_first_name          CHAR(20)
, pv_middle_name         CHAR(20)
, pv_last_name           CHAR(20)
, pv_contact_type        CHAR(12)
, pv_address_type        CHAR(12)
, pv_city                CHAR(30)
, pv_state_province      CHAR(30)
, …
[Read more]
Setting SQL_MODE

In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:

Create the set_full_group_by procedure:

-- Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;

-- Reset delimter to allow semicolons to terminate statements.
DELIMITER $$

-- Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN

  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF NOT EXISTS
    (SELECT NULL
     WHERE  REGEXP_LIKE(@@SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
  END IF; …
[Read more]
MySQL WITH Clause

When I went over my example of using the WITH clause to solve how to use a series of literal values in data sets, some students got it right away and some didn’t. The original post showed how to solve a problem where one value in the data set is returned in the SELECT-list and two values are used as the minimum and maximum values with a BETWEEN operator. It used three approaches with literal values:

  • A list of Python dictionaries that require you to filter the return set from the database through a range loop and if statement that mimics a SQL BETWEEN operator.
  • A WITH clause that accepts the literals as bind variables to filter the query results inside the query.
  • A table design that holds the literals values that an analyst might use for reporting.

It was the last example that required elaboration. I explained you might build a web form that uses a table, and the table could allow a …

[Read more]
Title Case Anyone?

Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his …

[Read more]
MySQL Transaction Scope

The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.

Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.

The code for this example on transaction management lets you perform the important tasks necessary to effect transaction …

[Read more]
Node.js & MySQL

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 …

[Read more]
MySQL Linux to Windows

My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.

The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:

ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30

It would return something like:

192.168.147.198

After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy …

[Read more]
Apache on Fedora 30

There was an option during the Fedora 30 Workstation installation to add the Apache Web Server, but you need to set it to start automatically. Unfortunately, there was no option to install PHP, which I thought odd because of how many web developers learn the trade first on PHP with a LAMP (Linux, Apache, MySQL, Perl/PHP/Python) stack. You see how to fix that shortcoming in this post and how to install and test PHP, mysqli, and pdo to support MySQL 8.

Before you do that make sure you install MySQL 8. You can find my prior blog post on that here.

You set Apache to start automatically, on the next boot of the operating system, with the following command:

chkconfig httpd on

It creates a symbolic link:

Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → …
[Read more]
Showing entries 1 to 10 of 43
10 Older Entries »