Configuring a Database Connection

Before you can sign-on to a database, you must configure an ODBC Datasource for that database.

The way you configure an ODBC Datasource is different for each type of database. In the following sections I describe how this is done for each database type. This section gives general comments about how this is done.

As a general rule, the following steps need to be done in order to access a database:

Install Database Client Software and/or ODBC Driver.

In most cases, before you can connect to your database you will need both:

• a Client for your database. The client manages the communication between your PC and the Database server.

• an ODBC Driver for your database. This provides the translation between the ODBC calls used by AQT and the native interface to your database.

In many cases, the client and ODBC Driver are packaged together (eg. when you install the client, you will also get the ODBC driver).

Client software is not required for MS Access, MS SQL Server, text, Excel and dBase databases, as drivers for these come as a part of the standard Windows install.

Configure the Client

You will need to configure the database client in order for it to connect to your database. For some databases, configuring the database client is done as part of the ODBC Datasource Setup, for other databases this is a separate step.

• for Oracle, see Installing and Configuring the Oracle Instant Client

• for DB2, see Configuring the DB2 Client

In order for your client to be able to connect with the server, it will need to know the following information:

• the hostname (or IP address) of the machine where the database resides. If the database resides on your own machine, specify localhost

• the port your database server is "listening" on. In general you should accept the default given by the configuration dialog.

• you may also be required to specify miscellaneous other parameters relating to the way the ODBC driver interacts with the database.

We cannot help you with this information - you will need to see your DBA for this information.

Setting up a new Datasource

This can be done in two ways:

• from the AQT Signon window, click on Add new Datasource

• using the ODBC Data Source Administrator

Defining a new Datasource with AQT

To create a new Datasource, from the AQT Signon window, click Add new Datasource

You will be shown a dialog where you can specify the definition of the connection.

Defining a new Datasource with the ODBC Data Source Administrator

You can define a Datasource with the Microsoft ODBC Datasource Administrator.

This is started in two ways:

• from the AQT Signon window, click on ODBC Manager

• from the Windows start menu, search on ODBC and select Data Sources (ODBC).

  On Windows 10 you will see 2 items ODBC Datasources (32-bit) and ODBC Datasources (64-bit).

• for 64-bit editions oif Windows, see 32-bit versus 64-bit Datasources

From the Datasource Adminstrator:

• click on Add...

• select the ODBC Driver for your Database

• you will be shown the database-specific dialog for defining the connection

At this point you will be taken into the dialog for specifying the information for the connection.

32-bit versus 64-bit Datasources

If you are running a 32-bit edition of Windows, life is simple!

If you are running a 64-bit edition of Windows there are a number of issues you need to be aware of.

On 64-bit systems, ODBC Drivers will be either a 32-bit driver, or a 64-bit driver.

• Datasources using a 32-bit Driver will only work with 32-bit AQT

• Datasources using a 64-bit Driver will only work with 64-bit AQT

To manage these, Microsoft supply two ODBC Administrator programs:

• the 32-bit ODBC Datasource Administrator is in c:\Windows\SysWoW64\odbcad32.exe

• the 64-bit ODBC Datasource Administrator is in c:\Windows\system32\odbcad32.exe

When you click on ODBC Manager from the AQT Signon window, you will get the either the 32-bit or 64-bit ODBC Administrator, depending on the architecture of the AQT you are running.

If you are starting the Datasource Administrator from the Windows start bar you need to make sure you get the correct one! If you are running Windows 7 you will only get the 64-bit program. To add or manage 32-bit datasources you will need to manually run c:\Windows\SysWoW64\odbcad32.exe

An additional complication..

Both AQT and the ODBC Datasource Administator will show you:

• For System Datasources - only those which are appropriate for the architecture of the program you are running

• For User Datasources - all datasources irrespective of whether they are 32-bit or 64-bit

So... you will can be shown datasources which can't be used. If you try to use one of these you will get:

Data source name not found and no default driver specified

or

The specified DSN contains an architectural mismatch between the Driver and Application.

Connecting to DB2

Prerequisite Software

In order to connect to a DB2 Server, you will need the DB2 Client on your PC. There are a number of different types of DB2 Client - Runtime Client, Administration Client etc. Any of these Clients will work. The DB2 Client includes the DB2 ODBC Driver. The DB2 Client will be on the DB2 Install CD (your DBA will have this) or can be downloaded from the IBM site.

