Advanced report design

Database access information

This documentation will help you to solve problems and setup your database.

Check installation

Before beginning the troubleshooting you should check this items:

• Install the client database software correctly, and server database if needed
• Check your database connection with a standard TCP/IP connection application provided by the database vendor. Check you can access tables and fields executing some querys
• Install additional software if needed, for example if you plan to access the database throught ODBC, install the ODBC software (unixODBC in Linux/ODBC32 in Microsoft Windows) and make sure to use ODBC driver compatible with the version you installed. Again check ODBC connections with the provided ODBC client tools.
• Check you are using the correct or latest drivers, for example for Visual FoxPro you should download the ODBC driver update from Microsoft web site.

This will ensure the database software is correctly installed.

Database drivers overview

There are some database drivers to connect with Report Manager to databases, usually a connection can be done using different database drivers, each database driver can have issues or bugs, if you find a issue using a database driver you should ask about the issue in a correct place.

You can also purchase drivers to third party, usually for a fee, you can find more DBExpress drivers, more ODBC drivers etc.

DBExpress

DBExpress is a technology introduced by Borland since the release of Delphi 6, as a replacement for the Borland Database Engine SQL Links, see section about BDE bellow for more information.

DBExpress libraries are small and efficient, they are easy to update when a version change of the vendor occur.

The main problem is that, Borland, the main developer of DBExpress drivers, does not frequently update this drivers, because limited resources etc.., this is a important issue because some database engine change versions often, this changes introduce sometimes incompatibilities (PostgreSQL, MySQL).

Borland was not released sourcecode of DBExpress drivers (but released the specification so third party can write them), so you can't update the Borland provided DBExpress drivers.

Provided DBExpress drivers:

Troubleshooting for all drivers

Check the libraries you need are in your system, you can also check dependences. If you have problems with some datatypes you can try to set BlobSize to a value like 10000

Unable to load library "libraryname"

This message is raised when the vendor librarys are not installed or are a different incompatible version, it's reported to work to install the correct version for the DBExpress driver.

Note that the error message usually refers to the DBExpress driver library, not the vendor library but the problem is in the vendor library.

For example when using Oracle 9i, you can install the Oracle 8i client library in the directory where Report Manager is installed, so Oracle 8i client library will be used, this is usually not recomended by the database vendor, so be aware of the risk, especially when updating the database (working with report libraries).

Check the dependences with a programming tool, for example in Linux:

ldd libsqlpg.so

It reports a link to libpq.so.2 so you should create a symbolic link if this library does not exists:

ln -s /usr/lib/libsqlpg.so.2 /usr/lib/libsqlpg.so.3

Borland Database Engine

Borland Database Engine was released by Borland since the first version of Delphi, and have been updated until Delphi 6.

It's not provided by default on Report Manager, unless you purchase the Standard Edition.

It's a really mature engine, but is big and have some overhead when accessing to SQL servers. It's very good to access DBase, Foxpro, and Paradox files because it includes a Local SQL processing engine for this file based databases, can also access text files and have a automatic and mature ODBC bridge. It has an interesting and flexible database connection manager, you can configure and test database connections using Borland Database engine configuration at the control panel.

The SQL Links are libraries installed with the Borland Database Engine, this SQL Links will not be updated in a future and was not updated from Delphi 6 release, I think from 2001, so you can have problems if you access a database server wich changed the client/server protocol or client libraries since that date. If you want continuing using BDE and the SQL Link is out of data you can create a ODBC connection, and install the correct driver for the newer database version.

The SQL Links provided are:

Borland replace the BDE SQL Links with DBExpress, because BDE is big in size (and functionallity), it's not cross-platform, and the update and programming of SQL Links (drivers for SQL engines) is a difficult thing, and can be done only by Borland. So for all SQL Servers is recommended the use of DBExpress.

Microsoft DAO

This driver is based on Borland interface to Microsoft Data Access Objects, it only works in Microsoft Windows, there is a wizard to build connection strings to connect to any installed OLE DB provider or ODBC datasource. This is a common system for database connection for Microsoft enabled development environments like Visual Basic, Visual FoxPro...

ZeosLib

