MacLochlainns Weblog

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the Mac OS X operating system:

You have the option of installing the Java SDK or JDK. I’ve opted to install Netbeans 8 with JDK 8u45, as you can tell from the screen capture after you launched the file:

It is a standard Mac OS installation, which is why I didn’t bother showing any dialog messages. After installing the Java JDK or SDK, you should download SQL Developer 4.1 from Oracle’s web site. Below is a screen shot of the Oracle download web page where I’ve accepted the license agreement:

If you attempt to launch the installation and you’ve set your Mac Security to the “Mac App Store and identified developers” setting, you should raise the following exception:

If you reset the Mac Security to an “Anywhere” setting, you can install Oracle SQL Developer on Yosemite. Just make sure you reset it to the “Mac App Store and identified developers” setting after you install SQL Developer.

If you launch SQL Developer with the Security “Anywhere” setting, it displays the following dialog:

After you launch the program, you will see the following progress dialog:

The last step of the installation launches SQL Developer, as shown below:

Click the Connections icon to create an initial connection, like the following:

After connecting to the database, you can write and execute a query as shown in the next screen capture:

As always, I hope that this helps those who require an example to install SQL Server on a Mac OS.

It’s possible folks didn’t notice but Mac OS X no longer includes XQuartz by default from Maverick forward. You need to download XQuartz and install it. I’d recommend after you install Xcode.

Launch XQuartz and then either use the bash shell it opens or open a Terminal bash shell session. Inside the shell, you might start Secure Shell (ssh) like this:

Mac-Pro-3:~ michaelmclaughlin$ ssh student@192.168.2.170
student@192.168.2.170's password: 
Last login: Thu Jun  4 14:33:37 2015
[student@localhost ~]$ xclock &
[1] 10422
[student@localhost ~]$ Error: Can't open display:

Granted that’s a trivial error and running the xclock X11 applications isn’t crucial, an error that makes it more important is the following from Oracle’s old Designer/2000 application:

FRM-91111: Internal Error: window system startup failure.
FRM-10039: Unable to start up the Form Builder.

This is the desired behavior. Secure shell (ssh) can’t run it unless you make the connection with the -Y flag. You should use the following syntax:

Mac-Pro-3:~ michaelmclaughlin$ ssh -Y student@192.168.2.170
student@192.168.2.170's password: 
Last login: Tue Jun  9 14:56:55 2015 from 192.168.2.1
/usr/bin/xauth:  file /home/student/.Xauthority does not exist
[student@localhost ~]$ xclock &
[1] 10760

You can safely ignore the .Xauthority does not exist warning message because it’ll create a .Xauthority file and store the magic cookie after the warning message. You should see the xclock program running in the upper left hand corner of your console, like:

It’s terrific that you don’t get a font warning like you typically would using UTF-8 on Linux. Nice that the Mac OS fonts are so well done that there isn’t a raised exception.

Using xclock or xeyes isn’t very useful as a rule, but this method also lets you run any of the Linux GUI applications. For example, the following gedit command lets you run the gedit utility from a Mac OS console. If you’ve installed the gedit plug-ins, you also can use the Terminal console on the remote system.

The process sequence for the command-line is shown below:

1030     1  /usr/sbin/sshd -D     - The root process launches the ssh daemon
3145  1030  sshd: student [priv]  - The sshd launches a ssh session to manage a student ssh session
3152  3145  sshd: student@pts/1   - The ssh session launched to manage the ssh session
3166  3152  -bash                 - The bash shell launched by connecting through the ssh session
3240  3166  gedit                 - The gedit command issued inside a ssh session
3166  3240  gnome-pty-helper      - Launching the gedit session across X11 
3169  3240  /bin/bash             - Launching the Terminal session inside the gedit session across X11
3269  3884  ps -ef                - Command run inside the gedit Terminal session

Hope that helps those who want to use X11 applications on the Mac OS.

What happens when iPhoto doesn’t export movies? One of two things, you re-install iPhoto and risk losing the movies and photos; or you drop down to the Terminal level and move the files manually before re-installing iPhoto.

Option one is easy, you open iPhoto, choose File from menu, and Export… from the File menu list. When you get to the dialog, change it Kind value to original. If everything is working, you should be able to double click the exported file in a Finder window and launch the program with QuickTime Player.

It’s important to know how to use option two when you’ve copied the movies from your iPhone, iPad, or iPod, and then deleted them. At this point, all you have is a recovery option from your local MacBook, MacBook Pro, or iMac to a safe folder (or command-line directory) before updating iPhoto.

This is how you perform option two:

