The Vertica Database Forums

Posted: **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.

Posted: **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.

Posted: **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 http://php.net/manual/en/function.odbc-execute.php?

Thanks
Marcello

Posted: **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

Posted: **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 libverticaodbc.so based on your installed version.

Code: Select all

[VerticaDSN]
Description = VerticaDSN ODBC driver
Driver = /opt/vertica/lib64/libverticaodbc.so.6.1.1
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

[Driver]
ODBCInstLib = /usr/lib64/libodbcinst.so
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
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1

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

[ODBC]
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


<?php


    # 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) ) {
            print_r($row);
        }
    }


    # Drop the table and projection
    $sql = "DROP TABLE TEST CASCADE";
    $result = errortrap_odbc($conn, $sql);
    # Close the ODBC connection
    odbc_close($conn);
?>

Patrice

The Vertica Database Forums

PHP and Vertica (on linux)

PHP and Vertica (on linux)

Re: PHP and Vertica (on linux)

Re: PHP and Vertica (on linux)

Re: PHP and Vertica (on linux)

Re: PHP and Vertica (on linux)