Page 1 of 1

PHP and Vertica (on linux)

Posted: Wed Mar 28, 2012 7:50 pm
by Tom
I plan on doing some integration with Vertica in the near future. There currently isn't any built in support from PHP but you should be able to use isql from unixodbc. I plan on using this thread to share my findings.

Re: PHP and Vertica (on linux)

Posted: Sat May 12, 2012 8:22 am
by DataWhisperer
Cool. Good luck.

Your bottleneck most likely won't be the Vertica cluster, but your PHP/Apache setup as PHP doesn't support connection pooling. Just something to keep in mind.

You could use DNS round robin on the clusters so when you connect, you'll be randomly distributed to one of the Vertica nodes so you don't overwhelm a single node with too many connections.

Re: PHP and Vertica (on linux)

Posted: Thu Nov 08, 2012 2:36 am
by marcellodesales
HI DataWhisperer,

Considering I have the setup you described, what do I need to access Vertica using PHP? Do I need to have the odbc.ini and vertica.ini described in the Python section? Could I just use the regular PHP ODBC


Re: PHP and Vertica (on linux)

Posted: Thu Jan 10, 2013 11:05 am
by hanswilmink
Tom, could you update a bit about your status in this topic? :) i will follow you for a bit :) i'm interested :D

Re: PHP and Vertica (on linux)

Posted: Sun Mar 24, 2013 5:32 pm
by pborne
Here's how you can connect to Vertica with PHP:

Install PHP, as root:

Code: Select all

yum install php
yum install php-odbc
Create an odbc.ini configuration file, for instance: /home/dbadmin/odbc.ini
Modify the path to based on your installed version.

Code: Select all

Description = VerticaDSN ODBC driver
Driver = /opt/vertica/lib64/
Database = vertica
Servername = localhost
UserName = dbadmin
Password =
Port = 5433
ConnSettings =
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US@collation=binary

ODBCInstLib = /usr/lib64/
ErrorMessagesPath = /opt/vertica/lib64/
DriverManagerEncoding = UTF-16
LogPath = /tmp
LogNameSpace =
LogLevel = 0
You may want to tweak the threading in /etc/odbcinst.ini

Code: Select all

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/
Setup           = /usr/lib/
Driver64        = /usr/lib64/
Setup64         = /usr/lib64/
FileUsage       = 1

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
Description     = ODBC for MySQL
Driver          = /usr/lib/
Setup           = /usr/lib/
Driver64        = /usr/lib64/
Setup64         = /usr/lib64/
FileUsage       = 1

Threading = 1
Then, you need to make sure your environment knows where to find the files. Add the following to your .bashrc

Code: Select all

export LD_LIBRARY_PATH=/opt/vertica/lib64:/usr/lib64:/usr/local/lib64
export VERTICAINI=/home/dbadmin/odbc.ini
export ODBCINI=/home/dbadmin/odbc.ini
You can then log out and then back in or simply run this:

Code: Select all

source /home/dbadmin/.bashrc
And finally, here's a small program to test that everything works:

Code: Select all

#!/usr/bin/php -q


    # Turn on error reporting
    error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

    # A simple function to trap errors from queries
    function errortrap_odbc($conn, $sql) {
        if(!$rs = odbc_exec($conn,$sql)) {
            echo "Failed to execute SQL: $sql" . odbc_errormsg($conn) . "\n";
        } else {
            echo "Success: " . $sql . "\n";
        return $rs;

    # Connect to the Database
    $dsn = "VerticaDSN";
    $conn = odbc_connect($dsn,'','') or die ("CONNECTION ERROR");
    echo "Connected with DSN: $dsn" . "\n";
    # Create a table
    $sql = "CREATE TABLE TEST(
            C_ID INT,
            C_FP FLOAT,
            C_VARCHAR VARCHAR(100),
            C_DATE DATE,
            C_TIME TIME,
            C_TS TIMESTAMP,
            C_BOOL BOOL)";
    $result = errortrap_odbc($conn, $sql);
    # Insert data into the table with a standard SQL statement

    $sql = "INSERT into test values(1,1.1,'abcdefg1234567890','1901-01-01','23:12:34 ','1901-01-01 09:00:09','t')";
    $result = errortrap_odbc($conn, $sql);
    # Insert data into the table with odbc_prepare and odbc_execute
    $values = array(2,2.28,'abcdefg1234567890','1901-01-01','23:12:34','1901-01-01 09:00:09','t');
    $statement = odbc_prepare($conn,"INSERT into test values(?,?,?,?,?,?,?)");
    if(!$result = odbc_execute($statement, $values)) {
        echo "odbc_execute Failed!" . "\n";
    } else {
        echo "Success: odbc_execute." . "\n";
    # Get the data from the table and display it
    $sql = "SELECT * FROM TEST";
    if($result = errortrap_odbc($conn, $sql)) {
        while($row = odbc_fetch_array($result) ) {

    # Drop the table and projection
    $result = errortrap_odbc($conn, $sql);
    # Close the ODBC connection