Easysoft XML-ODBC Server User Guide - Creating a Data Source
Creating an Easysoft XML-ODBC Server ODBC data source
This section explains how to create data sources for the Easysoft XML-ODBC Server.
The server is the machine where the ODBC driver for your database is located. The database itself may also be on this machine, although it can be located elsewhere.
To allow remote machines to access your database, you need to create a data source on the server machine to make the database available to client applications.
For details of the Easysoft XML-ODBC Server implementation, go to the section appropriate to your server platform:
ODBC data sources
An ODBC application (the Easysoft XML-ODBC Server in this case) connects to a database by using a description of that data source, the content of which depends on the ODBC driver being used to access the database and consists of a set of attribute/value pairs.
Usually, the application links with a driver manager that looks at the data source description in the connection string, loads in the required ODBC driver and then passes the connection string to the ODBC driver.
At its simplest the application passes a connection string which defines a data source name (DSN) to the ODBC driver (or driver manager), such as:
In this case the driver manager looks at the Driver attribute in the data source to decide which driver to load and then the driver looks up the data source to retrieve all the other required attributes.
This information is found in the registry on Windows and in the user/system files on Unix.
Therefore you have to create a data source containing all the attributes the driver requires to describe the database (or alternatively, the application can pass all the attributes in the connection string) before the Easysoft XML-ODBC Server can connect to an ODBC driver.
With the Easysoft XML-ODBC Server you need a data source on the server to describe the target database in whatever terms the ODBC driver requires (on Windows, for example, you use a dialog box provided by the ODBC driver and accessed from the ODBC Administrator).
Connecting to data sources
In general, ODBC applications must be linked with either an ODBC driver(although this is very rare) or adriver manager.
The Easysoft XML-ODBC Server takes a connection string from a client application and uses it in a SQLDriverConnectW call to the ODBC driver manager.
The driver manager examines the connection attributes and loads the required driver, which is either named in the connection string DRIVER attribute or is referenced from either a registry key (on Windows) or a .ini file (on Unix).
The connection string contains a list of connection attributes, normally including one of the following:
- a DSN=(Data Source Name) attribute which names the data source. Specifying a DSN explicitly tells the driver manager which driver to load because each DSN will contain a DRIVER attribute.
- a DRIVER=attribute which names the driver, which allows you to choose any database to which the driver has access.
- a FILEDSN=attribute which allows the connection string attributes to be read from a file.
A connection string would look something like:
where pubs is the data source name, demo is the user name with which to connect to the database, and easysoft is the password for the demo user.
Windows data sources
The Easysoft XML-ODBC Server can connect to any system data source configured on a Windows machine, given the necessary information. It can also connect to any database if all necessary ODBC attributes the driver needs have been specified by using a connection string.
When creating the data source on your server, you should use the ODBC driver suitable for your database. For example, if you want to connect to a SQL Server database, you should use the SQL Server ODBC driver to create the data source.
The instructions in this section show you how to create a data source for the Microsoft Northwind database, which is shipped with Microsoft Access. You should follow the same procedure to connect to your own database on your server machine, using the Microsoft Data Source Administrator.
To follow this example, you should have on your computer:
Using the Microsoft ODBC Data Source Administrator
1. Select Start > Settings > Control Panel, double-click Administrative Tools and then Data Sources (ODBC).
Select Start > Settings > Control Paneland double-click ODBC Data Sources (32bit).
Select Start > Settings > Control Paneland double-click Data Sources (ODBC).
The ODBC Data Source Administrator dialog box is displayed:
Figure 12: The ODBC
Data Source Administrator User DSN
2. Select the System DSN tab:
Figure 13: The ODBC
Data Source Administrator System DSN
It is important to create a system DSN rather than a user DSN, which is only visible to the desktop user who created it.
Since the Easysoft XML-ODBC Server runs as a service, User DSNs are not available to it.
3. Click Add... to add a new data source.
The Create New Data Source dialog box displays a list of drivers:
Figure 14: The Create New Data Source dialog box
4. Select Microsoft Access Driverand click Finish.
The ODBC driver for Microsoft Access displays a dialog box for configuring the data source (this dialog box and the attributes you need to specify vary depending on the ODBC driver you are using).
The Microsoft Access ODBC driver is NOT thread-safe unless run with Jet version 4. Previous versions require the Easysoft XML-ODBC Server to be configured to run in multi-process mode, rather than the default multi-threaded mode.
5. Enter your chosen name for this data source in the Data Source Name box (e.g. "XML-ODBC-Demo").
6. In the Description field, enter something that would help a user faced with a choice of data sources (e.g. "For demonstrating XML-ODBC"):4
Figure 15: The ODBC
Microsoft Access Setup dialog box
7. Click Select... to browse for the target database, select your chosen database and click OK.
Although the example used the Microsoft Office\Office\Samples\Northwind.mdb database, this database may not exist or may be in a different location on your system, in which case you may use any database you have to hand.
Note the data source name because you will need to specify it when you create a data source on the client machine.
8. Click OK.
You are returned to the ODBC Data Source Administrator window.
- the window now contains a line with your new data source in it
- the System DSN tab should be selected (if it is not, then you must remove the new data source, select the System DSN tab, and return to step 3).
9. Click OK.
You have now set up a system data source on your machine to a local database, making it visible to the Easysoft XML-ODBC Server.
Testing the data source
You now have the Easysoft XML-ODBC Server running on your Windows machine and a data source connecting to the database on the server.
To test that this data source is working, so that you can verify that the server side is functioning correctly, you can run any other ODBC application on your Windows machine, linking to this data source and accessing its data.
Refer to the documentation supplied with your ODBC application if you are unsure how to link to a data source.
If the database on your server machine is a Microsoft Access database then you cannot test the data source by linking to it from the Microsoft Access application.
You must connect to it via another ODBC application on your Windows machine, because although Microsoft Access is a multi-threaded application, the Microsoft Access ODBC driver is NOT thread-safe.
Unix data sources
The Easysoft XML-ODBC Server can connect to any system data source configured on a Unix machine, given the necessary information.
You MUST use the unixODBC driver manager on Unix.
A version of unixODBC is supplied with the Easysoft XML-ODBC Server, which requires version 2.2.2 or greater to be installed in order to function correctly (see "The unixODBC driver manager and Unicode" on page 176).
With unixODBC, you can create a data source by either:
- directly adding the data source and its attributes into a configuration file (odbc.ini)
- if you are running an X server you can create a data source using the graphical ODBC Data Source Administrator (ODBCConfig).
Editing a configuration file
With unixODBC, data sources are stored in a configuration file called odbc.ini.
If you accepted the default Easysoft XML-ODBC Server installation, system data sources will be stored in /etc/odbc.ini.
However, if you have built unixODBC yourself, then it will be whatever path you specified in the sysconfdir=directory configure option.
If sysconfdir has not been specified then the path will default to /usr/local/etc.
By default, you must be logged in as root to edit a system data source defined in /etc/odbc.ini, but user data sources created in an .odbc.ini file in a home directory are visible to an individual user only.
Each section starts with a data source name in square brackets [ ], followed by a number of attribute=value pairs.
The attributes that you need to specify vary depending on which ODBC driver you are using to connect to the local database.
A sample data source using the PostgreSQL driver is of the format:
Description = Main data on Admin box
Driver = PostgreSQL
Database = main
Servername = localhost
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
unixODBC uses the DRIVER attribute to look up the driver in the odbcinst.inifile and locate the shared object to use as the ODBC driver.
Refer to the documentation with the ODBC driver for full details of the attributes it requires to define a data source.
Using the ODBC Data Source Administrator
To create a data source using the graphical ODBC Data Source Administrator supplied with unixODBC:
1. Run an X session connecting to your Unix machine, ensuring that you log in as root.
2. Change into the <InstallDir>easysoft/unixODBC/bin directory.
3. Type ./ODBCConfig and press <Enter>.
The ODBC Data Source Administrator opens.
4. Click the System DSN tab to create a data source which is available to any user or service that logs into this machine.
5. Click the Add button to create a new data source.
The Adding a New Data Source dialog box displays a list of the drivers available.
6. Select the driver that you want to use to connect to the database and then click OK.
A configuration dialog box specific to that driver is now displayed, such as the PostgreSQL data source:
Figure 16: The Configuration dialog box for a PostgreSQL data source
Refer to the documentation with your ODBC driver for full details of the attributes you need to specify on this dialog box.
7. OK this dialog box when you have specified all the data source attributes and then close the ODBC Data Source Administrator.
Testing the data source
You should now verify that the data source you have created and the Easysoft XML-ODBC Server setup are functioning correctly by using any ODBC application available on your Unix machine or by using the unixODBC isql utility.
To use isql to test the data source:
1. Change into the <InstallDir>easysoft/unixODBC/bin directory.
./isql - v data_source_name
For example, to connect to the PostgreSQL data source illustrated earlier in this section, you would type:
./isql -v main
The isql "-v" returns ODBC diagnostic messages, which are useful if you have problems connecting to your data source using isql.
If your server database requires authentication, you should include user name and password arguments in the isqlcommand.
./isql - v data_source_name dbuser dbpassword
3. Once connected, type an SQL statement to query the data, such as:
select * fromtablename
where tablename is a table in that database.
Or simply type help to get a list of tables in the database.
4. To leave isql and return to the system prompt, press <Enter>.