Problem getting Vertica to work with php PDO

Tobbi_Filteau_1

Hello there, for the last two days, I've been struggling with a very strange bug while I'm connecting with Vertica using php PDO. You see, the following script works:

$stmt = $c->prepare("SELECT * FROM myClients WHERE ClientNum = 88");
$stmt->execute();

After that, I loop through the results and display them no problem. This basically means my connection is correct (otherwise I wouldn't get anything out of the database). On the other hand, the following makes the Apache server reset the connection completely (no error message whatsoever and nothing in the error log):

$stmt = $c->prepare("SELECT * FROM myClients WHERE ClientNum = :cl");
$stmt->bindValue(":cl", 88);
$stmt->execute();

The problem is present both in Linux and Windows and I'm using Vertica version 7.0.2-1 along with the corresponding ODBC driver. Did anyone ever get this error?

Thanks in advance,

Tobbi

norbert_krupa

You had asked this question on Stack Overflow. I'll say it again, you need error handling to generate an actual error. Guessing what could be wrong is just not realistic.

Prasanta_Pal

Can you put the entire php code including the connection string do a quick test in our lab and reproduce the issue?

Tobbi_Filteau_1

Here's the complete code for my test script (note that I obfuscated some of the parameter in the connection string for security purposes).

$c = new PDO("odbc:Driver=Vertica;Server=192.168.1.49;Port=5433;Database=db;", "MyUser", "MyPassword");
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$c->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
//$c->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try
{
    $stmt = $c->prepare("SELECT * FROM myClients WHERE ClientNum = :cl");
    $stmt->bindValue(":cl", 88);
    $stmt->execute();

    while($res = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo $res['noClient'] . "<br>";
    }
}
catch(Exception $e)
{
    echo $e->getMessage();
}

Tobbi_Filteau_1

So in the end, I was able to program my way around the problem with some wrapper classes. I posted my code on stackoverflow. Here's the link:

http://stackoverflow.com/questions/26149956/vertica-and-pdo-prepared-statements/

I hope this helps.