MacLochlainns Weblog

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

Sometimes I get poorly thought or just naive questions. That is naive questions because they didn’t read the documentation or don’t understand the semantics of a given programming language. The question this time said they tried to implement what they found on a web page but their sample json_decode function example failed after they followed directions.

Surprise, it didn’t fail because they followed directions. They overlooked part of the example because they didn’t understand how to read a nested array in PHP. The actual example sets up an array of JSON objects, then print_r to read the Array, but the student tried to read it in a foreach loop. Naturally, their sample program raised an error because the base object was an Array not a String, and their target JSON object was nested inside the base Array.

I rewrote the example file to simply convert a JSON structure to an associative array, as follow:

<?php
  // Assign a JSON object to a variable.
  $someJSON = '{"name":"Joe","moniker":"Falchetto"}';
 
  // Convert the JSON to an associative array.
  $someArray = json_decode($someJSON, true);
 
  // Read the elements of the associative array.
  foreach ($someArray as $key =--> $value) {
    echo "[" . $key . "][" . $value . "]";
  }
?>

When you call the program, like this

php test.php

It displays

[name][Joe][moniker][Falchetto]

As always, I hope this helps those looking to display a JSON structure in PHP.

Started playing around with a native installation of PostgreSQL on my Mac OS X. I navigated to the PostreSQL page to download the program. I downloaded and installed the PostgreSQL Version 11.

Then, I had to connect as the postgres user and start the server. You do that by opening a Terminal, assume the role of superuser root, and then connect as the postgres user. The commands are:

sudo sh
su - postgres
whoami 

As the postgres user, you need to set your $PATH environment variable to include the installation of PostgreSQL. You can use the following syntax to add the default directory to the existing $PATH environment:

export PATH=$PATH:/Library/PostgreSQL/11/bin

You can also add the previous line to the postgres user’s .bashrc file, which you’ll need to manually source. You need source the .bashrc file manually because the postgres user can’t connect directly to the server. You must assume the role of the postgres user from the superuser root.

After you have set the environment, you can start the PostgreSQL server with the following command as the postgres user:

pg_ctl -D /Library/PostgreSQL/11/data -l logfile start

Now, you can connect using pgAdmin 4. That’s it for the basic installation. You should see the following after logging in to the PostgeSQL instance:

As always, I hope this helps those trying to sort out the process.

What happens when your 16 year old doesn’t know he has a hole in his pocket, the iPhone slips down his pant leg unnoticed, and he runs over the device with a snowblower. It’s called instant shredded iPhone. You think that’s bad news but that’s why I purchased AppleCare for the device.

Then, you call Apple and discover that unless they can find and read the IMEI number from a chip, there is no warrantee coverage. That tells me AppleCare is worthless against EXTREME damage. It’s only of value when you drop it and break something while the unit remains more or less intact, or you have one of the growing number of iPhone’s with manufacturing defects that you can’t catch within the first year of ownership.

After three calls to Apple, the “senior” technical analyst said you can bring it into the local Apple Store. At that point, I asked, “Did you fail to hear that I live over 250 miles away from the nearest Apple Store?” The analyst said, “Yes, I didn’t hear that.” It was obvious that the situation didn’t fit inside the box that let them close the issue and move on with positive outcome on their staff metrics.

What Apple would like is: The customer pays $99 to replace the phone while they evaluate the pieces to see if they can find an IMEI number. They probably have that policy to avoid fraud on broken iPhones. If they can’t find an IMEI number though, they get to charge full price for the replacement iPhone. I opted for an alternative: they send me a box, I enclose the pieces for evaluation before pay $99 for a replacement iPhone, assuming they can verify from what’s left its was once an iPhone. This way, I can avoid getting soaked for the retail price when my plans support a less expensive upgrade of the now defunct device.

Ultimately, the likelihood of any value from AppleCare appeared to only occur when you break it without destroying it. However, I was wrong because Apple replaced the iPhone. 🙂

You should always have the most current version of pip installed when working with Python. You can upgrade the pip utility with the following command:

sudo pip install --upgrade pip

It should print the following to the console:

