JobDBInterface

JobDBInterface

SQL Query parameter types

Value	Description
clob	Parameter type clob (character large object).
timestamp	Parameter type timestamp (date and time value).
decimal	Parameter type decimal (fixed-precision value).
integer	Parameter type integer (whole numbers that range from -2,147,483,647 to 2,147,483,647).
text	Parameter type text (textual data)

Interface & Methods

interface JobDBInterface
{
    /**
     * Quotes a literal string.  This method is NOT meant to fix SQL injections! It is only meant to escape this
     * platform's string literal quote character inside the given literal string.
     *
     * @param mixed $value A literal string to quote
     * @param string|null $type Type to which the value should be converted to
     *
     * @return mixed according to value; otherwise null in case of error.
     *
     * @throws \JobRouterException
     */
    public function quote($value, ?string $type = null): mixed;

    /**
     * Executes an SQL statement and return the number of affected rows.
     *
     * @param string $sql SQL query
     *
     * @return mixed according to sql.
     *
     * @throws \JobRouterException
     */
    public function exec(string $sql): mixed;

    /**
     * Executes an SQL statement, returning a result set as a Statement object.
     *
     * @param string $sql SQL query
     *
     * @return \JobRouter\Sdk\ResultInterface|false SQL result set; otherwise false in case of error.
     *
     * @throws \JobRouterException
     */
    public function query(string $sql): \JobRouter\Sdk\ResultInterface|false;

    /**
     * Return a dataset from the result as an associative array.
     * If there are no more datasets, a null will be returned, false will be returned on error.
     *
     * @param \JobRouter\Sdk\ResultInterface $result Object returned by query()
     *
     * @return mixed data set as array, null or false in case of error.
     *
     * @throws \JobRouterException
     */
    public function fetchRow(\JobRouter\Sdk\ResultInterface $result): mixed;

    /**
     * Method returns all rows of the query result.
     * If an error occurs, the method returns false.
     *
     * @param \JobRouter\Sdk\ResultInterface $result result object returned by query()
     *
     * @return mixed array or false in case of error
     */
    public function fetchAll(\JobRouter\Sdk\ResultInterface $result): mixed;

    /**
     * Returns the first column of the first data set.
     * If an error occurs, the method returns false.
     *
     * @param \JobRouter\Sdk\ResultInterface $result result object returned by query
     *
     * @return mixed prepared statement; otherwise false.
     */
    public function fetchOne(\JobRouter\Sdk\ResultInterface $result): mixed;

    /**
     * Prepare an optionally parametrized SQL selection.
     *
     * @param string $sql SQL Query to prepare
     * @param array $params Optional sql query parameter
     * @param array|null $types Optional sql query parameter types
     *
     * @return \JobRouter\Sdk\ResultInterface|int|false Prepared result set; otherwise an integer based on sql
     * select statement or false.
     */
    public function preparedSelect(
        string $sql,
        array $params,
        ?array $types = [],
    ): \JobRouter\Sdk\ResultInterface|int|false;

    /**
     * Executes an optionally parametrized SQL selection.
     *
     * @param string $sql SQL Query to prepare
     * @param array $params Optional sql query parameter
     * @param array|null $types Optional sql query parameter types
     *
     * @return \JobRouter\Sdk\ResultInterface|int Prepared result set; otherwise an integer based on sql select
     * statement or false in case of error. The error message can then be retrieved via getErrorMessage.
     */
    public function preparedExecute(
        string $sql,
        array $params,
        ?array $types = [],
    ): \JobRouter\Sdk\ResultInterface|int;

    /**
     * Get additional user-supplied error information.
     *
     * @return string user-supplied error information; otherwise an empty if no error message exist
     */
    public function getErrorMessage(): string;
}

Example

<?php

use Doctrine\DBAL\Exception;
use JobRouter\Sdk\ConnectionManagerInterface;
use JobRouter\Sdk\ResultInterface;

return function (ConnectionManagerInterface $connectionManager): void {
    echo '<h1 style="color: #fc0">SDK Example to demonstrate the ConnectionManagerInterface and JobDBInterface!</h1>';
    echo '<h3 style="color: #fc0;">JobRouter Users (internal connection) - getJobDB()</h3>';

    $jobDB = $connectionManager->getJobDB();
    $result = $jobDB->query('SELECT COUNT(*) FROM JRUSERS');
    $count = $jobDB->fetchOne($result);
    // Do something with the $count

    $result = $jobDB->query('SELECT lastname, prename, supervisor FROM JRUSERS');
    if ($result === false) {
        echo '<h3 style="color: #f44;">ERROR!</h3>';
        echo '<p style="color: #f44;">Message: ' . $jobDB->getErrorMessage() . '</p>';
    } else {
        $users = $jobDB->fetchAll($result);
        // Do something with the $users
    }

    try {
        $preparedResult = $jobDB->preparedSelect(
            'SELECT DISTINCT language_name FROM JRPROCESSLANGUAGES WHERE processname = :processname AND version = :version ORDER BY language_name',
            [
                'processname' => 'example_processname',
                'version' => 1,
            ],
            [
                'text',
                'integer',
            ],
        );

        $result = $preparedResult->fetchOne();
        // Do something with the $result
    } catch (\JobRouterException|Exception $e) {
        echo '<h3 style="color: #f44;">ERROR!</h3>';
        echo '<p style="color: #f44;">Message: ' . $e->getMessage() . '</p>';
    }

    echo '<h3 style="color: #59aa6e;">JobRouter (Global connection) - getDBConnection(\'GC_JOBDATA\')</h3>';
    try {
        $gcJobdata = $connectionManager->getDBConnection('GC_JOBDATA');
        $data = $gcJobdata->query('SELECT COUNT(*) FROM DATA_TABLE');
        $dataObject = $data->fetchOne();
        // Do something with the $dataObject
    } catch (\JobRouterException|Exception $e) {
        echo '<h3 style="color: #f44;">ERROR!</h3>';
        echo '<p style="color: #f44;">Message: ' . $e->getMessage() . '</p>';
    }

    echo '<h3 style="color: #59aa6e;">JobRouter (Global connection) - getDBConnection(\'GlobalConnectionFromJobRouterDosNotExists\')</h3>';
    try {
        $connectionManager->getDBConnection('GlobalConnectionFromJobRouterDosNotExists');
    } catch (\JobRouterException|\Exception $e) {
        echo '<h3 style="color: #f44;">ERROR!</h3>';
        echo '<p style="color: #f44;">Message: ' . $e->getMessage() . '</p>';
    }
};