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.
to the top
does ODBC stand for? What is DSN?
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).
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.
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.
to the top
are the types of DSN?
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.
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 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.
to the top
are Data Sources created?
Sources are generally created by the end-user or technician with a program called
the ODBC Administrator.
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.
to the top
do Applications access the Data?
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.
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.
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.
more information about the ODBC components see the DataFlex Connectivity
Kit for ODBC User's guide.
to the top
is information stored?
on the type of Data Source, the information the ODBC Manager uses and needs
is stored in different places:
User Data Source is typically used on one machine by one user. The information
about the data and driver is stored in the machine's registry under the
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. Under this key, the available
drivers are stored — one subkey per driver. Furthermore, the available
User DSN's are stored under the "ODBC Data Sources" subkey.
DSN's are typically used in an environment where there is only one user
or as a test for ODBC connectivity.
System Data Source is typically used on one machine by all users of that
machine. The information about the data is stored in the machine's registry
under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. Here, all information
about the defined system Data Sources is stored. The information about
installed drivers is stored under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI
DSN's are typically used when there is a need to access data from one
machine. This usually is a server type process, like
File Data Source stores the information about the data, and how to connect
to it, in a disk file. File Data Sources can be shared among all users
who have access to the file.
to the top
to use the DataFlex Connectivity Kit for ODBC
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:
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:
to the top
Data Sources Type should be used with Data Access Products?
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.
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
Data Sources should be used whenever there is a need to access data from one
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.
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.
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.
a WebApp runtime environment, three setups are typical:
Data Source is used for the WebApp account. The WebApp is the only "user" of
the ODBC connection.
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.
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.
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.
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.
to the top
- Data Access Worldwide Support
Visit the DAW Support Home page for information about all of our
support offerings, including the list of supported products, bug report
forms and free support offerings, such as the Knowledge Base, White Papers and Peer Support Forums.
Data Access Worldwide
14000 SW 119 Ave
Domestic Sales: 800-451-3539
Data Access Worldwide - Brasil
Av.Paulista, 1776 - 21st.Floor
São Paulo -SP - Brazil
Data Access Worldwide - Europe
7553 AE Hengelo
Telephone: +31 (0)74 - 255 56 09
Fax: +31 (0)74 - 250 34 66
Data Access Corporation
You may reproduce and distribute this document in its
entirety on paper or electronically. Reproduction or distribution of a
modified version of this document or any of its content is not authorized.
DataFlex is a registered trademark of Data
DISCLAIMER - NO LIABILITY FOR CONSEQUENTIAL DAMAGES
To the maximum extent permitted by applicable law, in no
event shall Data Access Corporation be liable for any special, incidental,
indirect, or consequential damages whatsoever (including, without limitation,
damages for loss of business profits, business interruption, loss of business
information, or any other pecuniary loss) arising out of the use of or inability
to use any information provided in this document, even if Data Access
Corporation has been advised of the possibility of such damages. Because some
states and jurisdictions do not allow the exclusion or limitation of liability
for consequential or incidental damages, the above limitation may not apply to