Description

Execute a stored procedure against the connection.

Used for UPDATE, INSERT, and DELETE statements.

Syntax

incendium.db.o_execute_non_query(stored_procedure, out_params, [database], [transaction], [in_params])

Args:

• stored_procedure (str): The name of the stored procedure to execute.
• out_params (listOutParam): A list containing all OUTPUT parameters as OutParam objects.
• 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.
• in_params (listInParam): A list containing all INPUT parameters as InParam objects. Optional.

Returns:

• tuple: A tuple containing the number of rows modified by the stored procedure, or -1 if not applicable, and a Python dictionary of OUTPUT parameters.

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, OutParam
from java.lang import Exception as JavaException


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

    try:
        # Build params.
        in_params = [
            InParam("int_param", system.db.INTEGER, 1),
            InParam("decimal_param", system.db.DECIMAL, 1.2345),
            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()),
        ]
        out_params = [OutParam("out_int_param", system.db.INTEGER)]
        # Call stored procedure.
        # TODO: Do something with the update_count and output_params
        # returned by o_execute_non_query
        update_count, output_params = db.o_execute_non_query(
            "schema.stored_procedure",
            out_params=out_params,
            transaction=transaction_id,
            in_params=in_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, output_params