Unix ODBC
Welcome to the unixODBC User Manual.
This manual is targeted toward people who will be using unixODBC to access
data sources from tools and applications which have been developed by others.
This manual complements the Administrator
Manual and the Programmer Manual,
each of which is geared for a more technical audience.
Getting Started
At this point unixODBC has been installed
by your System Administrator. Your System Administrator should have installed
and registered at least one ODBC Driver. Your System Administrator accomplished
this by following the directions layed out in the Administrator Manual.
You will need an account on the UNIX/Linux
machine; this is also provided by your System Administrator. In fact, if
you run into problems at any point in this manual then you should refer
to your System Administrator to ensure that all required software is installed,
accounts given and priveleges granted. You may also want to talk to your
Database Administrator (DBA) to ensure that you have access to your database
and to resolve any questions about which driver to use and what options
to set.
UNIX users can be given a wide variety
of methods to access their account resources and these access methods usually
fall into one of two categories.
1. shell account (telnet and terminal sessions)
2. graphical desktop
If you are limited to a shell account then
you will not be able to use the ODBCConfig and the DataManager tools.
We are now ready to start using the unixODBC
tools. The first thing you should do is verify that you have a working
System DSN. If you do not; then you should create a User DSN. These tasks
can be accomplished using the ODBCConfig tool.
Using ODBCConfig
The ODBCConfig tool is designed to allow
you to easily setup a Data Source (DSN). DSN's act as an access point for
getting to your data. In many cases; creating a DSN is as simple as picking
a Driver to use, selecting a Server, and entering a Name for the DSN. In
fact; DSN stands for Data Source Name.
You should find using the ODBCConfig tool
to be quite intuitive because of the simple Graphical User Interface (see
Figure 1) but you must understand a few terms before getting started.
 |
 |
 |
 |
 |
