Section 19. Using SQL with various database vendors

19. Using SQL with various database vendors

Radiator's SQL modules can be used with any Relational Database that is supported by a database specific DBD module or ODBC DBD module. Some examples are: Oracle, Microsoft SQL Server, PostgreSQL, MySQL, MariaDB, SQLite, Firebird, Sybase and Informix. Many of these databases have both: you can use a database specific DBD module or DBD::ODBC to connect to them.

We have not directly tested every one of these. We have tested some of them, and this section contains some tips about using Radiator with them. We supply some simple schemas with Radiator for a few databases. See the goodies/*Create.sql files in the distribution for more information about them. You will probably want to create a more elaborate schema to handle the tasks you need, and the schemas we supply should be regarded as a starting point only. You should probably consult with a Database Analyst to maximise the performance of your SQL database.

19.1. General

Whenever Perl's DBI module is used to work with a database, you need to supply up to 3 pieces of information in order to specify the database to which you want to connect:

DBSource
This is the data source name. It has the special format: “dbi:drivername:options”, where driver name is the name of the DBI driver to use, and options is an option string whose exact format depends on the DBI driver you are using.
DBUsername
This is usually the SQL user name to use to connect to the SQL database, but for some database types, it has a different meaning.
DBAuth
This is usually the password for DBUsername, but for some database types, it has a different meaning or is not required.

19.2. MySQL and MariaDB

In DBSource, driver name is “mysql”, and options is the database name. DBUsername is the MySQL user name, and DBAuth is the password for the MySQL user.

To create a new database, see goodies/mysqlCreate.sql for more information.

Configure your SQL clause like this:

DBSource    dbi:mysql:radius
DBUsername  radius
DBAuth      password

Tip

To specify server name and port, use:

dbi:mysql[:database[;host=hostname[;port=port]]]

19.3. PostgreSQL

In DBSource, driver name is “Pg”, and options is the database name to use. DBUsername is the PostgreSQL user name, and DBAuth is the password for the PostgreSQL user.

To create a new database, see goodies/postgres-sqliteCreate.sql for more information.

Configure your SQL clause like this:

DBSource    dbi:Pg:dbname=radius
DBUsername  user
DBAuth      password

Tip

To specify server name and port, use:

dbi:Pg:dbname=radius[;host=hostname[;port=port]]

19.4. Oracle

In DBSource, driver name is “Oracle”, and options is the SID of the Oracle database instance you want to use. DBUsername is the Oracle user name, and DBAuth is the password for the Oracle user.

To create a new database, see goodies/oracleCreate.sql for more information.

Configure your SQL clause like this:

DBSource    dbi:Oracle:sid
DBUsername  user
DBAuth      password

Tip

To specify server name and port directly without $ORACLE_HOME/network/admin/tnsnames.ora, use:

dbi:Oracle:service_name=xe;host=10.20.30.40;port=1521

19.5. Microsoft SQL Server

Multiple methods are available to connect to Microsoft SQL Server.

If you run Radiator on Windows, use the ODBC administration tools and add a System DSN (data source name). Microsoft provides ODBC support for Linux and macOS with Microsoft ODBC Driver for SQL Server on Linux and macOS. The driver and installation instructions are available from the Microsoft web. For more about ODBC configuration on all operating systems, see Section 19.8. ODBC

To create a new database, see goodies/sqlserver-sybaseCreate.sql for more information.

Configure your SQL clause like this:

DBSource    dbi:ODBC:radius
DBUsername  radius
DBAuth      password

If you can not use Microsoft ODBC driver, consider DBD::Sybase with FreeTDS. Sybase and MS SQL Server have common history and for this reason DBD::Sybase is known to work with Microsoft SQL Server. You need to compile and link DBD::Sybase with FreeTDS libraries. For more about DBD::Sybase, see Section 19.9. Sybase

Yet another option is to use the ODBC driver that comes with FreeTDS and configure Radiator to use DBD::ODBC. For more about DBD::ODBC, see Section 19.8. ODBC

19.6. SQLite

DBD::SQLite is a lightweight integrated SQL library that keeps its database in a single flat file. It does not use a separate SQL server: all the SQL functions are embedded in the SQLite library. DBD::SQLite is available from CPAN. For more information, see Section 2.1.2. CPAN.

In DBSource, driver name is “SQLite”, and a required option is dbname, which specifies the path name of the single file where the database is to be stored. It can be an absolute file path or a relative path. DBUsername and DBAuth are not required and are ignored.

To create a new database, see goodies/postgres-sqliteCreate.sql for more information.

Configure your SQL clause like this. DBUsername and DBAuth are not required:

DBSource    dbi:SQLite:dbname=/path/to/your/dbfile

19.7. Firebird

In DBSource, driver name is “Firebird”, and a required option is database, which specifies the path name of the file where the database is to be stored. DBUsername is the Firebird user name, and DBAuth is the password for the Firebird user.

To create a new database, see goodies/ansiCreate.sql for more information..

Configure your SQL clause like this:

DBSource    dbi:Firebird:database=/path/to/database.fdb
DBUsername  username
DBAuth      password

Tip

DBSource can also contain other hints and directives for connecting to the Firebird server, for example:

DBSource dbi:Firebird:port=3050;host=10.20.30.40;db=/path/to/database.fdb;

19.8. ODBC

To use ODBC, you must first create the database and tables in a way that depends on the type of database to which you are going to connect. See your vendor's documentation. You need to install and configure your ODBC manager. The way to do this also depends on your ODBC data manager.

For example, on Linux, macOS and other Unix systems, ODBC configuration typically is done with odbcinst.ini and odbc.ini files in /etc, /usr/local/etc, $HOME or other directory that depends on the system. For Windows, use the ODBC administration tools and add a System DSN (data source name).

Configure your SQL clause like this:

DBSource    dbi:ODBC:datasourcename
DBUsername  user
DBAuth      password

19.9. Sybase

In DBSource, driver name is “Sybase”, and the options can be empty. DBUsername is the Sybase user name, and DBAuth is the password for the Sybase user.

To create a new database, see goodies/sqlserver-sybaseCreate.sql for more information.

Configure your SQL clause like this:

DBSource    dbi:Sybase:
DBUsername  user
DBAuth      password

Tip

DBSource can also contain other hints and directives for connecting to the Sybase server, for example:

DBSource dbi:Sybase:server=SERVERNAME;database=DBNAME

You may need to set SYBASE environment variable so that the Sybase libraries can find their interfaces library. Otherwise you may need to use directives. You may also need to set LD_LIBRARY_PATH, or similar, to $SYBASE/lib.

19.10. InterBase

In DBSource, driver name is “InterBase”, and a required option is database, which specifies the path name of the file where the database is to be stored. DBUsername is the InterBase user name, and DBAuth is the password for the InterBase user.

To create a new database, see goodies/ansiCreate.sql for more information.

Be careful to review the InterBase specific notes in goodies/ansiCreate.sql. Some SQL clauses require a couple of little tweaks. Note that, for example, the name of the password column is PASS_WORD. PASSWORD is a reserved word in InterBase.

Configure your SQL clause like this:

DBSource    dbi:InterBase:database=/path/to/your/database.gdb
DBUsername  username
DBAuth      password
AuthSelect  select PASS_WORD from SUBSCRIBERS where USERNAME=%0

19.11. Informix

In DBSource, driver name is “Informix”. DBUsername is the Informix user name, and DBAuth is the password for the Informix user.

The Perl DBD::Informix module is very mature and was developed in conjunction with Informix development staff. Building DBD::Informix requires that you have a working Informix installation and DBA access to a test database. We recommend that you carefully follow the procedures in the DBD::Informix README file, then build, test and install DBD::Informix before attempting to create and configure a Radiator database on Informix.

The create a new database, see goodies/ansiCreate.sql for more information.

You must ensure you have your INFORMIXDIR and INFORMIXSERVER environment variable set up specifying an operating Informix server. Also check that your $INFORMIXDIR/etc/sqlhosts file has correct information.

Configure your SQL clause like this:

DBSource    dbi:Informix:radius
DBUsername  user
DBAuth      password

19.12. CSV

DBD::CSV is a Perl database driver that uses flat text files as the database. The default supports comma separated files, but that can be customised in many ways, to support for example Unix password format and Excel spreadsheet formats. DBD::CSV is available from CPAN. For more information, see Section 2.1.2. CPAN.

In DBSource, driver name is "CSV", and a required option is f_dir, which specifies the directory where the database files exist. DBUsername and DBAuth are not required and are ignored.

DBD::CSV databases are flat text files. The file name is the table name, for example, subscribers for the default AuthSelect. The first line of a database file contains the names of the columns in the file. To create a new database file, you need to create the text file with an editor. A simple database file for subscribers that will work the default AuthSelect in AuthBy SQL would be named subscribers and it would look like this:

USERNAME,PASSWORD
mikem,fred
jim,jim
......
<more lines, one per user>
......

For DBD::CSV on Unix, configure your SQL clause like this. DBUsername and DBAuth are not required. f_dir specifies the directory where the database files are located. csv_eol in this example specifies that the line separators are Unix newlines. You must have this for a Unix style text file. You can leave it off to get Windows standard text files.

DBSource    dbi:CSV:f_dir=/your/data/dir;csv_eol=\012

19.13. DB2 and other database servers

IBM provides ODBC driver for DB2 which works with Linux and macOS. Follow the installation instructions from IBM and then configure Radiator to use ODBC to access DB2. For more information, see Section 19.8. ODBC. There is also DBD::DB2 in CPAN which works directly without ODBC.

Other databases should work with Radiator provided that there is a native DBD driver or ODBC driver available for them.