MacLochlainns Weblog

This is probably defensive because I’ve had to answer the question about two dozen times since the iPad product announcement. The question is naturally, what do yo think about the iPad?

My perspective is biased by the fact that I’ve been using both DOS/Windows PCs and Macs since the 1980s. They each have merits but in short, unlike the media, I have a bias toward Apple products. In fact, I’m an old NeXT system administrator (software gone from the scene because as rumors have it, Steve wouldn’t think of letting the company become ONLY a software company).

I think the idea of the iPad for eBooks is awesome, the features are terrific. It clearly is a better opportunity for my digital movies but a bit awkward because of its size.

I can’t travel with an iPad by itself because it doesn’t support Microsoft Excel, Word, or Visio. That means I’d have to have my MacBook Pro and iPad. Ouch, the security folks will go nuts at the airport, and my bags are now heavier by about 2 pounds. The iPad is 1.5 pounds but the charger has weight too.

I understand all the logic for the device but there’s an underlying assumption in placing everything on the web. Some data can’t be on the web because of legal limits. This goes to my sticking point. Apple’s Office Suite isn’t as robust as Microsoft’s Office Suite. Keynote is easier to use and easily preferred over PowerPoint, but Numbers isn’t even close to Excel (here Apple fails). The problem with Pages is that many companies have templates built around Word and there’s no easy migration back and forth.

Perhaps Apple will reach out to Power Excel users and invest in Numbers to bring it into this millennium; and maybe they’ll also fix the portability between Word and Pages too. For example, one company I work with insists that I use Word 2003 because they’ve never updated their templates to Word 2007 (easy to do through VMWare Fusion). Then, all that’s needed is a rock solid replacement for Visio on Mac OS X.

I think that I might buy one to experiment with, just so I’m current with the product and new features. I’ve also got some product ideas that I’d like to explore but I don’t think this is a home-run like the iPod and iPhone without vitualization software to enable Windows. As an afterthought, maybe the announcement this summer will be “you can have it all now” when they port most features to the core OS X operating system. That would induce me to upgrade my MacBook Pro, wouldn’t it get you to do so too?

Holiday Gift from Microsoft

Windows 7 ships. Then, we find it’s really Windows Vista+ (code base 6.1). Now, Microsoft give us a late year present, the Microsoft Black Screen of Death. What better excuse to rush out and buy a MacBook Pro?

Holiday Gift from Apple

Buying a MacBook Pro makes sense if you don’t already have one. Owning one, I’m hesitant to upgrade my MacBook Pro because the battery in the new one requires a service call when the battery wears out. An expensive item because the battery life is generally poor after 1,000 recharge cycles and that number of recharges may or may not occur before your Applecare service contract runs out.

Battery life/replacement is one of the reasons why I’ve stayed on my old MacBook Pro (purchased 16 months ago). The other reason is that I plan on getting by with a MacBook Pro for 4 to 5 years not Apple’s apparent plannned obsolescence of 3 years. While I’m in the gripe mode, the new Apple Cinema Display is attractive but not compatible with older MacBook Pro or MacBook computers. Also, the Altona DVI to Mini Display Port has mixed reviews out there and Apple seems disinterested in helping owners of older machines use the new Cinema screens. There aren’t any other alternatives to the Altona product (at least that I’ve found). I almost feel that somebody at Apple watched the movie Robots too often because it seems my 16 month old MacBook Pro is an outmode and there isn’t an upgrade option (only a new purchase).

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.

It’s the end of our Spring term, and yes occasionally somebody can’t sign on to their Oracle instance because their event log is full. They get the following message on Winodws:

C:\>sqlplus sys AS sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production ON Wed Jul 15 10:19:37 2009
 
Copyright (c) 1982, 2008, Oracle.  ALL rights reserved.
 
Enter password:
ERROR:
ORA-28056: Writing audit records TO Windows Event Log failed

The fix is simple, just delete your items from your Windows’ event log.

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.

I got all the screen shots out last night, then I realized that the configuration instructions were missing. They’ve been added now, and you can check if you’re interested.

In the process, I noticed that Zend Server Community Edition is using connection pooling by default with Oracle. This means that the TNS alias must be qualified by a hostname.

For example, on your local PC with Oracle Database 10g Express Edition, the default TNS alias is XE. You can’t use that anymore when you’re running the Zend Server. You must qualify it as localhost/XE or hostname/XE as the connection string. A natural alternative is to insert a fully qualified TNS network map

