MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for May, 2010

UDF replaces VLOOKUP

without comments

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)

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)

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

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.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0)
 
  ' Define a return variable
  Dim grade As String * 2
 
  ' Define a single dimension array of a UDT (record)
  Dim VirtualLookup(1 To 12) As VirtualLookupRecord
 
  ' Record initialization
  VirtualLookup(1).lowerLimit = 0.93
  VirtualLookup(1).upperLimit = 1#
  VirtualLookup(1).letterGrade = "A"
  VirtualLookup(2).lowerLimit = 0.9
  VirtualLookup(2).upperLimit = 0.93
  VirtualLookup(2).letterGrade = "A-"
  VirtualLookup(3).lowerLimit = 0.87
  VirtualLookup(3).upperLimit = 0.9
  VirtualLookup(3).letterGrade = "B+"
  VirtualLookup(4).lowerLimit = 0.83
  VirtualLookup(4).upperLimit = 0.87
  VirtualLookup(4).letterGrade = "B"
  VirtualLookup(5).lowerLimit = 0.8
  VirtualLookup(5).upperLimit = 0.83
  VirtualLookup(5).letterGrade = "B-"
  VirtualLookup(6).lowerLimit = 0.77
  VirtualLookup(6).upperLimit = 0.8
  VirtualLookup(6).letterGrade = "C+"
  VirtualLookup(7).lowerLimit = 0.73
  VirtualLookup(7).upperLimit = 0.77
  VirtualLookup(7).letterGrade = "C"
  VirtualLookup(8).lowerLimit = 0.7
  VirtualLookup(8).upperLimit = 0.73
  VirtualLookup(8).letterGrade = "C-"
  VirtualLookup(9).lowerLimit = 0.67
  VirtualLookup(9).upperLimit = 0.7
  VirtualLookup(9).letterGrade = "D+"
  VirtualLookup(10).lowerLimit = 0.63
  VirtualLookup(10).upperLimit = 0.67
  VirtualLookup(10).letterGrade = "D"
  VirtualLookup(11).lowerLimit = 0.6
  VirtualLookup(11).upperLimit = 0.63
  VirtualLookup(11).letterGrade = "D-"
  VirtualLookup(12).lowerLimit = 0#
  VirtualLookup(12).upperLimit = 0.6
  VirtualLookup(12).letterGrade = "F"
 
  ' Read through the possible lookup array values.
  For i = 1 To (UBound(VirtualLookup) + 1)
 
    ' Assign a grade if the percentage criterion or criteria match.
    If percentage > VirtualLookup(1).lowerLimit Then
      grade = VirtualLookup(1).letterGrade
      ' Exit the loop.
      Exit For
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
      grade = VirtualLookup(i).letterGrade
      Exit For
    Else
      grade = "UW"
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

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.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0, _
                     Optional minusTenth As Double = 0.03, _
                     Optional plusTenth As Double = 0.07)
 
  ' Define a variable length string variable.
  Dim grade As String * 2
 
  ' Define local variables.
  Dim minusInverse As Double
  Dim plusInverse As Double
  Dim tenth As Double
 
  ' Assign value to counter.
  Dim counter As Double
 
  ' Define a single dimension array of a UDT (record).
  Dim LetterGrades As Variant
  Dim VirtualLookup(0 To 11) As VirtualLookupRecord
 
  ' Fix incorrect numeric data entry of lower bound.
  If minusTenth >= 0.1 Then
    minusTenth = 0.03
  End If
 
  ' Fix incorrect numeric data entry of upper bound.
  If plusTenth >= 0.1 Then
    plusTenth = 0.07
  End If
 
  ' Assign values to local variables.
  counter = 1#
  minusInverse = 0.1 - minusTenth
  plusInverse = 0.1 - plusTenth
  tenth = 0.1
 
  ' Initialize letter array.
  LetterGrades = Array("A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "F")
 
  ' Use the array of letter grades to dynamically assign lower and upper bounds.
  For i = 0 To UBound(LetterGrades)
 
    ' There are three models for grades.
    ' -------------------------------------------------------------------
    '  [If]    =[A]     Has two possible values, a + or unadorned.
    '  [ElseIf]=[B,C,D] Have three possible values, a +, -, or unadorned.
    '  [ElseIf]=[F]     Has only an unadorned.
    ' -------------------------------------------------------------------
    If Left(LetterGrades(i), 1) = "A" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character and second character a minus.
      ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then
        VirtualLookup(i).lowerLimit = counter - tenth
        VirtualLookup(i).upperLimit = counter - minusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "B" Or _
           Left(LetterGrades(i), 1) = "C" Or _
           Left(LetterGrades(i), 1) = "D" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter - plusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character.
      ElseIf Len(LetterGrades(i)) > 1 Then
 
        ' The second character is a plus.
        If Mid(LetterGrades(i), 2, 1) = "+" Then
          VirtualLookup(i).lowerLimit = counter - plusInverse
          VirtualLookup(i).upperLimit = counter
          VirtualLookup(i).letterGrade = LetterGrades(i)
 
        ' The second character is a minus.
        ElseIf Mid(LetterGrades(i), 2, 1) = "-" Then
          VirtualLookup(i).lowerLimit = counter - tenth
          VirtualLookup(i).upperLimit = counter - minusInverse
          VirtualLookup(i).letterGrade = LetterGrades(i)
        End If
 
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "F" Then
 
      VirtualLookup(i).lowerLimit = 0#
      VirtualLookup(i).upperLimit = counter
      VirtualLookup(i).letterGrade = LetterGrades(i)
 
    End If
 
    ' Debug message demonstrating the changed values of the dynamically
    ' constructed array (remark out for deployment).
    ' MsgBox (" Counter [" + CStr(counter) + _
    '         "] Index [" + CStr(i) + _
    '         "] Grade [" + CStr(LetterGrades(i)) + _
    '         "] LLimit [" + CStr(VirtualLookup(i).lowerLimit) + _
    '         "] ULimit [" + CStr(VirtualLookup(i).upperLimit) + "]")
 
    ' Increment the tenth value when letter grades change, like A to B, et cetera.
    If LetterGrades(i) = LetterGrades(UBound(LetterGrades)) Then
 
      ' Force a loop exit to avoid reading past the last index value.
      Exit For
 
    ElseIf Not Left(LetterGrades(i), 1) = Left(LetterGrades(i + 1), 1) Then
 
      ' Decrement the tenth placeholder.
      counter = counter - tenth
 
    End If
 
  Next i
 
  ' Read through the possible lookup array values.
  For i = 0 To UBound(VirtualLookup)
 
    ' Assign a grade if the percentage criterion or criteria match, and
    ' force loop exits until the last element of the array is read.
    ' -------------------------------------------------------------------
    '  [If]     Assumes an A is always the first letter.
    '  [ElseIf] Handles all numbers greater than zero.
    '  [Else]   Assumes a zero or null indicate an unofficial withdrawal.
    ' -------------------------------------------------------------------
    If percentage > VirtualLookup(0).lowerLimit Then
 
      ' Assign grade, alsways the first element of the array.
      grade = VirtualLookup(0).letterGrade
 
      ' Force a loop exit when match found.
      Exit For
 
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
 
      ' Assign grade.
      grade = VirtualLookup(i).letterGrade
 
      ' Force a loop exit.
      Exit For
 
    Else
 
      ' Assign grade.
      grade = "UW"
 
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  ' MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

I hope this helps folks that have to perform grading activities in Excel.

Written by maclochlainn

May 28th, 2010 at 2:25 am

Posted in Excel 2007,Microsoft Windows 7,VBA,VBA Tutorial

Excel UDF Tutorial

with 34 comments

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.

  1. Displaying Excel’s Developer Tab

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

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

  1. Click the Excel Options button.

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

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

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

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

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.

Public Function newSerialDate(dateIn)
 
  ' Define local variables.
  Dim day As String
  Dim month As String
  Dim year As String
  Dim startPosition As Integer
  Dim endPosition As Integer
  Dim length As Integer
 
  ' Initialize local variables and parse the month from the left.
  startPosition = 1
  endPosition = InStr(startPosition, dateIn, "/")
  month = Left(dateIn, endPosition - 1)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Month [" + month + "]")
 
  ' Shift the start position and parse the day from the middle.
  startPosition = endPosition + 1
  endPosition = InStr(startPosition, dateIn, "/")
  day = Mid(dateIn, startPosition, endPosition - startPosition)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Day [" + day + "]")
 
  ' Get the remainder of the string.
  startPosition = endPosition
  length = Len(dateIn) - startPosition
  year = Right(dateIn, length)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Year [" + year + "]")
 
  ' Return a value from a User Defined Function (UDF) by using
  ' the function name as the return variable.
  newSerialDate = DateSerial(year, month, day)
 
End Function

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.

Function newSerialDate(dateIn As Double)
  newSerialDate = dateIn
End Function

Alternatively, you can accept a Variant, which maps to a String. Then, you convert it to a Date like this:

Function newSerialDate(dateIn)
  newSerialDate = DateValue(dateIn)
End Function
  1. 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))

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

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

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

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:

Function Unary(number As Integer)
  Unary = number + 1
End Function

Then, enter the following ordinary Increment macro:

Sub Increment()
  ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value())
End Sub

Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the increment macro, which increments the value previously in the cell by calling the Unary function.

As always, I hope this is helpful to a few folks.

Written by maclochlainn

May 26th, 2010 at 1:03 am

Posted in Excel 2007,Microsoft Office,Microsoft Windows 7,VBA,VBA Tutorial

Tagged with , , ,

Missing Features in R2

without comments

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.

Missing IF Syntax:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
GO

Generated error message:

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 ]

Local variable assignment Syntax:

DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = DECLARE ]

After I clear out the install and retry it with the other, I’ll update this as to whether or not these are available in the Database with Advanced Services SQL Server 2008 R2 Express Edition.

Written by maclochlainn

May 23rd, 2010 at 9:24 pm

Posted in Microsoft Windows 7,sql,SQL Server 2008

QuickTime Pro on Windows

with 5 comments

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

Written by maclochlainn

May 22nd, 2010 at 10:47 am

Posted in Excel 2007,Excel 2008,Mac,Mac OS X,Microsoft Windows 7,Premiere Pro,QuickTime Pro

MySQL REPLACE INTO

with 7 comments

I overlooked MySQL’s real equivalent to a MERGE statement, which is the REPLACE INTO statement. A previous example uses the INSERT statement with the ON DUPLICATE KEY clause. The following demonstrates how to perform a left join from and exernal source. These related posts all started with this one.