1. Open Finder and navigate to your user’s home directory, left click on Pictures, and then right click on iPhoto Library and choose Open With option from the first floating menu then Terminal on the context (or second) floating menu.
2. In the Terminal window, a pwd command will show you the following directory for a user with the name someuser:

/Users/someuser/Pictures/iPhoto Library

3. Again in the Terminal window, type a ls command will show you the following directory structure:

AlbumData.xml		Info.plist		Projects.db
Attachments		Library.data		ThemeCache
Auto Import		Library.iPhoto		Thumbnails
Backup			Library6.iPhoto		iLifeShared
Caches			Masters			iPhotoAux.db
Contents		Modified		iPhotoLock.data
Data			Originals		iPhotoMain.db
Data.noindex		Previews
Database		ProjectDBVersion.plist

4. In the Terminal window, change directory to the Originals directory with this syntax from the previous directory:

cd Originals

5. The directory structure can differ because of changes over time. The following is a snapshot example from 2012:

Jun 15, 2012    Jun 21, 2012    Jun 24, 2012	Jul 21, 2012    Sep 3, 2012
Jun 18, 2012    Jun 22, 2012    Jul 4, 2012     Aug 14, 2012    Oct 20, 2012
Jun 19, 2012    Jun 23, 2012    Jul 7, 2012     Aug 24, 2012    Nov 21, 2012

6. Change to any of the preceding directories, and copy the *.MOV files to new directory. I’ve elected to copy the files to the following directory or the someuser home directory (you can make the directory using the GUI Finder interface):

/Users/someuser/MovieBackup

While the directory structure can differ because of changes in iPhone versions over time. Once you change into one of subdirectories of the Originals directory, you can move all of the movie files from one of the subdirectories with this command to the MovieBackup directory:

cp *.MOV ~someuser/MovieBackup

It’s possible to move all your files by scripting a more elegant Bash file. As always, I hope this helps those who encounter the problem.

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates a robust development platform and gives you more testing options with the MySQL monitor (the client software).

Notwithstanding, some Windows users appear to malign Apple and the Mac OS X on compatibility, but they don’t understand that it’s a derivative of the Research Unix, through BSD (Berkeley Software Distribution). This Unix lineage chart illustrates it well:

I’m probably loyal to Apple because in the early 1990’s I worked on Mac OS 6, Mac OS 7, A/UX, NeXTSTEP, and AIX/6000 (Version 3) while working at APL (American President Lines) in Oakland, California. Back then, my desktop was a pricey Macintosh Quadra 950 and today I work on a pricey Mac Pro desktop. The Mac Pro lets me use VMware virtualize development environments for Oracle Linux, Red Hat Enterprise Linux, Fedora, and as you might guess Windows 7/8. My question to those dyed in the wool Microsoft users is simple, why would you choose a single user OS like Windows over a multi-user OS like Mac OS X?

In teaching, I had a problem because my students have different base operating systems, like Windows 7, Windows 8, Linux, and Mac OS X. I needed a teaching and lecture platform that would let me teach it all (not to mention support their environments). That meant it had to virtualize any of the following with a portable device:

I never considered a manufacturer other than Apple for a laptop since they adopted the Intel chip. Too many of the others sell non-hyperthreaded laptop machines that they market as i5 or i7 64-bit OS machines when they’re not. Some of those vendors disable the hyperthreading facility while others provide motherboards that can’t support hyperthreading. The ones I dislike the most provide a BIOS setting that gives the impression you can enable hyperthreading when you can’t. All Apple devices, MacBook, MacBook Pro, Mac Mini, and Mac Pro do fully support a 64-bit OS and their virtualization.

A MacBook Pro came to mind but the disk space requirements were 1 TB, and that’s too pricey. I went with the Mac Mini because with 16 GB of memory and a 1 TB drive it was only $1,200. Add a wireless keyboard and mighty mouse, and an HDMI and mini-DVI connections, and I had my solution. Naturally, my desktop is a one generation old Mac Pro with 64 GB of memory and 12 TB of disk space, which supports all the virtual machines used for testing. Note to Apple marketing staff: The prior version of the Mac Pro let you pay reasonable (3rd party) prices for the additional memory and disk drives.

The Mac Mini means I can travel anywhere and plug into the console and demo tools and techniques from a myriad set of platforms without the hassle of moving on and off to frequently VM images. It’s a great solution with only one downside, HDMI to DVI sometimes creates purple toned screens. It’s unfortunate because some venues have monitors that don’t support HDMI).

While I try to contain everything about installing MySQL in a single post that I update from time-to-time, Mac OS X, Mountain Lion (10.8.x), requires some pre-steps. You must install XCode, and the Command Line Tools. This post provides the screen shots and step-by-step instructions.

Before you can do any of these steps, you must connect to the Apple Store and download XCode. Dependent on your internet speed this may take some time. Generally, it’s an hour or less.

