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