MacLochlainns Weblog

Finally, the Oracle 11g XE Beta has arrived. Actually, it appears I missed it when Justin announce it. Anyway, it is available for download here. It’s now complete and downloadable, the links are here.

A student encountered a connection problem with a PHP application that failed to resolve to the database. The steps to validate this are to check the PHP credentials, which are in this older post. Once you’ve done that, you should do:

1. Check the tnsnames.ora file contents, they should have a hostname value not an IP address. When you’ve not set your machine hostname in Microsoft Windows host file (mine in the example is: McLaughlin7x64), Oracle opts for the IP address.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

2. Check if the Oracle TNS (Transparent Network Substrate) validates with this command:

tnsping xe

3. If the prior step fails, check to see if you’re listener is running and that it’s configuration file looks more or less like this sample. If it doesn’t, you should edit this listener.ora file and start the listener process through the services dialog.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    )
  )

4. You should then be able to connect like this:

sqlplus username/password@xe

Hope this helps a few people.

Somebody wanted to know if you could add a NOT NULL column constraint in MySQL. That’s a great question and the answer is yes. The following example shows you how to do it.

1. Create a sample table without a NOT NULL constraint on a column that should have one. After creating this table, describe it and you’ll see that the testing_text column is

CREATE TABLE testing
( testing_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, testing_text VARCHAR(10));

2. Change the column definition from null allowed to not null for the TESTING_TEXT column. The only problem with this syntax is that it only works when there are no null values in the table or there are no rows in the table.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10) NOT NULL;

3. Change the column definition from not null constrained to null allowed for the TESTING_TEXT column.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10);

As always, I hope this helps.

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.

Writing an anonymous block to conditionally drop tables and sequences got very old. I figured it was time to simply put a procedure that would simplify it. Avoiding repetition was important too, so it supports an invoker rights model. This mimics the IF EXISTS syntax available in MySQL.

You need to create this as the SYSTEM user and then grant execute permission on the procedure to PUBLIC. Then, you need to create a couple public synonyms.

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

-- Create a procedure to simplify dropping objects.
CREATE OR REPLACE PROCEDURE drop_ifexists
( pv_type   VARCHAR2
, pv_table  VARCHAR2 ) AUTHID CURRENT_USER IS
 
  -- String for DDL command.
  sql_text  VARCHAR2(2000);
 
  -- Declare a parameterized cursor.
  CURSOR find_object
  ( cv_type   VARCHAR2
  , cv_table  VARCHAR2 ) IS
    SELECT   uo.object_name
    ,        uo.object_type
    FROM     user_objects uo
    WHERE    uo.object_name = UPPER(cv_table)
    AND      uo.object_type = UPPER(cv_type);
 
BEGIN
 
  -- Open the cursor with the input variables.
  FOR i IN find_object(pv_type, pv_table) LOOP
 
    -- Check for a table object and append cascade constraints.
    IF i.object_type = 'TABLE' THEN
      sql_text := 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      sql_text := 'DROP '||i.object_type||' '||i.object_name;
    END IF;
 
    -- Run dynamic command.
    EXECUTE IMMEDIATE sql_text;
 
  END LOOP;
 
END drop_ifexists;
/

After creating the procedure in the SYSTEM user schema, you should run these Data Control Language (DCL) commands:

GRANT EXECUTE ON drop_ifexists TO PUBLIC;
CREATE PUBLIC SYNONYM drop_ifexists FOR system.drop_ifexists;

Assuming you have a table named MESSAGE, you can call the drop_ifexists procedure to conditionally delete it as follows below. The same procedure works with any object in a user’s schema.

EXECUTE drop_ifexists('table','message');

The only problem comes with types because they can have dependents. When they have dependents you can’t drop them until you’ve removed all the dependents. There are two types of dependents types and collection types.

Since discovering those may be difficult for new users, here’s another invoker rights function to discover user defined types. You should note that you’ll need to create the types before you try to compile the code (create the stored program in the Oracle database).

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

-- Create a SQL structure.
CREATE OR REPLACE TYPE dependent_type_obj IS OBJECT
( dependent_type  VARCHAR2(10)
, type_name       VARCHAR2(30));
/
 
-- Create a SQL collection of a user-defined data structure.
CREATE OR REPLACE TYPE dependent_type_tab IS TABLE OF dependent_type_obj;
/
 
-- Create a procedure to discover type dependents.
CREATE OR REPLACE FUNCTION type_dependents
( pv_type_name  VARCHAR2 ) RETURN dependent_type_tab AUTHID CURRENT_USER IS
 
  -- Declare a counter for the collection variable.
  c NUMBER := 1;
 
  -- Declare a return type variable.
  list DEPENDENT_TYPE_TAB := dependent_type_tab();
 
  -- Declare a parameterized cursor for dependent types.
  CURSOR find_dependent_types
  ( cv_type  VARCHAR2 ) IS
    SELECT   'STANDALONE' AS dependent_type
    ,        at.type_name
    FROM     all_types at INNER JOIN all_type_attrs ata
    ON       at.type_name = ata.type_name
    WHERE    at.owner = USER
    AND      ata.attr_type_name = UPPER(cv_type)
    UNION ALL
    SELECT   'COLLECTION' AS dependent_type
    ,        act.type_name
    FROM     all_types at INNER JOIN all_coll_types act
    ON       at.type_name = act.elem_type_name
    WHERE    at.owner = USER
    AND      act.elem_type_name = UPPER(cv_type); 
 
BEGIN
 
  -- Loop through all returns and add them to the return collection.
  FOR i IN find_dependent_types(pv_type_name) LOOP
    list.EXTEND;
    list(c) := dependent_type_obj(i.dependent_type, i.type_name);
	c := c + 1;
  END LOOP;
 
  -- Return the list.
  RETURN list;
END type_dependents;
/

You should also make the following grants and synonyms:

GRANT EXECUTE ON system.type_dependents TO PUBLIC;
CREATE PUBLIC SYNONYM type_dependents FOR system.type_dependents;
GRANT ALL ON system.dependent_type_obj TO PUBLIC;
GRANT ALL ON system.dependent_type_tab TO PUBLIC;

Then, you can call the type_dependents function to find any dependent user-defined data types. If so, you must drop them from the bottom or most specialized straight up the type tree to the root node data type.

SELECT *
FROM   TABLE(type_dependents('&type_name'));

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 was surprised to discover the MySQL Workbench couldn’t add a user while working with MySQL Workbench 5.2.31 (ce) and MySQL 5.5.9. Naturally, I was tempted to simply drop to the command line and add it manually, but I thought about my students who struggle at the command line. Poking around, I discovered a Severity 1 bug for this issue and a way to fix most of it before the next release.

The problem returns a dialog box that says very little, as you can see:

More detail is posted in the status bar, where it provides the following error message. Unfortunately, there wasn’t much luck Googling it. Hopefully, this post will fix that.

Error in securityManager module: error calling WbAdmin.openSecurityManager: see output for details

Bug 59000 contains a copy of the Python code you’ll need to manually patch into your MySQL Workbench installation. That’s the corrected wb_admin_security_be.py module that fixes most of the problem. The reason why I qualify it as most of the problem is because you can still raise an exception. The exception occurs when the the new user is granted any role.

You can avoid the error by only granting privileges. It appears that you can also ignore the error because it doesn’t mean that it failed. Just navigate away from the Accounts tab and back to see that the change was made. Also, it only happens when you’re creating a user not updating a user.

You can ignore the discussion over the %MYSQL_WORKBENCH_INSTALLDIR% environment parameter. It’s simply never set when you install with the mysql-5.5.8-winx64.msi file. You would have to set the environment variable manually in Windows. To save time, I’ve simply listed where the product installs on the 32-bit and 64-bit releases of Windows.

Here’s how you can manually patch it on Windows 7 (64-bit), and if you’re interested in learning a bit about Python, try The Quick Python Book, 2^nd Edition:

1. Download the file.
2. Open Windows Explorer and navigate to C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules directory; and delete both the wb_admin_security_be.py and wb_admin_security_be.pyc files. You have to remove both because removing only the source Python (.py) file won’t cause the interpreter to create a new byte code version (*.pyc). You must remove the byte code version to force the interpreted to read the source file. If you’re on a 32-bit version of Windows you’ll find it in this C:\Program Files\MySQL\MySQL Workbench 5.2 CE\modules directory.
3. Copy the downloaded wb_admin_security_be.py file into the C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules directory.
4. Launch MySQL Workbench from the Start menu and it will create the wb_admin_security_be.pyc file from the source file you copied into the directory for step #3.

That should do it. As always, I hope this helps folk save time too.

Somebody raised the question about writing stored functions while we were discussing scalar subqueries against COMMON_LOOKUP tables. Common look up tables store collections of possible <OPTION> elements. They’re basically generalized tables that contain a set of smaller tables, where the row sets make up a list of unique values.

When you write a function that must return one and only one row, the SELECT-INTO syntax is ideal in Oracle because it automatically raises an exception when the query returns no row or two or more rows. MySQL doesn’t raise an automatic exception when a SELECT-INTO fails to return a row but it does raise an ERROR 1722 when two or more rows are found.

Here are sample implementations written in Oracle’s PL/SQL and MySQL’s SQL/PSM languages. If you’re an Oracle developer and new to MySQL, the biggest oddity may be the delimiter, or it may be the sizing of formal parameters. Hopefully, its not the lack of formal declaration and exception blocks in SQL/PSM. For those coming from MySQL, the OR REPLACE command or dynamic sizing of formal parameter list values are the big changes.

Oracle PL/SQL Function

The Oracle PL/SQL function is very straightforward and helped by pre-defined exceptions for both the no data found and too many rows found errors. Formal parameters inherit their physical size at run time from the calling scope program.

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

-- Create or replace existing function with new implementation.
CREATE OR REPLACE FUNCTION get_common_lookup_id
( pv_table   VARCHAR2
, pv_column  VARCHAR2
, pv_type    VARCHAR2 )
RETURN NUMBER IS
 
  -- Declare a return variable.
  lv_return  NUMBER;
 
BEGIN
 
  -- Query data and assign it to a local variable.
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  -- Return the value found.
  RETURN lv_return;
 
EXCEPTION
 
  -- Handle errors.
  WHEN NO_DATA_FOUND THEN
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    RAISE;
 
END;
/

MySQL SQL/PSM Function

The lack of an implicitly raised exception when a SELECT-INTO structure fails to return a row adds several steps to a SQL/PSM function. You must define a custom exception and an if-block to raise the exception, which is trigger by returning a null value into the lv_return local variable.

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

/* Conditionally drop the function before trying to create it. */
DROP FUNCTION IF EXISTS get_common_lookup_id;
 
/* Change the delimiter to write a stored procedure into the database. */
DELIMITER $$
 
/* Create a function that replaces a scalar subquery. */
CREATE FUNCTION get_common_lookup_id
( pv_table   VARCHAR(30)
, pv_column  VARCHAR(30)
, pv_type    VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a return variable. */
  DECLARE lv_return  INT UNSIGNED;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE no_data_found CONDITION FOR SQLSTATE '99001';
 
  /* Query data and assign it to a local variable. */
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  /* The local variable is only null when no rows are returned by the implicit cursor. */
  IF lv_return IS NULL THEN
    SIGNAL no_data_found SET MESSAGE_TEXT = 'Result was no rows found.';
  END IF;  
 
  /* Return the value found. */
  RETURN lv_return;
 
END;
$$
 
/* Reset the delimiter to the default to run other programs. */
DELIMITER ;

Hope this help solve your problems.

The original blog post with the images was causing a page loading delay, so I’ve moved the post to a page of it’s own. This link send you to the original post with how you setup MySQL Connector/ODBC driver, add a new data source in Windows 7, and a new data import source in Excel 2007. As with other “how-to” posts, it includes screen shots to clear up any ambiguity. I apologize for any inconvenience caused by moving the content.

If you want instructions for using Excel to access an Oracle database, check this older post. I’m working on documentation for a native Mac OS X and Excel 2011 solution and will update this when it’s ready.

As always, I hope this helps those trying to sort through how this works. Naturally, a Visual Basic for Applications (VBA) solution is a better alternative once you’ve set up the data source. This is also on my calendar for documentation.

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.