1. After installing XCode, click the Rocket Spaceship in the Mac Dock to launch a view of your Applications. Launch XCode by clicking on the hammer overlaying the blue background XCode icon shown below.

2. After launching XCode, click the Install button on the lower right of the System Component Installation screen shown below.

3. You’re prompted for your default user (actually a sudoer authorized user) password. Enter it in the dialog and click the OK button to continue.

4. After entering the valid credentials and a couple minutes, you should see the System Component Installation screen shown below. Click the Start Using XCode button to continue.

5. Inside XCode, click on the XCode menu option and select the Preferences menu option, as shown below.

6. You should be on the General tab of XCode’s Preferences dialog. Click on the Downloads tab.

7. You should see three choices (at the time of writing) in the Downloads tab. As shown, select the Command Line Tools item. It should take only a couple minutes to download. Click the Install button to continue.

8. The Install button disappears and is replaced by an Installed label when the Command Line Tools are installed. You should see the following screen shot.

After completing these steps, return to the other post to install and configure MySQL. While it seems this exists already as content on Apple’s site, it seems some folks wanted me to add it with the step-by-step screen shots.

The first time I had a major failure on my Mac Pro, I lost a 1 TB Seagate drive and ten key virtual machines. It taught me to apply the principles from my production life to my private life. Swapping the fault-prone Seagate drives for Hitachi drives, I began the slow process of rebuilding those virtual environments.

My solution to minimize risk was two fold. First, I put the main 320 GB disk on a time machine backup. Second, I began weekly backups of my virtual machines to two external 2 TB disk drives. After all, I wanted to contain cost.

Paying more attention paid off this week, when I got the flashing drive folder error. It’s the equivalent on the Mac OS X to Microsoft’s Blue Screen of death. This error means the machine can’t find a healthy OS. The problem is that there a number of posts out there, and some aren’t as effective as they appear in solving the problem. More or less, when you see this screen you have two tests before getting a new disk and restoring the image from your time machine.

The first step requires you to reboot the machine that’s stuck looking for an operating system. You can do that by pushing the power button until a reset occurs while simultaneously holding down the Option and key. You’ll know it works if you don’t see the blinking file folder icon and you see a mouse arrow displayed in the screen. It should occur within 5 to no more than 10 seconds after you reboot.

Insert a valid Mac Operating System (OS) disk into the optical drive. It should launch the installation program within 30 to 60 seconds. Click the first proceed button and on the next screen launch the Disk Utility from the displayed menu. In the Disk Utility, click the First Aid tab. Look at the Total Capacity value in the bottom right of the screen. If it provides a numeric value, there’s a hope for your disk recovery. Click on the First Aid tab to try and recover the disk drive. If it provides a zero numeric value, there’s virtually no hope for your disk recovery.

Since the likelihood of recovering the disk at this point is low, buying a new disk is probably the best step. After you’ve ordered the replacement disk, you can boot your Mac in target mode. Target mode allows you to use another Mac and it’s operating system to run your disks (on a Mac Pro, there can be up to 4 disks).

Remove the CD copy of the operating system from the optical drive and push the power button to turn off your non-working Mac. Start the remote Mac and connect the two using a FireWire cable. After the new target machine has finished booting its copy of the OS, push the power button on the machine that can’t find the OS and hold down the T key. In the target machine, open the Finder and inspect which disks are found. If the base disk drive is excluded from the list, as it is in the screen capture on the right, your disk has failed.

At this point, you should definitely buy a new disk unless you’re covered by AppleCare. If the latter, log a ticket and let them fix it. It’s even possible they may be able to recover something from your failed disk. Although, it is unlikely your get any data back if the Mac OS X software can’t recognize the disk.

Your best bet is to recover the image from a time machine restoration. Hopefully, you had an active time machine image not too far before the failure. I did, and it fully recovered everything smoothly. The new disk arrived this morning, and I’m less than 30 minutes from a full recovery. Though that’s unimportant unless I tell you how.

Once you physically install the disk, you reboot the machine that’s stuck looking for an operating system. You push the power button while simultaneously holding down the Option key. Insert a valid Mac Operating System disk into the optical drive. It should launch the installation program within 30 to 60 seconds. Click the first proceed button and on the next screen launch the Disk Utility from the displayed menu. In the Disk Utility, click the Partition tab to partition the new disk. Then, choose to restore from a time machine in the installation program.

Bottom-line: I’m so happy that time machine works so well!!!

Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.

Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column B of your worksheet. Now you want to load them as comment images in Column A and insert a value in each column A cell that describes the comment image.

Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration. 😉

The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (C:\), a file path, and file name.

