Update LD_LIBRARY_PATH system wide for ODBC connection via PHP7.0-FPM

4
votes

I am trying to connect to NetSuite over ODBC from an Ubuntu Linux machine. I've got everything setup. It works via the shell, and PHP command line. But when I try and make this connection via a web request to PHP, it fails with this error:

 Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open lib '/opt/netsuite/odbcclient/lib64/ivoa25.so' : file not found, SQL state 01000 in SQLConnect in

In order for it to work from the shell, I had to add this to my .profile (via the readme.txt that came with the NetSuite driver)

ODBCINI="/opt/netsuite/odbcclient/odbc64.ini"
OASDK_ODBC_HOME="/opt/netsuite/odbcclient/lib64"
LD_LIBRARY_PATH="/opt/netsuite/odbcclient/lib64${LD_LIBRARY_PATH:+":"}${LD_LIBRARY_PATH:-""}"
export ODBCINI
export OASDK_ODBC_HOME
export LD_LIBRARY_PATH

The problem seems to be with setting the LD_LIBRARY_PATH. I noticed on this page about half way down the page it says 

"Note: You can only set this environment variable inside an interactive shell."

The NetSuite driver requires this update to the LD_LIBRARY_PATH, so are there any other options to making a web request to execute my odbc_connect() statement?

I tried and failed with

  • putenv()
  • adding . /etc/odbcinst.ini to the /etc/init.d/php7.0-fpm script
  • adding the required statements & exports to /etc/environment (even though it said it wouldn't work)
  • added /opt/netsuite/odbcclient/lib64 to a new file /etc/ld.so.conf.d/netsuite.conf

When I did the last one from the above list, I ran sudo ldconfig successfully but got this error when trying to connect & query (bunch of garbage, but different error!):

    [unixODBC]�pV�,8�(8��|D�x������H�R��ut�
(8eow�ut�(8�����|`gx�0x�pV�K����������M0�0�N��M0� ������4�
�0d��Ryy
2
phpnetsuiteunixodbc

2 Answers

1
votes

I don't have an answer, but I can't comment at this point... I do, however, have information that may be useful.

I've been struggling with this NetSuite ODBC driver for Linux for a long time. One interesting thing I learned was that the driver is not compatible with Ubuntu 14.02. It IS compatible with 12.02 which is going out of support in April 2017. However, on a Ubuntu 12.02 VM I set up, I still get this same error. They posted an article about the compatibility in late May 2016 which was after I had given up with it and just developed on a Windows machine.

I am getting the same error when I use ISQL and was getting the same error in my PHP code. I chcked that the permissions were set to have anyone r and x but it's not working.

When run this command:

sudo ldd /opt/netsuite/odbcclient/lib64/ivoa25.so

I get this:

linux-vdso.so.1 =>  (0x00007ffebfb4b000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fcfefc08000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fcfefa04000)
libicuuc.so.42 => not found
libicudata.so.42 => not found
libpam.so.0 => /lib/x86_64-linux-gnu/libpam.so.0 (0x00007fcfef7f6000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fcfef5d8000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fcfef2d4000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fcfeefce000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fcfeedb8000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fcfee9f3000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fcff01b6000)
libaudit.so.1 => /lib/x86_64-linux-gnu/libaudit.so.1 (0x00007fcfee7cf000)

Which doesn't make sense because they are in the directory:

drwxr-xr-x 2 root root     4096 Jan  8  2016 .
drwxr-xr-x 8 root root     4096 Jul 28 16:45 ..
-rw-r--r-- 1 root root  1534752 Jan  8  2016 ddtrc25.so
-rw-r--r-- 1 root root   260915 Jan  8  2016 ivldap25.so
-rw-r--r-- 1 root root    12137 Jan  8  2016 ivmgan25.so
-rw-r--r-- 1 root root   124833 Jan  8  2016 ivmgapi25.so
-rw-r--r-- 1 root root   437482 Jan  8  2016 ivmghu25.so
-rw-r--r-- 1 root root     1521 Jan  8  2016 ivoa25.ini
-rwxr-xr-x 1 root root  3255664 Jan  8  2016 ivoa25.so
-rw-r--r-- 1 root root 14920460 Jan  8  2016 libddicu25.so
-rw-r--r-- 1 root root 16010036 Jan  8  2016 libicudata.so.42
-rw-r--r-- 1 root root  1608359 Jan  8  2016 libicuuc.so.42
-rw-r--r-- 1 root root   354900 Jan  8  2016 libivoa25m.so
-rw-r--r-- 1 root root  1172888 Jan  8  2016 libodbcinst.so
-rw-r--r-- 1 root root  1659688 Jan  8  2016 libodbc.so
-rw-r--r-- 1 root root  1457352 Jan  8  2016 odbccurs.so 
-rw-r--r-- 1 root root  2154752 Jan  8  2016 openssl700.so

I get the file not found regardless of the permissions on the "not found" files. When I've created symbolic links to these files I was able to get the ivoa25.so not found error, but then got this one:

Warning: odbc_connect(): SQL error: [unixODBC], SQL state in SQLConnect in/var/www/html/DataBase.php on line 5

Could not connect:

With regard to this error, I contacted the guys at unixODBC.org and they said this:

Its a guess, but maybe try setting ODBCINI, the driver may need that to find the ini file
export ODBCINI=/etc/odbc.ini

but that didn't do anything either...

I'd love to help you fix this because it would help me too... we should compare notes.

UPDATE

with the error still in place using ISQL using the 

$ odbcisql64 -m 0

command. When you run this it looks like this:

DataDirect OpenAccess SDK Interactive SQL (ODBC) Version 7.2
(c) Copyright 1995-2013 Progress Software Corporation. All rights reserved.

Program Locale:en_US.UTF-8
ISQL>

Trying to connect this way using the connection string 

ISQL> connect "user"*"pw"@netsuite

I get the same error... 

SQL: connecting to database: netsuite...
SQL: Can't connect to database netsuite
The following error information describes the failure
ODBC Call     = SQLConnect()
SQL State     = 01000
Native error  = 0(0)
Error Message = [unixODBC][Driver Manager]Can't open lib     '/opt/netsuite/odbcclient/lib64/ivoa25.so' : file not found
SQL: isql_connect() failure
Elapsed time 2 ms.
ISQL>

...however, I can 'connect' (At least it seems to connect) using this more generic isql command

$ isql NetSuite user pw

...it connects. This works on both Ubuntu 14 and 12. But I can't get any SQL commands to work and it looks different from the other ISQL connection result

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

So the solution ended up being simpler than expected. I essentially created a bash script that exports the environment variables I need then run the PHP script. Here's the entire contents of my netsuite-odbc.sh script. I can now connect and run my queries.

ODBCINI="/opt/netsuite/odbcclient/odbc64.ini"
OASDK_ODBC_HOME="/opt/netsuite/odbcclient/lib64"
LD_LIBRARY_PATH="/opt/netsuite/odbcclient/lib64${LD_LIBRARY_PATH:+":"}${LD_LIBRARY_PATH:-""}"
export ODBCINI
export OASDK_ODBC_HOME
export LD_LIBRARY_PATH

/usr/bin/php /home/user/netsuite-odbc.php