[kleung]

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

[kleung]

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 |
+-----------------+-----------------------+

[amosbatto]

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.

[kleung]

Hi Amos,

Many thx.
I'll try your solutions.

cheers,
karl