Function InsertCommentImage(title As String, cellAddress As Range)
  Dim commentBox As comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (cellAddress.Value)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

A fully qualified address for the cellAddress parameter on a PC would look like this in let’s say cell B1:

C:\Data\Images\WizardChess.png

While it would be like this for the cellAddress parameter on a Mac OS X installation in cell B1:

Macintosh HD:Users:mclaughlinm:Desktop:WizardChess.png

You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell B1 of a worksheet named ImageSource:

=InsertCommentImage("Wizard Chess",B1)

Alternatively, you rewrite InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:

Function InsertCommentImage(title As String, absoluteFileName As String)
  Dim commentBox As Comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (absoluteFileName)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

With the change of the second parameter, you would call the InsertCommentImage() function with two strings, like:

=InsertCommentImage("Wizard Chess","C:\Data\Images\WizardChess.png")

Here’s how it would look if you’d put the formula in cell A1:

This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.

Sub RemoveComment()
  Application.ActiveCell.ClearComments
End Sub

If you want to allow the macro to detach all comments for a range, you would rewrite it this way:

Sub RemoveComment()
  Application.Selection.ClearComments
End Sub

As always, I hope this helps and furthers sharing information.

I finally got around to finishing my testing of Excel 2011 querying MySQL. That meant installing and configuring Excel 2011 and MySQL 5.5.9 on Mac OS X (Snow Leopard). While installing Microsoft Office is pretty trivial, installing and configuring MySQL wasn’t. You can read about installing and configuring MySQL here. A quick test after this, led me to discover that you still need a third party ODBC, as covered in this earlier blog. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver.

I downloaded and installed one of third party ODBC tool sets. I opted for OpenLink Software’s ODBC Driver. Instructions for the install with screen shots are in this blog page. The only downside of this was the discovery that Microsoft’s solution requires Rosetta, like Excel 2008. Rosetta enables Power PC application to run on Intel-based Mac OS X.

Launching the Database icon from Excel 2011, I configured the Data Source Name, which you can find here with screen shots. After you configure the Data Source Name, restarting Excel 2011 is the best choice because otherwise you may see several non-fatal errors.

The following screen shots show you how to establish a connection between Excel 2011 and MySQL, and how to query data from the MySQL database:

1. After you click the Database icon, you see the following dialog. Select a Data Source Name and click the OK button to begin a query.

2. The OpenLink MySQL Lite Login screen requires the user name and password. Click the Connect button to launch the Microsoft Query, which appears to be a native Power PC application that requires Rosetta to run it

3. Microsoft Query appears to be a native Power PC application that requires Rosetta to run it. If you want to enter a query, click the SQL View button.

4. Having clicked the SQL View button you now have a work area where you can enter a standard SQL SELECT statement, like the one below. Then, you click the Return Data button.

5. This dialog lets you select where you want to put the return result set from the query. The default is the absolute cell reference of the top and left most cell, $A$1. Click the OK button to query and load the data into the worksheet.

6. Now you can see the data in the worksheet. The only problem is the extraneous characters returned into the column headers of the table. While tedious, they’re easy to fix. The following illustrates the downloaded result set from the previous query:

7. If you perform a query with a join operation, the column names are never displayed whether you provide aliases to the query or not. It means you have to convert the table to a range, remove the false headers, and recreate the table. This appears to be a limitation of Microsoft Query and unlike the behavior in Excel 2010 on Windows. Perhaps it’s all wrapped up in the emulation provided by Rosetta but I couldn’t find any information about what’s happening. That leaves me with pure speculation, which I never like. If you find the reason, post a comment with a link because everyone would benefit.

As always, I hope this helps those who want to work only in the Mac OS X environment. The risk is Rosetta because it will go away, the only question is when and whether the vendors will fix their dependency first or not. The problem with this solution is that Microsoft Query doesn’t return any tables when it appears that it should.

I’d hoped for a better solution with Excel 2011 on Mac OS X but it appears we still need the third party drivers to connect Excel to MySQL. It’s the same as I reported earlier on Excel 2008. Here’s the message and you can click on it to go the web site and links to buy the drivers.

I tried getting away with installing the MySQL Connector/ODBC before downloading one of those recommended by Microsoft. I discovered that it didn’t work.

Therefore, I download and installed the OpenLink Software ODBC Software, instructions with screen shots are in this blog page. I discovered that this software support track isn’t purely native Mac OS X on Intel-based software because it requires Rosetta like Excel 2008. It also only connects with Microsoft Query, which appears to be a Power PC native application too, at least based on the interface and look and feel. I didn’t do much more research because I’ve concluded that connectivity isn’t ready for prime time on the Mac OS X platform. Microsoft still has more work to do.