Collecting pip
  Downloading https://files.pythonhosted.org/packages/c2/d7/90f34cb0d83a6c5631cf71dfe64cc1054598c843a92b400e55675cc2ac37/pip-18.1-py2.py3-none-any.whl (1.3MB)
    100% |████████████████████████████████| 1.3MB 971kB/s 
Installing collected packages: pip
  Found existing installation: pip 9.0.3
    Uninstalling pip-9.0.3:
      Successfully uninstalled pip-9.0.3
Successfully installed pip-18.1

It was a forced upgrade to run TurboTax. The upgrade was simple because I work on a Mac Pro 2012 (with 64 GB of memory and 12 TB of storage. As you can tell from Apple’s support article, you must upgrade the video card.

I bought the SAPPHIRE Radeon PULSE RX 580 8 GB GDDR5 but when I went to install it after upgrading to macOS 10.13 (High Sierra), there was a catch. The original mother board supports a six socket power supply, which Apple failed to mention in their support article. That meant that I had to order a StarTech.com PCI Express 6 pin to 8 pin Power Adapter Cable.

While I still prefer Apple, I don’t appreciate policy of removing devices from their supported parts list. Does Tim really need to sell new Apple devices that badly that he wants to obsolete hardware when the audience can’t support it by disallowing 3rd party vendors from purchasing parts? I know it’s six years old machine but it also isn’t an entry level machine because it costs more than $6,000 with the memory.

Over the years, this bug never gets fixed. I know it must irritate more people than just me. Unlike those who live in urban communities with great download speeds and relatively inexpensive Internet providers, I live in an area held hostage by expensive CableOne Internet service. Net neutrality won’t fix my issue.

The Apple iTunes bug occurs after you download a movie and the cloud symbol disappears. At first, it may appear as designed, with only one image displayed, like Papillon and Passengers is shown below:

From time to time, Apple iTunes gets confused (polite speak for an intermittent bug) and creates a new iCloud image side-by-side with the downloaded version of the movie. You can see that with the image of The Adventures of Robin Hood:

It annoys me and it takes time to fix. The present solution is to delete the downloaded file image and then re-download it if you must have a local copy (the tedious lives of those outside of large metropolitan areas). Naturally, for those of us outside of large metropolitan areas with monthly restrictions on the size of downloads, Apple’s solution is not a viable workaround. Unfortunately, Apple appears disinclined to figure out what causes the problem or fixing it in the existing iTunes code base. When I called Apple’s iTune support it took a third level engineer to agree that the problem even exists. 😉

It’s interesting to watch people try to solve problems. For example, the student is required to use a scalar subquery in a SQL lab exercise that I wrote. It should be a simple fix. The problem is structured with an incorrect foreign key value in an external CSV file and the restriction that you can not replace the value in the external CSV file. I hoped that students would see the easiest option was to write a scalar subquery in the SELECT clause to replace the value found in the external file. There’s even a hint about how to use a scalar subquery.

Students who are new to SQL can take very interesting approaches to solve problems. The flexibility of SQL can lead them to solve problems in interesting ways. While the following solution worked to solve the problem, it’s wrong on two levels:

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

INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          END AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN common_lookup cl
 WHERE    cl.common_lookup_table = 'TRANSACTION'
 AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
 AND      cl.common_lookup_type = 'CREDIT');

The CASE statement on lines 4 through 12 substitutes a value only when the source value is not a match. That means if the source file is ever correct a null value would become the transaction_type column value, which would make the statement fail because the transaction_type column is NOT NULL constrained in the target transaction table. Therefore, the logic of the student’s approach requires adding an ELSE clause to the CASE statement for the event that the source file is ever corrected. The modified CASE statement would be =the following:

4
5
6
7
8
9
10
11
12
13
14

 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          ELSE
            tr.transaction_type
          END AS transaction_type

The second element of student thought at issue is the CROSS JOIN to the in-line view. It does one thing right and another wrong. It uses the unique key to identify a single row, which effectively adds all the columns for that one row to all rows returned from the external transaction_reversal table. The CROSS JOIN is a correct approach to adding values for computations to a query when you need those columns for computations. The problem with this CROSS JOIN logic may not be immediately obvious when you write it in ANSI SQL 1992 syntax, but it should become obvious when you replace the inline view with a Common Table Expression (CTE) in ANSI SQL 1999 syntax, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

