#Sql

• Installing ODBC on Linux for MSSQL and AS400 DB2

  01 Mar 2013 | 5 minutes read

  #coding #perl #sql #as400 #mssql

  At work, we use Microsoft SQL Server and IBM AS400 databases. Here’s how I set our Linux boxes to allow them to connect to the databases through ODBC. There are seperate instructions for the Debian and Ubuntu (9.10 Karmic Koala) and Red Hat Enterprise Linux (RHEL4 and RHEL5) distributions.

  ODBC connections require several layers of software to work. The bottom layer consists of the individual ODBC drivers for each database system. Our top layer is the DBI/DBD interface for Perl. In between these layers is the ODBC driver manager, which keeps track of the DSN’s and their corresponding ODBC drivers.

  Packages and Software to Install

  Ubuntu Version (tested on 9.10 Karmic Koala)

  Debian Version (“unstable” distribution)

  We use unixODBC as our ODBC driver manager. First, install unixODBC:

  NOTE: Perl’s DBD::ODBC module requires the developer’s version of unixODBC, so install that one.

  # apt-get install unixodbc-dev
  

  read article

• Returing null Columns from iSeries AS400 with Perl DBI

  08 Aug 2012 | 2 minutes read

  #coding #perl #sql #as400

  We’re having an issue with database queries to the iSeries AS400 through Perl using DBI and DBD::ODBC. The issue occurs when the SQL statement contains an outer join and the query returns rows with NULL columns:

  my $sql_query = qq|select PRSK01, PSTA15
                      from TESTHA.POLMAST
                      left join TESTHA.P15POLDP
                      on POLC01 = PPOL15 where POLC01 = 003950136|;
  
  # Execute the sql query
  my $sth = $dbh->prepare( $sql_query )
                      or die $sql_query, "<BR>\n", $DBI::errstr;
  
  my $data = $dbh->selectall_arrayref( $sth, {Columns=>{}}, @sql_data )
                      or die $sql_query, "<BR>\n", $sth->errstr;
  

  The error returned is:

  DBD::ODBC::db selectall_arrayref failed: st_fetch/SQLFetch (long truncated
  DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)
  

  read article