Azarbod Blog

Here discuss anything related to IT, Software Systems for Manufacturing and Distribution or the latest Azarbod Events.

Connecting to ODBC on Ubuntu Command Line (Crontab)

Sat, Nov 12- 2016


Recently I had this problem that ODBC connection to Pervasive SQL did not work under Ubuntu and PHP5. It did work when I was using the program through a web page, but connection via Cron job it did not work.

Basically, I needed to import a large amount of data everyday from Pervasive and it took more than an hour to be completed. That is why, the idea of running it via a browser by user was not a very good one and I wanted to run it at night via Cron job.

This is how I was testing it. Using SSH connection and a user with admin privileges I was running

  • $ php nightly_run.php

Inside of the PHP file it was trying an odcb connection like this:

set_error_handler(function($errno, $errstr, $errfile, $errline ) 
{
  throw new ErrorException($errstr, $errno, 0, $errfile, $errline);
});

try
{
  $conn = odbc_connect("prevasive","user","password");
  if($conn)
    echo "Worked" ;
}
catch (Exception $e)
{
  echo ("Could not connect to Pervasive- Reason: " . $e -> getMessage()) ;
}
echo "
" ;

The connection was failing with this error: odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open lib '/usr/local/psql/lib64/libodbcci.so' : file not found, SQL state 01000 in SQLConnect Then I learnt that I did not need to run PHP and could check the connection via iSQL as in

  • $ ? isql -v odbc_source user password

At first I thought the problem is that the web page runs via Apache user www-data and was trying to run isql via the same user as well using this command

  • $ sudo -u www-data isql -v odbc_source user password

Still the same error: odbc_connect(): SQL error: [unixODBC][Driver Manager] Can't open lib '/usr/local/psql/lib64/libodbcci.so' : file not found, SQL state 01000 in SQLConnect

Then I tried to make the environment variables the same for both Apache and Cron job or command line. I created a small PHP program that would run only one function called 'phpinfo(INFO_ENVIRONMENT) ;' I ran this program via web browser. Then on the command line did run

  • $ env > env.out

Compared the results saved in env.out file and the ones from 'phpinfo(INFO_ENVIRONMENT) ;' They were exactly the same. Specillay I was comparing the variable LD_LIBRARY_PATH.

Searched more and at the end got an idea from this web page. Looks like I needed to define LD_LIBRARY_PATH64 as well as the system was running on 64 bit Ubuntu. Then I did the following:

  • $ sudo nano /etc/environment

Edited the file and add this line after LD_LIBRARY_PATH
LD_LIBRARY_PATH64="/usr/local/psql/lib64"
Then exit my Putty SSH session, re-logged in and ran the same program

  • $ isql -v odbc_source user password

It was magic. It worked this time and connection established.

Run Cron job as a different user like www-data

Now that connection problem is solved, I ran into another problem.
The problem was that when user runs the program through a browser I log the changes into a log file. Now that Cron job also updates the same type of record, then it could not open the file already created by web. The same would happen if the file was first created by Cron and then web was trying to append to it.

Basically I had to run cron job under the same user as Apache, in this case www-data and I had no ider what user it was running uder. In FTP it was showing the gid 1001. This is how it was fixed. I ran this command:

  • $ crontab -e

And commented the cron job. Then ran the following command to create a Crontab that runs unser www-data (which is the user used by Apache.)

  • $ sudo crontab -u www-date -e

It opens the editor where you can enter the exact same command to run regular Cron job. Again magic and it works.

Hope it was helpful and saved you some time !

Reza Zare
November 2016