Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By kleung
#823964
Hi,

Grateful if I can be advise whether MySQL Temporary Table is support in PM Bitnami 3.2.1 Linux version.

I found the following sql work directly in the MySQL of the installation.
mysql> CREATE TEMPORARY TABLE TMP AS ( SELECT MEMNO, NAME FROM PMT_MEMBER WHERE NAME = 'Albert' );
MySQL could create a temporary table with all the rows where NAME = "Albert" and the temporary table TMP can be used normally as a table in that session.

However, when I put it in PM with the following statements,
$sql = "CREATE TEMPORARY TABLE TMP AS ( SELECT MEMNO, NAME FROM PMT_MEMBER WHERE " . $memname . " )" ;
$res = executeQuery($sql) ;
where $memname is a string with value "NAME = 'Albert' ",
$res returned false and the temporary table TMP could not be found.

How could I create and use temporary tables in a session?

Many Thanks

cheers,
Karl
By kleung
#823965
I would like to supplement with the following information on the Create_temporary_tables privilege in MySQL. I think PM has the privilege to create temporary tables.

mysql> select user, Create_tmp_table_priv from mysql.user ;
+-----------------+-----------------------+
| user | Create_tmp_table_priv |
+-----------------+-----------------------+
| root | Y |
| root | Y |
| root | Y |
| root | Y |
| bn_processmaker | Y |
| bn_processmaker | Y |
+-----------------+-----------------------+
User avatar
By amosbatto
#823994
The executeQuery() function doesn't support CREATE statements.

Try using this code in your trigger:
Code: Select all
    $sql = "CREATE TEMPORARY TABLE TMP AS ( SELECT MEMNO, NAME FROM PMT_MEMBER WHERE " . $memname . " )" ;
    $con = Propel::getConnection( 'workflow' );
    $con->begin();
    $rs = $con->executeUpdate( $sql );
    $result = $con->getUpdateCount();
    $con->commit();

Another is option is to change the source code of the executeQuery() function in workflow/engine/classes/class.pmFunctions.php to allow CREATE statements.
generate daily excel report

I have exported table into excel sheet as shown in[…]

Please replay if there is any solution!!!!

Task doesnt have any users.

Amosbatto: One more thing if you can help me with.[…]

Just guide me to implement this?