Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 21
Joined: Mon Oct 07, 2013 12:15 pm

Setting up ODBC for Python and PHP (SLES)

by ChakLeung » Fri May 30, 2014 1:13 pm

Setting up an ODBC connection is a great way to check for issues before establishing a connection via external script. Here is a guide on setting up ODBC connections for Python and PHP.

Requirements
SLES system with SSH access via PuTTY
A Kognitio system where the ODBC can be connected to

Install Kognitio driver

Download: http://www.kognitio.com/forums/latest_8 ... nts.tar.gz

This is the driver to connect to a Kognitio system, being able to retrieve data from this system is the primary objective. Transfer this file to the SLES system via WinSCP or Filezilla into the directory:
/opt/kognitio/wx2/current
Then via PuTTY, SSH into the system and navigate to the directory where you placed the driver. Decompress the file into a tar file using
gunzip latest_810_linuxclients.tar.gz
Then use
tar -C /opt/kognitio/wx2/current -xf latest_810_linuxclients.tar
which installs/unpacks it in that directory. In /opt/kognitio/wx2/current/lib/Linux64 there are 3 drivers to try in the odbc.ini files:
libwcsodbc.so
libwcsodbc_utf16.so
libwcsodbc_utf8.so
If the Kognitio driver is already installed then other drivers may be present. Others may include "dmc" drivers which are used if a driver manager like UnixODBC is not used and "legacy" drivers which are old drivers should only be used when communicating with pre-version 7 servers. The focus will be on the drivers mentioned above.

Installing unixODBC
This will install unixODBC from the default SUSE repositories:
Zypper install unixODBC
Installing Python and Pyodbc
Note that Pyodbc was installed locally from an rpm file.
Zypper install python
Zypper install python-pyodbc-2.1.8-6.1.x86_64.rpm
Installing PHP-ODBC
Zypper install php53-odbc
To ensure that the odbc extension for PHP5 is loaded by default, firstly find the odbc.ini file by using:
find / -name odbc.ini
Various directories may appear showing different odbc.ini files. The one of interest is the one under the following directory:
/etc/php5/conf.d
Ensure that the odbc.ini file has the following:
; comment out next line to disable odbc extension in php
extension=odbc.so
Finding and configuring ini files
Use the following to find the locations of the ini files:
odbcinst -j
The result should be 3 locations, labelled "DRIVERS", "SYSTEM DATA SOURCES", "USER DATA SOURCES" respectively.
Firstly open the ini labelled "DRIVERS" for editing, here the system needs to know which driver to use for an ODBC. An example of the ini file:
[WX2_driver]
Description = Kognitio ODBC driver

Driver = /opt/kognitio/wx2/current/lib64/libwcsodbc_utf16.so
This is where the 3 drivers from before are specified, there is a small amount of trial and error between which driver to use based on the character encoding.
Now for the data sources open the ini file labelled "SYSTEM DATA SOURCES" and edit it in a similar fashion to the following:
[AWS]
ServerAddress1 = "IP Address"

ServerPort1 = 6550

Timeout = 30

SSLEnabled=N

Now the system is ready for ODBC to a Kognitio system. Move a simple script to the SLES system and execute it to retrieve the rows from the database. For example:
PHP
<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php
$conn = odbc_connect('Server IP', 'username', 'password');
$sql = "select top 10 * from schema.table_or_view";
$rs = odbc_exec($conn,$sql);
?>
</body>
</html>
Python
import pyodbc

cnxn = pyodbc.connect('DRIVER=','DSN=Server IP','UID=username','PWD =password')
cursor =cnxn.cursor()

cursor.execute('select * from schema.table_or_view')
for row in cursor.fetchall():
print row
Note for PHP ODBC:
Running ODBC via PHP may produce a “zend_mm_heap corrupted” error. Use
export USE_ZEND_ALLOC = 0

to rectify this. This is due to the memory management in the Zend framework (open source web application framework for PHP5) crashing when it is allocating memory. Various internet sources have suggested to increase the output buffering memory but this was not helpful in this case, disabling the ZEND_ALLOC function altogether was more effective.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron