PHP and Vertica (on linux)

Moderator: NorbertKrupa

Post Reply
Posts: 6
Joined: Wed Jan 25, 2012 8:11 pm

PHP and Vertica (on linux)

Post by Tom » Wed Mar 28, 2012 7:50 pm

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.
Last edited by Tom on Mon Mar 25, 2013 2:46 pm, edited 1 time in total.

Posts: 16
Joined: Fri May 11, 2012 12:25 pm

Re: PHP and Vertica (on linux)

Post by DataWhisperer » Sat May 12, 2012 8:22 am

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.

Posts: 1
Joined: Thu Nov 08, 2012 2:29 am

Re: PHP and Vertica (on linux)

Post by marcellodesales » Thu Nov 08, 2012 2:36 am

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


Posts: 1
Joined: Thu Jan 10, 2013 11:01 am

Re: PHP and Vertica (on linux)

Post by hanswilmink » Thu Jan 10, 2013 11:05 am

Tom, could you update a bit about your status in this topic? :) i will follow you for a bit :) i'm interested :D

Posts: 20
Joined: Mon Feb 18, 2013 1:37 am

Re: PHP and Vertica (on linux)

Post by pborne » Sun Mar 24, 2013 5:32 pm

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

Post Reply

Return to “PHP”