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 compliments 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 (we will assume that the ODBC Text File Driver has been installed). Your System Administrator accomplished this by following the directions laid 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 privileges 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.
 

User DSN

These are your personal 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 separate from other User DSN's allows you a great deal of flexibility 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, logged into the ODBC enabled computer, 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 available.

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. Notice that the Database in Figure-2 is /home/pharvey/test.db. You should ensure that this points to something like; /home/YourLoginID/test.db where you replace YourLoginID with your ID. The test.db file should not already exist (for the purposes of this excercise); it will be created for you. This is how to create a new database using the ODBC Text File Driver.

4. Your 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 useful 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 ODBCConfig 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 available for the selected item in the Tree View.

Just like ODBCConfig, you can execute 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 only useful 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 available 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.
 

isql is a powerful 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/sofficeand 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 useful and powerful 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.