DB2 z/OS and DB2 iSeries

If your database is a DB2 z/OS or DB2 iSeries database, you will also need DB2/Connect. This provides connectivity to mainframes and will perform EBCDIC-ASCII conversion (if this is required). DB2/Connect is a purchaseable product.

You have two choices of DB2/Connect products:

• DB2/Connect Personal Edition. This is installed on your PC and provides a direct connection from your PC to the DB2 z/OS or DB2 iSeries system.

• DB2/Connect Enterprise Edition. This is a server version of DB2/Connect. It will be installed on a server in your Lan. You configure a connection to your DB2 z/OS or DB2 iSeries system by going via the DB2/Connect Server.

DB2 iSeries

You can also connect to a DB2 iSeries database using Client Access Express. If you use this, you do not need either the DB2 Client or DB2 Connect.

Configuring the DB2 Client

This is done using the Configuration Assistant or the DB2 Command Line Processor. This is described in Configuring the DB2 Client.

Binding the Packages

If your DB2 client is a different version or release level than your DB2 server, you may need to bind the DB2 Client packages. If this is not done, AQT may hang when you try to sign on, or give you messages such as package NULLID.SQLLF000 not found.

To bind the packages from the Configuration Assistant, select the database then click on Selected > Bind. In the next window, select <CLI/ODBC Support> then click on Bind.

Other notes on Binding:

• only one person needs to bind the packages. All other users with the same release level of the DB2 client will not need to do the bind.

• in order to do the bind, you need a reasonably level of authority on the database server.

• the bind needs to be done against every DB2 database you access.

• if your DB2 server or DB2 client is upgraded to a new version (or fixpack applied) you will need to repeat the binding.

AQT Options

When using AQT against a DB2 database, we recommend using the following AQT options.

• Options > Technical Parameters > Uncommitted Read. This prevents queries running under AQT from tasking any locks on your tables.

• Options > Cancel Queries > Use the Async Capabilities of the ODBC Driver. This allows you to cancel an inflight query

Configuring the DB2 Client

Before you can define an ODBC Datasource, you must first configure the database connections to the DB2 Client.

This can be done with:

• the DB2 Configuration assistant (if you have this installed on your PC)

• alternatively you can use DB2 CLI commands. These are given below.

Copying Database Definitions between machines

The Configuration Assistant gives you the ability to Export and Import database connection definitions.

If you do not have the Configuration Assistant, you can do this with commands db2cfexp and db2cfimp.

Defining Database Connections with DB2 CLI commands

To run these commands you need to start the DB2 Command-Line Processor (CLP).

You do this by running the following command in Windows

db2cmd

Defining a Node

The first step is to define a Node (which is a DB2 server). This is done with a command such as the following:

db2 catalog tcpip node DB2SERV1 remote SERVER1 server 50000

where:

• DB2SERV1 is the name you wish to give this node. This can be any name

• SERVER1 is the Hostname or IP address of the DB2 Server

• 50000 is the TCP/IP Port the DB2 Server is listening on

You may need to contact your Database Administrators to find out the values for Hostname and TCP/IP Port.

You will get the following response:

DB20000I The CATALOG TCPIP NODE command completed successfully.

DB21056W Directory changes may not be effective until the directory cache is refreshed.

The above response is normal.

Checking that the node has been defined successfully

You can check that the Node has been defined with:

db2 list node directory

This won't tell you whether it is correct or not! A better way is to "attach" to the node. This is done with:

db2 attach to DB2SERV1

If this succeeds, or you get an authentication failure, then your definition is correct.

If, instead, you get an error such as:

SQL1336N The remote host "SERVER1" was not found. SQLSTATE=08001

then this means that either:

• Hostname or Port are incorrect

• a firewall (or other network problem) is preventing you from communicating with the DB2 server

• the DB2 system is not running

Defining a Database

Once you have defined a Node, you can define the database. The most basic form of this command is:

db2 catalog database CUSTDB at node DB2SERV1

where:

• CUSTDB is the name of the database on the DB2 Server

• DB2SERV1 is the Node. This is the name you used

Another example:

db2 catalog database CUSTDB as CUSTDEV at node DB2SERV1 authentication client

In this:

• use as CUSTDEV to give your own name for the database. This is often called the database alias.

