MacLochlainns Weblog

It’s always interesting troubleshooting things that students run into when they work with Excel. For example, in Excel when is one not equal to one, or for that matter when is one less than one? If you use the CODE(cell_reference) function, you’d find both cells contain the same ASCII value of 49.

If you’re wondering why the one on the left is less than the one on the right, the one on the right is a text value and the one on the left is a number. If you use the TYPE(cell_reference) function, you’d find that a text field returns a 2 and a number (or date) cell returns a 1. All equality comparisons are identity comparisons in Microsoft Excel, which means they compare type and value. Inequality comparisons, compare type and return a value based on the type number and never compare value when the types differ. There are three other types: (1) A logical value is 4; (2) An error message is 16; and (3) An array is 64.

The answer is when one cell contains a number and the other cell contains a string (text value). That’s done here by putting an apostrophe before the number and right aligning the cell. You solve this by using the VALUE(cell_reference) function to ensure that you’re comparing values not unknown data types because there’s no implicit casting resolution in Microsoft Excel and all comparisons are identity operations.

I haven’t checked Open Office or Numbers, but I’ll do that and update this posting with findings.

I’d tweeted about the award last night but apparently the Oracle ACE Directory Server is down at the moment. At least, I got an AIM message to that effect. Fortunately, I’d left the browser open on my machine on that page. So, here’s the image you’d see on the server when it’s back up.

It’s nice to have received the award.

Oracle™ spoils us for other databases. We begin to expect too much of other database products. At least, that’s the way that I felt while working comparative syntax samples out for my students. I assumed wrongly that all strings would compare based on case sensitive strings. I found that Oracle does that, but MySQL and SQL Server don’t do that with an ordinary = (equals) comparison operator.

Oracle

The = (equals) symbol compares case sensitive strings. The following query returns nothing because a title case string isn’t equal to an uppercase string (databases, as they should be).

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

The query only resolves when the strings are case sensitive matches, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'String';

MySQL

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (a case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol. You may also chuckle that you need the DUAL pseudo table to make this work when there’s a WHERE clause, covered in yesterday’s blog.

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

You can make it case sensitive by using the strcmp function, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE strcmp('String','STRING');

You can also make it case sensitive by using collation, which is the ability to shift it’s character set. Unfortunately, MySQL doesn’t support casting to a binary string, so you must apply a different character set for the equality comparision.

SQL> SELECT 'Truth' AS OUTPUT
  2> FROM dual WHERE 'String' COLLATE latin1_bin = 'STRING' COLLATE latin1_bin;

SQL Server

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (another case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol.

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('STRING' AS VARBINARY);
3> GO

You can make SQL Server resolve case sensitive strings by casting both of the strings to VARBINARY data types, like this:

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('String' AS VARBINARY);
3> GO

There is a clear lack of portability for basic comparison operations. I think its time that folks drop that time worn database agnosticism line because all it means is I don’t want to use that other database. I’ve heard the line too often with regard to PL/SQL, but oddly not about MySQL’s stored procedures or Microsoft’s T-SQL. Maybe I’m an Oracle bigot but it sure seems more ANSI standard like to me to compare strings with a simple = (equals) operator.

All this time I thought MySQL was so progressive by eliminating the DUAL pseudo table when selecting a string literal. I also thought it interesting that they also supported selecting a string literal from the DUAL pseudo table. While playing around with examples for my students, I found out that there is a use case when MySQL requires you to use the DUAL pseudo table.

You must use the DUAL pseudo table when you select a literal that includes a WHERE clause. For example, this fails:

mysql> SELECT 'Truth' WHERE 'STRING1' >= 'string1';

with the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE 'string1' >= 'STRING1'' at line 1

It’s complaining that you haven’t designated a table. However, this succeeds when you add the FROM dual and it does case promotion or demotion to resolve whether the strings are case insensitive matches.

mysql> SELECT 'Truth' FROM dual WHERE 'STRING1' >= 'string1';

It looks so much like Oracle. 😉 The only difference is that Oracle does compare them on the basis of their ASCII values, and would return no rows for this query.

One of my students asked how you validate the foreign keys in a MySQL database. First off, this only works if the database engine supports referential integrity (the fancy word for foreign keys as database level constraints). InnoDB and Falcon support referential integrity. The answer can be found by leveraging the data catalog in the INFORMATION_SCHEMA.

Here’s the query:

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name;

It’s a bit different then the USER_CONSTRAINTS and USER_CONS_COLUMNS views in Oracle, which are covered in this prior post.

I recently ran across another interesting detail on MySQL foreign keys creation and removal that has to do with the case sensitivity of constraints. If you create the constraint in lower case and then try to drop the foreign key constraint in upper case, you may encounter the following error:

ERROR 1025 (HY000): Error on rename of '.\database_name\table_name' to '.\database_name\#sql2-79c-1' (errno: 152)

The reason appears to be that MySQL can’t find the table with the constraint name, and therefore throws an error that appears related to failure writing the new structure. It can be misleading.

I always tell my students how to protect their Oracle credentials but hesitated to do the same for MySQL because it involves Windows batch shell scripting (albeit simple one line scripting). Oracle is nice and clean about hiding credentials. If you only provide the USER name at the command prompt, then your PASSWORD is never displayed. Only the user name is displayed in the DOS command prompt session’s window frame. You do that in Oracle with a student user name, as follows:

C:\Data\Oracle> sqlplus student
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 09:20:25 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:

MySQL isn’t quite so convenient as a command-line tool. You must enter the -u or --user followed by the user name and -p or --password followed by the password when connecting to the database. For example, this shows a typical command prompt window frame:

As noted by Stephen in his comment, you can also mimic Oracle by using the following syntax:

C:\Data\MySQL> mysql -ustudent -p
Enter password:

Unless you use that syntax, anybody walking up can see your USER and PASSWORD in clear text. Not exactly the best security practice, eh? You can also suppress the USER name from displaying by writing a small batch file with your text editor or at the command-line, like this:

C:\Data\MySQL>COPY CON mysqlcmd.bat
@mysql -ustudent -p
^Z

While you’ll need to enter your password because you really don’t want to store that in a file. The @ symbol suppresses echo of the command, which means you won’t display your USER name or PASSWORD when you start the mysql client tool. It will only display the batch file name and the welcome message:

Hope this helps you when you’re working at the MySQL command-line. However, I’m often stunned by how few use it. They prefer phpMyAdmin or Quest’s Toad for MySQL.

A couple students stumbled on the MySQL on-line documentation today. Specifically, they didn’t find instructions on how a non-root user could change their password. I’ve also added this as an explanation on the MySQL documentation for 12.5.1.6. SET PASSWORD Syntax page.

You reset your own password when you’re not the root user with this syntax:

mysql> SET PASSWORD = PASSWORD('new_password');

This is simple syntax but carries a risk if you walk away from your terminal because somebody can run it without needing to know the original user password. Oracle requires that you know the original password if you use this syntax:

SQL> PASSWORD
Changing password FOR PLSQL
OLD password:
NEW password:
Retype NEW password:
Password changed

If you think that you’re safe walking away from an Oracle SQL*Plus console, check this because it doesn’t require knowing the current password either.

SQL> SHOW USER
USER IS "PLSQL"
SQL> ALTER USER plsql IDENTIFIED BY plsql;
USER altered.

Don’t walk away, always lock your terminal or use the QUIT command to exit MySQL. This generally prevents somebody hijacking your password. You can check this post if you’re interested in changing the root password when you don’t have it to begin with. Hope this helps a few folks.

Another faculty member asked me how to convert a DVD to an ISO on his Mac. Here are the instructions for everybody.

He needed to convert it because he wanted to deploy it on a Netbook from a USB drive. We all know you shouldn’t do this unless you’ve paid for the license and are only installing it on a single machine, like the Adobe license specifies.

The only tricky part to this is the last command-line step, which I borrowed from Mac OS X hints.

1. Open Disk Utility on your Mac OS X. It’ll look something like this:

2. Click the New Image icon in the tool bar, and save it as a compressed file. This writes it as a .dmg file, which is an Apple format .iso file.

It’ll take a while to write. The length of time is relevant to the size of the image on the DVD.

3. This is the first of two tricky steps. They’re tricky because you need to use the command-line. If you followed the instructions, you’ve saved the .dmg file on the Desktop. Now, you need to convert the .dmg formatted file to an .iso formatted file. You open Terminal, which is found in your Applications folder. Once launched follow the steps below. Change the directory to the Desktop or the folder where you put the file. You must do this before running this command because I’ve used relative file syntax (more or less only the file name). Alternatively, you could provide fully qualified file names.

MacPro:~ mclaughlinm$ cd Desktop
MacPro:Desktop mclaughlinm$ ls *.dmg
Acrobat9.dmg
MacPro:Desktop mclaughlinm$ hdiutil convert Acrobat9.dmg -format UDTO -o Acrobat9.iso
Reading Acrobat9                         (Apple_ISO : 0)…
...............................................................................................................
Elapsed Time: 27.655s
Speed: 22.2Mbytes/sec
Savings: 0.0%
created: /Users/mclaughlinm/Desktop/Acrobat9.iso.cdr

4. Before you copy it to your Windows USB, you need to remove the trailing .cdr from the file name. The syntax at the command-line is:

MacPro:Desktop mclaughlin$ mv Acrobat9.iso.cdr Acrobat9.iso

5. Open Finder, and copy the .iso to your USB drive, and delete the temporary copy.

Hope this helps some folks.

The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.

The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.

1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.

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
44
45
46
47
48
49
50
51

-- Conditionally delete objects in reverse dependency order.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_type IN ('TABLE','SEQUENCE','TYPE')
            AND      object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE'
                                    ,'CONTACT_BOOK','CONTACT_BOOK_S1')
            ORDER BY object_type, object_name DESC) LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
