Data mirroring to SQL server

[PMM_CAI]

Greetings. We have a client that upgraded to PxPlus 2019 Update 2 a few months ago. Before their upgrade, we had been successfully using data mirroring to push their data to a SQL server. We have an AR file for them where the key is Customer + Invoice Number + Invoice Type. Invoice Type is a single character element, and it can be a blank space. (In fact, it frequently is; the ones with alphanum in it are less common.)

When the record is new, the mirroring/insert works fine, for any invoice record.
But when it is updating an existing record, the mirroring/update does not result in the table getting the updated data...if the record's Invoice Type is a blank. If the Type has an alphanum in it, it successfully updates the SQL table.

We have not gotten to the point yet of trying to step through it because of the complexity and the timeframe to dig down into it. And I'm not sure if watching our PxPlus command of write(channel) will tell us anything. Does this information trigger any sort of "aha!" explanation? TIA.

[Stéphane Devouard]

How is the invoice type defined in the SQL database ?


If it is defined as varchar(1), I would try char(1) instead and see if that changes anything.


write(channel) may not tell you anything per se, however if you have issued :

Code Select Expand

SET_PARAM '!Q' = 1


Then it will tell you the exact SQL statement that is generated. There should be several statement generated, by the way. As you use WRITE(), the system does not know if the record exists or not, so it should try an INSERT first, and then an UPDATE if the INSERT fails.


You could also change your code and replace the WRITE () by something like :

Code Select Expand

IF newRecord = 1 THEN INSERT(...) ELSE UPDATE (...)


And see if that helps as well.