Demonstration

Here are the steps to accomplish an import/upload with the REPLACE INTO statement. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the sampledb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS KNIGHT;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6

LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  REPLACE INTO kingdom
  (SELECT   DISTINCT
            k.kingdom_id
   ,        kki.kingdom_name
   ,        kki.population
   FROM     kingdom_knight_import kki LEFT JOIN kingdom k
   ON       kki.kingdom_name = k.kingdom_name
   AND      kki.population = k.population);
 
   REPLACE INTO knight
   (SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date);
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

Written by maclochlainn

May 14th, 2010 at 12:46 am

Posted in MySQL,sql

User-defined SYS_CONTEXT

with 2 comments

Looking through an error on the web, I notices that the solution is nested in Ask Tom. That’s true for so many solutions, but they likewise have long discussions like this one in the OraFAQ Forum.

It seems that most folks search on is the following. The problem appears to be linked to attempts to call the DBMS_SESSION.SET_CONTEXT directly in their code, instead of through a predefined procedure. The procedure is generally inside a security package in a security schema for reference.

BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2

I figured it might help to provide a simple example because I use VPDs in my second database class, and this is where some of my students get hung up. It strikes me others in the Oracle community may get stuck here too.

  1. Create a user with necessary permissions as the SYSTEM user:
CREATE USER sample IDENTIFIED BY sample;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE ANY PROCEDURE TO sample;
  1. Create the CONTEXT reference as the SAMPLE user, which uses a function to populate the CONTEXT.
