Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-134

Connect Perl to EXASolution

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0
    • Fix Version/s: None
    • Component/s: ODBC
    • Labels:
      None
    • Solution:
      Hide

      This article describes how to connect Perl on AIX, HP-UX, Linux, Solaris to EXASolution. Perl on Mac OS is not supported at the moment.

      Preparation

      Perl is connected to EXASolution via ODBC.

      Required Perl modules

      Install the DBD::ODBC Perl module with your system's package manager.

      Example: Ubuntu 14.04
      apt-get install libdbd-odbc-perl
      
      Example: openSUSE 13.1
      zypper install perl-DBD-ODBC
      

      This will also install the DBI Perl module and a matching ODBC driver manager.

      Unfortunately, most systems do not ship the DBD::ODBC module. See below for how to handle this.

      ODBC driver

      Install the EXASolution ODBC driver as described in SOL-153.

      Example

      For this example we assume the correct DSN is "exasolution-uo2214lv2_64". The DBI module does not allow to use user/password from odbc.ini configuration file; they have to be provided with the connect call.

      #!/usr/bin/perl
      
      use DBI;
      
      $dsn = 'exasolution-uo2214lv2_64';
      $user = 'sys';
      $passwd = 'exasol';
       
      @driver_names = DBI->available_drivers;
      print join(', ', @driver_names), "\n";
       
      %drivers=DBI->installed_drivers;
      print join(', ', @drivers), "\n";
       
      @data_sources = DBI->data_sources('ODBC', \%attr);
      print join(', ', @data_sources), "\n";
       
      $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $passwd, { AutoCommit => 0 });
      
      $statement = 'SELECT * FROM (VALUES(1, 2, 3))';
      $sth = $dbh->prepare($statement);
      $rv = $sth->execute;
      
      @row = $sth->fetchrow_array;
      print join(', ', @row), "\n");
      
      $sth->finish();
      $dbh->disconnect();
      

      For details of the DBI module, call perldoc DBI.

      Missing packages

      Missing Perl modules

      The easiest way to fetch and compile missing Perl modules is from CPAN with the cpan program. Depending on your system, the cpan program is located in an extra package.

      Before you compile DBD::ODBC, install the unixODBC ODBC driver manager and development files (usually in an extra package). If your system provides multiple versions of unixODBC, choose the newest one whose architecture matches your Perl.

      Missing ODBC driver manager

      If your system does not provide an unixODBC package, compile and install it from source. unixODBC 2.3.1 or later is recommended.
      Compiling unixODBC on AIX is a bit tricky. Please read the extra README in the EXASolution ODBC driver package for details.

      In order for Perl to find the non-system (i.e. self-compiled) ODBC driver manger, make sure that the environment variable "ODBCHOME" is set to the path of unixODBC, e.g. "/usr/local/unixODBC-2.3.1".

      Show
      This article describes how to connect Perl on AIX, HP-UX, Linux, Solaris to EXASolution. Perl on Mac OS is not supported at the moment. Preparation Perl is connected to EXASolution via ODBC. Required Perl modules Install the DBD::ODBC Perl module with your system's package manager. Example: Ubuntu 14.04 apt-get install libdbd-odbc-perl Example: openSUSE 13.1 zypper install perl-DBD-ODBC This will also install the DBI Perl module and a matching ODBC driver manager. Unfortunately, most systems do not ship the DBD::ODBC module. See below for how to handle this. ODBC driver Install the EXASolution ODBC driver as described in SOL-153 . Example For this example we assume the correct DSN is "exasolution-uo2214lv2_64". The DBI module does not allow to use user/password from odbc.ini configuration file; they have to be provided with the connect call. #!/usr/bin/perl use DBI; $dsn = 'exasolution-uo2214lv2_64' ; $user = 'sys' ; $passwd = 'exasol' ; @driver_names = DBI->available_drivers; print join( ', ' , @driver_names), "\n" ; %drivers=DBI->installed_drivers; print join( ', ' , @drivers), "\n" ; @data_sources = DBI->data_sources( 'ODBC' , \%attr); print join( ', ' , @data_sources), "\n" ; $dbh = DBI->connect( "dbi:ODBC:$dsn" , $user, $passwd, { AutoCommit => 0 }); $statement = 'SELECT * FROM (VALUES(1, 2, 3))' ; $sth = $dbh->prepare($statement); $rv = $sth->execute; @row = $sth->fetchrow_array; print join( ', ' , @row), "\n" ); $sth->finish(); $dbh->disconnect(); For details of the DBI module, call perldoc DBI . Missing packages Missing Perl modules The easiest way to fetch and compile missing Perl modules is from CPAN with the cpan program. Depending on your system, the cpan program is located in an extra package. Before you compile DBD::ODBC, install the unixODBC ODBC driver manager and development files (usually in an extra package). If your system provides multiple versions of unixODBC, choose the newest one whose architecture matches your Perl. Missing ODBC driver manager If your system does not provide an unixODBC package, compile and install it from source. unixODBC 2.3.1 or later is recommended. Compiling unixODBC on AIX is a bit tricky. Please read the extra README in the EXASolution ODBC driver package for details. In order for Perl to find the non-system (i.e. self-compiled) ODBC driver manger, make sure that the environment variable "ODBCHOME" is set to the path of unixODBC, e.g. "/usr/local/unixODBC-2.3.1".
    • Category 1:
      Clients, Interfaces & Drivers - ODBC

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: