[unixODBC & PDO] execute two queries one by one in single "session"
Posted: 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:
I was plaing with PDO attributes like:
I've tried to create single instance of PDO and then call each query one by one but nothing happens:
last fetchAll() return empty array like no option set. Here is what I've expected to see:
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!
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
Code: Select all
PDO::ATTR_EMULATE_PREPARES
PDO::ATTR_AUTOCOMMIT
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();
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
Thank you!