Home | DataFlex | Contact

ODBC: a Technical Overview

A Data Access Worldwide White Paper

Contents

Introduction

What does ODBC stand for? What is DSN?

What are the types of DSN?

How are Data Sources created?

How do Applications access the Data?

Where is information stored?

How to use the DataFlex Connectivity Kit for ODBC

What Data Sources Type should be used with Data Access Products?

Contacting Data Access Worldwide

Introduction

This white paper intends to answer some of the questions that are frequently asked by users attempting to work with ODBC and Data Access products – DataFlex Console Mode, Visual DataFlex and WebApp Server.

back to the top

What does ODBC stand for? What is DSN?

ODBC, Open Database Connectivity, is an open standard method of accessing data. The data to be accessed needs an interpreter (driver), which understands the format of the stored data, and a connection manager that determines how the connection needs to be made.  All this information is stored in a so-called Data Source Name (DSN).

Data Source simply means where the data is kept. It can be a file — typically, a database on a Database Management System (DBMS) — or even a live data feed. The purpose of a Data Source is to gather all the technical information needed to access the data — the driver name, network address, network software, and so on — into a single place and make the data access transparent to the user.

For example, a user should be able to look at a list of databases — that could include Payroll, Inventory and Employees — choose Payroll from the list, and have the application connect to the payroll data, all without knowing where the payroll data resides or how the application got to it.

back to the top

What are the types of DSN?

There are two types of Data Sources: machine Data Sources and file Data Sources. Although both contain similar information about the source of the data, they differ in the way the information is stored. Because of these differences, they are used in somewhat different manners.

Machine Data Sources are stored on the system with a user-defined name. Associated with the Data Source Name is all the information the database driver needs to connect to the Data Source and that the Driver Manager needs to coordinate all the Data Sources and drivers. There are two machine-data-source types: User- and System Data Sources.

File Data Sources are stored in a file and allow connection information to be used repeatedly by a single user or shared among several users. When a File Data Source is used, the Driver Manager makes the connection to the Data Source using the information from a .dsn file. This file can be manipulated like any other text file. A File Data Source does not have a Data Source name, as does a machine Data Source, and it is not registered to any user or system.

back to the top

How are Data Sources created?

Data Sources are generally created by the end-user or technician with a program called the ODBC Administrator.

When adding a new Data Source, the ODBC Administrator presents a list of the available database drivers from which the user chooses one. Then, the Administrator calls the selected driver and the driver displays its dialog box (The contents of the dialog depends on the driver's needs — see an example below) containing the fields to be filled out with the information the driver needs to connect to the Data Source. The driver, then, stores the information on the system.

back to the top

How do Applications access the Data?

When an application needs to access the data from those databases, it calls the Driver Manager and passes the name of the machine Data Source or the path of the file Data Source – depending on what is being used.

When a machine Data Source name is passed, the Driver Manager searches the system to find the driver used by the Data Source. It then loads the driver and passes the Data Source name to it. The driver uses the Data Source Name to find the information it needs to connect to the Data Source. Finally, it connects to the Data Source, typically prompting the user for a user ID and password, which, generally, are not stored.

When a file Data Source is passed, the Driver Manager opens the file and loads the specified driver. If the file also contains a connection string, it passes this to the driver. Using the information in the connection string, the driver connects to the Data Source. If no connection string is passed, the driver generally prompts the user for the necessary information.

For more information about the ODBC components see the DataFlex Connectivity Kit for ODBC User's guide.

back to the top  

Where is information stored?

Depending on the type of Data Source, the information the ODBC Manager uses and needs is stored in different places:

back to the top

How to use the DataFlex Connectivity Kit for ODBC

The DataFlex Connectivity Kit for ODBC uses the so-called Intermediate Files to identify an ODBC table type and location for the DataFlex system. This information is part of the Intermediate File and is stored after the "SERVER_NAME" keyword. If, for example, we have created a machine Data Source called "DSNExample" we would use the following line to identify that Data Source:

SERVER_NAME DSN=DSNExample

Alternatively, if we had created a file Data Source in the location \\Amachine\Avolume\Apath\DSNExample.dsn we would have created the following setting for the SERVER_NAME keyword:

SERVER_NAME FileDSN=\\Amachine\Avolume\Apath\DSNExample.dsn

back to the top

What Data Sources Type should be used with Data Access Products?

Depending on the desired use, one Data Source type will be better than the others, but developers should choose a DSN type according to their application design and users' needs.

Besides the DSN, Data Access products need the DataFlex Connectivity Kit for ODBC in order to access the chosen Data Source. There are three runtime environments that are able to connect to ODBC Data Sources through the Connectivity Kit:

·        DataFlex Console Mode apps

·        DataFlex Windows Desktop apps

·       DataFlex Web Application Server apps

Machine Data Sources should be used whenever there is a need to access data from one particular machine.

This can be illustrated as the case of WebApp Server applications, for example. You generally have one server housing the applications, and the applications accessing your database (local or not). So, no matter how many users are accessing your applications, only one machine — that which houses the applications — is connecting to the database.

Note: The WebApp Server is a service that runs on a Windows Server machine. You should make sure the user connected (logged on) to that service has sufficient rights to access the Data Source. More information on how to connect a user to the WebApp Server service can be found in the WebApp documentation.

On the other hand, if there is a need to access data from multiple machines, it is easier to use a File Data Source. To picture this in the WebApp Server situation, you can think of different machines housing your applications, all of which access the same database.

 In a WebApp runtime environment, three setups are typical:

·        A machine-User Data Source is used for the WebApp account. The WebApp is the only "user" of the ODBC connection.

·        A machine-System Data Source is used. The WebApp account has sufficient rights to access the DSN information in the registry. This setup is used if the machine running the WebApp service is the only machine connecting to the particular Data Source and all users logging on this machine need access to the Data Source.

·        A File Data Source is used. The connection to the Data Source is shared among the WebApp server and other applications running from different machines.

The WebApp service often runs "under" the system account. Accounts and Data Sources are completely different entities in completely different worlds. The system account, therefore, does not automatically have access to system Data Sources. You can set up rights to the registry using the regedt32 program.

While developing the WebApp, the developer also needs to access the ODBC Data Source for testing and building purposes. One should determine the runtime requirements and add an extra development Data Source if needed. If, for example, the choice made is to use machine-User Data Source in the WebApp runtime environment, the developer needs to create an additional Data Source that enables the developer to work on the same data. The type of that additional Data Source depends on the desired use.

In a Console Mode or Visual DataFlex application, one would typically have a multi-user environment. This is handled best by using a File Data Source if the applications are installed on each workstation. If you have workstations accessing a central machine where the application is housed, it is best to use a Machine Data Source.

back to the top