Friday, March 28, 2014

SQL 2005 - Creating a view and updating db through the view

1. Introduction


A View is nothing but a select query with a name given to it or we can simply say a view is a "Named Query". Ok. Why we need a view? There will be lot of answer for this. Some of the important stuff is given below:


  1. A View can bring data from multiple tables by using adequate joins and while bringing so, it may require complex filters and calculated data to form the required result set. In user point of view, all these complexities are hidden and data is queried from a single table.
  2. Some time for security purposes, access to the table and its internal details like table schema and relationships are not given to the database user. All they have is, access to a view without the knowledge of what tables are actually exists in the database.
  3. Using the view, you can restrict an user to update only some portions of the records.


2. Sql view Example


Have a look at the below picture which shows a Sql view definition:

Creating a View
Fig.1 - Creating a view


So, to create a view, just form a select query then use the create view statement on the top (shown in the above picture). Many people use the prefix “v” before the view name to denote that the database object is a view. Once the statement is executed, we can see a new database view created for us.

In the above example, we created a view from the table Titles, which is actually a Microsoft supplied sample table available in the Pubs Database. Once a view is created, data can be queried from it like how we query the data from any existing table. Below is the example that shows retrieving the data from the view:

Selecting Data From a View
Fig.2 - Selecting Data From a View

The Sql script is below:

-- Make sure Microsoft Pubs
-- database is avaiable in your DB server
Use pubs;
Create view vTitles as
Select
      Title, type, Royalty, 
      (ytd_sales/12) as Monthly_Avg,
      (price * 0.05) as discount_price
from titles;

Select top 5 Title, Monthly_Avg from vTitles;
Drop view vTitles;


3. SQL View – Join Two Table


Sometimes it will be very useful to create a view when data is taken from two or more different tables. Later the data can be queried from the view as it is queried from a single table. Have a look at the below example:

SQL View with Three Tables Joint together
Fig.3 - SQL View with Three Tables Joint together


In the above example, first, the view created in the previous example is dropped. Then a view is created on a select query, which takes data from three different tables. The two inner joins are shown in blue colour and the data taken from the all three tables are marked in Red colour. Note that how the aliases T, Au, TA are used in the FROM clause and how they referred in the select clause of the query. Now, have a look at the below example, which queries data from the view:

Querying data from the view
Fig.4 - Querying data from the view


Surprisingly, the aliases are gone out. And when client uses the query from this view they are not aware of the fact that the data is actually queried from three different tables through table joins in the background. This is how a view hides the complexity behind it in the user perspective.

The SQL Script is below:

Drop View vTitles;
Go

Create view vTitles as
Select
      T.Title, Type, T.Royalty,
      (T.ytd_sales / 12) as Monthly_SalesAvg,
      (T.Price * 0.05) as Discount_price,
      Au.au_fname + ', ' + Au.au_lname as AuthorName,
      TA.RoyaltyPer as RoyaltyPercent
From Titles T
      Inner Join TitleAuthor TA
      On T.title_id = TA.title_id
      Inner Join Authors Au
      on TA.au_id = Au.au_id;
Go

Select AuthorName, Title,
            Monthly_SalesAvg, RoyaltyPercent
From vTitles
Order by AuthorName;



4. Updating through SQL View


The data in the database can be updated using the view also. But, updating the data through SQL view has some limitations. Consider the previous example again by looking at the below picture:

Fig.5 - Joins and Calculated Columns

In the picture above, the marker 1 denotes the calculated or computed columns. That means the data taken from the data table is computed through arithmetic or data manipulation operation. For Example, the Discount_price column is computed by multiplying value 0.05 with the database column Price.  Similarly, the columns Monthly_SalesAvg, AuthorName also computed like this. The above picture also shows that data is taken from three different tables and you can see that in the From Clause which is marked as 2, 3 and 4.

