You must read C/C++ before connecting to Vertica through PHP. The following example ODBC configuration entries detail the typical settings required for PHP ODBC connections. The driver location assumes you have copied the Vertica drivers to /usr/lib64.
Verify the Vertica UnixODBC library can load all dependant libraries with the following command (assuming you have copies the libraries to /usr/lib64):
For example:
ldd /usr/lib64/libverticaodbc.so
You must resolve any "not found" libraries before continuing.
Install the PDO and ODBC PHP extensions. On Linux, these are available as the following packages:
php-odbc
php-pdo
2 - PHP unicode support
PHP does not offer native Unicode support.
PHP does not offer native Unicode support. PHP only supports a 256-character set. However, PHP provides the UTF-8 functions
utf8_encode() and
utf8_decode() to provide some basic Unicode functionality.
See the PHP manual for strings for more details about PHP and Unicode.
3 - Querying the database using PHP
The example script below details the use of PHP ODBC functions to connect to the Vertica Analytics Platform.
The example script below details the use of PHP ODBC functions to connect to the Vertica Analytics Platform.
<?php
# Turn on error reporting
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
# A simple function to trap errors from queries
function odbc_exec_echo($conn, $sql) {
if(!$rs = odbc_exec($conn,$sql)) {
echo "<br/>Failed to execute SQL: $sql<br/>" . odbc_errormsg($conn);
} else {
echo "<br/>Success: " . $sql;
}
return $rs;
}
# Connect to the Database
$dsn = "VerticaDSNunixodbc";
$conn = odbc_connect($dsn,'','') or die ("<br/>CONNECTION ERROR");
echo "<p>Connected with DSN: $dsn</p>";
# 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 = odbc_exec_echo($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 = odbc_exec_echo($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 0
9:00:09','t');
$statement = odbc_prepare($conn,"INSERT into test values(?,?,?,?,?,?,?)");
if(!$result = odbc_execute($statement, $values)) {
echo "<br/>odbc_execute Failed!";
} else {
echo "<br/>Success: odbc_execute.";
}
# Get the data from the table and display it
$sql = "SELECT * FROM TEST";
if($result = odbc_exec_echo($conn, $sql)) {
echo "<pre>";
while($row = odbc_fetch_array($result) ) {
print_r($row);
}
echo "</pre>";
}
# Drop the table and projection
$sql = "DROP TABLE TEST CASCADE";
$result = odbc_exec_echo($conn, $sql);
# Close the ODBC connection
odbc_close($conn);
?>
Example output
The following is the example output from the script.