Special Features

ODBC
(Open Database Connectivity)


Use your powerful desktop to the max! Easily incorporate your MyBooks data into your word-processing and spreadsheet served needs.

ODBC (Open Data Base Connectivity) is the technology standard that allows applications to easily communicate with each other and their data. (It is basically a filter on top of SQL.) MyBooks includes an ODBC driver (and server for client/server installations) which allows you to take advantage of the technologies available in most of today's Windows-based applications.

For example, when controllers have ever changing needs in financial reporting and projecting, they can easily bring their general ledger data from their MyBooks system into a popular spreadsheet for the ultimate in flexibility.


Table of Contents

Chapter 1: Overview Chapter 2: Configuring ODBC

Chapter 1: Overview

What is SQL?

SQL is a programming language created at IBM in the mid 1970's. (Its official pronunciation is 'ess cue ell' although many pronounce it 'sequel'). Primarily, the SQL language consists of a set of facilities for defining, accessing, and managing relational databases.

SQL has been adopted as an international standard by the International Organization for Standardization (ISO). It has also been adopted as a national standard in many individual countries, including in particular, the United States, where it is both an American National Standards Institute (ANSI) standard and a Federal Information Processing Standard (FIPS) as well. Furthermore, numerous SQL-based products—well over 100 of them at the last count—have been available in the marketplace for several years. There can thus be absolutely no doubt that, from a commercial point of view at least, SQL represents an extremely important feature of the database world.


What is ODBC?

The ODBC (Open Database Connectivity) interface from Microsoft has emerged as the standard mechanism for client applications to access data from a variety of different sources through a single SQL-based interface. This single interface insulates the various programs from the differences between the data sources.

In the ODBC environment, a Microsoft Windows® based application sends data requests to the Microsoft-supplied ODBC Driver Manager. The manager then routes the requests to the appropriate ODBC driver on the client. This driver, in turn, sends the request over the network to the server driver where the data actually resides. The server driver translates the standard ODBC function calls into calls the data source can process, and returns the requested data to the client application.

In technical terms, the resulting effect is a client/server database application environment as it provides the capability of an application on one computer (the client) to request and receive data from a source that resides on another computer (the server), communicating over a network.

In practical terms, it provides users with the ability to quickly and easily bring corporate data into popular desktop productivity products, like Microsoft Excel® and Corel WordPerfect®, for further processing. Users of client applications that support ODBC merely select a database from a point-and-click menu to connect, transparently, to data from that data source.


What Can You Do With ODBC?

The possibilities of application integration are virtually unlimited.

Here are just a few examples:
  • Use a popular word processing package like Microsoft Word® or Corel WordPerfect® to do a mail merge for sending a form letter to all customers or vendors.

  • Use a popular spreadsheet program like Lotus 1-2-3® or Microsoft Excel® to extract data from the General Ledger system for unlimited forecasting and analysis capabilities.

  • Use a popular reporting package like Seagate's Crystal Reports® to format sophisticated reports for corporate presentations.


You can even extend your development capabilities by using desktop application development tools like Powersoft's PowerBuilder®, Borland's Delphi®, or Microsoft's Access® to build client front-end screens and applications that process Appgen data.


Chapter 2: Configuring ODBC

The configuration of your ODBC software is dependant on the platform you are using (e.g., Windows, Linux). ODBC is not currently available for MacOS X.


To configure ODBC on a Windows-only system

Note: These instructions will serve you wether you are using a stand-alone PC or a network of PCs.

ODBC for MyBooks was installed along with all the other MyBooks software and automatically configured for your use with the demonstration software. When you create your own company, the Appgen ODBC database is created and the ODBC driver is configured, automatically. You should never have to do anything to use Appgen ODBC for MyBooks on a stand-alone Windows-based PC.

If you ever find the need to manually configure ODBC for MyBooks, you must use the Microsoft ODBC Driver Manager. Click on the ODBC Administrator (Data Sources) in your Windows Control Panel (usually found under Settings on your Start Menu, or under My Computer on your desktop or in Windows Explorer. If you cannot find it, please see your System Administrator). Then:

  • Click on the System DSN tab

  • Click on the Add button

  • In the list box that appears, click on Appgen ODBC for MyBooks

  • Enter the following in the dialogue box that appears:

    • Data Source Name: enter a name that you will use to refer to your MyBooks data

    • Description: optionally, enter a description of the database

    • Host: leave this field blank

    • Database: The actual name of the database. For the MyBooks demonstration company, the name is simply 'appgen'. If you created your own company, you can see the name of the corresponding ODBC database on the ODBC tab of the Properties box on the File menu.

    • User ID: This must be 'dharma'

    • Password: leave blank

You are now ready to connect your MyBooks database to other ODBC compatible applications.


To configure ODBC on a Linux/Windows client/server system

Note: These instructions will serve you when you have MyBooks installed as a client/server system, with Linux as the server, and Windows as the client(s).

The ODBC server software was installed on your Linux system along with the rest of the MyBooks software, but before you can use ODBC for MyBooks, you must first configure the server and the client(s).