Zeos library is a component set to access some database engines, it's compiled inside all Report Manager binaries, so you can use it normally, but if you use Report Manager components and want to add the support in compilation (Kylix/Builder/Delphi) modify the rpconf.inc file to enable the Zeos Library compiling and linking.

Usually you set the hostname, database, user name and password, but other parameters are available like the TCP/IP port and character set.

To configure ZeosLib connections the same files as DBExpress drivers are used (dbxdrivers and dbxconnections).

This driver is allows reading of TClientDataset files (.cds) in binary or XML format, also allows reading of text files as a database.

The protocol property allow the selection of the database server. It supports Mysql,Ppostgresql, Interbase/Firebird, Microsoft SQL Server, Sybase, Oracle and IBM DB2.

Interbase Express

This driver is based on Interbase Express components provided by Borland in Delphi/Kylix and C++Builder environments, the main advantage is that no external shared object libraries are needed.

Interbase Objects

This driver is only available for Windows, it's implemented but not provided because it's a comercial product. Allow access to Interbase and Firebird.

Mybase

This driver is allows reading of TClientDataset files (.cds) in binary or XML format, also allows reading of text files as a database.

Getting to work all databases and database drivers in Suse 9.0

PostgreSQL

Execute Yast, install PostgreSQL server packages and client packages (this where not installed by default), the important file missing by default to install is libpq.so.3, located at a client library package. I went to Software installation selected search postgre and checked all related packages to be sure.

Related packages: postgresql-libs pgaccess postgresql

First step is to start and setup the server I had some problems here because I'm new to PostgreSQL, I don't know if the way I do things is ok but it worked, I don't know why this steps are not done automatically by the setup scripts in software installation. Of course that forced me understanding how PostgreSQL works, learn reading documentation and also loosing lot of time.

We will also install the unixODBC related packages and the qt and tk interfaces for configuration, this is for testing purposes , if you do not plan to use ODBC you don't need them. This are packages: unixODBC unixODBC-gui-qt unixODBC-gui-tk psqlODBC

The startup script uses the postgres user to launch the PostgreSQL server for security reasons, so this user have limited privileges but should have all the privileges for the main "database repository", we will create a database directory and give ownership to this user.

Login as root and type:

mkdir /var/pgdata

chown postgres /var/pgdata

Now we must initialize the "database repository", but we must do it with postgres user, another problem here, I dont know the postgres default password, so I changed the password with the Yast configuratoin tool.

Login as postgres and type:

initdb -D/var/pgdata

This initializes the "database repository".

Before starting the service we should say to the startup script /etc/init.d/postgresql the location of "database repository". I did it by executing the Yast tool: Editor for /etc/sysconfig. Go to the branch Applications-PostgreSQL and set:

POSTGRES_DATADIR /var/pgdata

POSGRES_OPTIONS -i

The -i option is to tell PostgreSQL to listen TCP/IP incoming connections.

Now we will start the database server but we will need to restart in a few minutes. Go to Yast and execute the tool: Execution Level editor. Select PostgreSQL and check levels 3 and 5, select start and check it's started.

Loign as posgresql and type:

createuser yourloginname

You will be asked, let the user create databases and optionally users.

Now you are a postgresql database user, we will create a database:

Login as yourloginname and type:

createdb test

Still not finished, we must say PostgreSQL wich IP connections are able to connect to the database server, by default all connections are closed.

Login as postgres and type, or edit with your favourite editor:

vim /var/pgdata/pg_hba.conf

Remove the # symbol from the line:

host all all 127.0.0.1 255.255.255.255 trust

Now we need to restart the postgresql service, go to Yast Execution Level Editor and restart the service.

Login as yourloginname and create some data.

I executed pgaccess from a X-Window console to start a client application to connect to database test, this application uses also TCP/IP, so it's usefull to test a connection, other tools like createdb are not usefull to test connection configuration because does not use TCP/IP.

Now we are sure PostgreSQL is up and running correctly.

PosgreSQL with DBExpress

Go to Report Manager connections configuration window, select PostgreSQL and click add.

After configuring the hostname as localhost, the database name as test, and placing login information (the same as your login) It does not work, a error message is raised: library libsqlpg.so can not be found.

But the library is in the path, this is a problem with the vendor library, lets see executing:

ldd libsqlpg.so

This commands shows a link to libpq.so.2, but in my system I have a library libpq.so.3, I cross my fingers and expect libqt.so.3 be compatible with the expected libpq.so.2.

Login as root and type:

ln -s /usr/lib/libpq.so.3 /usr/lib/libpq.so.2

This creates a symbolic link to the newer library, I think you can have problems if you use features available only with the newer version. But it works ok.

For consistency we will create another link:

Login as root and type:

ln -s /usr/lib/libpq.so.3 /usr/lib/libpq.so

PosgreSQL with Zeos

Go to Report Manager connections configuration window, select ZeosLib and click add.

Select protocol PostgreSQL, database as test, hostname as localhost and set user_name and password, you can left the port to default 0.

It does not work but it says correctly the missing library: unable to find libqp.so

Login as root and type:

ln -s /usr/lib/libpq.so.3 /usr/lib/libpq.so

Now it works. I created a report library to test functionallity but before I executed from pgaccess:

CREATE DOMAIN BLOB as bytea

PosgreSQL with DBExpress-OpenODBC

After installing unixODBC to allow odbc connections to the database a script must be executed.

Login as yourloginname and type:

psql -d test -f /usr/share/psqlodbc/odbc.sql

We need to create a ODBC data source.

I executed ODBCConfig (as root) and created a System DSN, to access databases from other services you should add a System DSN instead a User DSN, for example to access from Report Manager web server (that is executed from apache for example).

You must add a driver:

I setup Driver Name as POSGRESQL. Driver as /usr/lib/psqlodbc.so Setup as /usr/lib/unixODBC/libodbcpsqlS.so

Then add a System DSN and assign parameter values to each item, Database, UserName, Password. Save configuration.

I execute qtodbctest to check the connection without success. I think /etc/unixODBC/odbc.ini contains a setup configuration incorrect for this PostgreSQL version, again a configuration nightmare, this was so simple as providing a odbc.ini sample entry in the psqlodbc pacakage, but there is no documentation, oh yes documentation about how to build the driver (make clean..) but none about how to install it.

This driver cannot be tested with Report Manager because it fails with basic ODBC connections tests (qtodbctest). Note that in Suse 8.2 and Suse 7.3 the ODBC driver for PostgreSQL worked without problems.

MySQL

Intallation is done using software package installation in Yast, intall mysql package, then the service is activated using Execution Level Editor also in Yast (select 3 and 5 execution levels).

Install also the client libraries, in this caso mysql-shared package contains the libmysqlclient.so.12 libraries, mysql-client will be useful for installation testing.

Login as yourloginname and type:

mysqlshow

It will show you the database test.

I installed mysqlcc, a usefull client tool. Note that by default you must not enter any password when connecting to mysql, that is to access your database server connecting with mysqlcc set Host Name as localhost, User name as yourloginname and blank password. It will connect and show you databases, I created a test table an populated it.

Zeos and MySQL

Open Report Manager dataacess configuration, select ZeosLib and click add, select a connection name, select the new connection, set Database Protocol to mysql, Host Name to localhost, Database to test, User_Name to yourloginname and important, blank password, Suse 9 default installation is with blank passwords for MySQL.

When you click Test connection, a error is raised: libmysqlclient.so not found. To solve the problem you must provide a symbolink link to the correct version of MySQL client library.

Login as root and type:

ln -s /usr/lib/libmysqlclient.so.12 /usr/lib/libmysqlclient.so

Retry your connection with yourloginname user. Now it works ok.

DBExpress and MySQL

Open Report Manager dataacess configuration, select MySQL and click add, select a connection name, select the new connection, Host Name to localhost, Database to test, User_Name to yourloginname and important, blank password, Suse 9 default installation is with blank passwords for MySQL.

If you test the connection a error is raised: libmysqlclient.so not found.

Login as root and type:

ln -s /usr/lib/libmysqlclient.so.12 /usr/lib/libmysqlclient.so

But still the same error is launched, this is because DBExpress find a diferent version of the driver not supported by the DBExpress driver. You need a previous version of the client library for example libmysqlclient.so.10 search it at google for example and install in your system, to be persistent about the libmysqlclient.so is the latest edit your dbxdrivers file in ~/.borland and set vendor lib to of mysql driver to libmysqlclient.so.10. Now you are using a different client library. This is a bit dangerous because you are using a wrong client library, but should work.

