Skip to main content
Question

How to abort execution of series of statements on error when using ODBC (PHP PDO)

  • January 14, 2020
  • 3 replies
  • 17 views

brocha

Hello,
I am running a number of statements inside a single ODBC command and I would like the processing to abort if an error is encountered.
I saw posts about how to do this in vsql
https://forum.vertica.com/discussion/239932/exiting-a-vsql-script-following-an-error
and in DBVisualizer
https://forum.vertica.com/discussion/239934/exiting-a-dbvisualizer-script-following-an-error
But not for my case
I am using PHP PDO which wraps ODBC and this is how I connect:
$this->pdo = new PDO('odbc:' . $dsn, $QDb->User, $QDb->Password, array(
PDO::ATTR_EMULATE_PREPARES=>false,
PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));
Thanks,
Brocha

3 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • January 14, 2020

brocha
  • Author
  • New Participant
  • January 14, 2020

Thanks for your quick response.
In my case I do some insert/merge statements and the last statement in the series truncates the staging table. I don't want the truncate to happen if there was an error on the insert or the merge. I want the whole operation to abort. Since I am using a wrapper to the ODBC driver I dont have low level access to odbc functions. Is there anything I can include in the SQLs to force the abort? Something like
\set ON_ERROR_STOP ON;
but that will work for ODBC?


Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • January 14, 2020

I don't see an equivalent option to STOP_ON_ERROR for ODBC. The best option is probably to wrap the script excluding the TRUNCATE into a transaction, i.e. beginTransaction - script - check for error - commit on success, rollback on error.
The TRUNCATE statement in Vertica auto-commits and can't be rolled back so it needs to be executed separate from the script following the commit on success.