Here are three possible connection patterns:

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:32:11 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1405)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "hostname/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:29:16 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1403)) * establish * xe * 0

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

<?php
  $tns = "(DESCRIPTION =
              (ADDRESS=(PROTOCOL = TCP)(HOST = mclaughlinxp32)(PORT = 1521))
              (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XE)))";
 
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", $tns);
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:27:10 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1401)) * establish * XE * 0

I’m sure it was in the documents but then again, I didn’t read them.

While installing the MySQL 6.0 Alpha release, I encountered a failure running the configuration component. It shows the following dialog, which hangs until you cancel it. By the way, I’ve encountered this on other MySQL 5.0 and 5.1 installs from time to time.

Don’t uninstall and reinstall because nothing will change. The only problem appears to be setting the root password. This show you how to verify it and fix the missing configuration step. While the service says it failed, it actually started. You can check that by launching services.msc from Start and Run.

You can verify the problem by attempting to connect to the MySQL server. My server is setup on localhost with port 3308 because there are multiple MySQL servers running on my virtual machine. A typically connection would look like this if your password was cangetin (the old Solaris training password):

C:\>mysql -uroot -pcangetin -P3308

If you get the following error message, it’s most likely a missing root password.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Since my machine is running multiple MySQL servers and it’s my preference to associate their execution to their binaries, the paths to the installations aren’t loaded automatically on installation. A quick caution, my path statements are from the Windows XP 64-bit installation and they’ll differ from a 32-bit installation path. Specifically, the executable programs are in C:\Program Files (x86) directory not C:\Program Files. You can set the path like this:

C:\>set PATH=C:\Program Files (x86)\MySQL 6.0\MySQL Server 6.0\bin;%PATH%

To verify and fix the problem requires you login without a password, connect to the mysql database, and query the user table. All those steps follow below, unless you’re on Microsoft Vista. If you’re running Microsoft Vista follow these instructions.

C:\>mysql -uroot -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.10-alpha-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> USE mysql;
Database changed
 
mysql> SELECT host, user, passowrd
    -> FROM user WHERE user='root' AND host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password:
1 row in set (0.00 sec)

You fix this problem by running the following grant of privileges to the root user:

mysql> GRANT ALL ON *.* TO 'root'@'localhost'
    -> IDENTIFIED BY 'cangetin' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

I learned this technique by attending the MySQL for Database Administrator’s course. I hope it solves a mystery for somebody along the way. I also hope that Oracle Education maintains the excellent folks that Sun Microsystems acquired when they snagged MySQL.

There is a nasty little surprise Microsoft PowerShell can deliver when you try to install it in a virtual machine. You can’t do it if you’ve built the VMWare Fusion instance with the default SCSI hard drive option.

It appears that Microsoft PowerShell, a prerequisite for Microsoft SQL Server 2008 Express edition, can’t work with the SCSI driver. At least, when I rebuilt the Microsoft Vista instance with an IDE hard drive it worked.

If anybody knows the details of why it fails, please share it with me and other readers.

Another tidbit about installing Microsoft SQL Server 2008 Express without having installed the .NET Development Suite, you must patch it. These were required on Microsoft Vista (32-bit) before you could successfully install SQL Server 2008 Express Edition.

1. Microsoft .Net Framework 3.5 SP1 (even if you’re more current).
2. Microsoft PluginInstall (verifies you’ve a licensed copy).
3. Microsoft Installer 4.5.
4. Microsoft Windows PowerShell 1.0.

The list of files for the installation on Windows Vista 32-bit are:

The installation screen shots from my earlier install are here. You must also import the correct certificate. A pre-installation Microsoft .NET Application Security warning will stop the installation completely. If you click the warning, you’ll see this message:

Enter the URL in your browser and you’ll get the following if it was successful.

I choose not to enable automatic updates because it often runs at awkward times. Ultimately, the installation worked fine.

I noted a bug in the emca utility in this earlier blog on reconfiguring OEM. It manifest itself in Oracle 11g (11.1.0.6) on the Windows platform. Testing found that it’s fixed in the new Oracle 11g (11.1.0.7). This means you no longer have to manually drop the SYSMAN schema because the following command does that now, like it should.

C:\Data> emca -deconfig dbcontrol db -repos DROP