DBExpress OpenODBC and MySQL

Installation of MySQL odbc driver is needed. You must install package MyODBC-unixODBC.

You can read the documentation provided (you can see the files of the package executing rpm -ql MyODBC-unixODBC)

After the installation you can add to /etc/unixODBC/odbcinst.ini file this section:

[myodbcdriver]

Description = MySQL ODBC Driver

Driver = /usr/lib/unixODBC/libmyodbc.so

And add to /etc/unixODBC/odbc.ini this section:

[myodbc]

Driver=myodbcdriver

Description= Test of DSN for MySQL

SERVER=localhost

PORT=

USER=yourloginname

PASSWORD=

Database=test

OPTION=3

Now you can test the connection with qtodbctest (remember, blank password and yourloginname). Full connection must be correct.

Open Report Manager dataacess configuration, select OpenODBC and click add, select a connection name, select the new connection, and set Database to myodbc (the DSN name) the User_name to yourloginname and blank password.

In this case the test connection failed (the driver hangs), so a bug report to OpenODBC driver project should be posted

Firebird

Intallation is done downloading the sofware from http://sourceforge.net/projects/firebird

Login as root and type

rpm -U FirebirdSS-1.5.0.4027-RC7.i686.rpm

Then use Execution Level editor to startup the server and activate at runlevels 3 and 5.

To check installation:

We create a directory for databases, giving privileges only to firebird user:

mkdir /var/data

chown firebird /var/data

cd /opt/firebird/bin

./isql -user SYSDBA -password masterkey

CREATE DATABASE '/var/data/test.fdb';

quit;

Now test connection:

./isql -user SYSDBA -password masterkey localhost:/var/data/test.fdb

Login as yourloginname:

To create some I used IBAccess (http://www.ibaccess.org), after downloading:

tar zxvf ibaccess-1_18.i386.tar.gz

./ibaccess.sh

A symbolink link must be created because this tool uses Interbase Express, this link is also necessary to work with Interbase Express driver in Report Manager.

Login as root and type:

ln -s /usr/lib/libfbclient.so /usr/lib/libgds.so.0

Now you can use ibaccess to create a test table.

Firebird using Interbase Express

You must generate a symbolic link to enable Interbase Express:

Login as root and type:

ln -s /usr/lib/libfbclient.so /usr/lib/libgds.so.0

Now you can create a connection and set the type to IBX. Configure the connection in the same way as a DBExpress Interbase connection.Set database to localhost:/var/data/test.fdb and it's working.

Firebird using DBExpress

You must generate a symbolic link to enable Interbase Express:

Login as root and type:

ln -s /usr/lib/libfbclient.so /usr/lib/libgds.so

Now you can create a connection and set parameters for that connection. Set database to localhost:/var/data/test.fdb and it's working.

Firebird using Zeos

Open Report Manager dataacess configuration, select ZeosLib and click add, select a connection name, select the new connection, set Database Protocol to Firebird-1.5, Host Name to localhost, Database to /var/data/test.fdb, User_Name to SYSDBA and password to masterkey.

When you click Test connection, a error is raised: fbclient.so not found. To solve the problem you must provide a symbolink link to the correct version of Firebird client library.

Login as root and type:

ln -s /usr/lib/libfbclient.so /usr/lib/fbclient.so

Retry your connection, now it works ok.

Oracle and DBExpress (by Stefano Tronu -stefano.tronu@gmail.com)

1) Installing latest version of Oracle Client (10g actually) (for
example on /usr/lib/oracle/10.1.0.3/)
2) Installing ReportManager (for example on /opt/repmand)
3) verify if the Oracle VendorLib name on /root/.borland/dbxdriver is
the same of libclntsh.so* under lib root oracle installation. If is
not the same, create a link to the .so library or rename the VendorLib
name under dbxdriver. (for example, Oracle10g library name is
libclntsh.so.10.1).
4) Copy the TNSNAMES.ORA contents into the databasename connections
configuration, for example
"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
hostname)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = ORATEST) ) )".

On Linux is not a valid environment TNS_ADMIN=TNSNAMES.ORA path, so
is'n need create environment variables such as
ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH and TNS_ADMIN.