|
||||||||||||||||||||
Figure 7
User DSN
|
These are your personnal Data Sources.
You are able to; Add new ones, Remove existing ones, and Configure existing
ones. User DSN information is stored in a secret location where only you
can access them. Keeping your User DSN's seperate from other User DSN's
allows you a great deal of flexability and control over creating and working
with Data Sources which are only important to you.
System DSN
These are created by the System Administrator.
They act very much like the User DSN's but with three important differences.
1. ONLY the System Administrator can; Add,
Remove and Configure System DSN's.
2. System DSN's will be used only if the
DSN does not exist as a User DSN. In other words; your User DSN has precedence
over the System DSN.
3. Everyone shares the same list of System
DSN's.
Drivers
Drivers contain the special code required
to talk to the specific type of database you will work with. The Drivers
often come from the vendor of the database but may also be found in the
unixODBC package. Your System Administrator is the only user who can install
and register a Driver. You will select a Driver to use when adding a new
DSN.
Add a DSN
You will want to ensure that you have at
least one working DSN. Here is a quick step-by-step guide to creating your
first User DSN. We will not actually use it yet because that will involve
using other tools and we have not talked about them yet.
1. Execute ODBCConfig
This can be done in a variety of ways.
If you know that you have an icon or menu item for ODBCConfig on your desktop
then execute it using one of these methods. If you do not; then start a
shell and enter the command ODBCConfig.
You should see a window popup (see Figure 1).
2. Add
Click on the User
DSN tab to ensure that you are working with User DSNs. Click on the Add
button. Select a Driver from the list. If the list is empty then contact
your System Administrator; only the System Administrator can add Drivers.
For this example we will try to use the Text File Driver. Select the Text
File Driver if you have it availible.
3. Edit Options
You should be presented
with a list of DSN options which you can edit. Figure 2 shows the
options for the Text File Driver but you may have a different set of options
if you selected a different driver. Common options are; Name (a unique
name must be entered). Description, Trace and TraceFile.
Enter a unique name,
enter a comment, turn Trace off and click Ok to save it. You may click
on Configure, at the main window, to come back to these options at any
time in the future.
4. You're done
Notice that you now
have your new DSN listed in the main form. This means that you can try
to use it in any tool or application which uses ODBC DSNs for data access.
This includes many applications such as Word Processors and Spread Sheets.
You may want to test your DSN using the DataManager.
Summary
ODBCConfig is a usefull
tool for PowerUsers but it is simple enough for almost any user to use.
ODBCConfig exposes the most important reason for using ODBC to access your
data; the ability for you or your System Administrator to change the Data
Source for your tools and applications. Please take some time to get familiar
with ODBConfig and your Driver options, perhaps sit with someone who is
a bit more technical and talk about it for one or two minutes. You will
be rewarded.
Using DataManager
The DataManager is a great, graphical,
tool for exploring your Data Sources. It allows you to explore you Data
Sources in a similar manner to exploring your file system. The DataManager
( see Figure 6 ) is split into two views. On the left hand side you have
a Tree View'. The Tree View is where you can drill down to the information
that interests you. On the right hand side you have a Detail View. The
Detail View shows any details that may be availible for the selected item
in the Tree View.
Just like ODBCConfig, you can excute the
DataManager in a variety of ways. One way is to go to a shell and enter
the command DataManager. This should bring
up a window that looks similar to Figure 6.
Next; expand the nodes to drill down to
the information that interests you. You will be asked to login if you try
to drill past a Data Source. If this happens, enter the login ID and Password
provided by your Database Administrator or System Administrator. You will
know that you are logged in when the little computer screen changes from
Red to Green.
One of the interesting Detail Views occurs
when you select a Data Source item in the Tree View when you are logged
into it (the little computer screen is Green). The Detail View is an SQL
editor. This is onlu usefull if you know the SQL command language but for
those that do... it can be very handy.
Summary
The DataManager tool is a good way to test
a DSN and then to see what resources are availible inside the Data Source.
It is also very easy to use.
Using isql
This is a command line tool. This means
that you can use it even if you are not working on a Graphical Desktop
(for example; in a telnet session). This tool is designed for more advanced
uses of unixODBC. You can use isql to test a connection but it is designed
to be used by those experienced with the Structured Query Language (SQL).
You probably will not want to use this tool if you are unfamiliar with
SQL.
isql allows you to;
1. connect to your Data Source (using a
DSN)
2. send SQL commands to the Data Source
3. receive results from the Data Source
This tool can act in batch mode or interactive
mode. Figure 3 shows a simple, interactive, session.
Figure 4 shows an example of isql being
used in batch mode. Notice that it is being told to run a similar query
as above but this time it is coming from a file ( My.sql ).
The example, in Figure 4, is also formatting
the results into an HTML table and is sending them to a new file ( My.html
). Figure 5 shows the resulting html table.
Summary
isql is a powerfull tool for working SQL
to access your Data Source but it is more for the advanced user.
StarOffice 5
StarOffice is an application similar in
goals to MS Office. A free version, for non-commercial use, can be downloaded
from StarDivisions web site.
Figure 7 shows a PostgreSQL table being browsed in StarOffice. StarOffice
can use ODBC data but it can be tricky to get going. Here are some things
to note about using StarOffice with unixODBC. Make sure that unixODBC is
installed on your machine before trying to use StarOffice ODBC on UNIX.
Q. StarOffice disappears when I try to
load a list of ODBC DSNs and I get an error in my terminal window
about some library file missing?
A. If you have StarOffice 5.0 you may want to try adding this to your soffice start script export
LD_PRELOAD=/usr/lib/libodbc.so Your soffice
start script can be found in Office50/bin/soffice and can
be edited with any text editor. If you are not sure of where libodbc.so
is or where soffice is then you may want to use the UNIX find
command.
However with the release of StarOffice 5.1 and beyond all you need to do is
to add the path to the libodbc.so to either /etc/ld.so.conf or to your
LD_LIBRARY_PATH environment variable.
Q. Do all ODBC drivers work with StarOffice?
A. No. StarOffice is very demanding upon an ODBC driver. StarOffice
needs many ODBC features in order to accept a driver. Two drivers
which are known to work are; 1. PostgreSQL and 2. MySQL. People are actively
working on other drivers.
Summary
StarOffice is a rising 'star' in the UNIX
world. You can combine StarOffice with unixODBC to get at your data.
With StarOffice and unixODBC you can pull your data into a Spreadsheet,
Word Processor or even create Web forms based upon your data.
Conclusion
unixODBC comes with a variety of usefull
and powerfull tools to allow you to configure you ODBC access and to work
with your ODBC data. Familiarity with these tools is a great start to using
your ODBC in applications such as word processors, spreadsheets and even
applications developed at your company of employ. I hope you enjoy them!
Please email comments and/or suggestions to me, Peter
Harvey.
No comments:
Post a Comment