The SQL View has some limitation while updating the data. The limitations are:

  1. Updating the computed columns are not possible.
  2. Updating the data that belongs to only one table can be possible. It is not possible to update data that comes from two different tables.

Now let us look at some examples.

1) The below example tries to update the columns Type and Royalty through the view vTitles. As the columns are coming from the same table called “Titles”, this update is allowed.

A Successful Update through view
Fig.6 - A Successful Update through view

2) In the below example, the update through the view “vTitles” is failed because the AuthorName column is a computed column. Have a look at the view definition and you can see that AuthorName column is formed by combining the two columns au_fname and au_lname from the table Authors. The Error “Msg 4406, Level 16, State 1” specifies here that the update is not successful as it tries to update a computed column.

Update through view Failed because of derived column
Fig.7 - Update through view Failed because of derived column

3) The Final Example is shown below. In this example we are trying to update the column from two different tables. The Column “type” is from the table “Titles” and the column “RoyaltyPercent” is from the table “TitleAuthor”.  As this update does not obey the rule of “update should be in a Single table”, the update operation got failed. This is shown as error in the message “Msg 4406, Level 16, State 1” marked as 2 in the below picture.

Updating columns from different table
Fig. 8 - Updating columns from different table 



5. SQL view with check option


Before we talk about the usage of check option, have a look at the below picture:

Fig. 9 - A view with Filtered Rows

The select portion of the vAuthors returns two rows because of the existence the where condition. So, through this view you can update only two rows. OK, what if somebody updates the view changing the state. For Example something like the below one:

Update vAuthors set state = 'OH';

These kinds of updates succeed and at the same time, only two rows gets affected as that is the whole content of the view. What happens when somebody queries something from the view? The query does not return anything as the view definition does not return any data. Look at the update statement again keeping in mind that the view is created for the state of UT and view becomes empty after the update as there is no state of OH.

To avoid the above said situation, one can create a "View with the check option". The below example shows creating the view with check option:

Creating view with Check Option
Fig. 10 - Creating view with Check Option

If you try with the update again, the below shown error will be reported as the check option guards the view:
 
The check option restricts the update as it affects the view content
Fig.11 - The check option restricts the update as it affects the view content



[ No Downloads for this Article ]


Tuesday, March 18, 2014

[ MFC ] - Using SetWindowsHookEx & WH_KEYBOARD to Set Keyboard Hook Filter Function

1. Introduction


A Windows Hook intercepts specific type of windows hardware event before it reaches its destination say target window. The intercepted hardware events are passed to a function and that function can modify the event and even it can discard it. Such a function is called as Filter function. There are different types of windows hook available. Below is the most used one (Taken from MSDN):

WH_KEYBOARD:
Installs a hook procedure that monitors keystroke messages.

WH_MOUSE:
Installs a hook procedure that intercepts the mouse hardware events.

WH_GETMESSAGE:
Installs a hook procedure that monitors messages posted to a message queue.

A hook should be attached to a Filter Function. For Example, when we attach the WH_MOUSE hook to a filter function, all the mouse hardware messages first go to the filter function before it reaches the target window. In this article, we are going to use the Keyboard hook. Note that the filter function can also be called as hook procedure as it is attached to a hook.

When more than one filter function attached to a windows hook, then this forms a Hook Chain. For example, let us say you have two filter functions for a WH_KEYBOARD, then this forms a hook chain. When a keyboard event triggers, it reaches to all the filter functions in the keyboard hook chain. Have a look at the below picture:





Hope you gone through the above picture. Let us say user pressed a numeric key ‘7’ to display that in the notepad application. The keyboard hook monitors the hardware event of pressing the keyboard key 7. So before this hardware event reaches the notepad application, the keyboard hook (WH_KEYBOARD) will direct that to the attached filter functions. The filter function(s) after processing the message sends that to the actual target window or application.

From the above picture, you can see four filter functions attached to the keyboard hook. Let us say the filter function D is added last. Now, the lastly added filter function receives the tapped keyboard event first and after processing it sends that to Filter function ‘C’. This way the event reaches the notepad application at last once the filter function ‘A’ processed the message. Ok, let us go with creating the example application.



2. About the Example


The example is a single document interface application. Under the file menu, two menu items called “Hook CAPS lock”, “Unhook CAPS Lock” are added. When the “Hook Caps Lock” menu items are clicked, the Keyboard hook is attached to the application and hooks the keyboard event. The “UnHook CAPS Lock“ event removes installed hook from the application.

Under view menu, two menu items called First Dialog and Second Dialog are added. The menu items bring two dialogs with text boxes in it. When a hook is installed, you can type only the capital letters in the dialogs. After performing the UnHook operation, you will not have upper case restriction in the dialogs. Let us jump to the explanation-based walk-through.





3. Create the Example


1) Create a MFC Single document Interface Project called WinHooks. While creating the project remove the support for Document/View architecture and Unicode libraries. Accept the defaults for all other settings. This is shown in the below video:

Video 1:  Creating the Example


2) Once the project is created, add two dialog templates to it. In each dialog add some text boxes. For each dialog add the class using the class wizard that can be accessed through the right-click context menu of the dialog template. Adding the dialog through resource and attaching a class to it is shown in the below video:

Video 2: Adding Dialog Templates



3) After adding the dialog templates to the project through the dialog editor, we need to add two menu items and handler functions. From these handler functions, we will launch our dialogs. Below video shows adding two menu items:

Video 3: Adding Menu Items


Like how we added the menu items for launching the dialogs, the same way add two menu items under the file menu to engage and disengage the hook for tapping the keyboard events. The display names of the menu items are shown in the previous picture:

4. Launching the Dialogs

1) In the MainFrm.cpp file, provide the #include pre-processor directive to include the dialog classes. Below are the two statements:

//Sample 01: Header files to access the dialogs
#include "Dialog1.h"
#include "Dialog2.h"

2) Next, in the menu items handler (Created in video 3) add the code that brings the dialog in front of the user. Note that we are launching the dialogs as Modal dialogs. Below is the code that displays the dialogs:

void CMainFrame::OnViewFirstdlg()
{
            //Sample 02: Open the First Dialog
            CDialog1 dlg;
            dlg.DoModal();
}
void CMainFrame::OnViewSeconddlg()
{
            //Sample 03: Open Second Dialog
            CDialog2 dlg;
            dlg.DoModal();
}

5. Hook Filter Function

I hope by this time you are aware of what is filter function. In this function we will add a filter function and this filter function will be attached to the windows hook that we are going to create in the coming section.

1) Add the Win32 handle for the Windows Hook as a global variable. Note that we are going to add a global filter function sooner and for convenience we are adding the win32 handle at a global level. Below is the Declaration:

//Sample 04: Win32 Handle to Keyboard Hook
HHOOK hKBHook;

2) The signature of the keyboard hook procedure is given below:

//Sample 05: A Global Filter function that will be hooked to the
//                                  Key board event
LRESULT CALLBACK KeyboardProc ( int code, WPARAM w, LPARAM l )

In the keyboard hook procedure, the code indicates how should a procedure respond. That is when we receive the negative code; the keyboard procedure will call the next hook filter function, which is in the hook chain. In our example, we are going the test “WPARAM” to know what key is pressed. The “LAPARAM” is useful to know repeat count of a button; say, for Example, a keyboard button Up Arrow pressed down for some 4 or 5 seconds. 

3) As already told the first parameter received is tested to see whether the filter function can process the message or not. When the code is lesser than zero we should not do anything the make a call to the CallNextHookEx to call the next filter function in the hook chain. Below is code, which does that:

//Sample 5.1: As Per MSDN, Do not intercept the message when
//                                    code is negative
if ( code < 0 )
            return CallNextHookEx ( hKBHook, code, w, l ) ;

