Database Functions

executeQuery()

executeQuery() executes an SQL statement in a database connection or in one of ProcessMaker's internal databases.

Note: To avoid database integrity issues when SQL queries are executed directly in ProcessMaker Core tables, the executeQuery() function has been improved to protect Processmaker Core tables and PM_Tables defined by the user. For more information, see Protecting Processmaker Core Tables and PM_Tables.

variant executeQuery(string SQLStatement, string DBConnectionUID='workflow')

Parameters:

  • string SQLStatement: The SQL statement to be executed, which must begin with SELECT, INSERT, UPDATE, DELETE, EXECUTE, EXEC, SHOW, DESCRIBE, EXPLAIN or BEGIN. To learn basic SQL syntax, see this tutorial.
    Do NOT include the database name in the SQL statement. For example, use "SELECT * FROM USERS", instead of "SELECT * FROM wf_workflow.USERS". SQL key words such as "SELECT" and "WHERE" are case insensitive and can be written as "select" and "Where". If ProcessMaker is installed in Windows, the table and field names in its MySQL databases are also case insensitive by default, but in UNIX/Linux they are case sensitive by default (although this can be changed with the lower_case_table_names setting). If consulting ProcessMaker databases, it is recommended that all table and field names be in UPPERCASE in the SQL statement, so the processes will be exportable between Windows and UNIX/Linux.
  • string DBConnectionUID: Optional. The unique ID of the database connection where the SQL statement will be executed. The unique ID is a string of 32 hexadecimal numbers that uniquely identifies database connections, and can be found by opening a process, going to DATABASE CONNECTIONS, and clicking on its Show ID button in the list.

    If this parameter is not included, the SQL statement will automatically be executed in the wf_<WORKSPACE> database of the current workspace, which is named wf_workflow by default. In ProcessMaker 3, each workspace has only 1 database by default, but each workspace upgraded from version 2 will have three databases by default, which can be accessed using the following prefixes:
    • 'workflow' for the wf_<WORKSPACE> database.
    • 'rbac' for the rb_<WORKSPACE> database.
    • 'rp' for the rp_<WORKSPACE> database.
    To access a ProcessMaker database in a workspace that is not the current workspace, create a Database Connection to the database and use its UID.

Return value:

If executing a SELECT, EXECUTE, EXEC, SHOW, DESCRIBE, EXPLAIN or BEGIN statement, the function returns an associative array of associative arrays in the format:

array( '1' => array( 'FIELD1' => 'VALUE1', 'FIELD2' => 'VALUE2', ... ), '2' => array( 'FIELD1' => 'VALUE1', 'FIELD2' => 'VALUE2', ... ), ... )

The counting of the outer array starts from 1 (not 0 like a normal PHP array). All field values returned by SELECT statements will be strings, regardless of their original type in the database. In most cases, it is not necessary to explicitly convert the values with type casting, because PHP will gracefully convert integers and floating point numbers automatically if a field value is used in a mathematical operation. Datetime controls in Dynaforms treat dates as strings, so date values generally do not need to be converted either.

For INSERT statements, the function returns 1 if the operation was successful; otherwise it returns 0.

For UPDATE and DELETE statements, the function will return the number of affected rows.

If an error occurs in the database query, it returns a string containing the error message, and subsequent executeQuery() functions that call to the same database in the same trigger will not execute correctly.

If the SQL statement starts with an unrecognized SQL command, then executeQuery() will return FALSE.

Example 1:

Look up information about the logged-in ProcessMaker user:

$UID = @@USER_LOGGED;   #assign to normal PHP variable to insert in string
$result = executeQuery("SELECT * FROM USERS WHERE USR_UID='$UID'");
if (is_array($result) and count($result) > 0) {
   @@UserFirstName = $result[1]['USR_FIRSTNAME'];
   @@UserLastName = $result[1]['USR_LASTNAME'];
}

Note 1:

ProcessMaker system and case variables can NOT be inserted in double quoted strings, like this:

$result = executeQuery("SELECT * FROM USERS WHERE USR_UID='@@USER_LOGGED'");

Instead, ProcessMaker system and case variables first need to be assigned to normal PHP variables to be inserted inside a double quoted string, like this:

$UID = @@USER_LOGGED;   #assign to normal PHP variable to insert in string
$result = executeQuery("SELECT * FROM USERS WHERE USR_UID='$UID'");

Also note that single quoted strings are treated like literal strings, so PHP variables can NOT be inserted in a single quoted string like this:

$result = executeQuery('SELECT * USERS WHERE USR_UID=\'$UID\'');

Note 2:

String, date and datetime values in SQL queries should be enclosed within single quotes, while integer and floating-point values should NOT be enclosed within single quotes. If it is necessary to use an apostrophe in a string, escape it with a backslash. For example:

$result = executeQuery("SELECT FIRSTNAME, LASTNAME FROM CLIENTS WHERE ID=8734 AND STATUS='Don\'t wait'");

Some databases like MySQL will accept double quotation marks in place of single quotation marks around strings and dates, but it is not recommended to do this because it is not SQL standard.

When using text input by a user in a database query, it is strongly recommended to pass that text through mysql_real_escape_string() if using a MySQL database, pg_escape_string() if using a PostgreSQL database, or addslashes() for other types of databases to avoid code injection attacks.

For example, the following code uses text entered in a Dynaform field named "clientName" in the SQL query, so problematic characters are escaped with backslashes by using mysql_real_escape_string():

$db = '89445967454f5d18dd694f4084525230'; //unique ID of database connection
$client = mysql_real_escape_string(@@clientName);
$result = executeQuery("SELECT * FROM CLIENTS WHERE CLIENT_NAME='$client'", $db);
if (is_array($result) and count($result) > 0) {
   @@contractAmount = $result[1]['CONTRACT_AMOUNT'];
}

Example 2:

Look up all the users in a ProcessMaker group named "accounting":

$query = "SELECT U.USR_FIRSTNAME AS FNAME, U.USR_LASTNAME AS LNAME " .
   "FROM USERS U, CONTENT C, GROUP_USER GU WHERE " .
   "U.USR_UID = GU.USR_UID AND GU.GRP_UID = C.CON_ID AND " .
   "CON_VALUE = 'accounting'";
$result = executeQuery($query, 'workflow');

if (is_array($result)) {
   #loop to extract the returned rows
  foreach ($result as $row)
      @@GroupMembers[] = $row['FNAME'] . ' ' . $row['LNAME'];
}

Example 3:

The unique ID for the database connection is different in each process. If planning to reuse the same same code in multiple processes, this code looks up the unique ID of a database connection to an external database named "company_data" with the table "clients" at 192.168.2.100. This example also shows how to insert, update and delete records in the database:

#first look up the UID of the database connection
$proc = @@PROCESS;
$result = executeQuery("SELECT DBS_UID FROM DB_SOURCE WHERE PRO_UID='$proc' " .
   "AND DBS_DATABASE_NAME='company_data' AND DBS_SERVER='192.168.2.100'");

if (!is_array($result) or count($result) == 0)
   die("Error: Unable to look up UID for database 'company_data'!");

$db = $result[1]['DBS_UID'];
$query = "INSERT INTO CLIENTS (FNAME, LNAME, COMPANY) VALUES ('Jane', 'Doe', NULL)";

if (executeQuery($query, $db) == 0)
   die("Unable to insert record into company_data.CLIENTS table");

$query = "UPDATE CLIENTS SET COMPANY='Acme Inc.' WHERE FNAME='Jane' AND LNAME='Doe'";

if (executeQuery($query, $db) == 0)
   die("Unable to update the company_data.CLIENTS table");

$query = "DELETE FROM CLIENTS WHERE FNAME='Jane' AND LNAME='Doe'";

if (executeQuery($query, $db) == 0)
    die("Unable to delete record in company_data.CLIENTS table");

Example 4:

In this example, executeQuery() is used to look up the next available case to which the logged in user is assigned. If a case is found, then the web browser will automatically be redirected to that case with the G::header() function, otherwise the browser will be redirected to the Case List page. Before redirecting the browser, the current task is automatically routed to the next task in the process with PMFDerivateCase(). This trigger code can be added after the last step in the task to avoid displaying the screen that shows the next assigned user and the "Continue" button.

$userUID = @@USER_LOGGED;
$query  = "SELECT APP_UID, DEL_INDEX FROM APP_DELEGATION " .
   "WHERE USR_UID = '$userUID' AND DEL_THREAD_STATUS = 'OPEN' " .
   "ORDER BY DEL_DELEGATE_DATE DESC";
$queryResult = executeQuery($query);

if (is_array($queryResult) && count($queryResult) > 0)
   $caseURL = 'cases_Open?APP_UID=' .  $queryResult[1]['APP_UID'] .
       '&DEL_INDEX=' . $queryResult[1]['DEL_INDEX'];
else
   $caseURL = 'casesListExtJs';


//Route the current case to the next task in process:
PMFDerivateCase(@@APPLICATION, @%INDEX);
//Redirect the web browser to a new location:
G::header("location: $caseURL");
die();

Other examples:

To see an example using executeQuery() to access information about Dynaform file objects and write files to a database, read Accessing Files with PHP. For an example using executeQuery() to populate a grid, see Accessing Grids with PHP.