Search This Blog

Wednesday, November 26, 2014

Distributed Queries in MS SQL


With a single query from My SQL Server 2000, you can query data from multiple databases. These
are called distributed queries. In this article, we see how to create one

What makes MS SQL Server 2000 differ from other databases? One of
the answers is distributed queries. With distributed queries you can query
data in multiple databases with a single query from MS SQL Server.For instance, imagine a situation where you want data from a table named EMP in one of the databases (say Oracle), a table named DEPT in another database (say SQL Server) and a table named ACCOUNT in yet another database (say MySQL). In such situations we use distributed queries.


What is the need to query different databases? Think you are in a clientserver environment and each computer maintains its own database, in fact a different database.Now,if the client needs to be given data from all the sources but need not know what the underlying databases are, then the system administrator can create a view by querying different databases and give the client permission to access the view. Now, the client can query all the sources as if it’s a single source. Further, they can be used in migration from one or more legacy databases (see screenshot below).







 

Let us see how we can create one. First,you need to create a linked server.A linked server is one, which once created, is used by SQL Server 2000 to implicitly connect to and retrieve data from it. To create a linked server for Oracle, follow the steps.

EXEC sp_addlinkedserver ‘OrclDB’,’Oracle’,’MSDAORA’,’ORACLEDB’

EXEC sp_addlinkedsrvlogin ‘OrclDB’,false,’sa’,’SCOTT’,’TIGER’


In the first step, we created a linked server where first argument refers to the server name, second to server product, third to the server provider name and the fourth to the server data source. The server data source name is the name of the oracle instance or an SQL * Net alias name that points to an Oracle data instance.

In the second step,we created the login credentials that are used by SQL Server to log to a particular database.The second argument specifies whether SQL Server login credentials be used or not. The default value is TRUE, which specifies that SQL Server uses its own username and password to connect with the remote server with remote_userid and remote_pwd arguments ignored .The third argument refers to the SQL Server user account name. The last two arguments refers to the remote_userid and remote_pwd.Now you can query Oracle from MS SQL as

SELECT * FROM OrclDB..SCOTT.EMP

SQL Server uses a four-part name to access a linked server.The syntax for this is

LinkedServerName.Catalog.Schema.ObjectName

For Oracle the syntax slightly differs as:

OracleLinkedServerName..OwnerUserName.ObjectName

For MySQL, the code slightly differs. Since MySQL provider does not have a friendly name (such as MSDAORA, MSDASQL etc) and instance name, we give MSDASQL as the provider name and Null as the data source.

sp_addlinkedserver ‘mysqlDB’, ’MySQL’, ’MSDASQL’, Null, Null, ‘Driver= {MySQL ODBC3.51 Driver}’

That’s all for MySQL and no need to create a login, as they are not needed for MySQL. But observe that we have used two more arguments.The fifth argument here refers to the location,which is again a Null and sixth refers to the provider string. Now to query it we use a rowset function named ‘OpenQuery ()’. This is because the MySQL provider does not support a four-part name as yet.We query it as:

SELECT * FROM OPENQUERY (mysqlDB,’SELECT * FROM MYSQL.ACCOUNT’)

Now to list all the linked servers we have just created along with their details, use the following stored procedure.

EXEC sp_linkedservers


To drop a linked remote server we need to drop all the existing logins to it and then drop the server. To drop all logins to a linked server we use the keyword ‘droplogins’along with sp_dropserver as:

Exec sp_dropserver Linked_ServerName, droplogins

Now that we have created the linked servers, we can query the 3 tables in 3 databases in Query Analyzer as:

SELECT * FROM OrclDB..SCOTT.EMP O, DEPT S,
OPENQUERY (mysqlDB,’SELECT * FROM MYSQL.ACCOUNT’) M
WHERE O.DEPTNO=S.DEPTNO AND S.DNAME=M.DNAME


For those who use MSDE (MS SQL Server Desktop Engine) there is a small (192 kb) but powerful free GUI utility called DBTray that is very simple and easy to use and can be downloaded from www.sulaco.co.za or http://home.global.co. za/~jhorn.