I was looking for a cool post to point my students to about overriding the ed tool in SQL*Plus but couldn’t find one. A number of posts showed how to set vi as the default editor in Linux or Unix but none showed how to replace Microsoft Notepad with something else. Instructions for both operating environments are here.

Linux or Unix:

This is simple because all you need to do is open a terminal session and type the following command:

# which -a vi

vi is typically a symbolic link to /usr/bin/vi, and it points to /usr/bin/vim in many cases, like Linux or Mac OS X. You can now add that to your SQL*Plus session interactively by typing:

SQL> define _editor=vi

You can set this in your Oracle Database 10g or 11g home, or in the Oracle Database Instant Client. It is found in the $ORACLE_HOME/sqlplus/admin/glogin.sql file, and example is noted at the end of this blog.

Windows:

This is actually quite easy but different releases of Windows provide different behaviors. Some of those behaviors provide alternatives that don’t work in all Windows releases. The off-beat approaches let you launch the alternate editor but they don’t always edit the active buffer. The ones I’ve chosen to show you should work in all Windows releases, but let me know if they don’t in your environment.

1. Install the editor(s) you want to use. I’ve installed and tested GVIM (a vi editor) and Notepad++ on Windows XP and Vista with Oracle Database 10g and 11g.

2. Add the directory path to these products to your system path. This takes four steps. First, you open your System Properties dialog box. Click the Environment Variables button to set an environment variable.

In the Environment Variables dialog box, you should select the PATH variable from the System variables list. Click the Edit button to change the PATH variable.

Add the following in the Edit System Variable dialog box. You should note that you use a semi-colon to separate path elements in Windows (not a colon like Linux or Unix). After you add the editor path, click the OK button. You can append any number of editors if you’ve got a bunch that you like to use.

You should now click the OK button on the Environment Variables and System Properties dialog boxes in turn. Now you can open any command prompt and type the executable name to run the program, like gvim72.exe.

3. The Oracle Database 10g and 11g expect the executable for the default or override ed (editor) utility exists in the %SystemRoot%\System32 directory, which is the C:\WINDOWS\System32 directory. Copy only the executable, like GVIM.EXE, to the C:\WINDOWS\System32 directory.

4. You can now interactively type the following each time you log into the database at the SQL command prompt:

SQL> define _editor=gvim

Alternatively, you can place that command in the following file:

%ORACLE_HOME%\sqlplus\admin\glogin.sql

It is run each time you login to the database. The file would look like this if you wanted to run gvim as your override editor, which means when you type ed to change the SQL buffer file. The SQL buffer file contains the last SQL statement executed. That file is named afiedt.buf, which stands for A File Eidtor Buffer (debunked by Niall in the comment, the AFI stands for AFI Advanced Friendly Interface). As pointed to by Laurent’s comment, you should change the file extension to take advantage of GeSHi (Generic Syntax Highlighter) for your code.

5. After you’ve done all that. If you’d like to include your USER name and TNS alias, you can run the following command interactively or put it in your glogin.sql script. Caution, this only works for Oracle 10g forward.

SQL> SET sqlprompt _user"@"_connect_identifier>

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

STUDENT@orcl>

The rules for setting the SQLPROMPT aren’t intuitive. You can only use one set of double quotes. In the preceding example, the quotes surround the @ symbol between two SQL*Plus macros, which are the _USER and _CONNECT_IDENTIFIER. There’s no magic in that symbol and you can replace it with another. When you want text before, in between, and after a macro or two, you surround the whole thing with double quotes, and allow a white space before macros or use single quotes around string literals.

The white space example works like this:

SQL> SET sqlprompt "SQL: _user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL: STUDENT at orcl>

The nested single quotes example works like this:

SQL> SET sqlprompt "'SQL:'_user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL:STUDENT at orcl>

The single quotes around the SQL: lets you remove the space between the colon and user name. I’ve never seen a way to control case for the macro return values but there may be one. Perhaps somebody will add a comment about it. If you put more than two double quotes in the descriptor passed to SQLPROMPT environment variable, SQL*Plus raises an SP2-0735 error.

6. Here is a sample of the glogin.sql file:

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
-- Define the override or default editor.
define _editor=gvim
-- Set the edit file to allow GeSHI highlighting.
SET editfile=afiedt.sql
-- Set the SQL*Plus prompt to show user and TNS Alias.
SET sqlprompt "'SQL:'_user at _connect_identifier>"

Hope this helps a few folks stuck with Windows as the operating system for Oracle.