• the authentication clause is used to specify how your userid/password will be authenticated, This is an important parameter. You may wish to consult with your support group for advice on what to specify for this.

You can check that the database has been defined successfully with:

db2 list database directory

Alternatively you can see whether you can connect to the database with either:

db2 connect to CUSTDEV

or

db2 connect to CUSTDEV using userid

(you will be prompted for the password)

Creating an ODBC Datasource

The DB2 CLP allows you to create an ODBC Datasource. This saves the need for having to do this using AQT or the Datasource Administrator.

The command for this is:

db2 catalog odbc datasource CUSTDEV

where CUSTDEV is the name of yor database alias.

Connecting to Informix

Prerequisite software

In order to connect to an Informix database you need to install an Informix Client, such as IBM Informix Connect. Ensure that you select the ODBC Driver during the install of the client.

After installing the Client, you should reboot your PC.

Configuring the Informix ODBC Driver

In order to connect to your Informix server using AQT, you will need to configure an ODBC Datasource for your database. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Datasource Name. The name you give this ODBC datasource.

• Description. Not used. Leave blank.

• Server Name. The name of your Informix server. You should get this name from your DBA or person who created the Informix server. By default, the name will be ol_machinename (example: if your machine is called server1, the default name for the informix server will be ol_server1).

  If you specify this incorrectly, you will get the following message when you try to connect: "INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIAS".

• Host Name. The hostname or IP Address of the machine on which the Informix Server resides. Specify localhost if the Informix server resides on your PC.

• Service. The name of the TCP/IP port your Informix Server is listening on. The default port for Informix is 1526; if in doubt check with your DBA.

• Protocol. Use onsoctcp

• Database Name. This specifies the database you will be connected to once you sign on to the Informix server. This is mandatory. You can click the drop-down list to get the valid choices - make sure the other attributes (including user-id and password) are filled out before you click this).

  If you do not know the name of a database, use sysmaster.

Locales

These are specified on the Environment tab. Both Client Locale and Database Locale must be specified correctly. If not, you will get the mesage "Unable to load translation dll".

• The default values, en_US.CP1252 for both Client and Database Locales, should work.

• Alternatively, a client locale of en_US.8859-1 and a Database Locale of en_US.819 should also work.

• Make sure you reboot your PC after installing the client, otherwise you will get "Unable to load translation dll" no matter what you specify.

• If you are using Unicode in your database, specify en_US.UTF8 for both of these.

You can find the locales of your databases by the following query (if you have a connection!)

Select * from sysmaster:informix.sysdbslocale

Test Connect

After configuring your datasource, click on Apply & Test Connection (on the Connection tab) to check that the connection has been set up correctly.

Connecting to MS Access

Prerequisite Software

No software is needed in order to access a MS Access database (mdb file). The necessary ODBC Driver is present in the standard Windows install.

If not, you can download the MS Access ODBC Driver from https://www.microsoft.com/en-us/download/details.aspx?id=54920

Furthermore, you do not need Microsoft Access in order to access an MS Access database with AQT. See Database-specific notes - MS Access for more on this.

Signing onto an MS Access Database

You have three methods of signing accessing an MS Access database:

• setting up an ODBC Datasource for the database

• setting up a Generic Datasource

• open the mdb file with AQT

The first method is useful if you are accessing a particular database on a frequent basis.

Setting up an ODBC Datasource for your Access Database

The general procedure for this is descibed in Configuring a Database Connection.

• select a Driver of Microsoft Access Driver

• you select your mdb file by clicking on the Select button (in the Database frame).

Once you have done this, the ODBC Datasource name for your Access database will appear in the list of databases in the AQT signon window.

Setting up and using a Generic Datasource

This sets up a datasource which you can use for accessing any mdb file.

To set this up, follow the steps in the previous section, however do not select a mdb file (leave this blank). It is recommended that you give this Datasource a name such as Access Databases.

To use this generic datsource:

• from the AQT signon window, sign onto the Access Databases datasource.

• you will be prompted with a signon dialog. Click on the Database button to select the mdb file.

Open the mdb file with AQT

To do this, right-click the mdb file, select Open With > Choose Program. Click on Browse to select AQT. You can check Always use the selected program to open this kind of file if you want to always open mdb files with AQT.

Opening an Accdb file

MS Access 2007 and later will (by default) create an accdb file, rather than an mdb file.

