Linux odbc connection test

Testing the ODBC Connection

You can use DSN connection strings and DSN-less connection strings for your connections.

Example Connection String with DSN

The following is an example of a connection string for a connection that uses a DSN:

[DataSourceName] is the DSN that you are using for the connection.

You can set additional configuration options by appending key-value pairs to the connection string. Configuration options that are passed in using a connection string take precedence over configuration options that are set in the DSN.

Example Connection Strings without DSN

Some applications provide support for connecting to a data source using a driver without a DSN. To connect to a data source without using a DSN, use a connection string instead. The placeholders in the examples are defined as follows, in alphabetical order:

• [ClusterName] is the name of the ZooKeeper cluster to which you are connecting.
• [DomainName] is the fully qualified domain name of the Drill server host.
• [PortNumber] is the number of the TCP port that the Drill server uses to listen for client connections.
• [Server] is the IP address or host name of the Drill server to which you are connecting.
• [ServiceName] is the Kerberos service principal name of the Drill server.
• [YourPassword] is the password corresponding to your user name.
• [YourUserName] is the user name that you use to access the Drill server.

Drillbit Connections

The following is the format of a DSN-less connection string for a Drillbit that does not require authentication:

Driver=MapR Drill ODBC Driver;ConnectionType=Direct; Host=[*Server*];Port=[*PortNumber*] 

Driver=MapR Drill ODBC Driver;ConnectionType=Direct; Host=192.168.222.160;Port=31010 

ZooKeeper Connections

The following is the format of a DSN-less connection string for a ZooKeeper cluster that does not require authentication:

Driver=MapR Drill ODBC Driver; ConnectionType=ZooKeeper; ZKQuorum=[*Server1*]:[*PortNumber*1], [*Server2*]:[*PortNumber2*], [*Server3*]:[*PortNumber3*]; ZKClusterID=[*ClusterName*] 

Driver=MapR Drill ODBC Driver; ConnectionType=ZooKeeper; ZKQuorum=192.168.222.160:31010, 192.168.222.165:31010, 192.168.222.231:31010; ZKClusterID=drill; 

Kerberos Authentication Connections

The following is the format of a DSN-less connection string for a Drillbit that requires Kerberos authentication:

Driver=MapR Drill ODBC Driver; ConnectionType=Direct; Host=[*Server*];Port=[*PortNumber*]; AuthenticationType=Kerberos; KrbServiceHost=[*DomainName*];KrbServiceName=[*ServiceName*] 

Driver=MapR Drill ODBC Driver; ConnectionType=Direct; Host=192.168.222.160;Port=31010; AuthenticationType=Kerberos; KrbServiceHost=maprdriverdemo.example.com; KrbServiceName=drill 

Plain Authentication Connections

The following is the format of a DSN-less connection string for a Drillbit that requires Plain authentication:

Driver=MapR Drill ODBC Driver;ConnectionType=Direct; Host=[*Server*];Port=[*PortNumber*]; AuthenticationType=Plain; UID=[*YourUserName*];PWD=[*YourPassword*] 

Driver=MapR Drill ODBC Driver;ConnectionType=Direct; Host=192.168.227.169;Port=31010; AuthenticationType=Plain; UID=username;PWD=mapr999 

Testing the ODBC Connection

The procedure for testing the ODBC connection differs depending on your platform, as described in the following sections:

Testing the ODBC Connection on Linux

To test the ODBC connection on Linux use the test utilities in the samples directory of the driver manager installation: iodbctest and iodbctestw . Use iodbctest to test how your driver works with an ANSI application. Use iodbctestw to test how your driver works with a Unicode application.

There are 32-bit and 64-bit installations of the iODBC driver manager available. If you have only one or the other installed, then the appropriate version of iodbctest (or iodbctestw) is available. However, if you have both 32- and 64-bit versions installed, then you need to be careful that you are running the version from the correct installation directory.

See http://www.iodbc.org for further details on using the iODBC driver manager.

Example of a Test on Linux

To test the ODBC connection on a Linux cluster, follow these steps:

Start Drill. For example, to start Drill in local mode on a Linux cluster:

 [root@centos23 drill-1.10.0]# bin/drill-localhost apache drill 1.10.0 "the only truly happy people are children, the creative minority and drill users" 

 [root@centos23 libiodbc-3.52.7]# samples/iodbctest iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0709.0909 

 Enter ODBC connect string (? shows list): ? 

 DSN | Driver ------------------------------------------------------------------------------ MapR Drill (64-bit) | MapR Drill ODBC Driver 64-bit Enter ODBC connect string (? shows list): 

However, if you are connecting directly to a Drillbit and the DSN was not previously configured in the .odbc.ini, type an ODBC connection string using the following format:

 DSN=; ConnectionType=Direct; Host=;Port=

OR If you are connecting to a ZooKeeper cluster, type an ODBC connection string using the following format:

 DSN=; ConnectionType=ZooKeeper; ZKQuorum=,; ZKClusterID=

Example: Connection String for a Direct Connection

 DSN=MapR Drill 64-bit; ConnectionType=Direct; Host=localhost;Port=31010 

Example: Connection String for a ZooKeeper Cluster Connection

 DSN=MapR Drill 64-bit 64; ConnectionType=ZooKeeper; ZKQuorum=centos23.lab:5181; ZKClusterID=docs60cluster-drillbits 

Testing the ODBC Connection on Mac OS X

To test the ODBC connection on Mac OS X:

1. Start Drill.
2. Start the iODBC Data Source Administrator app in /Applications . The iODBC Data Source Administrator dialog appears.
3. On the ODBC DSN tab, select MapR Drill.
4. Click Test. The MapR Drill login dialog appears.
5. If you configured Plain (or Basic Authentication) in the .odbc.ini file, enter the user name and password you also configured. Otherwise, click OK. The success message displays.

Testing the ODBC Connection on Windows

To test the ODBC connection on Windows, follow these steps:

1. Follow instructions to configure the ODBC connection on Windows. The MapR Drill ODBC Driver DSN Setup dialog is displayed.
2. Click Test. A Test Results popup will display that states you have successfully connected to the data source.

Copyright © 2012-2022 The Apache Software Foundation, licensed under the Apache License, Version 2.0.
Apache and the Apache feather logo are trademarks of The Apache Software Foundation. Other names appearing on the site may be trademarks of their respective owners.

Источник

Open Database Connectivity

Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources. An ODBC engine needs drivers to be able to interact with databases.

ODBC engines

You have two options to chose from: unixODBC and iODBC. Apparently unixODBC is more widely supported. This document shows how to set up unixODBC. First to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.

Additionally you can choose from various Devart ODBC drivers for SQL Server, Oracle, MySQL, SQLite, Firebird, PostgreSQL, Interbase.

Installation

Configuration

Driver are declared in /etc/odbcinst.ini , and connections in /etc/odbc.ini . More instruction at each driver section.

Drivers

FreeTDS

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients.

Installation

Configuration

[FreeTDS] Driver = /usr/lib/libtdsodbc.so UsageCount = 1

The configuration file of FreeTDS itself is /etc/freetds/freetds.conf .

Myodbc

Myodbc is ODBC driver/connector for mariadb.

Installation

Configuration

Starting with odbcinst.ini , which lists all installed drivers.

[MySQL] Description = ODBC Driver for MySQL Driver = /usr/lib/libmaodbc.so FileUsage = 1

SQLite

sqliteodbc is ODBC driver/connector for sqlite.

Installation

Configuration

Starting with odbcinst.ini , which lists all installed drivers.

[SQLite3] Description=SQLite ODBC Driver Driver=/usr/lib64/libsqlite3odbc.so Setup=/usr/lib64/libsqlite3odbc.so Threading=2 UsageCount=1

PostgreSQL

psqlodbc is ODBC driver/connector for PostgreSQL.

Installation

Configuration

Starting with odbcinst.ini , which lists all installed drivers.

[postgresql] Description=General ODBC for PostgreSQL Driver=/usr/lib64/psqlodbca.so Setup=/usr/lib64/psqlodbcw.so Threading=2 FileUsage=1

Databases

Microsoft SQL Server 2000

[server_name] Driver = FreeTDS #Trace = Yes #TraceFile = /tmp/odbc Servername = server_name Database = database_name

[server_name] host = 192.168.0.2 # Host name or IP address. port = 1433 # Default port. tds version = 7.1 client charset = UTF-8

SQL Server ODBC driver connection strings and configuration guide

Mariadb

Set up your data sources in /etc/odbc.ini (system wide) or ~/.odbc.ini (current user). If a data source is defined in both of these files, the one in your home directory take precedence.

[MySQL-test] Description = MySQL database test Driver = MySQL Server = localhost Database = test Port = 3306 Socket = /var/run/mysqld/mysqld.sock Option = Stmt =

MariaDB ODBC driver connection strings and configuration guide

Create a test database

Create a new database «test». You can use one of the MySQL front-ends such as mysql-workbench , or the command-line mysqladmin command:

$ mysqladmin -h localhost -u root -p create test

Testing the ODBC

To test the ODBC connection

If the connection is established, you will see

+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>

If you have a problem connecting then check the error message by running

A couple useful websites

This website got me going on ODBC with MySQL but left out some things that were necessary for me to get isql up and running. However this might be a good reference for the OpenOffice part.

To work around error messages this URL proved helpful so here it is as well.

Postgresql

Set up your data sources in /etc/odbc.ini (system wide) or ~/.odbc.ini (current user). If a data source is defined in both of these files, the one in your home directory take precedence.

[Postgres-test] Description = Postgres database test Driver = postgresql Servername = localhost Username = postgres Password = test123 Database = test Port = 5432 ReadOnly = No

Virtuoso / SPARQL

[ODBC Data Sources] VOS = Virtuoso [VOS] Driver = virtuoso-odbc Description = Virtuoso Open-Source Edition Address = localhost:1111

[virtuoso-odbc] Driver = /usr/lib/virtodbc.so

Opening a connection using the default credentials (username: «dba», password: «dba»):

SQLite

Setup odbc.ini by assign the sqlite file location.

[ODBC Data Sources] sampleDB = SQLite3 Driver [sampleDB] Driver = /usr/lib/libsqlite3odbc.so Description = Sample DB database = /home/db/sample.sqlite

• This page was last edited on 6 November 2022, at 12:34.
• Content is available under GNU Free Documentation License 1.3 or later unless otherwise noted.

• Privacy policy
• About ArchWiki
• Disclaimers

Источник