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

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

Jim_Knicely

Hi,
There are examples of using SQLGetDiagRec here:

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConnectingToVertica/ClientODBC/TrackingLoadStatusODBC.htm?https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConnectingToVertica/ClientODBC/RetrievingDataThroughODBC.htm

See:
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdiagrec-function

brocha

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

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.