INSERT INTO TRANSACTION
(WITH cte AS
 (SELECT *
  FROM   common_lookup
  WHERE  common_lookup_table = 'TRANSACTION'
  AND    common_lookup_column = 'TRANSACTION_TYPE'
  AND    common_lookup_type = 'CREDIT')
 SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        cte.common_lookup_id AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN cte);

Unfortunately, you would discover that Oracle Database 11g does not support the use of an ANSI SQL 1999 WITH clause inside as the source for an INSERT statement. Oracle Database 12c does support the use of the ANSI SQL 1999 WITH clause inside a subquery of an INSERT statement. That’s an “Oops!” for Oracle 11g because that means the Oracle database fails to meet the ANSI SQL 1999 compliance test. 😉 Great that they fixed it in Oracle 12c. While the nested query would work in Oracle as an ordinary query (outside of an INSERT statement). It raises the following error when you embed it in an INSERT statement:

ERROR AT line 20:
ORA-32034: unsupported USE OF WITH clause

The WITH clause does highlight a key problem with the idea of a CROSS JOIN in this situation. You don’t need all the columns from the common_lookup table. You only need the common_lookup_id column. That make the CROSS JOIN approach suboptimal if it worked.

The complex logic in the original approach is wasted. That’s true because the common_lookup_id value can be supplied to each row as the value from a scalar subquery. The scalar query runs once and the result is placed in the return set for each row. You implement the scalar subquery in the SELECT clause, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,       (SELECT common_lookup_id
          FROM   common_lookup
          WHERE  common_lookup_table = 'TRANSACTION'
          AND    common_lookup_column = 'TRANSACTION_TYPE'
          AND    common_lookup_type = 'CREDIT') AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr);

There really was no intent or logical outcome where the value from the original CASE statement would be different than the subquery’s common_lookup_id value. That fact makes adding an ELSE clause useless, and the solution viable though inefficient. Also, there was no need for the additional columns from the common_lookup table because they are unused. The subquery on lines 4 through 8 provides the optimal solution and improved efficiency.

Developers should ask themselves two questions when they write SQL:

• If my logic is so elegant why do I need it to be so elegant?
• Is there a simpler solution to provide the desired result set?

If there aren’t good answers to both questions, they should re-write it. I hope the examples answer questions and help folks solve problems.

This blog entry shows you how to reset the system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the student user is the sudoer user.

After you sign on to the student user account, you open a Terminal session and you should see the following:

[student@localhost python]$

The oracle user account should be configured to prevent a login. So, you should use the su command or sudo command to open a terminal shell as the root user.

[student@localhost python]$ sudo sh
[sudo] password for student:

As the root user, you can login as the oracle user with the following command:

su - oracle

and, you should see the following prompt. You can see the present working directory (pwd) with the pwd command:

-bash-4.2$ pwd
/u01/app/oracle

You need to source the oracle_env.sh shell file created by the installation of the Oracle Database during the installation. You have two approaches to source the environment file, the first approach is with a dot (.), like

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

or, this

source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

The oracle_env.sh file contains the following:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$ORACLE_HOME/bin:$PATH

Now, you can connect to the Oracle Database as the internal user with the following command:

sqlplus / as sysdba

Once connected as the internal user, you can reset the system user’s password to “cangetin” with this command:

ALTER USER system IDENTIFIED BY cangetin;

At this point, you can also stop and start the database. You stop the database with this command:

shutdown immediate

You can then start the database with this command:

startup

After setting the system user password, sign out of SQL*Plus. Then, you can type two exits to return to the student user account, like this:

-bash-4.2$ exit
logout
sh-4.2# exit
exit
[student@localhost python]$

As always, I hope this helps those who need to reset the system password when they don’t know what it was to begin with.

It was quite annoying to find that TurboTax couldn’t send me a text message to confirm my order of a second state. However, I made the mistake of clicking the “Confirm my account a different way (takes longer)” radio button to get to their web page.

After I got two-step verification enabled on their web site, now it’s impossible to order the second state software. It appears that once you click that button, the software writes it to a file and never prompts you for text, email, or other verification. That seems like a bug to me, what do you think?

Hope this helps others …