MySQL users can use any GUI utility given on the February 2004 PCQEssential CD, of which SQLyog is a free utility and is more convenient.




Monday, November 24, 2014

Struts Actions - DispatchAction, LookupDispatchAction, MappingDispatchAction and ActionDispatcher - Revisited

In my previous article Aggregating Actions in Struts, I have given a brief idea of how to create action aggregations. An action aggregation is a grouping of a set of related actions into a single unified action in order to eliminate the need to write a new action class for every action. In this part, I will walk through a full-fledged practical example of each of the types presented in Part I namely the DispatchAction, LookupDispatchAction, MappingDispatchAction and ActionDispatcher along with two new aggregations which slightly differ in syntax but are more efficient than the previous ones. These are EventDispatchAction and EventActionDispatcher. I call these new actions as “Event Aggregating Actions” and the ones in Part I as “Non-Event Aggregating Actions.”

            Now let’s go into some details.

Non-Event Aggregating Actions

DispatchAction
           
How to create one?

1.      Extend your action class from DispatchAction.

// src/CRUDDispatchAction.java

/**
 * This is an example class for demonstrating DispatchAction
 * functionality. Observe here that we are extending our
 * action class not from Action but from DispatchAction.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDDispatchAction extends DispatchAction {

      public ActionForward create(ActionMapping mapping,
                                  ActionForm form,
                                  HttpServletRequest request,
                                  HttpServletResponse response)
                                  throws       Exception {

            // Code for Create Employee follows
            System.out.println("I am in CRUDDispatchAction - create");

            return ( mapping.findForward("success") );
      }
      ...
}

2.      Define a query string parameter or a hidden variable (‘methodToCall’ in our examples)

// dispatchaction.jsp

<tr>
      <td>
            <html:link  action="crudDispatchAction?methodToCall=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>

3.      Set the parameter’s value to the desired function name of the action class (create, read etc).

// dispatchaction.jsp

<tr>
      <td>
            <html:link  action="crudDispatchAction?methodToCall=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>


What are the advantages?

  1. If you observed carefully, we have created only one action class (CRUDDispatchAction) instead of four different classes for each of the Create, Read, Update and Delete actions. This is why aggregating actions are for.
  2. Easy to use.
  3. Cleaner code syntax.

What are the disadvantages?

  1. We cannot extend our CRUDDispatchAction from any custom base class and still have the aggregation.
  2. Observe that we have used html:link in dispatchaction.jsp and a query string variable appended to the URL. So, the method name is visible in the URL. In order to avoid this, we need to set it using javascript.
  3. Cannot attach events. More about this later.


LookupDispatchAction

How to create one?

  1. Extend your action class from LookupDispatchAction.

// src/CRUDLookupDispatchAction.java

/**
 * This is an example class for demonstrating CRUDLookupDispatchAction
 * functionality. Observe here that we are extending our
 * action class not from Action but from LookupDispatchAction.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDLookupDispatchAction extends LookupDispatchAction {

      protected Map getKeyMethodMap() {

            Map map = new HashMap();
            map.put("button.create", "create");
            map.put("button.read", "read");
            map.put("button.update", "update");
            map.put("button.delete", "delete");

            return map;
      }

      public ActionForward create(ActionMapping mapping,
                                  ActionForm form,
                                  HttpServletRequest request,
                                  HttpServletResponse response)
                                  throws Exception {

            // Code for Create Employee follows
            System.out.println("I am in CRUDLookupDispatchAction - create");

            return ( mapping.findForward("success") );
      }
      ...
}

  1. Write an html:form and change the action name to the desired action mapping name.

// lookupdispatchaction.jsp

<html:form action="crudLookupDispatchAction">
 ...


  1. Set the ‘property’ attribute of the submit button to parameter name which should hold the method name to call.

// lookupdispatchaction.jsp

<tr>
      <td align="center">
            <html:submit property="methodToCall"><bean:message key="button.create"/></html:submit>
      </td>
</tr>

           
What are the advantages?

  1. Multiple submit buttons within the same form only differing in their name.
  2. The actual method to call is mapped in the action class.
  3. Button names can be set in a properties file.


What are the disadvantages?

  1. We cannot extend our CRUDLookupDispatchAction from any custom base class and still have the aggregation.
  2. Change to the button name requires JVM restart.
  3. Extra overhead of creating a separate method for button-to-method name mapping.(getKeyMethodMap() in our example)
  4. Cannot attach events. More about this later.


MappingDispatchAction

How to create one?

  1. Extend your action class from MappingDispatchAction

// src/CRUDMappingDispatchAction.java

/**
 * This is an example class for demonstrating MappingDispatchAction
 * functionality. Observe here that we are extending our
 * action class not from Action but from MappingDispatchAction.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDMappingDispatchAction extends MappingDispatchAction {

      public ActionForward create(ActionMapping mapping,
                                  ActionForm form,
                                  HttpServletRequest request,
                                  HttpServletResponse response)
                                  throws Exception {

            // Code for Create Employee follows
            System.out.println("I am in CRUDMappingDispatchAction - create");

            return ( mapping.findForward("success") );
      }
...
}

  1. Define different mappings for each action as needed.

// struts-config.xml

<!-- MappingDispatchAction mappings Starts -->

      <!-- MappingDispatchAction is used when the mappings differ
            (observe that the number/type of attributes differ for each action) -->

            <action path="/createMappingDispatchAction"
                  type="com.CRUDMappingDispatchAction"
                  scope="request"
                  parameter="create"
                  >
                  <forward name="success" path="/success.jsp"/>
            </action>

            <action path="/readMappingDispatchAction"
                  type="com.CRUDMappingDispatchAction"
                  scope="session"
                  parameter="read"
                  >
                  <forward name="success" path="/success.jsp"/>
            </action>

            <action path="/updateMappingDispatchAction"
                  type="com.CRUDMappingDispatchAction"
                  scope="request"
                  input="/mappingdispatchaction.jsp"
                  parameter="update"
                  >
                  <forward name="success" path="/success.jsp"/>
            </action>

            <action path="/deleteMappingDispatchAction"
                  type="com.CRUDMappingDispatchAction"
                  scope="request"
                  name="employeeForm"
                  parameter="delete"
                  validate="false"
                  >
                  <forward name="success" path="/success.jsp"/>
            </action>

      <!-- MappingDispatchAction mappings Ends -->

  1. Set the mapping name to the action attribute of html:link


// mappingdispatchaction.jsp

<tr>
      <td>
            <html:link action="createMappingDispatchAction"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>
<tr><td>&nbsp;</td></tr>
<tr>
      <td>
            <html:link action="readMappingDispatchAction"><b>R</b>ead an Employee details</html:link>
      </td>
</tr>
<tr><td>&nbsp;</td></tr>
<tr>
      <td>
            <html:link action="updateMappingDispatchAction"><b>U</b>pdate Employee details</html:link>
      </td>
</tr>
<tr><td>&nbsp;</td></tr>
<tr>
      <td>
            <html:link action="deleteMappingDispatchAction"><b>D</b>elete Employee record</html:link>
      </td>
</tr>


What are the advantages?

  1. Single action class can be used for multiple action mappings. The attribute of the action mapping can be added or removed and a new mapping can be created while the action class remains the same.
  2. Useful in places where there are 2 actions, one of which needs a formbean while the other does not but both uses the same Action class. In such cases, two new action mappings can be created one with ‘name’ attribute and one without and the same Action class name can be used.


What are the disadvantages?

  1. We still cannot extend our CRUDMappingDispatchAction from any custom base class and have the aggregation intact.
  2. Cannot attach events. More about this later.


ActionDispatcher

How to create one?

  1. Extend your action class from Action. Do not confuse about the inclusion of BaseAction.java. I have just moved the common code part to this BaseAction and extended my action class from it.

// src/CRUDActionDispatcher.java

/**
 * This is an example class for demonstrating ActionDispatcher
 * functionality. Observe here that we are extending our
 * action class from BaseAction where the session validation
 * is done.
 * <p>
 * See BaseAction for more details
 * <p>
 * If we use any other aggregating action then we cannot
 * use session validation as we cannot extend our custom class
 * (BaseAction in this case). This is the biggest strength of ActionDispatcher
 * type of aggregation and the biggest weakness of all other types.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDActionDispatcher extends BaseAction {

      public ActionForward create(ActionMapping mapping,
                                  ActionForm form,
                                  HttpServletRequest request,
                                  HttpServletResponse response)
                                  throws Exception {

            // Code for Create Employee follows
            System.out.println("I am in CRUDActionDispatcher - create");

            return ( mapping.findForward("success") );
}
...
}


// src/BaseAction.java

/**
 * This is an example class where session validation
 * is done normally. But if you look at closely we have
 * actually written our ActionDispatcher code here and
 * simply extended our example class for demonstrating
 * ActionDispatcher functionality from this class.
 * <p>
 * This is the place where every time the session is
 * checked. A sample code snippet could be :
 *
 * <code>
 * session = request.getSession(false); // which returns the existing session
 * if( session != null ) {
 *    // allow the user in
 * } else {
 *  // throw him/her out to the relogin page
 * }
 * </code>
 * <p>
 * This is perhaps the most crucial part of any web application
 * since this is to be done for every class or every class is to be
 * extended from this kind of (Base) class.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public class BaseAction extends Action {

      protected ActionDispatcher dispatcher =
                  new ActionDispatcher(this, ActionDispatcher.DEFAULT_FLAVOR);

      public ActionForward execute(ActionMapping mapping,
                                   ActionForm form,
                                   HttpServletRequest request,
                                   HttpServletResponse response)
                                   throws Exception {

            // This is a default method and is mandatory since we are
            // not extending any dispatcher action class(eg, extends ActionDispatcher{ }).
            // This method will handle the dispatching of the action to the appropriate method.

            // Code for Session Validation follows
            System.out.println("I am in BaseAction - execute");

            return dispatcher.execute(mapping, form, request, response);
      }
}

  1. Declare a variable of type ActionDispatcher as follow (see above code snippet):

protected ActionDispatcher dispatcher =
new ActionDispatcher(this, ActionDispatcher.DEFAULT_FLAVOR);

  1. Add the following line as the last line of your default ‘execute’ method. Note that this is not needed for other methods. (see above code snippet)

return dispatcher.execute(mapping, form, request,    response);

  1. Define a query string parameter or a hidden variable (‘methodToCall’ in our examples)

// actiondispatcher.jsp

<tr>
      <td>
            <html:link action="crudActionDispatcher?methodToCall=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>

  1. Set the parameter’s value to the desired function name of the action class (create, read etc).

// actiondispatcher.jsp

<tr>
      <td>
            <html:link action="crudActionDispatcher?methodToCall=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>


What are the advantages?

  1. Observe that we have extended our action class (CRUDActionDispatcher) from our custom base class (BaseClass) which is what we wanted in order to decouple the session validation code into the BaseClass.
  2. Only a couple of changes to the code avoid the need to extend from a subclass.
  3. Easy to use.


What are the disadvantages?

  1. We cannot attach events to the controls.


Event Aggregating Actions

If you are familiar with swing programming you might have heard of events. An event is nothing but a piece of code that is executed when, say, a button is clicked, a linked is clicked etc. In all the above types of actions we have defined a parameter (methodToCall) which is in turn mapped to the action name. This is a good feature for security concerns. However, we need a way to have the method name different from the actual method called, at the same time eliminating the need to define a hidden variable or a query string. This is where the Event Aggregating Actions comes into the picture.

There are two important types of Event Aggregating Actions as said earlier, namely, EventDispatchAction and EventActionDispatcher

Let’s see each one of them in detail.


EventDispatchAction

How to create one?

  1. Extend your action from EventDispatchAction

// CRUDEventDispatchAction.java

/**
 * This is an example class for demonstrating EventDispatchAction
 * functionality. Observe here that we can also extend our class from
 * BaseAction class like in ActionDispatcher example and move our
 * session validation to that class.
 * <p>
 * The EventActionDispatcher as the name suggests is used to attach
 * events to the html controls. This type of aggregation eliminates the
 * need to define a parameter(eg., methodToCall). We havent used any
 * hidden parameters to combine an event to a method. This event-to-method
 * mapping is removed using EventActionDispatcher.
 *
 * We can also use an Alias for the method name and map it to the method name
 * as can be seen in the createAlias event.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDEventDispatchAction extends EventDispatchAction {

      public ActionForward create(ActionMapping mapping,
                                                ActionForm form,
                                                HttpServletRequest request,
                                                HttpServletResponse response) throws Exception {

            // Code for Create Employee follows
            System.out.println("I am in CRUDEventDispatchAction - create");

            return ( mapping.findForward("success") );
}
...
}


  1. Set the action name to the action attribute of html:link

// eventdispatchaction.jsp

<tr>
      <td>
            <html:link action="crudEventDispatchAction?createAlias=create"><b>C</b>reate an Employee record</html:link>
           
      </td>
</tr>

  1. Either directly use the method name to call as a query string parameter, or use an alias.

// eventdispatchaction.jsp ( Direct calling )

<tr>
      <td>
            <html:link action="crudEventDispatchAction?update"><b>U</b>pdate Employee details</html:link>
      </td>
</tr>

// eventdispatchaction.jsp ( Calling using an alias )

<tr>
      <td>
            <html:link action="crudEventDispatchAction?createAlias=create"><b>C</b>reate an Employee record</html:link>
           
      </td>
</tr>


What are the advantages?

1.      Events can be attached to controls directly.
2.      No need to use a separate hidden variable or a query string for mapping the method name to the actual method to call.
3.      Actual method to call can be aliased with a pseudo name.

What are the disadvantages?

  1. The need to extend the class from EventDispatchAction and hence cannot extend our class from a custom base class.


EventActionDispatcher

How to create one?

  1. Extend your action from Action, similar to the case in ActionDispatcher. This is what is desirable for us.

// CRUDEventActionDispatcher.java

/**
 * This is an example class for demonstrating EventActionDispatcher
 * functionality. Observe here that we can also extend our class from
 * BaseAction class like in ActionDispatcher example and move our
 * session validation to that class.
 * <p>
 * The EventActionDispatcher as the name suggests is used to attach
 * events to the html controls. This type of aggregation eliminates the
 * need to define a parameter(eg., methodToCall). We havent used any
 * hidden parameters to combine an event to a method. This event-to-method
 * mapping is removed using EventActionDispatcher.
 *
 * We can also use an Alias for the method name and map it to the method name
 * as can be seen in the createAlias event.
 *
 * @author Praveen Babu Kusuma
 * @version 1.0.0
 *
 * http://www.javahome.co.nr
 * http://praveen.awardspace.com
 */
