Stephen Smith's Blog

All things Sage ERP…

Archive for August 2010

Writing Server Side Code for Accpac 6 Web UIs

with 11 comments

We discussed how to create Accpac 6 Web based UIs in http://smist08.wordpress.com/2010/08/14/creating-a-web-form-for-accpac-6/ and how to write code that runs as JavaScript in the Browser in http://smist08.wordpress.com/2010/08/21/client-logic-for-an-accpac-6-web-form/. This blog posting looks at how you can write server side code in Java to assist your UIs do their job more efficiently.

Accpac 6 Web UIs communicate with the Server using SData (http://smist08.wordpress.com/2009/11/24/sdata-in-sage-erp-accpac-6/ and http://sdata.sage.com/) which is a REST based Web Services protocol. On the Server we run a Java application that converts incoming SData requests into Accpac View calls. Accpac Views are the business logic objects within Accpac. The Accpac business logic is remaining largely unchanged in this new environment and it’s the job of this Java application to convert the new world into the older world.

Theoretically then, you could do much of what we are talking about directly in the Views. We could expose new calculated fields or new functionality directly from the Views. However Views are used by all sorts of things including VB based UIs, macros, import/export as well as by third party applications. We don’t want to clutter up the Views with lots of extra logic needed to support or optimize the new Web based UIs. What we are looking to do is split the code that used to run entirely in VB UIs to half run in the browser and half run on the server. We also want to provide support for our Web based UIs to minimize program size running in the Browser and to minimize the number of RPC (remote procedure calls) they need to make to the server. The current VB UIs call many Views at once to do what they need, but this won’t work so well over the Internet since it will require many SData calls to do things, we really want to consolidate all the info the UI requires in one feed, so it only needs to do one SData call to get everything it needs.

Within the SData server’s configuration are a number of XML files that define all the SData feeds for the various accounting applications. For each application there is a classmap.xml file that lists all the feeds and the Java class that processes that feed. Then for each feed there is a specific resourcemap.xml file that defines various properties or configurations for that feed such as which Views is sits on, or some extra fields to present. To add an SData feed on top of an existing View is easy and just a matter of creating these simple XML files. There is a generic class ViewResourceKind that will provide standard SData functionality for any View. But if you want to add your own functionality you need to extend one of our classes and add your own logic.

The hardest way to implement an SData service is to create a class that implements our SDataResourceKind interface. This interface defines what a class needs to implement in order to be used as an SData feed from our SData application server. Here you need to do all the work, but if you want to implement a feed that hasn’t got anything to do with normal Accpac processing, perhaps this is a way to go. Fortunately we provide a class that implements this interface that you can extend. This is the ViewResourceKind class, mentioned previously, that implements SData on top of a View. This is really intended for data type Views. You can extend this to add fields and/or provide other helpful services for your UI form (or other consumer of your SData feed).

SData provides data type feeds similar to Views built closely over database tables, but it also provides “service” feeds that are similar to Accpac superviews. These feeds are meant to perform service type operations like printing a report, posting a batch or doing a calculation. They can operate asynchronously, meaning the originating call returns immediately, but then can poll the service for status updates (which can be used to update a meter control).

Here is a bit of sample code from the SData service that provides the data for the pie charts in the G/L Balance Sheet data snapshot.

public class GLBALSHTService extends BaseService
{
   private final SDataView            viewGLFSUM;
   private final List<ServiceField>   requestFields;
   private final List<ServiceField>   responseFields;
   private static final String        STR_RSCID  = "GLBALSHTSP";
   private boolean                    bPermitted = false;
   private GLLanguageResourceContents GLLanguageResource;
   public GLBALSHTService(final ApplicationContext applicationContext, final ResourceContextImpl resourceContext,
         final Resource resource, final Service service, final SDataViewSet viewSet)
   {
      super(applicationContext, resourceContext, resource, service, viewSet);
      viewGLFSUM = new SDataView(getResourceContext().getAccpacProgram(), GLFSUM.VIEW, resource);
      bPermitted = getResourceContext().getAccpacProgram().isPermitted(STR_RSCID);
      if (!bPermitted)
      {
         String appVersion;
         String appLang;
         //language dependent resource content is only used when access is not permitted.
         appVersion = resourceContext.getAccpacProgram().getActiveApplications().get(GLLanguageResourceContents.APPL)
               .getAppVersion();
         appLang = viewGLFSUM.getProgram().getSession().getUserLanguage();
         GLLanguageResource = new GLLanguageResourceContents(appVersion, appLang);
      }
      requestFields = new ArrayList<ServiceField>();
      responseFields = new ArrayList<ServiceField>();
      setupRequestFields();
      setupResponseFields();
    }
   @Override
   public void shutdown()
   {
      if (this.viewGLFSUM != null)
         this.viewGLFSUM.dispose();
   }
    private void setupRequestFields()
   {
      requestFields.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_YEAR)));
      requestFields.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_PERIOD)));
      requestFields.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_PERIODS)));
   }
   private void setupResponseFields()
   {
      responseFields.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_ENDDATE)));
      responseFields.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_CASH)));
     // … Lots more response fields defined …
   }
   protected List<ServiceField> createRequestFields(final SDataRequest request)
   {
      return requestFields;
   }
   @Override
   protected List<ServiceField> createResponseFields(final SDataRequest request)
   {
      return responseFields;
   }
   @Override
   protected List<ServiceField> execute(SDataResourceElement payload, SDataRequest request, AsyncStatusListener listener)
   {
      List<ServiceField> list = new ArrayList<ServiceField>();
      if (!bPermitted)
      {
         throw (new RuntimeException(GLLanguageResource.getValue(GlobalContent.ACCESS_DENIED_MSG.toString()), null));
      }
      int errorCode;
      this.viewGLFSUM.recordClear();
      setGLFSUMViewFieldsValue(payload);
      MeterEventListener meterListener = null;
      if (listener != null)
      {
         meterListener = new MeterListener(listener, listener.getProgress().getProgressPct(),
               TrackingPayload.PROGRESSPCT_GET_RESULT);
      }
      errorCode = viewGLFSUM.process(meterListener);
      if (errorCode > 0)
      {
         throw (new RuntimeException(null, null));
      }
      list.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_ENDDATE)));
      list.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_CASH)));
      // … Lots more reponse fields added …
      return list;
   }
 
   /**
    * function to set field value
    */
   private void setGLFSUMViewFieldsValue(SDataResourceElement payload)
   {
      for (SDataResourceElement elem : payload.getContents())
      {
         final String name = elem.getName();
         String value = (String)elem.getValue();
         if (value != null)
         {
            viewGLFSUM.putBySDataPropertyName(name, value, Boolean.FALSE);
         }
      }
   } 
   /**
    * set the default value for the request fields
    */
   @Override
   protected List<ServiceField> createTemplateFields(final SDataRequest request)
   {
      List<ServiceField> list = new ArrayList<ServiceField>();
      this.viewGLFSUM.recordClear(); 
      list.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_YEAR)));
      list.add(ServiceHelper.viewFieldToServiceField(viewGLFSUM.getFields().get(GLFSUM.IDX_PERIOD)));
      return list;
   }
}

 

