MacLochlainns Weblog

While working through export techniques in MySQL (5.1.34-community), I discovered some tricks and techniques with MySQL exports to a CSV file.

Here’s a standard export statement to a CSV file format:

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
Query OK, 4079 ROWS affected (0.02 sec)

On Microsoft Windows, when you attempt to export it a second time, you’ll get an error unless you’ve previously deleted the physical file. You’ll also need the \r on the Windows platform but not the Linux platform.

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
ERROR 1086 (HY000): File 'c:/Data/City.csv' already EXISTS

While reviewing Alan Beaulieu’s Learning SQL, 2^nd Edition, I noticed he’s got a small example in his Appendix B. He’s using the back-quoted backslash approach to directories in Windows. You can use it, but I prefer the one shown in my examples. Here’s the alternative syntax for the outbound file line:

    -> INTO OUTFILE 'C:\\Data\\City.csv'

When you want to use the CASE statement, you need to use a derived (MySQL terminology). It appears that you can’t include a CASE statement in the SELECT clause when exporting the contents to an OUTFILE. Also, for reference, MySQL doesn’t support the WITH clause.

SELECT *
INTO OUTFILE 'c:/Data/City4.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM (SELECT ID
      ,      CASE
               WHEN Name IS NULL THEN '' ELSE Name
             END AS Name
      ,      CASE
               WHEN CountryCode IS NULL THEN '' ELSE CountryCode
             END AS CountryCode
      ,      CASE
               WHEN District IS NULL THEN '' ELSE District
             END AS District
      ,      CASE
               WHEN Population IS NULL THEN '' ELSE Population
             END AS Population
      FROM City) Subquery;

Hope this helps somebody.

Starting writing with the new publisher’s template and found that I couldn’t use Word 2007 or Word 2008. I ran into a neat twist with the F8 key when building a Windows XP virtual machine for Word 2003. Whether inside or outside the virtual machine, the focus for the F8 key always stayed with Mac OS X. That meant F8 launches Spaces instead of letting the Windows XP install proceed.

The fix was simple enough, I disabled F8 as the launch key for Spaces. Then, it let the Windows XP install work.

Should that work around be there? It’s probably a bug in VMWare Fusion, Version 2.0 (116369). If you know for sure, let me and other readers know.

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

When my old Toshiba Tecra died because of a fried mother board, I salvaged my hard disk. I snagged a 2.5″ USB hard disk case but didn’t have time to clean it up. While cleaning up the old Windows XP files tonight, I ran into the following error:

E:\WINDOWS\Temp\hsperfdata_SYSTEM>attrib +R *.*
Access denied - E:\WINDOWS\Temp\hsperfdata_SYSTEM\2792

I thought the attrib utility might help me fix the error but it didn’t. I checked the file Property dialog for a quick fix and didn’t find one.

A little poking around on the web and I decided that disabling simple file sharing may provide additional options. You disable it by navigating to Start, My Computer, where you click on the Tools option in the menu and chose the Folder Options … to get the following dialog box. You clicked the View tab. In the tab, you scroll down in the Advanced setttings section until you see the Use simple file sharing (Recommened) option. You click the check box to turn off simple file sharing. Click the Apply button to make the change.

Open a separate Explorer session, and navigate to the directory where you’ve encountered a file that denies you access to delete it. Right click on the file. You should see two tabs, one labeled General and the other Security. If you see only the General tab, you may have forgot to check the Apply button earlier.

Click the Security tab to see the following dialog. Click the Allow check box in the Permissions for Everyone section and then click the Apply button. You should now be able to delete the file in Windows Explorer.

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.