The standard MS Access ODBC Driver installed on your PC may not be able to open a file of this type, but only mdb files.

The solution is to install a more recent version of the MS Access ODBC Driver. This can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=54920

Other Notes

• with MS Access you have the option of opening the database in Read Only mode. When in read only mode, the contents of the database cannot be changed. This option is a useful to prevent accidental changing of the data.

• if you have a table opened in design mode in Microsoft Access, you will not be able to view the table with AQT (Microsoft Access will have an exclusive lock on the table).

Connecting to MS SQL Server

Prerequisite Software

No software is needed in order to access a SQL Server server. The necessary ODBC Driver is present in the standard Windows install.

Configuring the Connection to SQL Server

The general process for configuring an ODBC datasource is described in Configuring a Database Connection.

To configure a SQL Server datasource, specify:

• Name. The name you give this ODBC datasource.

• Description. Not used. Leave blank.

• Server. The hostname or IP address of the machine on which the SQL Server server resides. Specify localhost or (local) if the server is on your own PC.

• Authentication. This window allows you specify whether you connect to SQL Server specifying a user-id/password, or for SQL Server to automatically use your LAN identity. The latter option (Windows NT authentication) appears only to work if your PC is part of a Domain. If you are using a Workgroup, this option will not work.

• Client Settings. In most cases you will not have to change these. If your SQL Server server is using a port other than the default (1433) you will need to change it here.

• Change the default database to. Use this option if you wish to specify the database to connect to. If not specified, you will be connected to the database which is defined as your default database.

• Other options. The remainder of the options should not be changed from the default values

It is recommended that you click on Test Data Source to test that your connection has been configured correctly.

Connecting to MySQL

Prerequisite Software

In order to connect to a MySQL server you will need the MySQL Client and ODBC Driver installed on your machine. These components are packaged together as the MySQL Connector (they had previously been called MyODBC). MySQL Connector can be downloaded from the MySQL site www.mysql.org/