This service is then defined in the GLServiceMap.xml file under the tomcat\portal directory:

<?xml version="1.0" encoding="UTF-8"?>
<serviceMap>
<services>
       <service
         className="com.sage.accpac.gl60a.common.server.GLBALSHTService">
       </service>
… other definitions …
</services>   
</serviceMap> 

 

In the execute method, notice the View calls to the GLSUM View. You can see calls to recordClear and process. Basically these are calls to our Java JNI layer (Java Native Interface, http://en.wikipedia.org/wiki/Java_Native_Interface). Here we have a hierarchy of classes very similar to what we had in COM for VB programming where you can access all the View methods as well as a selection of convenient other System Manager APIs.

The basic logic here is to first initialize the operation in the constructor, get the input fields from the SData feed (the request fields as in setupRequestFields), perform the various View operations that are required (the execute method) and then setup the response fields to send back to the UI running in the Browser (created in setupResponseFields and then set in the execute method).

Hopefully, this gives a bit of a flavor for what sort of programming goes on the server for helping User Interface programs from an SData point of view.

Written by smist08

August 28, 2010 at 4:21 pm

Posted in Sage 300 ERP

Tagged with , , , ,

Client Logic for an Accpac 6 Web Form

with 6 comments

Last week we talked about creating Accpac 6 Web UIs (http://smist08.wordpress.com/2010/08/14/creating-a-web-form-for-accpac-6/). Part of the process is creating the XML representation of the Web Form which we discussed previously: http://smist08.wordpress.com/2010/05/28/how-to-layout-forms-in-sage-erp-accpac-6/. Now suppose you have an XML form designed either in an XML editor or using our SwtUIDesigner and you want functionality beyond that which is given to you by default. By default you have record navigation, save and delete. From last week’s article you will now have a simple runnable project. So where do you put your client side code? Within the client files is a file xxFeatureModule.java which contains:

/*
 * Copyright 2010 Sage Software Inc. All rights reserverd.
 */
package com.sage.accpac.SS60A.ss1005.client;
import com.sage.swt.client.ui.builder.AbstractSynchronousFeatureModule;
import com.sage.swt.client.ui.builder.InstanceContext; 
public class SS1005FeatureModule extends AbstractSynchronousFeatureModule
{
   public String getDebugName()
   {
      return "SS1005 Feature Module";
   } 
   protected boolean handleInstanceLoaded(InstanceContext context)
   {
      // TODO: Handle what happens when an instance context is loaded.
      // Return true if the handling was successful; false otherwise.
      return true;
   }
}

This is where you put your code. The handleInstanceLoaded routine is provided for you to hook your code in. Basically this is called once the UI has initialized and the XML form has been processed and everything has been created. At this point you can hook into any UI controls, data sources or do any other initialization you require. The context that is passed into this routine is the object from which you can get to everything else in the UI. It contains the collection of controls on the form, collection of datasources , etc. Below is a snippet of code for a simple handleInstanceLoaded routine that retrieves a button from the context by calling its getWidgets() method. Then it calls addClickListener() to add a listener that will be called whenever the button is clicked.

   protected boolean handleInstanceLoaded(InstanceContext context)
   {
      DefaultAllClickListener defaultAllListener = new DefaultAllClickListener(context); 
      SwtButton btnDefaultAll = (SwtButton) context.getWidgets().get
          ( UIConstants.WidgetIDs.CONFIG_BTN_DEFAULTALL );
      if (null != btnDefaultAll)
         btnDefaultAll.addClickListener(defaultAllListener);      
      return true;
   } 
   private class DefaultAllClickListener implements ClickListener
   {
      public DefaultAllClickListener(InstanceContext context)
      {
      } 
      public void onClick(Widget sender)
      {
          SwtMessageDialog.showMessageDialog(
               UILanguageResources.AppContent.DIALOG_RESTORE_DEFAULT.getValue(),
               UILanguageResources.AppContent.DIALOG_CONFIRM_DEFAULT.getValue(),
               SwtMessageDialog.ResponseButtonSetType.YES_NO,
               SwtMessageDialog.MessageType.CONFIRM, "",
               new MessageDialogListener[] {new ConfirmDefaultAllListener()});
      }
   }
   private class ConfirmDefaultAllListener implements MessageDialogListener
   {
      public void onRespond(MessageDialogEvent evt)
      {
         SwtMessageDialog.ResponseButtonType response = evt.getResponse();
         if (response == SwtMessageDialog.ResponseButtonType.YES)
         {
            defaultConfigInfo(popupContext);
         }
      }
   }

Notice that we defined a new class DefaultAllClickListener which implements ClickListener. Basically this is defining the class for the object that will be called whenever a button is pressed. So in handleInstanceLoaded we call “new DefaultAllClickListener(context)” to create a new object in the DefaultAllClickListener class. This object will now be called whenever the button is pressed. In Java this is the typically way you get event notifications. There is nothing like a COM Event special type of method. Event handlers are just regular methods in regular classes. You hook them up by passing them to the object that does the notifying, usually by some sort of addXXXListener() type method call. It’s up to each routine that does notifications to keep a list of all the objects they need to notify and to call each one in turn whenever anything happens.

This is true for all UI events, notice that even the confirmation dialog displayed has a ConfirmDefaultAllListener() that will be called when the button is pressed on this dialog. All notifications are asynchronous like this. You can’t call a UI action and expect to wait (in the code) for a response. If VB we could just do answer = MsgBox () and our code would wait for the user to choose Yes or No. In the browser, everything is asynchronous, nothing waits, everything is via notifications. This is a different metaphor than people are used to. In languages like VB some things are asynchronous and work via event notifications and some things are synchronous. The big difference is that calls to the server are asynchronous, so whenever you make an SData call, you are notified via this mechanism when something arrives. Contrast this to VB where you make a server call, your code waits for the response and no more code is executed until you get it. In the web world this could take quite a long time and the browser would be completely unresponsive if no code executed until you got this response. This is the world of AJAX (Asynchronous JavaScript and XML http://en.wikipedia.org/wiki/Ajax_(programming) ). AJAX is the key to creating rich internet applications in the Browser. If we didn’t use this, then the input forms would be quite boring lists of titles and edit boxes followed by a submit button, where nothing happens until you hit submit. This is another reason that you want to put groups of View calls in your server module, where everything works synchronously as normal. Generally you only want to make one server call at a time, since then you don’t need to chain together a number of responses, plus you get the performance benefit of only making one server call over the Internet.

Here we’ve been putting the code in the xxFeatureModule.java file, but unlike VB, there are no restrictions on how many source files you have and no rules that certain things have to be in certain files. So you can freely create as many class files as you like and organize your code in whatever manner you like. The nice thing is that you can follow industry best practices and aren’t under constraints like an event listener has to be in a special file in order to be called. So the expectation would be that you can create common Java libraries to share over UI projects and that you can keep your class files small and maintainable without putting everything including the kitchen sink into the xxFeatureModule.java file.

One aspect of Java that trips up VB (and C) programmers is the use of inheritance. When using an API or framework in C or VB, basically you look for all the functions, properties and events you can use. If you take this approach with Java, you will probably find that you are having trouble finding what you are looking for. This is because Java supports inheritance in a proper object oriented manner. So often the paradigm isn’t to do something by calling API functions, rather what you do is extend a class that does much of what you want and then just add the code that’s needed to make the difference. People new to Java often take a bit of time getting used to this. If you are stuck and aren’t finding an API that you are sure must be provided, instead look for a class that is along the lines of what you need that you can extend.

This code is Java and definitely not JavaScript. Yet this is what we are writing to run in the Browser. To produce the final runnable code we have to feed this through the GWT Compiler to get it compiled into JavaScript. The GWT Compiler will produce 6 versions of JavaScript from this code, one optimized for each major Browser family including WebKit (Safari and Chrome), Gekko (Firefox) and separate versions for IE 6 and 8. This then is the final code that you would deploy to your customer’s web servers to run.

In the meantime while you are developing, you keep working in Java. You can run this code as a Java application inside the Eclipse IDE. You can use the Eclipse Java debugger to single step through the code, set breakpoints and examine variables. You can also use all sorts of other Java tools to make yourself more productive like JUnit (http://www.junit.org/), Emma (http://emma.sourceforge.net/) or TPTP (http://www.eclipse.org/articles/Article-TPTP-Profiling-Tool/tptpProfilingArticle.html). Plus you can use all the plug-ins that are available for Eclipse such as say SubVersion for source control (http://en.wikipedia.org/wiki/Apache_Subversion).

All the methods are documented in the HTML JavaDoc which is generated from the comments in the source code. This is included as part of the SDK. Below is a sample of the beginning of the page that documents the InstanceContext. This page documents all the methods in this object.

 

Customization

Generally you can do quite a bit of customization just with the screen XML file. But at some point you are going to have to write some code to do the more deep customizations. To customize such UIs with code, you basically want to get hold of the context object, so you can access the collections of widgets and datasources. Then you can add your own listeners, or change the properties of the various objects. Basically you extend the main entry point class of the UI and extend the xxFeatureModule class. Add your functionality and then feed everything back through the GWT compiler to generate a new set of web pages to deploy.

Summary

Hopefully this gives a flavor for how the Browser part of a UI control is coded in the new Accpac SWT framework. Most of the basic functionality is automatic from the XML screen definition, but UIs are more complex than just CRUD (Create/Read/Update/Delete) and this is how you add that extra complexity to handle sophisticated UI needs.

Written by smist08

August 21, 2010 at 4:31 pm

Posted in Sage 300 ERP

Tagged with , , , ,

Creating a Web Form for Accpac 6

with 12 comments

Sage ERP Accpac 6.0A is just entering beta (lucky Friday 13th August, 2010). With this release you will be able start to run Web based User Interface forms. We’ve covered how this looks in several other blog posts like http://smist08.wordpress.com/2009/12/03/the-sage-erp-accpac-6-0a-portal/ and http://smist08.wordpress.com/2009/12/24/sage-erp-accpac-6-0-data-portlets/. But as a developer, how do you create new Web Based forms using the Accpac SDK?

The new Web based forms are written using a number of tools that are integrated into the Eclipse development environment (http://www.eclipse.org/). Our SDK involves programming with the Sage Web Toolkit (SWT) which is based on the Google Web Toolkit (GWT – http://code.google.com/webtoolkit/). With this toolkit you develop your programs in Java and then GWT provides a compiler that will compile these programs into JavaScript, which will run in your browser.

To start with we provide a project wizard integrated into Eclipse. You use this to create a starting Accpac screen project. From the File menu in Eclipse you choose File – New – Project… Then select under “Sage Web Toolkit”, “New SWT UI”. This gives the following dialog that gets the basic information on the UI you want to create:

When you are done and click Finish, our wizard will create a new project in Eclipse. This eclipse has a simple UI form and everything you need to start working on your UI:

This is a completely ready to run minimal UI that is all set for you to add your own logic to. If you don’t do anything and just choose run, then your project will be run in GWT hosted mode, which means it is run as a Java program in a container that simulate browser behavior. This is how you debug your programs. In this mode you can set breakpoints, single step through code, examine variables and do anything else supported by the full Java debugger. When you run it will pull up the sign-on screen:

And then when you sign-on you get the screen with the single table control connected to the A/R customers SData feed that we put on the form. Normally you would delete this control and then add the controls you really want on your form.

Behind the scenes is the development shell that show useful messages on what is going on, especially if something goes wrong. Plus status messages are sent to the Java Console inside the Eclipse IDE.

Once you have debugged the program and have it working then you run the project through the GWT compiler to produce a Web HTML/JavaScript version of the program that you can run directly in the Browser, or which you can add to the Accpac Portal.

When you are writing code for your UI, it can go into one of two places. If can reside on the Browser side of things running as JavaScript or it can run on the server side as an SData service (http://sdata.sage.com/ or http://smist08.wordpress.com/2009/11/24/sdata-in-sage-erp-accpac-6/). In VB all the UI code was within the VB OCX control and this talked directly to the Accpac Views (Business Logic Objects). In the Web world we have JavaScript running in the Browser (created by compiling Java programs with GWT), that talk to SData services running on the Server. These SData services then talk to the Accpac Views.

When programming the Browser half, you have nearly all the power you had in VB. You can add listeners to any control and get events as things happen. You can add listeners to data sources to get notified as things happen to data. This is very similar to VB event handlers. You can make SData calls that translate directly into View calls, like you called the Accpac COM API in VB. However there are some major differences. Just because you can do something, doesn’t mean it’s a good idea. In the Web world you want as much to happen on the server as possible. Any code you add to the JavaScript side, increases the size of JavaScript that has to be downloaded to the Browser (it will be cached and JavaScript code is very compact). Plus whenever you make an SData call to the Views, this call will happen over the Internet. This means it could be slow. Generally you only want to make 1 SData call to the server to do anything. You don’t want to make a bunch of SData calls to accomplish something. You really only want to make one call. Additionally in the Web world all calls are asynchronous, meaning that other processing continues after you send off the request. When you send the call you set a listener, which will be called when the response comes back from the server. This makes it quite complicated to make multiple calls and we don’t provide any help to do this in our framework (and neither does GWT), because we really want to discourage it.

So how do you do complicated sets of View operations? You create these on the server where we’ve made it easy. We let you create your own SData services or extend the standard services that we put on top of all the Views. So for instance to perform the operation to calculate taxes, you would send all the input fields in a service SData request, the server Java class would do all the viewPuts, viewProcess, etc and return any fields that changed as a result. This way you make 1 SData call from the UI, the SData process on the server makes all the View calls and returns the request. This minimized network time both in latency and bandwidth. On the server we have a generic class that wraps a View, this class is responsible for converting SData requests into View calls. It handles all the standard View protocols. You can extend this class with your own class and add extra logic to it. You can easily add additional fields from other Views or calculated fields. Generally you would like to only interact with one SData source from a UI, so you want to include any side lookups like description fields into the main SData source. You also would rather do calculations on the server for performance reasons, plus then anyone else using your SData service benefits from this code. All these server classes are controlled by a number of XML configuration files that define all the SData sources and configure which server side Java classes process them.

This was a quick overview of how to create a new Accpac Web based UI along with a bit of discussion on conceptually what you do next without any details. Hopefully in future blog posts we can fill in a few more details on how to program both the Browser and server in this new model.

Written by smist08

August 14, 2010 at 4:28 pm

Posted in Sage 300 ERP

Tagged with , , ,

On Database Dump and Load

with 3 comments

Sage ERP Accpac has always shipped with a number of very useful utilities for transferring data between Accpac installations. This is useful for backing up data, or sending it to say your business partner to do some work for you. These utilities are the reason that it is so easy to move an Accpac database from one database server to another, say from Pervasive.SQL to SQL Server or from SQL Server to Oracle.

Database Dump is an Accpac utility program that will export an Accpac company or system database to a set of database independent files in a folder you specify. You can run this either from the Start Menu – Sage Accpac – Tools – Database Dump or from the Database Dump icon in Administrative services. You need to be the ADMIN user to run this. These files can then be backed up, or sent to someone else to use.

Database Load is the Accpac utility program to load the files dumped by Database Dump. It doesn’t matter whether the source of the files was SQL Server, Pervasive.SQL or Oracle. Database Load can load them into any Accpac company (or system) database. You can run this either from the Start Menu – Sage Accpac – Tools – Database Load or from the Database Load icon in Administrative Services. You need to be the ADMIN user to run this. To run Database Load, all users must be out of the system. Database Load will first erase any tables in the database you have selected and then load the files into the newly emptied database. Generally you would load into a newly created database. The Database ID doesn’t have to be the same as it was when you dumped. If the Database ID is differences then Database Load will fix up and references to this in the database.

Database Copy is a shortcut that copies a database from on company to another on the same system. It works just like doing a Database Dump and then a Database Load. The only difference is that it doesn’t bother creating the dumped files as it processes. This utility is only available from the Start Menu – Sage Accpac – Tools – Database Copy icon; it is not available from Administrative Services. Again you must be the ADMIN user to run this.

Why Two Sets?

You might wonder why there are two sets of utilities, those run from the Start Menu and those run from Administrative Services. If you’ve run these, you will have noticed that these are similar programs, but they aren’t exactly the same. The set of program run from the Start Menu are the original set, these have been included with Accpac since the beginning. However with version 5.0A we introduced our current Web Deployed mode. In this mode screens can run inside the IE browser as ActiveX controls. The original utilities weren’t ActiveX controls and people might want to run these from the Browser. The original utilities also talked directly to the Accpac Database API rather than Views (Accpac Business Logic Objects), and once Web Deployed a program can only talk to the Accpac COM API and not directly to the Database API. So we moved all the logic for these to a new View: AS0025. This View does all the real work of dumping and loading. You can even call it from a macro if you want. Then we created two simple Accpac VB UIs that drive this View. These can be run Web Deployed. We didn’t move Database Copy, because we didn’t feel there was much need for it, and that people really just used Database Dump and Load. For Sage ERP Accpac 6.1 when we move Administrative Services to be a true HTML/Javascript Web Based UI, we will create new UIs for these, but continue to use AS0025 (now wrapped as an SData Web Service).

What Are the Dumped Files?

Database Dump creates a set of files consisting of an orgid.DCT file and then a subdirectory which is named after the orgid. This subdirectory then contains a large set of *.REC files each on named after a database table that has been dumped. The orgid.DCT file contains the data dictionary for the database, it lists all the tables in the database along with all the fields in each table and their name, type and some other info. It also lists all the indexes in all the tables. From the DCT files, we create an empty database containing the correct database structure to be used by our programs. The *.REC files then contain the data. Since all the structure information is stored in the DCT file, the *.REC file contains only data. Basically we take the data from each table and store it in our internal format (the same one used by the Views). There is one line for each record in the database. We don’t store the indexes, these are re-created by the database server as the records are loaded (due to this, some people use Database Dump/Load as an index repair utility; this sometimes works, except when index problems prevent the table being dumped correctly; generally it’s better to use the database vendor’s repair utility).

Since we don’t store any extra information, the set of REC files tends to be more compact than the original database. We use a very simple compression scheme to save some space; but a utility like 7-zip (http://www.7-zip.org/) will do a much better job of compressing the files and provide a single archive file to email or burn to DVD.

If there is no data in the table being dumped, then we don’t create a .REC file at all. So if you see some files missing, don’t worry they are just empty (or only worry if they should have some data in them). If you delete a .REC file, then that file will end up empty when you load; this isn’t recommended as it will probably compromise database integrity.

Issues with Third Party Tables

Some third party ISV programs create additional tables in the Accpac company databases. However if they don’t use the Accpac Database API to create these tables, then we don’t know about them and won’t dump or load them. Most third party programs do use our API these days, but there are still a few out there that don’t. If you have a third party add-in for Accpac that doesn’t dump and load properly then perhaps suggest to the vendor that they switch to using our Database API, so their files can be as easily transferred.

This is part of the reason we create the DCT file. The structure of an Accpac database isn’t the same for all installations or even all companies. The structure of each database depends on which applications have been activated for that database, including both Accpac applications and third party applications and add-ons.

Issues with Backing Up

Many people use Database Dump and Load as a mechanism to back up their company databases. This is fine, but it requires everyone be out of the system. It does make it easy to say send a backup to tech support or your Business Partner if you have a problem, but the higher end backup programs along with the backup programs built into the database servers have some advantages. For instance the SQL Server backup program is very fast, this is by far the fastest way to load a database. Also the SQL Server backup program can run while people are in the system and it will ensure that the backup is to a consistent place based on the transactions being executed. Either way is fine, just be aware that there are tradeoffs in any solution and what is best for you will perhaps be different than the solution for your neighbor. Some people will do a selection of backup techniques so if there is a problem with one, and then they have another, a backup of the backup.

Also beware that if Database Load fails, you won’t have a working database. Database Load needs to finish loading all the tables in order to give you a workable company database. If it does fail, fix whatever is wrong (usually out of disk space or a network interruption) and run it again. Database Load writes to the database using fairly small transactions of 50 records or so at a time, since from our performance measurements, this gives the quickest load time.

Summary

Database Dump and Load are extremely useful utilities that let you transfer Accpac companies between different organization IDs and even between different types of database servers. They provide an easy mechanism to send data to your Business Partner or Tech Support. They provide a mechanism to back up your data, if you need one.

Update 2010/8/8: One thing to watch out for when moving data between database server is sort order. Generally the data will be re-sorted in the new sort order (if they are different) when you Database Load. However if the original sort order is binary based and the new sort order is case-insensitive then you run the risk of any keys that allow upper and lower case being rejected as duplicate records and the Database Load failing. The main case of this is the Unit Of Measure table where people often have EA, Ea and ea. To avoid problems you have to delete the records whose keys only differ by case before dumping.

Written by smist08

August 7, 2010 at 8:16 pm

Follow

Get every new post delivered to your Inbox.

Join 249 other followers