Configuring the Server

Begin by doing the following on the Linux server:
  • Use the appropriate system administration program supplied with your operating system to create a user named "dharma". This user's home directory must be the base directory where you chose to install the My Books software during the installation process. Refer to your operating system's System Administration manual if you need more information on creating users.

    Important Note: Before you create this user, you may want to make a safe copy of the '.profile' or '.login' file in the base directory if any other user also has the same home directory.

    The 'dharma' user needs to have certain environment variables set properly. In addition to the regular Appgen user variables (AGHOME, AGTERM, AGCLIENT, AGINIT), dharma must have the variable 'TPEROOT' set to its home directory. The installation software includes the sample files 'ag_profile' and 'ag_login' in the home directory. Please consult these files and set the dharma user environment accordingly.

  • While still logged in as root, go to the base directory where you chose to install the software (during the installation process step 4), and type the following command: 

         ./ag_setup



Starting the Network Process on the Server System

Configuring the Appgen network involves steps on the system where the ODBC Server resides (the server) and on systems that will access the server (clients). The following sections describe the steps you need to complete on the server.
  • Edit the network services file to associate the sqlnw service name with a port number.

  • Start the dhdaemon process.

The Services File

The network services file (typically, the file /etc/services) must associate a service name for the Appgen network with a port number. Log in as root to modify the services file.

Use the service name 'sqlnw' for the Appgen network. Edit the network services file and add an entry similar to the one shown in the following example (choose a port number that will not conflict with any other network application—it must be unique in the services file). 

     sqlnw              1990/tcp
Note: It may be wise to copy the last existing line in the file, and then edit it, as sometimes the system requires a tab character to separate the fields.


Start the dhdaemon Process

The Appgen network daemon program, 'dhdaemon', must be running to enable network access from clients. To start the daemon, follow these instructions:
  1. Log in as the user 'dharma'

  2. Type the following command:

     
         dhdaemon  -e  dhserver  start



Installing the ODBC Client Software (ODBC Driver)

Client systems that access the Appgen ODBC Server must first have the Appgen ODBC Driver installed and configured on their systems. The ODBC Driver runs on Microsoft Windows systems (95, 98, NT, ME, 2000 Professional).

Note: To install the ODBC Driver, systems must be running Windows and TCP/IP.

Follow these steps to install the driver:
  1. Insert the MyBooks software CD into your CDROM drive

  2. If the install procedure does not start up automatically, run Setup.exe on the CD

  3. Follow the directions and select "ODBC Client Only"


The installation automatically installs the Microsoft ODBC Administrator utility if it is not already present.


Configuring the Appgen ODBC Client

Editing Network Configuration Files

Once the ODBC Driver is installed on the client system, you need to supply information about what systems the driver will connect to. To do this, you need to edit two network configuration files: the services file and the hosts file.
  1. Edit the services file.

    On Windows 95, 98, and ME systems, this file is simply named 'services' and can be found in the Windows directory.

    On Windows NT and 2000 Professional systems, this file is typically named 'services' and is usually located in the 'WINNT\system32\drivers\etc' directory.*

    Add an entry identical to that added in the server-side services file.

    Example: 
         sqlnw          1990/tcp
    Be sure to use the same port number as that specified in the server-side services file (see the instructions under Installing the Server Software).

  2. Edit the hosts file.

    On Windows 95, 98, and ME systems, this file is simply named 'hosts' and can be found in the Windows directory.

    On Windows 2000 Professional and NT systems, this file is typically named 'Hosts' and is usually located in the 'WINNT\system32\drivers\etc' directory. *

    Add the addresses and names of any hosts you wish to access with ODBC.

    * You can use the Windows 'Find' or 'Search' utility to locate the network files.



Adding Names of Data Sources for the Appgen ODBC Server

Use the Microsoft ODBC Administrator utility to add the names of any Appgen ODBC Server data sources to which the ODBC Driver will connect:
  1. Invoke the Microsoft ODBC Administrator from Windows (by default, from the Control Panel program group).

  2. In the Data Sources window, click on the 'Add' button.

  3. Select the Dharma ODBC SDK driver.

  4. The Dharma ODBC Setup dialog box appears. Enter information in the following text boxes:

    • Data Source Name: a reference name for the Appgen ODBC Server data source for use in connect calls and by the ODBC Administrator (this is the name users will see in their choice of data sources—e.g., Inventory )

    • Description: an optional descriptive string

    • Host: the name of the system where the Dharma ODBC Server data source resides

    • Database: the actual name of the database for the process to connect to on the host system. For the MyBooks demonstration company, the name is simply 'appgen'. If you created your own company, you can see the name of the corresponding ODBC database on the ODBC tab of the Properties box on the File menu.

    • User ID: the user name for the process (this must be 'dharma')

    • Password: the password for the process (dharma's Linux password)

You must supply the name of the data source. If you omit the host name, database name, user name, or password, the driver prompts the ODBC application user for that information when it connects to the data source.

The ODBC Administrator utility updates the ODBC Driver manager registry entry with the information supplied in the dialog box.