MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for February, 2009

Excel string parsing

with 14 comments

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

Written by maclochlainn

February 27th, 2009 at 10:15 pm

Easier way than NDS

with 9 comments

Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.

The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.

Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a weakly typed system reference cursor.
  item_cursor   SYS_REFCURSOR;
 
  -- Define a target variable for the query result.
  item_type_out VARCHAR2(30);
 
  -- Create NDS statement, with a bind or placeholder variable.
  stmt          VARCHAR2(2000) := 'SELECT type_name '
                               || 'FROM   item_type '
                               || 'WHERE  UPPER(type_name) = UPPER(:type_name_in)';
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  OPEN item_cursor FOR stmt USING item_type_in;
 
  -- Fetch the first row return and return the value.
  FETCH item_cursor INTO item_type_out;
 
  -- CLose the cursor.
  CLOSE item_cursor;
 
  -- Return the value.
  RETURN item_type_out;
END;
/

This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a dynamic cursor.
  CURSOR c (item_type_name VARCHAR2) IS
    SELECT type_name
    FROM   item_type
    WHERE  UPPER(type_name) = UPPER(item_type_name);
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  FOR i IN c(item_type_in) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

An even more primitive approach relies on implicit assignment, like the following:

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  -- Open the cursor and rely on implicit assignment within the cursor.
  FOR i IN (SELECT type_name
            FROM   item_type
            WHERE  UPPER(type_name) = UPPER(item_type_in)) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.

Written by maclochlainn

February 24th, 2009 at 9:22 pm

Posted in Oracle,pl/sql

Oracle ENUM Framework

without comments

I went back and edited that post about MySQL nuances not in Oracle from last week about the MySQL ENUM data type. You can find a framework suggestion that lets you not have to change existing string conditioning application code during a migration from MySQL to Oracle.

You’ll see this in the middle of the page, just click it to unfold the details. Yes, JQuery has arrived on my blog.

oracleenumfw

As with everything else on this blog, let me know if you see an opportunity for improvement.

Written by maclochlainn

February 23rd, 2009 at 3:52 am

Posted in MySQL,Oracle,pl/sql,sql

Localhost WordPress on MAMP

with 19 comments

There’s so much energy that goes into blogging with big blog pages, I figured it was time to setup WordPress on my MacBook Pro MAMP (Mac, Apache, MySQL, and PHP) installation. That way, the world doesn’t need to know when I mess up a <div> tag.

The biggest benefit of creating a local blog becomes obvious when you start building your own plugins, templates, and themes. You also have the benefit of a built in backup if you post your work locally before moving it to your blog. Likewise, you can work on your blog when not connected to the network. By the way, thanks to Joel for pointing out that I forgot to say why this was important.

Here are the setup instructions for those interested in doing it too.

1. Download the software from WordPress.org by clicking their button or this one. If you need the tar.gz, click on the web site link.

wpdownload

2. When you download it, you’ll be prompted to open it. Choose to save it.

wpsavedmg

3. You’ll find it in your downloads folder, where you can double click it to launch the unzip process into a file folder. Mine happens on the desktop because it’s convenient.

wpunzip

4. After you’ve unzipped it into a folder. You drag it into your MAMP htdocs folder. You can check where the htdocs folder is by launching the MAMP application, and choosing Preferences.

mampprefs

In the preferences dialog, click the Apache tab, you’ll see where the htdocs folder location is. Mine is set to the default location.

mampapache

5. After you drag the wordpress folder into the htdocs folder, you return to the MAMP application and click the Open start page button.

mamphomemenu

6. Click the phpMyAdmin link and create a database, like the screen shot.

mysqlcreatedbms

After a moment, you should see that you’ve created a new database named WordPress.

mysqlcreateddb

7. Go to your /Applications/MAMP/htdocs/wordpress folder, and open up wp-config-sample.php file in a text editor. You should see the same lines 19, 22, and 25. The editor screen capture is from the TextMate editor.

wordpress-config

Edit those lines to reflect your database name, and the root password. Naturally, if you’re concerned that your local host isn’t secure, you’ll need to purchase MAMP Pro to change your root password. You can’t change it in the free version.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'WordPress');
 
/** MySQL database username */
define('DB_USER', 'root');
 
/** MySQL database password */
define('DB_PASSWORD', 'root');

After these changes, save the wp-config-sample.php file as wp-config.php in the same folder.

8. Enter the following in URL in your browser, and press the enter key.

http://localhost:8888/wordpress/wp-admin/install.php

9. On the Welcome web page, enter your blog name and email (mine is omitted for obvious reasons). Click the Install WordPress button to proceed.

wpinstall

10. You’ll then be prompted with the admin user and a password. You should print it or copy it to your buffer because you’ll need it when you first login. Then, click the Log In button to proceed. You should probably change your password to something that you’ll remember before you do anything else.

11. You now have a complete localhost WordPress installation. Now, you can go to your real web accessible WordPress install and export your blog from the Tools menu. Then, on your localhost blog, you can import from the export file of your blog. If you’re using plugins (like anybody doesn’t), then copy the contents from the wp-content/plugins folder from your online blog to your local one; and then, activate them. You can snag your Akismet key from your online site too, but it is unlikely anybody will see your localhost blog.

Violà, c’est fine.

You can now access WordPress on your local machine by typing in the following URL:

http://localhost:8888/wordpress/

Written by maclochlainn

February 22nd, 2009 at 1:30 am

Posted in Mac,MAMP,MySQL,WordPress

MySQL nuances not in Oracle

with 2 comments

I use Alan Beaulieu’s book in my entry level SQL class because it is simple and short. The problem is that we focus on Oracle products as an Oracle Academic Partner. Three items that come up frequently are the MySQL ENUM and SET data types, and how to perform multiple row inserts.

MySQL’s ENUM data type

The ENUM data type lets you enter a list of possible string values. It acts like a check constraint in an Oracle database. As such, it restricts what you enter in the column to a value found in the list, or a NULL value provided you’ve not added a not null column constraint.

You could define a table that contains video store item types, like the following:

CREATE TABLE item_type
( id INT
, text ENUM ('VHS','DVD','Blu-ray') );

You should note that the case sensitivity for display is set by how you define them in the ENUM data type when you create the table. They may be entered in mixed, lowercase, or uppercase in an INSERT statement because they’re actually stored as a number. The numbers correlate to their order in an internal list of values, and that list start with the number one.

An alternative syntax with a VARCHAR data type is:

CREATE TABLE item_type
( id INT
, TYPE VARCHAR(20) CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

In MySQL, this syntax is exactly equivalent in behavior to an ENUM data type. The same isn’t true in an Oracle database. While the equivalent check constraint statement is simple, it isn’t alike behavior. The comparable statement for Oracle names the constraint. You can’t name constraints in MySQL.

CREATE TABLE item_type
( id   NUMBER
, TYPE VARCHAR2(20) CONSTRAINT it_type CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

The difference between an ENUM type and a check constraint in MySQL is that the data may not display in a uniform way. MySQL check constraints don’t impose case sensitive validation on input strings, and they also store the data however it is input. Whereas, Oracle does impose case sensitive check constraints and rejects non-conforming strings.

Both databases support single and multiple row INSERT statements. The syntax for single row INSERT statements is very much alike. Multiple row INSERT statement syntax differs between the implementations, as shown later in the blog post.

MySQL’s SET data type

The SET data type lets you enter a list of possible string values but differs from the ENUM data type only in how the values are indexed. Values in the SET are stored as bit values. You may store up to 64 members in a set.

You create a table the same way as you did with ENUM data type, except that you use the SET key word.

CREATE TABLE item_type
( id INT
, TYPE SET ('DVD','VHS','Blu-ray'));

All insert patterns shown for the ENUM data type work with the SET data type.

Multiple row INSERT statements

The most common multiple row insert statement in MySQL is a comma delimited set of parenthetical values. Each parenthetical set of values maps to a row in the INSERT statement.

INSERT INTO item_type
VALUES
( 1, 'DVD' ), ( 2, 'Blu-ray'), ( 3, 'VHS' );

You can’t use the foregoing syntax in Oracle. Oralce only supports a multiple row insert with a subquery.

You can write a subquery in MySQL that fabricates an aggregate table by using numeric and string literals, like the following statement.

INSERT INTO item_type
SELECT 1, 'DVD'
UNION ALL
SELECT 2, 'Blu-ray'
UNION ALL
SELECT 3, 'VHS' ;

Another way to perform multiple row inserts in MySQL is to select from an existing table or filtered result set, like this:

INSERT INTO item_type
SELECT some_int, some_varchar FROM some_table;

You raise an error when you use parentheses in a multiple INSERT statement in MySQL. It’s important to note because the Oracle allows you to write the statement either way.

If you’re coming from MySQL to Oracle, you should note that you may use parentheses to enclose a subquery in Oracle. The Oracle SQL parser works with or without them in an INSERT statement.

The MySQL subquery example is the closest to the Oracle syntax for a multiple row insert. The difference is that Oracle give you the option to enclose a subquery in parentheses when using them inside INSERT statements. Oracle requires that you use the FROM dual clause. I actually wish Oracle would adopt the shorter syntax and maintain backward compatibility to the dual pseudo table.

INSERT INTO item_type
( SELECT 1, 'DVD' FROM dual
  UNION ALL
  SELECT 2, 'Blu-ray' FROM dual
  UNION ALL
  SELECT 3, 'VHS'  FROM dual);

Another way to perform a multiple row insert is to select from an existing table, like this:

INSERT INTO item_type
(SELECT some_int, some_varchar FROM some_table);

Other resources:

You should check Oracle’s document that qualifies differences between MySQL and Oracle. It is the Oracle® Database SQL Developer Supplementary Information for MySQL Migrations.


Written by maclochlainn

February 19th, 2009 at 2:58 am

Posted in MySQL,Oracle,sql

Quick XML update

without comments

Somebody commented last week that they’d like the source code for an XSL example file found in this older post. I’ve added the requested code there. The code lets you query directly from your XSL against an Oracle database.

I’ve also created a top page for collecting references to XML blog pages and posts. It’s only got a few but it is an area that will be expanded.

Written by maclochlainn

February 17th, 2009 at 12:20 am

Posted in Oracle,xml,XQuery,xslt

Excel 2007 AND() function

with one comment

Since Excel 2007 and 2008 don’t support a direct exclusive or=XOR() – function, you’d think the logical operators could do it. Something like this:

=AND(NOT(AND(A1,B1)),OR(A1,B1))

It surprised me when it didn’t work. Curious, I figured out why it wouldn’t work. The AND() function doesn’t work as qualified in the help file.

excellogicdefinitions

The AND() function only works correctly when both cell values are not null values. It returns a Boolean true when one value is true and the other value is null, which was quite a surprise.

Fortunately, the IF() function works correctly. If you check a cell with a null value, the IF() function finds it false. That means you can wrap the cells in the IF() function, like:

=AND(NOT(AND(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE()))),OR(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE())))