There are a number of third-party ODBC Drivers, such as Devart (https://www.devart.com/odbc/mysql/). We haven't used this ODBC Driver so cannot comment on its capabilities.

Configuring the Connection to your MySQL Server

Once MySQL Connector has been installed, you need to configure an ODBC Datasource for your database. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Data Source Name. The name you give this ODBC datasource.

• Description. Not used. Leave blank.

• Host/Server Name. The hostname or IP address of the machine on which the MySQL server resides. Specify localhost if the server is on your own PC.

• Database Name. The MySQL database (on the MySQL server) you wish to connect to. If this is left blank, you will connect to database mysql. If you do not have access to this database, your connection will fail.

• User/Password. Not used. Leave blank.

• Port. The TCP/IP port the MySQL server is listening on. In most cases the default (3306) is the correct value. If in doubt, contact the person who has configured the MySQL server.

• SQL command on connect. Not used. Leave blank.

It is recommended that you click on Test Data Source to test that your connection has been configured correctly.

Connecting to Oracle

These notes were written for Oracle Version 9 and Version 10g. The names of the Oracle products, and how they are packaged, can be different for different releases of Oracle.

Prerequsite Software

In order to connect to an Oracle database, you need to install the Oracle Client or Instant Client on your PC.

In addition to this, you need an Oracle ODBC Driver. You will have a choice of:

• the Microsoft-supplied ODBC Driver for Oracle. This comes as part of the standard Windows install.

• the Oracle-supplied ODBC Driver. This comes as an optional part of the Oracle Client install. When installing the Oracle Client you need to do a Custom install; the ODBC Driver will be part of the Oracle Programmer component.

• there are a number of third-party ODBC Drivers, such as Devart (https://www.devart.com/odbc/oracle/). We haven't used this ODBC Driver so cannot comment on its capabilities.

While AQT works with both ODBC Drivers, we strongly recommend that you use the Oracle ODBC Driver. The Microsoft ODBC is limited in functionality. Principally:

• Large objects (BLOBs, CLOBs) are not supported

• Unicode data cannot be displayed

• A query using the WITH clause will not display any data

Oracle Net Configuration

To configure a connection between your PC and an Oracle server you need to run the Oracle Net Configuration. Once you start this, select Local Net Service Name configuration then Add. You will be asked the following information:

• Service Name. This is the global database name for your database. Your DBA will know what this is.

• Communication Protocol. Use TCP.

• Host Name. Enter the IP address or host name of the machine on which the Oracle server resides. Use localhost if the database resides on your own machine.

• Port Number. The default port (1521) will generally be the correct value. However it is possible that your DBA has configured Oracle to use a different port; check with him/her.

• Net Service Name. This is the name you give for this connection. A good standard is to give it the same name as the Service Name.

Once you have configured this connection, you will be able to access your Oracle database using SQL*PLUS.

ODBC Driver Configuration

In order to access your Oracle database using AQT, you will need to configure an ODBC Datasource for your database. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Data Source Name. The name you give this ODBC datasource.

• Description. Not used. Leave blank.

• User Name. Not used. Leave blank.

• Server (Microsoft ODBC Driver). The Net Service Name as configured in Oracle Net Configuration. This is the name you use when you sign onto the database using SQL*PLUS.

• TNS Service Name (Oracle ODBC Driver). As with Server above.

With the Oracle ODBC Driver, it is a good idea to click on Test Connection to check that the connection has been set up correctly.

In addition (for the Oracle ODBC Driver)

• on the Oracle tab, ensure that Enable LOBs is checked. If this is not checked, you will be unable to insert LOB columns successfully.

Installing the Oracle Instant Client

To install this, you need to download two packages:

• Client Package - Basic

• Client Package - ODBC

We found that the "Client Package - Basic Lite" did not work for us.

Follow the instructions to install the products, and to install the ODBC Driver. You will also need to:

• change your PATH environment variable to include the directory containing SQORA32.DLL

• set up a tnsnames.ora file. This can be placed either in the same directory as the Instant Client or some other directory. The Instant Client provides no means to configure the tsnames.ora file (which contains the Oracle network names). The easiest way to create tnsnames.ora is to set it up on a machine that has the full client then copy it across.

• set environment variable TNS_ADMIN to the directory containing tnsnames.ora

When configuring the ODBC Driver (as per the ODBC Driver Configration discussion above), you may get error The setup routines cannot be loaded due to system error code 126. The cause of this appears to be due to a couple of dll files missing from the install. These are:

• mfc71.dll

• msvcr71.dll

You will need to get these dlls from a machine which has the full client installed on it (they will be in c:\Windows\System32). Copy them either to your c:\Windows\System32 or your Instant Client directory.

Diagnosing Connection Problems

See the section Oracle - Diagnosing Connection Problems

Installing and Configuring the Oracle Instant Client

Oracle supply several different database clients; the most popular is the Oracle Instant Client.

This information is current as of September 2017 - parts of it may be no longer valid for more recent versions of the client.

Download the Oracle Instant Client and ODBC Driver

This can be done from the Oracle site using the following links. You will need to have a signon to Oracle in order to do this.

For the 32-bit Client / ODBC Driver use:

https://www.oracle.com/technetwork/topics/winsoft-085727.html

For the 64-bit Client / ODBC Driver use:

https://www.oracle.com/technetwork/topics/winx64soft-089540.html

32-bit versus 64-bit

You have the choice of using either the 32-bit client or 64-bit client. Both will work. Which one you choose will depend on whether you are wishing to run the 32-bit AQT or the 64-bit AQT.

It is possible to install both the 32-bit and 64-bit Clients / ODBC Drivers. You should place these in different directories.

Which Version?

The above links have a number of different versions of the Oracle Client.

In general we do not recommend using a version of the client which is higher than the version of the database server. We have found that this can cause problems. For instance, if you are running Oracle v11g we recommend you use v11 of the Client.

Download Packages

You will need to download two packages:

• the Basic files

• the ODBC files

These are shown on the image below.

You may also wish to download the SQL*Plus file if you are planning on running SQL*PLUS

Unzip the Oracle Packages into a directory

Having downloaded the Oracle packages:

• create a directory on your PC for your Oracle Client. Example d:\Oracle_Client

• unzip both files into this directory

• if you also downloaded the SQL*PLUS package, unzip this into this directory also

Note that the ODBC Driver is SQORA32.DLL in this directory.

You do not run any "install" process as a setup.exe. The install is done by placing the Oracle client files in a directory and following the other steps in this process.

Install the Oracle ODBC Driver

To install the Oracle ODBC Driver:double-click odbc_install.exe in your Oracle Client folder (eg. d:\Oracle_Client)

• you may be prompted to authorize this to run.

• apart from this you will receive no response that this has run. The screen may flicker briefly.

Note that this will not actually install any software - what it has done it to register the Oracle ODBC Driver (SQORA32.DLL) to the Microsoft ODBC Driver Manager.

Check that the ODBC Driver has installed correctly

To see whether this has installed, go to the AQT Signon window and click on Add new Datasource. If the ODBC Driver has installed correctly, you will see it in the list. .

You cannot create a datasource yet until you have done some other steps.

Install the Windows Visual Studio Redistributable

In order to run, the ODBC Driver requires a number of Windows system files. To obtain these you must install the Microsoft Visual Studio Redistributable.

This can be dowloaded from:

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads#bookmark-vs2013

Oracle recommend that the Visual Studio 2013 Redistributable be installed, however most versions of the redistributable will be OK.

On our system we have installed the Microsoft Visual C++ 2005 SP1 Redistributable, and this works OK. This can be downloaded from:

https://www.microsoft.com/en-us/download/details.aspx?id=5638

Set Environment Variables

A couple of environment variables need to be set for the Instant Client.

To set environment variables, on Windows 7/10, do a search on Environment Variables and select Edit the system environment variables

The will show you the System Properties dialog. Click on Environment Variables

On the Environment Variables dialog:

• amend the PATH environment variable to include the Oracle Client directory (eg. d:\Oracle_Client)

• set the TNS_ADMIN environment variable to point to the Oracle Client directory (eg. d:\Oracle_Client)

Set up the tnsnames.ora file

Set up the tnsnames.ora file

The tnsnames.ora file contains information needed to connect to the Oracle servers.

• the TNS_ADMIN environment variable points to the location of the tnsnames.ora file. It is recommended that this is held in the same directory as the Oracle Client / ODBC Driver (d:\Oracle_Client in our example).

• you may need the assistance of an Oracle specialist to help with the setup of your tnsnames.ora file

• when an Oracle server is configured, a tnsnames.ora file is created in the Oracle directory structure. You may wish to locate this file and use this.

  For our system, the location for this is: d:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

Example of a tnsnames.ora file

The following shows the contents of a tnsnames.ora file. The first entry defines the connection information to database XE. There can be multiple entries such as these if you wish to connect to multiple databases.

XE =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORASERV)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

)

