Archive for the ‘Microsoft Windows 7’ Category

I finally upgraded from VMware Fusion 6 to VMware Fusion 7 to take advantage of the new features. It was interesting to upgrade the Windows 7 virtual machine because of the unique failure message it raised.

The message said it was incompatible, and that I should navigate to:

Virtual Machine -> Settings -> Compatibility -> Upgrade

The Upgrade button checks the Allow upgrading the virtual hardware for this virtual machine checkbox. You will get prompted with the Would you like to upgrade this virtual machine? dialog for the next virtual machine.

The Oracle Database 12c documentation says you can set the maximum size of a VARCHAR2 to 32,767 bytes. That’s true except when you’re trying to map a large Java string to a VARCHAR2. It fails when the physical size of the Java string is greater than 4,000 bytes with an ORA-01002 or fetch out of sequence error.

You need to grant privileges before you can test this code. You can grant privileges by connecting as the SYS user of a CDB (or non-multitenant database) or as the ADMIN user of a PDB with the AS SYSDBA clause. Then, you run the following command to grant external file access to the JVM inside Oracle Database 12c:

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 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.

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.

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.

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.

Microsoft Excel has many Visual Basic for Application (VBA) features that are seemingly not well understood. One of these features is passing a range value into a VBA procedure (a Sub) or function. The following example demonstrates how to pass a dynamic range to a local multiple dimensional array, and process the uploaded data in a VBA function.

My sample spreadsheet looks like the screen shot below. The formula call is in cell A11 and the text of the formula is in cell B11.

I left a debug MsgBox() call that demonstrates how you size a range. This shows the range above based on zero-based numbering, which means 5 rows are reported as 4 rows because 0 contains a row, and 3 columns are reported as 2 columns for the same reason. If the dialog looks strange to a Windows user, that’s because it’s one generated on a Mac OS X running Excel 2011. 🙂

I kept this as simple as possible to demonstrate the how to do this. Unfortunately, feedback required adding more extensive comments and making it a bit more bulletproof on concepts. The variable names were chosen to help read the syntax. Two loops are used to demonstrate (a) how you assign the range values to a multidimensional array, and (b) how you read the array values in your VBA code.

Function dynamicArray(lookupValue AsString, table As Range)
' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.' Row 1 is the first row and 1,048,576 is the last possible row.Dim rowStart AsIntegerDim rowSize AsInteger' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.' Column A is the first column and XFD is the last possible column.Dim columnStart AsIntegerDim columnSize AsInteger' Create a dynamic multiple dimension array without any physical size.Dim multidimensionArray AsVariant' Define and declare a local returnValue variable.Dim returnValue AsBoolean
returnValue = False' Assign the starting row and column values, and the length of values.' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,' which means you subtract one from the length.
rowStart = table.Row
rowSize = table.Rows.Count - 1
columnStart = table.Column
columnSize = table.Columns.Count - 1
' This demonstrates that the range starts in the row and column, and' the length and width of the multiple dimension array.' ----------------------------------------------------------------------' Insert single quotes for the next two lines to suppress testing the program with variables.
MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
"(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
' ----------------------------------------------------------------------' Redimension the arrays maximum size, rows first, columns second.ReDim multidimensionArray(rowSize, columnSize)
' Read through the range and assign it to a local and dynamically sized array variable.' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two' parameters, the absolute row and column number or relative row and column numbers. In this sample, the' easiest solution is to use absolute row an dolumn numbers.For i = 0 To rowSize
For j = 0 To columnSize
multidimensionArray(i, j) = CStr(Cells(rowStart + i, columnStart + j))
Next j
Next i
' Read through the local variable range and view the content set.For i = 0 To rowSize
For j = 0 To columnSize
' Check if the lookupValue has been found and return true.If lookupValue = CStr(multidimensionArray(i, j)) Then
returnValue = TrueExitForEndIfNext j
Next i
' Return a Boolean value: true when found and false when not found.
dynamicArray = returnValue
EndFunction

