Transaction with custom sql exec not updating results

[rednazkela]

Context:
I'm using JSON fields on MySQL 8 though a custom function that allows me to handle all changes on MySQL instead of retrieving whole object to PHP, modify it and store it again, that must be the basic approach of propelorm on this data type.

I've created a custom setXXX method that takes the data to be modified and a Connection Interface as parameters to acomplish this but things gets weird at this point.

I'm testing the use cases for this with PHPUnit doing the following:

1. Create an object with a JSON field set to empty json
   $obj = new \TableName();
   $obj->setXXX('{}'): // Using setter method provided by PropelORM
   $obj->save($con);

all asserts pass at this point

2. Modify JSON field with custom method
   $obj->setXXXJSON('{"action":"set", "prop":"val"}', $con);

   1. This method internally call the MySQL function that creates a SQL stament that resolves that given instruction and returns it as string stored at $stmt
   2. Then that code is executed using $con->exec($stmt)

3. Save method is not used because there's any change to the proper object but it needs to be refreshed to get the new JSON Field Value
   $obj = TableNameQuery::create()->findOneById($obj->getId());

at this point equality assert fails because
$obj->getXXX():
Expected: {"prop":"val"}
Actual: "{}"
At this point you may say... well your custom function didn't affect the database
BUT looking at MySQL table directly the actual value of JSON Field is {"prop":"val"}

Issue:
This means that 2. Modify JSON field with custom method actually happend but i cannot get that value back even though its stored at the database and instead I get the previous unmodified version of it

I've tried to create a new object
$other = \TableNameQuery::create()->findOneById($obj->getId());
But it shows the same field without been affected
$other->getXXX():
Expected: {"prop":"val"}
Actual: "{}"

While writing this I realized that I can change this from fuction to procedure to be executed internally by MySQL without the need to return the statement string to PHP to do that.
Also I'll try to create a new accesor method using custom sql to get this particular field to see if I'm able to see the change.

Thanks!

[DavidGoodwin]

tried turning instance pooling off?

[rednazkela]

tried turning instance pooling off?

Just tried with this:
Propel::disableInstancePooling();
and I see no difference.

I made a custom accesor getXXXJSON($con) and it succesfully see those changes using
$sql = 'Select field from tablename where id = :id';
$id = $this->getIdnentest();
$stmt = $connection->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();

[mentalstring]

My bet was also on instance pooling. Maybe activate query logging on mysql (SET global general_log = 1) to see if your "reload" query actually hits the server?

Or, since the title speaks of transactions, another idea is if your custom mysql function hasn't yet committed the changes in a way that the connection session of the reload sees them.

[rednazkela]

@DavidGoodwin @mentalstring
I finally made it, disabling instance pooling was winning shot I just put it in the wrong place.
Finally, I ended up wrapping just the reload of the object in disable/enable pooling to keep all benefits of it in the rest of the code an its working fine.

Propel::disableInstancePooling();
$obj = TableNameQuery::create()->findOneById($obj->getId());
Propel::enableInstancePooling();

Thank you very much.

P.D. Do you know a way to handle DML senteces with JSON capabilities through PropelORM?

[DavidGoodwin]

P.D. Do you know a way to handle DML senteces with JSON capabilities through PropelORM?

No; sorry; beyond my pay grade ;-) (not something I've had to think about / use)