Configure the Oracle ODBC Driver

Having completed all the previous steps, you can now create an ODBC Datasource for your database.

This is done from either:

• the AQT Signon window > Add new Datasource

• the AQT Signon window > ODBC Manager > Add

• Windows search on ODBC > ODBC Datasources > Add

In all cases you will be shown the Oracle ODBC Driver Configuration dialog.

You only need to specify 2 things:

• Data Source Name. This is your name for the datasource. This can be any unique name

• TNS Service Name. You will have a dropdown list of the "Services" which have been defined in the tnsnames.ora file. Select the database you wish to connect to.

  If there are no entries in the dropdown list it means that the Oracle Client could not find your tnsnames.ora file. The most likely cause is that the TNS_ADMIN environment variable has not been set correctly.

Problems?

You may get a crash such as System Error code 14001. If this happens, the cause if that you have not installed the Visual Studio Redistributable.

Connecting to SAP/Sybase ASE

These notes were written for SAP ASE v16.0, and the SAP ASE ODBC Driver SYBDRVODB.DLL.

SAP ASE was previously known as Sybase ASE.

Prerequiste Software

In order to connect to a SAP ASE database, you need to install the SAP ASE Client. When you install this, make sure the ODBC Driver is selected as part of the install.

Configuring the SAP ASE ODBC Driver

In order to access your SAP ASAE server using AQT, you will need to configure an ODBC Datasource for your server. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Datasource Name. The name you give this ODBC datasource.

• Description. Not used. Leave blank.

• Server Name. Specify the network name of the server on which the SAP ASE database resides. Use localhost is it the same machine as your client

• Server Port: The port number the database is listening on. You may need to consult your DBA for this info. The default port for SAP ASE is 5000 but your server may have been configured to use a different one.

• Database Name. The name of the database you are to connect to. Leave blank to connect to the default database.

• BackEnd Type: ASE

In addition, there is a settings on the Advanced tab which we recommend you set:

• Fetch Array Size Increasing the value beyond the default can improve performance quite noticeably. We suggest a value of 200 or higher. The optimum value of this settings depends on your network configuration and amount of data you commonly query

It is recommended that you click on Test Connect to ensure that your connection has been configured correctly.

AQT Options

In addition, we recommend you set the AQT Option > Technical Parameters > For Sybase and SQL Server, use SET ROWCOUNT. This is discussed in more detail in Rowcount for SQL Server and Sybase.

Connecting to HP SQL/MX

In order to connect to an HP SQL/MX database:

• you need to have the SQL/MX client and ODBC Driver installed on your PC

• MXCS on the database server must be running to accept ODBC-MX client connections

• you must define a datasource to MXCS, as well as to your client PCs

The datasource names must match between those defined to MXCS on the database server, and the client PCs. In other words, if you define a datasource called HR to MXCS, the datasouce on the client PCs must also be called HR.

MXCS Configuration

When you define a datasource to MXCS, it is recommended that the SQL_ATTR_NO_HEADING attribute is set to TRUE. �This is needed in order for the column names to be displayed correctly when a table is displayed.

The following is an example of adding a datasource called HR: �

�/home/dba: mxci

>>mode mxcs;

CS>add ds "HR";

-- ADD DS \APSGP.$MXCS.HR Successful

CS>add evar $MXCS."HR"."SQL_ATTR_NO_HEADING", type SET, value 'TRUE';

-- ADD EVAR \APSGP.$MXCS.HR.SQL_ATTR_NO_HEADING Successful

CS>start ds "HR";

-- START DS \APSGP.$MXCS.HR Successful

CS>exit; �

/home/dba:

Other Notes

For more on using AQT with SQL/MX, see Database-specific notes > HP SQL/MX �

Connecting to Excel

AQT can be used to access Excel files, which gives you an alternate way to read Excel data. With a "database-style" interface into Excel, you can build searches, sorts and summaries in way that (arguably) is easier than in Excel.

For more on the use of Excel with AQT, see Database-specific notes - Excel.

Prerequisite Software

No software is needed in order to access an Excel file (xls file). You access the Excel file using the Microsoft ODBC Driver for Excel; this is present in the standard Windows install.

You do not need Microsoft Excel on your machine in order to access an Excel file with AQT.

Accessing xlsx, xlsm, xlsb files

The ODBC Driver that comes standard on your Windows PC will only be able to access xls files.

If you wish to open xls, xlsm or xlsn files you will need to install a more modern ODBC Driver. This can be downloaded from:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Signing onto an Excel file

You have three methods of signing accessing an Excel file:

• setting up an ODBC Datasource for an Excel file

• setting up / using a Generic Datasource

• open the xls file with AQT

The first method is useful if you are accessing a particular Excel file on a frequent basis.

Setting up an ODBC Driver for your Excel File

This sets up a datasource which you can use for accessing any Excel file.

The general procedure for this is descibed in Configuring a Database Connection.

• select a Driver of Microsoft Excel Driver

• you select your xls file by clicking on the Select Workbook button (in the Database frame).

Once you have done this, the ODBC Datasource name for your Excel file will appear in the list of databases in the AQT signon window (make sure you have select Show All (not Show Recent)).

Setting up and using a Generic Datasource

To set this up, follow the steps in the previous section, however do not select a workbook (leave this blank). It is recommended that you give this Datasource a name such as Excel Files.

To use this generic datasource:

• from the AQT sign-on window, sign onto the Excel Files datasource.

• you will be prompted with the name of the xls file to use. Select this.

Open the xls file with AQT

To do this, right-click the xls file, select Open With > Choose Program. Click on Browse to select AQT. You can check Always use the selected program to open this kind of file if you want to always open xls files with AQT.

Read-only Mode

By default, when you set up an Excel datasource, it will be in read-only mode. You will not be able to update any of the Excel data. If you wish to update your data, you need to switch off the Read Only flag when you configure the datasource. This is done by clicking on Options and de-selecting Read Only.

When you use the Open the xls file with AQT method of connecting, the connection will be in non readonly mode (so you can update data).

Connecting to Text and CSV Files

AQT can be used to access structured text files, such as CSVs (files where the data is a set of comma-separated-variables). This gives you the ability for querying these files with a database-style interface.

Prerequisite software

No software is needed in order to access Text files. This is done using the Microsoft Text ODBC Driver which is present in the standard Windows install.

Signing onto a text file

You have two methods of signing accessing a Text file:

• setting up an ODBC Datasource for a Text file

• open a csv file with AQT

The first method is useful if you are accessing particular Text files on a frequent basis.

Setting up an ODBC Driver for your Text Files

The general procedure for this is described in Configuring a Database Connection.

• select a Driver of Microsoft Text Driver

• you select the directory where your text files reside by:

  • de-select Current Directory

  • clicking Select Directory button

• you may also wish to click on Options and Define Format. In this, you can specify:

  • the character that delimits the columns

  • whether the first line of the files contains column titles

  • the data types of the columns in your files

Once you have done this, the ODBC Datasource name for your Text file will appear in the list of databases in the AQT sign-on window (make sure you have select Show All (not Show Recent)).

When you sign onto a Text datasource, you will see all the text (*.txt and *.csv) files in the specified directory. It is assumed that all your files are in the same format - eg. have the same column delimiter, and whether the first line has titles.

Open a csv file with AQT

To do this, right-click the csv file, select Open With > Choose Program. Click on Browse to select AQT. You can check Always use the selected program to open this kind of file if you want to always open csv files with AQT.

This method can only be used for csv files, and not any other file type.

When you use this connection method:

• you will be shown all files in the directory, not just the selected file.

• the column-delimiter character is assumed to be a comma

• the first line of the files will be assumed to contain column titles

Using text datasources

• you can create new files with the Create Table command

• you can delete files with the Drop Table command

• rows can be inserted into files, however you cannot update or delete rows

Copy data from text files to a database table

The best option for this is to use the Data Loader. An alternate method is:

• set up a Datasource for the text file (as described above) and sign onto it

• use Export as Insert to export the data from the file as a series of insert statements. When you do this, you should specify the target table name in the Table name for Insert Statements field.

• sign onto the target database and run these insert statements.

Connecting to SQLite

Before you can use an SQLite database with AQT, you need to download and install the SQLite ODBC Driver.

This can be downloaded from http://www.ch-werner.de/sqliteodbc/. Go to the Current Version section and select sqliteodbc.exe. Once you have downloaded this file, run it to install the ODBC Driver.

There are a number of third-party ODBC Drivers, such as Devart (https://www.devart.com/odbc/sqlite/). We haven't used this ODBC Driver so cannot comment on its capabilities.

Once you have done this, you can connect to an SQLite database in one of two ways.

Setting up a Datasource for the SQLite Database

You would use this method if you had an SQLite database you wish to use on a frequent basis.

• from the AQT Signon window, click on New

• select SQLite3 ODBC Driver (probably near the bottom) and click on Next

• on the dialog that is displayed, click on Browse to select the SQLite file you wish to access. Enter a Data Source Name for this Datasource then click on OK.

• you should now see this Datasource in the list of Databases. Select this then click on Sign On.

Doing a one-off connection to an SQLite Database

If you do not wish to create a Datasource for your SQLite database, you can connect to it by:

• on the AQT Signon window, select Direct Connect (at the top of the window)

• in the list of Drivers, select SQLite3 ODBC Driver (probably near the bottom)

• click on the button to the right of the File Name box to select the SQLite database

• click on Sign On.

Connecting to Snowflake

Prerequisite Software

In order to connect to a Snowflake database, you will need the Snowflake ODBC Driver installed on your PC. This can be downloaded from the Snowflake site https://developers.snowflake.com/odbc/

This is a 64-bit ODBC Driver, so youwill need to use 64-bit AQT.

Configuring the Snowflake ODBC Driver

In order to access your Snowflake database, you will need to configure an ODBC Datasource for your database. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Datasource Name. The name you give this ODBC datasource.

• User / Password. Your signon credentials to Snowflake.

• Server. Specifies the hostname of your account in the format <account_identifier>.snowflakecomputing.com

  We discuss the account identifier later.

• Database Name. The name of the database you are to connect to. You must enter a value here. If not, once you sign onto AQT you will get the error:

  22000(90105)Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

It is recommended that you click on Test to ensure that your connection has been configured correctly.

Account Identifier

To find your account identifier, from the Snowflake panel, go to Accounts and find your account url.

Your account identifier is the account url without the https:// at the start.

The account identifier is entered in the Server field of the ODBC Datasource configuration.