incendium.db.execute_non_query

Description

Execute a stored procedure against the connection.

Used for UPDATE, INSERT, and DELETE statements.

Syntax

incendium.db.execute_non_query(stored_procedure, [database], [transaction], [params])

Args:

• stored_procedure (str): The name of the stored procedure to execute.
• database (str): The name of the database connection to execute against. If omitted or "", the project's default database connection will be used. Optional.
• transaction (str): A transaction identifier. If omitted, the call will be executed in its own transaction. Optional.
• params (list[InParam]): A list containing all INPUT parameters as InParam objects. Optional.

Returns:

• int: The number of rows modified by the stored procedure, or -1 if not applicable.

Recommendations

We recommend using transactions for all DELETE, INSERT, and UPDATE statements, since in some situations you may be modifying more than one database table at a time.

Code Examples

import traceback

import system.date
import system.db
from incendium import constants, db, exceptions, util
from incendium.db import InParam
from java.lang import Exception as JavaException


def insert():
    # Initialize variables.
    transaction_id = system.db.beginTransaction(timeout=30000)

    try:
        # Build params.
        params = [
            InParam("int_param", system.db.INTEGER, 1),
            InParam("decimal_param", system.db.DECIMAL, 1.0000),
            InParam("varchar_param", system.db.VARCHAR, "VARCHAR value"),
            InParam("nvarchar_param", system.db.NVARCHAR, "NVARCHAR value"),
            InParam("datetime_param", system.db.TIMESTAMP, system.date.now()),
        ]
        # Call stored procedure.
        # TODO: Do something with the update_count value returned by
        # execute_non_query
        update_count = db.execute_non_query(
            "schema.stored_procedure",
            transaction=transaction_id,
            params=params,
        )
    except JavaException as exc:
        # system.db functions throw java.lang.Exception
        # Rollback transaction.
        system.db.rollbackTransaction(transaction_id)
        # Get error message to raise ApplicationError.
        message = constants.UNEXPECTED_ERROR_CAUSED_BY.format(
            util.get_function_name(),  # Function's name.
            "\n".join(traceback.format_exc().splitlines()),  # Preserved traceback.
            exc.cause,
        )
        # Raise ApplicationError.
        raise exceptions.ApplicationError(
            message, exc, exc.cause
        )  # Handle this at the event calling this function.
    else:
        # Commit transaction.
        system.db.commitTransaction(transaction_id)
    finally:
        # Close transaction.
        system.db.closeTransaction(transaction_id)

    return update_count