Function dynamicArray(lookupValue As String, table As Range)
' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
' Row 1 is the first row and 1,048,576 is the last possible row.
Dim rowStart As Integer
Dim rowSize As Integer
' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
' Column A is the first column and XFD is the last possible column.
Dim columnStart As Integer
Dim columnSize As Integer
' Create a dynamic multiple dimension array without any physical size.
Dim multidimensionArray As Variant
' Define and declare a local returnValue variable.
Dim returnValue As Boolean
returnValue = False
' Assign the starting row and column values, and the length of values.
' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
' which means you subtract one from the length.
rowStart = table.Row
rowSize = table.Rows.Count - 1
columnStart = table.Column
columnSize = table.Columns.Count - 1
' This demonstrates that the range starts in the row and column, and
' the length and width of the multiple dimension array.
' ----------------------------------------------------------------------
' Insert single quotes for the next two lines to suppress testing the program with variables.
MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
"(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
' ----------------------------------------------------------------------
' Redimension the arrays maximum size, rows first, columns second.
ReDim multidimensionArray(rowSize, columnSize)
' Read through the range and assign it to a local and dynamically sized array variable.
' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
' easiest solution is to use absolute row an dolumn numbers.
For i = 0 To rowSize
For j = 0 To columnSize
multidimensionArray(i, j) = CStr(Cells(rowStart + i, columnStart + j))
Next j
Next i
' Read through the local variable range and view the content set.
For i = 0 To rowSize
For j = 0 To columnSize
' Check if the lookupValue has been found and return true.
If lookupValue = CStr(multidimensionArray(i, j)) Then
returnValue = True
Exit For
End If
Next j
Next i
' Return a Boolean value: true when found and false when not found.
dynamicArray = returnValue
End Function

You would call it with syntax like that below. The range can be any valid range value in Excel, which is any two cell references separated by a colon.

=dynamicArray("LookupString",A1:G5)

=dynamicArray("LookupString",A1:G5)

As always, I hope this helps others looking for a way to perform this task natively in Microsoft Excel. As an addendum, I thought the absence of a code example for VB on the MSDN web page was quite interesting.

A colleague asked how a User Defined Function (UDF) could simplify their grading. They were using a VLOOKUP() function and range component that they’d copy into spreadsheets generated from Blackboard.

You typically want a letter grade before you transcribe it into the student record system. What you usually start with is a percentage and a defined range of percentages that map to letter grades. While you can solve that probelm with a VLOOKUP() function, is it the best way. I’d suggest this type of activity is ideally suited to a UDF solution.

The VLOOKUP() function call in cell C2 is:

=VLOOKUP(B2,$E$2:$G$13,MATCH("Letter Grade",$E$1:$G$1,FALSE),TRUE)

=VLOOKUP(B2,$E$2:$G$13,MATCH("Letter Grade",$E$1:$G$1,FALSE),TRUE)

A User Defined Function (UDF) can replace the VLOOKUP() function, the nested MATCH() function, and lookup range. You create a record type, like VirtualLookup, which must be defined outside of a subroutine or function.

The letterGrade() UDF call in cell C2 is much simpler:

=letterGrade(B2)

=letterGrade(B2)

You can refer to the following blog post for clarification on how to create a UDF. UDFs can simplify our lives for routine operations. If you’re not interested in the programming, just copy it into your Visual Basic for Application library and it should work.

These letterGrade examples require a custom data type. It must be defined before any function or subroutine in a module. It’s generally a good idea to make these available in other modules by making them Public.

' Define a record type (must happen outside of a function or sub block.
Type VirtualLookupRecord
lowerLimit As Double
upperLimit As Double
letterGrade As String * 2 ' A variable length string up to 2 characters.
End Type

For those interested in understanding how to program this UDF. You leverage that User Defined Type (UDT) when you define the function. In this example, you create a fifteen element array of the record structure. The example defines an array with 1-based numbering index. The default for Microsoft Excel is 0-based numbering. You would define a 0-based numbered array of fifteen elements like the one in the example.

Here’s the complete function that you can copy into your spreadsheet.

Dim VirtualLookup(15) As VirtualLookup

Dim VirtualLookup(15) As VirtualLookup

Since the formal parameter of the function is optional, you need an if-block to replace a null value with a zero. Then, you need a loop to read through the array and find where the call parameter value is within the lower and upper grade range values. When it finds a match, it assigns the letter grade to a variable and then breaks out (exits) the loop. If you implement this, don’t forget to comment out the debugging MsgBox() function call.

Somebody asked for a variable array set by the input parameters. That’s a bit more programming and requires understanding the two types of array initialization and the difference between 0-based and 1-based numbering systems. Since this program assigns a constructed Array type to a Variant data type variable, 0-based arrays are consistently used in the example. That’s a change from the foregoing example.

Here is a function that lets you set the hundredth limits to something other than the traditional .x3 and .x7. It substitutes default values if you attempt to enter something greater than a tenth for either limit.

Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF). Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested.

Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula. That having been said, they can dramatically hide the complexity of mega-formulas and remove them from the editing control of users.

Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. They show you how to display the developer ribbon, open a module for editing, save VBA code into a library (*.xlam file), and add the library file as an “Add-in” library to your Excel 2007 installation. The name of the library will be the same as the Workbook where you created it.

Displaying Excel’s Developer Tab

There are four steps to make this visible. They are:

Choose the Office Button. You’ll see the following:

