[unixODBC & PDO] execute two queries one by one in single "session"

Forum to discuss topics related to accessing and using Vertica via PHP

Moderator: NorbertKrupa

Post Reply
vaitac
Newbie
Newbie
Posts: 1
Joined: Wed Jun 03, 2015 1:06 pm

[unixODBC & PDO] execute two queries one by one in single "session"

Post by vaitac » Tue Jun 09, 2015 4:04 am

Hello Everyone,

I have following issue. I need to setup specific option for vertica Vertica Analytic Database v7.1.1

I'm using WITH clause (http://my.vertica.com/docs/7.1.x/HTML/i ... Clause.htm) and option ENABLE_WITH_CLAUSE_MATERIALIZATION it allows to increase performance of query.

Everything works from vsql client:
\i /home/user/query.sql

file query.sql contains:
SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
WITH
basic AS (...),
purchases AS (...),
...
...
activity AS (...)
SELECT
...
FROM each_day dr
JOIN ..;

this way it works. but I need this query to be executed thru ODBC and then fetched.

I use PDO (ex: http://jservedio.com/article/4) and when I'm running them as single query then I'm getting following error:

Code: Select all

Cannot insert multiple commands into a prepared statement
I was plaing with PDO attributes like:

Code: Select all

PDO::ATTR_EMULATE_PREPARES
PDO::ATTR_AUTOCOMMIT
I've tried to create single instance of PDO and then call each query one by one but nothing happens:

Code: Select all

    
    $r = $instance->query("SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION')");    
    $r = $instance->prepare("SELECT show_current_vertica_options()");
    $r->fetchAll();
last fetchAll() return empty array like no option set. Here is what I've expected to see:

Code: Select all

db => SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
 add_vertica_options 
---------------------
 Options Set
(1 row)

=> SELECT show_current_vertica_options();
                          show_current_vertica_options                          
--------------------------------------------------------------------------------
 Opt Vertica Options
--------------------
ENABLE_WITH_CLAUSE_MATERIALIZATION
Does anyone have any idea hot to enable this option thru ODBC and keep it alive for next query, this option improves the speed of my query, it become aroud 5 times faster.

Thank you!

Post Reply

Return to “PHP”

Who is online

Users browsing this forum: No registered users and 1 guest