Page 1 of 1

Connecting to Vertica with Wamp (php on windows)

Posted: Mon Mar 25, 2013 8:22 pm
by Tom
Install WAMP Server: http://www.wampserver.com/en/

In your php extension turn on php_pdo_odbc (left click on the icon in the menu bar, go to PHP, PHP extensions)

Follow this guide to setup your dsn, but use the system dsn instead of user dsn: viewtopic.php?t=119 (thanks, knicely87)

Steal the code from here: viewtopic.php?f=51&t=129 (thanks, pborne)
(Fill in username and password).

Code: Select all

<?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,'username','password') 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);
?>

This should get you started.

Re: Connecting to Vertica with Wamp (php on windows)

Posted: Thu Mar 28, 2013 2:54 pm
by debfawn
Thanks, Tom! Great example!