ODBC connection works through command line, not through apache

Question

I'm trying to connect to a Pervasive 11 DB on a remote system using PHP. The DSN is setup in /etc/odbc.ini. The psql (created by the Pervasive client installer) and www-data user can connect to the remote system using

isql -v remote  

Furthermore, the www-data user is added to the pvsw group and the needed environment variables PVSW_ROOT and LD_LIBRARY_PATH are set in /etc/apache2/envvars as well as in the vhost using SetEnv.

My PHP script is as follows:

<?php  
$connect = odbc_connect("remote", "", "") or die("Could not connect");  
$query = "SELECT * FROM \"ITEMS\"";  
$prepared = odbc_prepare($connect, $query);  
$result = odbc_execute($prepared);  
odbc_result_all($prepared);  

The script runs fine from the command line:

sudo -u www-data /usr/bin/php /var/www/odbc.php

and outputs as expected.

However, accessing http://example.org/odbc.php results in a blank page and no data is being sent from Apache (checked with wget and Chrome). Using tcpdump shows the connection being made between the server and the Pervasive remote DB, both by invoking the script from the CLI as from Apache. The system is Debian 64 bit 7.7. 'LogLevel debug' is set in the vhost config, but no errors are logged.

Why is Apache not returning any data?

EDIT: using gdb and stepping through the apache webserver process that is handling the request, I get this error:

Program received signal SIGSEGV, Segmentation fault.  
0x00007ff48c68ea2f in ErrStmtWithState () from /usr/local/psql/lib64/libodbcci.so  

Looks like a bug, no?

Answer 1

Probably a bug: http://cs.pervasive.com/forums/p/14802/53328.aspx.

I got the same issue but with mod_wsgi and python and googled "ErrStmtWithState" to get here. My (far from optimal) solution was inspired by https://serverfault.com/questions/451220/psql-64bit-driver-error. I created the following shell script:

#!/bin/bash
PVSW_ROOT=/usr/local/psql
PATH=$PATH:$PVSW_ROOT/bin:/bin:/usr/bin
LD_LIBRARY_PATH=$PVSW_ROOT/lib64:$PVSW_ROOT/bin:/usr/lib
MANPATH=$MANPATH:$PVSW_ROOT/man
export PVSW_ROOT
export LD_LIBRARY_PATH

python wrapper.py "$@"

Where "wrapper.py" runs a query against a dsn passed as an argument and outputs a serialized resultset:

import pyodbc
import cPickle
import sys, getopt

def main(argv):
   dsn = ''
   query = ''
   hm = 'usage: wrapper.py -d <dsn> -q <query>'
   try:
       opts, args = getopt.getopt(argv,"hd:q:",["dsn=","query="])
   except getopt.GetoptError:
      print hm
      sys.exit(2)
   for opt, arg in opts:
      if opt == '-h':
         print hm
         sys.exit()
      elif opt in ("-d", "--dsn"):
         dsn = arg
      elif opt in ("-q", "--query"):
         query = arg

   cnxn = pyodbc.connect('DSN='+dsn)
   cursor = cnxn.cursor()
   cursor.execute(query)
   rows = cursor.fetchall()
   print(cPickle.dumps(rows))

if __name__ == "__main__":
   main(sys.argv[1:])

Environment variables copied from pervasive documentation: http://docs.pervasive.com/products/database/psqlv10/wwhelp/wwhimpl/js/html/wwhelp.htm#href=getstart/unixappconf.15.3.html

Answer 2

When I ran your code, I got a message saying that odbc_result_all expects parameter 1 to be a resource.
When I changed it to the following, it worked:

<?php  
$connect = odbc_connect("remote", "", "") or die("Could not connect.");  
$query = "SELECT * FROM \"ITEMS\"";  
$prepared = odbc_prepare($connect, $query);  
if (!odbc_execute($prepared)) {
    die(odbc_errormsg()); 
}
if (odbc_result_all($prepared) < 1) {
    die(odbc_errormsg());
}
?>

This makes sense because odbc_excute is declared as:

bool odbc_execute ( resource $result_id [, array $parameters_array ] )

and odbc_result_all is declared as:

int odbc_result_all ( resource $result_id [, string $format ] )

in the PHP docs.