Click the Excel Options button.

Click the Popular tab if not highlighted (it’s the default). Inside the right side pane, click the Show Developer tab in the Ribbon check box to enable it, and click the OK button to set it.

Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.

Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.

Copy the following function definition into the open Module. The name of the UDF will be the case-sensistive name of the function in the VBA module. That means you’ll now have a newSerialDate function in your list of functions when you click the insert function button.

I struggled to come up with a simple function to illustrate this and how you debug UDFs. A word to the wise, you can’t use a numeric variable inside a MsgBox by itself. If you attempt it, Excel will return a #Value! error in the cell where you call the UDF. You must include a numeric variable as an argument (also known as a call parameter) to the CStr() function. An example is: Cstr(myVariable). You call this sample function by entering:

=monthEnd(cell_reference)

=monthEnd(cell_reference)

When you pass a date (actually an integer in the spreadsheet), the function handles it as a string. While no data type is assigned the dateIn variable in the example, variables without an explicit data type are always of the Variant data type. The Variant data type is a master data type and can hold any other type, which makes it like the Object data type in Java.

This program parses the string, then uses the DateSerial() function to return it as a number. It’s critical to note that the last line returns the value in the newSerialDate variable, and that variable must always be the function name. Place a single quote mark before all MsgBox() function calls after verifying that the function works.

Aside from the fact that all this parsing isn’t really necessary because the problem is much simpler and cleaner. At least, it becomes so when you understand the breadth of built-in functions in VBA. You can solve the problem by designating the formal parameter as a Double like the example below.

Create the following spreadsheet, the formula values are noted below the screen shot. You should be able to copy and past them into the spreadsheet. After you’ve created the spreadsheet, entering a new date in cell A1 causes the UDF to run. When the UDF runs “as-is”, you’ll see message dialog boxes that show values set during runtime.

Spreadsheet Functions

Cell

Formula

A2

=newSerialDate(A1)

C1

=VLOOKUP(TYPE(A1),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE))

C2

=VLOOKUP(TYPE(A2),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE))

You can now save this as an Add In library but comment out those debug MsgBox() function calls. Click the Office Button and click SaveAs in the menu, then accept Excel Workbook initially. When you get to the SaveAs dialog, choose Excel Add-In as the file type. Below is a screen capture of the drop down selection box.

Open a new Excel Workbook. Click the Excel Options button. Click the Add-Ins tab. Inside the right side pane, make sure the Manage drop down says Excel Add-ins before you click the Go button.

Check the Samplefunction check box as shown below. Samplefunction is the name of the Workbook that contains the module, and it is saved as an *.xlam file. Click the OK button to add the library. You’ve now created, and added an Add-In library to your new spreadsheet. It will create a SampleFunction.xlam file in the users directory.

The file is found by default in:

C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

Ranjit asked how you could call a UDF from inside a module. In the answer noted below, I show how to do it with the Unary function:

As I’ve mentioned before, I try to support Oracle, MySQL, and SQL Server in my database classes. When I downloaded SQL Server 2008 R2 Express Edition, I just downloaded the Database with Management Tools. That’s the one on the first page and shown on the left.

It appears what I really wanted was the Database with Advanced Services because I found basic features weren’t available in the Database with Management Tools version. So, you should click the Other Installation Options link. On that page you can choose between the various options.

For example, you can’t use an IF statement or local variable assignment. Also, the available data types are a subset of the mainline product. You can’t use a VARCHAR but must use the NVARCHAR. Many features of the Microsoft SQL Server Management Studio are removed too. A brief forum discussion shows that these types of problem exist in other versions too.

I thought a couple quick examples of raised exceptions would be helpful. They’re listed below.

Major Error 0x80040E14, Minor Error 25501>IFEXISTS(SELECT*FROM INFORMATION_SCHEMA.TABLES WHERETABLE_NAME='CORE_SYSTEM_USER')DROPTABLE CORE_SYSTEM_USER
There was an error parsing the query.[ Token line NUMBER=1,Token line offset =1,Token IN error =IF]

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
DROP TABLE CORE_SYSTEM_USER
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I popped for QuickTime Pro for Windows 7 ($29.99). The reason for doing so, was to create native screen capture that would integrate with Mac Adobe Premiere Pro, CS4. That’s because my Camtasia output didn’t work due to a compression mismatch.

Unfortunately, QuickTime Pro on Windows 7 doesn’t support screen capture. Quite a difference between what the product does on a Mac OS X versus a Windows 7 OS. I thought only Microsoft shorted users on the other platform, like Microsoft Excel 2007 versus Microsoft Excel 2008 (a far inferior product). Oops, I was wrong! Apple does it too. 🙁