END;
/
 
-- Create a composite data type (structure) and collection.
CREATE TYPE telephone_type AS OBJECT
( country_code   VARCHAR2(3)
, prefix_code    VARCHAR2(5)
, phone_number   VARCHAR2(9));
/
 
CREATE TYPE telephone_table AS TABLE OF telephone_type;
/
 
-- Create a table with a nested composite table and sequence.
CREATE TABLE contact_book
( contact_book_id   NUMBER
, first_name        VARCHAR2(30)
, last_name         VARCHAR2(30)
, telephone_numbers TELEPHONE_TABLE )
NESTED TABLE telephone_numbers STORE AS telephone_struct;
 
CREATE SEQUENCE contact_book_s1;
 
-- Insert two rows of data.
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Harry','Potter'
,telephone_table(telephone_type('44','20','7299 2127')
                ,telephone_type('44','17','8926 8115')
                ,telephone_type('44','18','3344 5566')
                ,telephone_type('44','72','9878 5436')));
 
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Weasley','Ronald'
,telephone_table(telephone_type('40','021','407 47 46')
                ,telephone_type('44','19','4088 1062')
                ,telephone_type('44','21','4075 5066')));

2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the CARDINALITY function introduced in Oracle 10g. The second leverages a CROSS JOIN and traditional aggregation tools.

2(a). Using the CARDINALITY function is generally the simplest and best approach to this problem:

1
2
3
4

SELECT first_name
,      last_name
,      CARDINALITY(telephone_numbers) list
FROM   contact_book;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Weasley      Ronald            3

2(b). Using the traditional CROSS JOIN and GROUP BY clause:

1
2
3
4
5
6
7

SELECT   cj.first_name
,        cj.last_name
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Ronald       Weasley           3

3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a CROSS JOIN and GROUP BY clause, like this:

1
2
3
4
5
6
7
8
9

SELECT   cj.first_name
,        cj.last_name
,        cj.country_code
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name
,        cj.country_code;

This yield the following data set:

FIRST_NAME   LAST_NAME    COUNTRY_CODE   LIST
------------ ------------ ------------ ------
Harry        Potter       44                4
Ronald       Weasley      44                2
Ronald       Weasley      40                1

If I captured the basics of the question, great. If there are more questions, please let me know.

I’m back from vacation and will start blogging again. I’ve got a couple items folks have asked me to cover and I’ll try to work those in during the next couple of weeks.

My son went over to Beijing to study Chinese this summer. I picked him up at the Airport last Friday. When he was there he found a copy of my Oracle Database 11g PL/SQL Programming in Chinese. He told me about it via Skype. I asked him to bring home a copy since my royalty statements don’t show the book was translated into Chinese. It cost about $12 US (click on the image to see a larger copy). The statements indicate it has only been translated into Russian, Portuguese, and Tata’s Indian Press at this point.

I thought it was an illegal copy, even though it was sold in a mainline store in Beijing as an original. Anyway I sent the scans of the book and ISBN to McGraw-Hill so they could sort it out. They told me it’s a legal copy, translation licensed, and that not all translations fees post to royalty statements before the translation. It’s only the fifth book, and I continue to learn about the process. Tsinghua University Press produces it. If you want a copy in Chinese, you’ll find it at their web site and my book is here. My son said they had an impressive collection of Oracle Press books on sale there too.