4) Next using the WPARAM w, we test to see the CAPS Lock key is pressed. Have a look at the below depiction:


Note that each slot in the above picture denotes the unsigned char and hence each slot consumes 8 bits. The constant VK_CAPITAL denotes 20 and using the GetKeyBoardState function call we get all the key status in an array of 256 unsigned char slot. At the char location 20, we get the CAPS lock key status. When lower order bit is 1 then the CAPS Lock status is ON. So our filter function gets the keyboard status and always keeps the lower order bit of VK_CAPITAL as one as shown above. The net effect is, when the message reaches the application message queue, the CAPS is always ON. Below is the code which is taken from the filter function:

//Samle 5.2: Always keep the CAPS lock state to 1.
unsigned char state [256] ;                                                         
if ( w == VK_CAPITAL )
{
            GetKeyboardState(state);
            state [VK_CAPITAL] = 1 ;
            SetKeyboardState ( state ) ;
}

5) Once we are done dealing with the CAPS LOCK Toggle state, make a call to the next filter function as shown below:

//Sample 5.3: Call the Next Hook in Chain
return CallNextHookEx ( hKBHook, code, w,1 ) ;

The entire keyboard hook procedure (or) filter function is listed below:

//Sample 05: A Global Filter function that will be hooked to the
//                                  Key board event
LRESULT CALLBACK KeyboardProc ( int code, WPARAM w, LPARAM l )
{
            //Sample 5.1: As Per MSDN, Do not intercept the message when
            //                                    code is negative
            if ( code < 0 )
                        return CallNextHookEx ( hKBHook, code, w, l ) ;
            //Samle 5.2: Always keep the CAPS lock state to 1.
            unsigned char state [256] ;                                                         
            if ( w == VK_CAPITAL )
            {
                        GetKeyboardState(state);
                        state [VK_CAPITAL] = 1 ;
                        SetKeyboardState ( state ) ;
            }
            //Sample 5.3: Call the Next Hook in Chain
            return CallNextHookEx ( hKBHook, code, w,1 ) ;
}

6. Install the Hook

1) Remember, the filter function is ready in the previous section and now it is time to set the hook specifying the filter function. Before that we will set the initial caps lock state as ON. Below is the code called from the application’s InitInstance that you are already familiar in the previous section:

//Sample 06: Set the Keyboard state
unsigned char state [256] ;                           
GetKeyboardState(state);
if (state[VK_CAPITAL] == 0 )
       state [VK_CAPITAL] = 1 ;
SetKeyboardState ( state ) ;

2) The SetWindowsHookEx Win32 function installs the windows hook and attaches that to the specific filter function routine. Have a look at the below picture:


The first parameter passed to this function specifying the hook category. In our example, we are going to hook the keyboard events and hence the first parameter passed to this function is WH_KEYBOARD. The second parameter passed to this function specifies the filter function, which will receive all the intercepted keyboard messages. The third parameter specifies the module handle. Say for example, if the hook filter function is in the DLL module, this parameter specifies that dll module name. In our case, this parameter is null as same exe module is implementing the filter function. The final parameter species the thread handle linked to the hook procedure.
Below is the message map handler for the menu items, which will hook and unhook keyboard messages:

void CMainFrame::OnFileSetcapslockhook()
{
       //Sample 07: Set the Key Board Hook
       hKBHook = SetWindowsHookEx(WH_KEYBOARD, KeyboardProc, NULL, GetCurrentThreadId());
}
void CMainFrame::OnFileUnhookcapslock()
{
       //Sample 08: Unset the Key Board Hook
       UnhookWindowsHookEx(hKBHook);
}

7. Running the Application

After setting the hook, launch the dialog. Once the dialogs are launched, you can observe that the dialog accepts only the Capital letters. You can see this in the below video:
Video 4: Running the Application
Source Code: Download
Like this site? Tell it to your Firend :)