DB Engine

Select the desired database engine to connect. QLR Manager will only display the database engines that have the necessary PHP connection extensions already installed.

User ID

Enter a User ID that has already been established in the database to be accessed.

Password

This is the Password for the database User ID entered. If the User ID does not have a password assigned, the password can be entered as [none].

Database

Some database configurations require that a database name be provided at connect time. If the Database input field is present on the  Connect  panel, then a valid entry is required.

The Systems Administrator can manually edit the qlr.ini file to change the value of "DBlogin", such as DBlogin = inventory, to preset the database that is selected at log on.

DB server

The DB server is the server where the database resides in relation to QLR Manager. For MySQL, it is generally "localhost", meaning that the database resides on the same server as QLR Manager.

In MySQL or PostgreSQL, it might be necessary to specify a port if the database's default port is not used. This is accomplished by adding the port to the end of the server reference, such as localhost:3305.

For Oracle database connections, this is the TNS alias that is created in Oracle's TNSnames.ora file which is used to reference the desired database and server.

When connecting to an SQLite3 database file, the User specifies the desired path and file name. The path can be absolute or relative. A button to launch a pop-up File Browser will appear when the User clicks inside the SQLite file input field. A larger field to edit the path and file name will also appear, allowing for easier editing. Clicking the  Apply  button will insert the new value.

If the submitted file name is not found, QLR Manager will prompt the User to ask if they wish to create a new database file using the submitted value. Responding with      OK      will submit the request and a new empty SQLite3 database will be created.

A selection list of available servers can be added by the Systems Administrator. This is accomplished by logging into QLR Manager using the master QLR Manager ID and creating an Input Control called "qlr_server_list". This feature is available in the Enterprise Edition only. The set of server choices can be entered as a List of values, using the text :: value format, such as:

local::localhost,
china::9.9.14.201,
denmark::23.45.98.2

This will trigger a select list to appear below the DB server input field. The User can then choose an entry from the list, or type a new entry into the DB server field.

Server list: 

The qlr_host table

A more advanced method for managing connections, especially for ODBC connection strings, is to utilize the qlr_host table. When data is present in this table, it overrides the use of "qlr_server_list" input control (see above).

The qlr_host table contains three fields:
alias_text  This contains the values that will be displayed to the User to select
db_engine  This contains the database engines that may appear in the DB Engine field of the connect panel. Valid values are DB2, MySQL, ODBC, Oracle, PostgreSQL and SQL Server.
host_string  This is the actual value that will be used for the connection.
Here are some examples of entries for this table:
alias_textdb_enginehost_string
games.xlsODBC Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\picks_2011\games_only2.xls;readonly=0
SQLite \firstdbODBC Driver=SQLite3 ODBC Driver;Database=C:\sqlite3\firstdb.sl3
This serverMySQLlocalhost
MySQL po_dataODBC DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=po_data;Option=3

Notice that in the case of ODBC connections, "ODBC" is used as the db_engine, not the underlying database engine.

QLR Manager automatically creates a form called "QLR edit qlr_host" which can be used to add, edit and delete entries in this table. The form is owned by your QLR Master ID and can be accessed from the  User Menu  option, then selecting the *My_Menu* menu. It will appear in the "Forms" section of *My_Menu*.

Function

This selection determines what page set of functions the User wishes to access.

Queries · Layouts · Reports allows for the creation of queries and layouts, and viewing, e-mailing, or downloading the output. It also provides a menu listing all the macros and queries previously saved by the User ID, and those that have been built into a menu and shared by other users. The Start-up preference selection will direct the logon to the desired panel and save this preference as a cookie on the local PC.

Macros · Menus · Forms allows for the creation of macros, menus and forms, and provides the capability of previewing the output.

User ID Admin and Tools provides a set of panels to create input controls, export QLR data for import into another QLR instance, load data from various source file types into a database table, and set authorities for User IDs.

Note: What the User ID actually sees after logon to any of the above functions is determined by the authority granted the ID through User ID Admin. For example, a Restricted User will not see the header tabs for  Query  Query and  Layout  after logon, only  User Menu  and  Report . If a Restricted User attempts to select the option for Macros · Menus · Forms, they will receive a message that their User ID is not authorized. An Advanced User could have access to both page sets and all functions. These authorities are normally set by the Administrator logging on with the master ID and password established when QLR Manager was initially installed, but can also be delegated to other IDs through User ID Admin.

Log off

Clicking the  Log off  button takes the User back to the  Connect  panel and erases all reports from the User's current session. The User must log on again in order the gain access to QLR Manager.

ODBC connections

Overview

Short for Open DataBase Connectivity, ODBC is a standard database access method developed by the SQL Access group in 1992. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS. This provides a great deal of flexibility to the number of data sources that QLR Manager can connect to, but it also increases the complexity of establishing the connection. QLR Manager has been tested and can connect to these data sources through ODBC: MySQL, Oracle, PostgreSQL, MS SQL Server, MS Access, .xls and xlsx spreadsheets, Firebird, and SQLite3. Each source has it's own peculiarities. Please contact the QLR Manger team if you wish to add an additional source.

Note: For those database engines that can connect directly to QLR Manager using their specific PHP extension (MySQL, PostgreSQL, Oracle and SQL Server), it is better to use the specific data base connection. This is accomplished by selecting the appropriate DB Engine on the  Connect  panel, instead of selecting ODBC.

QLR Manager attempts to determine the underlying data source when connecting using ODBC. When you save objects, such as queries, etc., QLR Manager will store these objects associated to the underlying data source, such as Firebird or SQLite3.

There are two main application components that need to be in place for QLR Manager to utilize an ODBC connection:
 •  

The first is that the ODBC PHP extension has to be running. Generally, in newer versions of PHP, the ODBC extension is installed by default. You can verify that it is present and working by issuing the phpinfo.php command/URL in your browser, such as www.mysite.com/qlr/phpinfo.php. The phpinfo.php file is located in the same directory as qlrmanager.html. When executed, it will display information about your PHP environment. Scroll down (the various extensions are listed in alphabetical order) and look for the ODBC section. If present, your PHP environment is set up to work with ODBC.

 •   The second component, which is the additional component for ODBC, is that a "driver" has to be installed in your operating environment to interact with your data source. Each database engine, such as Firebird, will have it's own driver. Microsoft provides a single package, such as "Microsoft Access Database Engine 2010 Redistributable" that will connect to multiple Microsoft products. A search of the Web will result in you finding the proper drivers for your operating system environment.

DSN and DSN-less connections

Once your operating environment is set up for ODBC, you have to tell QLR Manager the Data Source Name (DSN) to which you want to connect. When you select "ODBC" as your DB Engine, QLR Manager will require that you to enter the "DSN". QLR Manger supports two types of DSNs:
 •  

The first is a Systems DSN. These can be defined in Windows operating systems within the Control Panel's Administrative tools: Data Sources (ODBC). This function allows you to map the underlying connection values to a data source and create a nickname for that connection. For Systems DSNs, you can enter the nickname for that data source.

There are limitations using a Systems DSN. A Systems DSN defines the connection down to the database level and QLR Manager cannot see the database value, nor is it able to change it, as the definition resides outside of the confines of QLR Manager. For example, when setting up a Systems DSN for SQL Server, you define the database that will be part of the connection. But once inside QLR Manager, you will not be able to change databases. This is only an issue in database engines that allow access to more than one database. For connections to MS Access, Excel spreadsheets, Firebird, SQLite3, or any other singular database source, this is not a problem. It is also not an issue with schema oriented database engines such as Oracle or IBM DB2.

 •  

The second method is called a DSN-less connection. It is really the same as the Systems DSN, except you are not creating a "nickname" for the connection. Instead, you are using a "connection string" to define how to connect to your data source. The advantage to this method is that QLR Manager can see and utilize the connection variables found in the connection string. The connection strings can get rather lengthy, but adding values to the qlr_host table allows you to create your own DSN nicknames within QLR Manager. Here are some examples of connection strings.

Note: The driver referenced in the connection string must be installed in your server's operating environment.

 
Data source  Connection string
Firebird  DRIVER=Firebird/InterBase(r) driver;DBNAME=C:\firebird\my_db.fdb;
MS Access  Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\MS_access_db\Nwind.mdb;Exclusive=1;
MS .xls file  Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\inventory\purchases.xls;readonly=0
(Notice the readonly=0 at the end. XLS connections are readonly unless specified as FALSE.
MS .xlsx file  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=D:\user_data.xlsx
(When accessing Excel spreadsheets, the "Named Ranges" within the workbook act as the table names in QLR Manager. The top row in the range will be used as the Table Column names. The data ranges should not contain any blank rows of data. An empty row may cause QLR Manager to calculate that it has reached the end of the data.)
PostgreSQL  Driver={PostgreSQL};DATABASE=data1;SERVER=localhost;PORT=5432;
Oracle  Driver={Microsoft ODBC for Oracle}; Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=your host name)(PORT=1522)) (CONNECT_DATA=(SID=XE)));
SQLite3  Driver=SQLite3 ODBC Driver;Database=C:\sqlite3\my_db.sl3
IBM DB2  driver={IBM DB2 ODBC DRIVER}; Database=qlr;hostname=localhost; port=50000;protocol=TCPIP;
MySQL  DRIVER={MySQL ODBC 5.1 Driver}; Server=localhost;Database=po_data;Option=3
 

Notice how none of the above examples includes the user ID and Password information. These are entered separately in the Connect panel fields. Searching the web is a good way of finding the proper ODBC connection string for your data source.