Search code examples
pervasivepervasive-sql

Pervasive SQL: Can a client send multiple SQL Statements in one Database Request?


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?


Solution

  • 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;