CREATE OR REPLACE CONTEXT sample_ctx USING set_context;
  1. Create the function as the SAMPLE user to set the context. The CONTEXT is a literal value inside the procedure with a name and value pair.
CREATE OR REPLACE PROCEDURE set_context
( pname  VARCHAR2
, pvalue VARCHAR2) IS
BEGIN
  -- Create a session with a previously defined context.
  DBMS_SESSION.SET_CONTEXT('SAMPLE_CTX',pname,pvalue);
END;
/
  1. Set the local session sample_ctx CONTEXT as the SAMPLE user.
EXECUTE set_context('email','sherman@atlanta.org');
  1. You now query the user-defined CONTEXT with case insensitive strings that match the CONTEXT and pname call parameter that you set it. The following shows that query against dual. You should note that it returns a case sensitive string of the pvalue call parameter.
SELECT sys_context('sample_ctx','email') FROM dual;

As always, I hope this helps somebody and saves them time.

Written by maclochlainn

May 5th, 2010 at 8:15 am

Posted in Oracle,Oracle XE,sql

When dropping is adding?

without comments

I was working through some example files and test scripts with Virtual Private Databases and discovered a nifty and potentially misleading error. Google didn’t pick up any search results with it, so I thought noting it would be a good idea.

When you create a security policy with DBMS_RLS.ADD_POLICY incorrectly, and then try to drop it, you must make sure to include the OBJECT_SCHEMA parameter. If you don’t and provide named parameters like the following, you’ll raise an error.

BEGIN
  DBMS_RLS.DROP_POLICY(object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

The error is quite misleading, as shown below.

BEGIN
*
ERROR at line 1:
ORA-28103: adding a policy TO an object owned BY SYS IS NOT allowed
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2

The error is actually triggered when the OBJECT_SCHEMA is required. The default value is a NULL in the DBMS_RLS package specification.

The correct syntax is:

BEGIN
  DBMS_RLS.DROP_POLICY(object_schema=>'valid_schema'
                      ,object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

Written by maclochlainn

May 4th, 2010 at 5:35 pm

Posted in Oracle,sql

Seagate 1TB Disk Fails

with 5 comments

Two posts in a row on hardware failures – whew :-(. Just after fixing my Mac Pro Video card, one of my 1 TB Seagate Barracuda hard drives failed. Naturally, it’s the one with half a terabyte of virtual machines.

First thing I checked was whether or not the disk still spun. It did but was not recognized by the Mac OS X or Windows 7 OS. Then, I swapped the disk controller card with another one of the same model numbered disks. The Mac OS X recognized it and launched Disk Utility but with a catch. I’d need to re-partition it, which would trash the important data.

I’m now working on recovering it byte-by-byte, big ouch! As time consuming as it is, I’m going to start performing weekly backups to DVD on my test and development machine.

To add insult to injury, I found out that Seagate knew about the problem but kept it quiet. Hoping the data recovery works, I’ll do an RMA afterward on the broken drive, which also makes a clicking noise after the disk controller failure.

This is the Seagate Barracuda 7200.11 (1 Terabyte) Drive, Model #ST3100340AS, which was heralded for its breakthrough four 250 GB platters. It is prone to failure. You may avoid failure by applying a firmware update but only before the disk fails. I’ve got two more of these that require a firmware update. Also, my eroded confidence in Seagate causes me to worry whether they’re in my External Seagate drives. I use them for time machines. You can read the story from January 2009, which unfortunately I missed before now.

A quick update, you can download DriveDetect from Seagate if you’re running the Windows OS but they’ve not got a utility for Mac OS X. You’ll have to manually pull the drives. For the USB drives, it appears that you’ll need a PC to run their utility.

Written by maclochlainn

May 1st, 2010 at 3:14 pm

Posted in Hardware Problems,Mac OS X,Seagate