Naturally, you can substitute the respective string literal for either the TRUE() or FALSE() function calls. You could also rewrite the formula to check for a null value by using two double quotes. By the way, it’s fun to note that the TYPE() function recognizes a null value cell as a number data type.

It’s ashame it doesn’t work the way you’d think it should.

Written by maclochlainn

February 15th, 2009 at 9:48 pm

Data normalization notes

without comments

I’m trying to eliminate the textbook from my database class, and wrote a data normalization blog page for my students; however, it is only done through 3NF so far. The post tries to remove the discrete math and provide clear examples. The students are checking it out for opportunities to make it clearer, if you’ve thoughts let me know.

Also, I’ve substantially update my initial blog page on SQL joins, and will put one out on set operators probably this Monday. If you’ve time to review it, I would appreciate suggestions for improvement.

The length of these blog pages has compelled me to move to a more friendly editor. I’ve opted for Blogo as my Mac blog editor. It comes from Brainjuice. My son’s experience with their customer service and product convinced me. You can find his blog post discussing customer service here.

Blogo prompts you, but beat the rush. Go to your Site Admin, select Writing under Settings in WordPress, and enable the remote XML-RPC publishing protocol. More or less like this prompt.


Written by maclochlainn

February 15th, 2009 at 2:21 am

No code scrolling on iPhone

with 2 comments

I wanted to have scrolling on my iPhone for code segments. It appeared simple. Change the style.css file by adding .code { overflow-x:scroll; }. I first tested it in Safari by setting User Agent to Mobile Safari 1.1.3 – iPhone, where it worked. Alas, it doesn’t work on my iPhone. A quick look at the iPhone simulator taught me it doesn’t work there either. It appears that horizontal scrolling is disabled on the iPhone.

That style sheet is found in the /public_html/blog/wp-content/plugins/wptouch/themes/default directory. I’d love to have missed something. If there’s a trick to do it, let me know.

Written by maclochlainn

February 15th, 2009 at 12:59 am

Posted in iPhone,Mac,WordPress

Notes on SQL Joins

with one comment

It’s almost funny reflecting on when I first learned how to write SQL in 1985. It was using SQL/DS 1.1 (Structured Query Language/Data System) on a VMS/CMS operating system at the IBM center in the Los Angeles, California. That was in the Crocker Bank Building when I worked for First Interstate Bank. The building is still there and so are databases but both banks are long gone.

After 24 years of SQL a quick primer on joins seems like a good idea. I have a simple example that should help illustrate various joins and SQL semantics. You’ll find it as a blog page here …

Written by maclochlainn

February 4th, 2009 at 2:50 am

Posted in MySQL,Oracle,sql