Travel with Sybase 12

Customers who wish to access Travel databases from third-party modules that do not use COM or NetServer need to use either SQL Server Express or Sybase SQL Anywhere 12, installed and configured manually. The information in this document applies to Sybase 12 and assumes that the customer has purchased a separate SQL Anywhere license.

Installing Sybase

The Sybase SQL Anywhere product can be installed with default options – we do not require any special functions. In fact, SuperOffice requires only the core database engine and ODBC drivers; all other options may be left OFF if the customer desires.

How it works – travel on Sybase

SuperOffice Travel follows a series of distinct steps that are the same regardless of the target database. Some definitions of commonly used terms:

Area: A definition of a subset of the central database, containing data from “included” users based on ownership (companies where an associate is “our contact” are owned by that associate). An area also has a set of “assigned” users; these are the ones that will get that area as their travel database. Assigned users should always also be “included”, but vice versa is not required – an area can contain data from more than the assigned users.

• Area Filtering: The process of applying the area definition (i.e., “included” associates) to a database, to derive the subset that contains only the data corresponding to the definition
• Prototype: A copy of the central (or satellite) database, created at some point in time, with area filtering applied. The prototype database is the starting point for a travel session.
• Local Update: Prepare to use a local, offline database, also termed “go on Travel”. In this operation the appropriate prototype is copied to the local PC and updated.
• Central Update: Return to use the central/satellite database; the local database is deleted
  1. Before anyone can go on Travel, a prototype needs to be made. A “default area” that performs no filtering always exists, and any users not assigned to a specific area will use the default one.
     Prototypes should be regenerated regularly to avoid too-long update processes when performing a local update.
     
     a. Technically, the prototype starts as an “empty” database file, residing in the so_arc\template\travel directory
     b. This file is copied to the same place, the new file is called AREAn.DB where n is the area id; default area has id 0
     c. An ODBC connection is made, using a data source called “AREA”, which must be set up on the machine where prototype generation is done
     d. The database is populated with data
     e. The ODBC connection is closed and the Sybase db engine stopped
     f. The file is zip-compressed, the final result is called AREAn.ZIP, with n being the area id.
     b) When a local update is performed, the prototype is copied to the local so_arc directory, updated with all changes since it was created, and settings are made in the database and souser.ini file to note that the user is on travel
     a. The superoffice.ini file is read to determine the data source name for the local ODBC connection, specified in the local_datapath setting
     b. The ODBC connection is examined to determine the target database file/path. There cannot be a programmatic default algorithm for this, since the whole point of running a separate Sybase engine is to make it accessible to other applications – thus the ODBC connection has to contain the full specification of engine, database file etc. so it can be used by any application.
     c. The prototype is copied/expanded to this file
     d. An ODBC connection is initated, starting the local database engine
     e. The local database is updated with changes
     f. LastLoginTravel=1 is set in souser.ini, and a record is made in the traveller table in the database
     g. The CRM client is shut down
     c) While “on travel”, the local ODBC connection is used to communicate with the local database
     d) During a central update, information is written back to the central database to update it with all changes done locally (or if Remote Travel has been used, with those that have not yet been read by the central database). Afterwards the SQL Anywhere engine is shut down and the database file deleted.
  
  The steps involved in travel are as follows:

Setting up the ODBC connections

Note that the prototype has to be made by a compatible version of the SQL Anywhere engine, ideally the same version as the one running on the client computers to be used for Travel. During testing one often uses the same computer for both prototype and local update, so this is trivial, but in a production environment prototypes will typically be made on some server and it should ideally run the exact same version/revision.
Neither the prototype nor local ODBC connection can be tested before use, since the databases do not exist yet; thus, a trial prototype run and local update should be performed before the user is let loose.

Prototype

All prototypes are made through a single ODBC connection. It has to be called AREA, and it needs to be set up with the following options:
Authentication: Database
Action: Start and connection to a database on this computer
Server name: [something], typically “Area”
Start line: Full path to the dbeng12.exe database engine, for instance “C:\Program Files\SQL Anywhere 12\Bin64\dbeng12.exe”, plus any desired parameters (for instance, -c 50M to set the initial memory cache size to 50MB).
Stop database after last disconnect: [X]
The Database file options should be left blank, as this will be overridden by the prototype-generation code during generation (the settings from the dialog are stored in the registry and are not updated by the generation).
An example of an AREA definition:

Local data source

This data source needs to be configured on each client computer to be used for Travel. Sybase SQL Anywhere 12 first has to be installed on the computer, as described at the beginning of this document; please test the installation make sure it works.
The local data source can have any name desired. This name is reflected in the setting local_datapath in the superoffice.ini file; local update will fail if it is missing or incorrect (if the data source is called LocalDb, then the superoffice.ini setting should be local_datapath=odbc:LocalDb).
The data source needs to be configured as follows:
Authentication: Database
Action: Start and connecto to a database on this computer
Database file: Full path and name to where the database should be, note that at the time the connection is configured that file does not exist – this setting is what tells SuperOffice where to create it.
Server name: [something], typically SoTravel
Start line: Full path to the dbeng12.exe database engine, for instance “C:\Program Files\SQL Anywhere 12\Bin64\dbeng12.exe”, plus any desired parameters (for instance, -c 50M to set the initial memory cache size to 50MB).
Stop database after last disconnect: [X]
An example of a local data source definition: