3.2 - 3.8 - Processmaker Functions - Database Functions

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.

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 and do NOT use `...` (back ticks) to identify fields and tables. 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:

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:

Note 1:

Note 2:

Example 2:

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

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:

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.

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.