From a client-application, is it possible to send multiple SQL statements at once to PSQ v13?
I have a situation where I'd like to send both a delete statement and an insert statement in one call via ODBC. It would also be ideal if I could do this as a transaction so that both statements either succeed or fail.
Are either of these things possible without creating a server-side stored procedure and if so what is the proper syntax for combining multiple statements?
There is not a way to execute both statements and guarantee that both succeed without using a transaction. Transactions are only available within Stored Procedures.
What you're describing (executing a DELETE
and INSERT
statement in an atomic manner) is perfectly suited for a Stored Procedure using a transaction.
Something like this is a good start:
CREATE PROCEDURE TransactionTest (:ID integer)
RETURNS (
ID INTEGER);
BEGIN
START TRANSACTION;
DELETE FROM Table where ID = :ID;
SELECT MAX(ID) from table;
COMMIT WORK;
END;