public final class CRUDEventActionDispatcher extends Action {

      protected ActionDispatcher dispatcher = new EventActionDispatcher(this);

      public ActionForward execute(ActionMapping mapping,
                                                 ActionForm form,
                                                 HttpServletRequest request,
                                                 HttpServletResponse response) throws Exception {

            // This method will handle the dispatching of the action to the appropriate method.
            System.out.println("I am in CRUDEventActionDispatcher - execute");

            return dispatcher.execute(mapping, form, request, response);
}
...
}

  1. Set the action name to the action attribute of html:link.

// eventactiondispatcher.jsp

<tr>
      <td>
            <html:link action="crudEventActionDispatcher?createAlias=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>

  1. Either directly use the method name to call as a query string parameter, or use an alias.
// eventactiondispatcher.jsp ( Direct calling )

<tr>
      <td>
            <html:link action="crudEventActionDispatcher?delete"><b>D</b>elete Employee record</html:link>
      </td>
</tr>


// eventactiondispatcher.jsp ( Calling using an alias )

<tr>
      <td>
            <html:link action="crudEventActionDispatcher?createAlias=create"><b>C</b>reate an Employee record</html:link>
      </td>
</tr>


What are the advantages?

  1. Events can be attached to controls directly.
  2. No need to use a separate hidden variable or a query string for mapping the method name to the actual method to call.
  3. Actual method to call can be aliased with a pseudo name.

What are the disadvantages?

  1. None.

Note that the use of true (read=true) while using the query string variable to specify the event name is optional. Also, note that this variable is different from the one we used earlier (methodToCall). Here we directly specify the event name or its alias not the variable name or the actual method to call.