[TKitaina]

Dear Experts,
I created a new workspace named "compliance". And I wanted to use function executeQuery($sql, $DBConnectionUID) in my Plugin. The wiki has a description: "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."
Question: How can I find the $DBConnectionUID value for a new workspace named "compliance"?

[Ironguts25]

One way of doing this is to create a database connection to other workspace database using the PM interface:

https://wiki.processmaker.com/3.2/Database_Connections#Creating_a_New_Database_Connection

Then access the newly created connection and click the Show ID button.

https://wiki.processmaker.com/3.2/Database_Connections#Managing_Database_Connections

Hope this is helpful.

[TKitaina]

Ironguts25, thanks! My main goal was to use the function executeQuery() in my plugin (in the new workspace 'compliance') to fetch data from the main workspace 'workflow' (from the table 'users'). But it turns out that DBConnectionUID should be necessarily associated with a specific Process. I do not configure Database Connections in processes, since I only use internal MySQL DB. As far as I can understand it in this case it is enough to specify the DB name in the sql query in another workspace?
For example: SELECT * FROM wf_ $ pmWorkspace.users
Is this true?

[Ironguts25]

Hello TKitaina,
Any database outside the current workspace database, requires a Database Connection in order to execute PM query's on it (such as execQuery()). Since the DB you want to query is not the current workspace DB, you need the database ID in order to run the query successfully. Unfortunately, your example will not work.

[TKitaina]

Hello, Ironguts25. It is very strange. This construction works for me.
I am using PM version 3.1.3 Community (bitnami). There are two workspaces:
1) 'workflow' - MySQL DB 'bitnami_pm',
2) 'compliance' - MySQL DB wf_compliance'.
In the Trigger of the Process for the Workspace named 'compliance', I use the construct:

Code: Select all

//$complianceUserName - User Name in Workspace named 'compliance'
$sql = "SELECT usr_uid FROM bitnami_pm.users WHERE usr_username ='$complianceUserName' limit 1";
try {
	$result = executeQuery($sql);
	$wfUserUID = $result[1]['usr_uid']; //User UID in Workspace named 'workflow'
} 
catch (Exception $e) {
	$wfUserUID = null;
	echo $e->getMessage();
}

And it works! Why?
P.S. Naturally, before that, I ran the command in MySQL:
GRANT SELECT ON bitnami_pm.* TO {user DB wf_compliance'}@'localhost';

[Ironguts25]

Strange indeed, because the PM documentation says "Do NOT include the database name in the SQL statement and do NOT use `...` (back ticks) to identify fields and tables."

https://wiki.processmaker.com/3.2/ProcessMaker_Functions/Database_Functions

Potential security or database integrity issue perhaps? Maybe I'm missing something but if it works that's great.