PostgreSQL Boolean data type

Products

• CA Plex

Issue/Introduction

CA Plex does not support boolean data type as a target database column out of the box.

PostgreSQL is not an officially supported database for CA Plex, though many customers are successfully using it as a target database.

Below steps outline a possible workaround to make CA Plex built Java application work with PostgreSQL boolean datatype. 

Environment

CA Plex 7.2.x and 7.3

Resolution

The local model we will be considering here is having below modeling details: 

C++ Client - Java Server

1. CA Plex modelling changes

CA Plex datatype field that fits well for Plex to PostgreSQL database Boolean datatype mapping is:

• Inherit the field (e.g. Char2BlnColMap in this example here) from OBJECTS/*Boolean
• Add field length 2
• Add triple Control SYS as Check Box

Keep Literal values as ‘t’ and ‘f’ for *True and *False value objects respectively.

2. PostgreSQL database changes

Using aforementioned field Char2BlnColMap that gets mapped to PostgresSQL Boolean datatype column, ensure to update the CA Plex generated Char2BlnColMap (impl name here – aa1ba) column in PostgreSQL Table from character to boolean data type.

Sample queries to change the column from character type to boolean type: 

ALTER TABLE aa3t drop column aa1ba cascade;      -- Cascade option to drop column in views as well.
ALTER TABLE aa3t ADD aa1ba boolean;

Note: As part of dropping the column in above SQL query, associated views will be deleted. Rebuild only the respective views from CA Plex IDE again.

3. Generated code changes

Updates required in the functions scoped by the views, as an example using Foundation/EditDetail entity

• Generate the Entity (e.g. PlexPostgresSQLBlnCharMap)
  • Inherited from Foundation/EditDetail, Storage/RelationalTable entities
  • Having aforementioned field Char2BlnColMap mapped to Boolean datatype in PostgreSQL
• Generate the Entity > PlexPostgresSQLBlnCharMap
• Edit the below generated functions:
  • PlexPostgresSQLBlnCharMap Update.InsertRow

Open InsertRow function’s source code and locate for the “Insert into” SQL statement.
Update the SQL statement for the field Char2BlnColMap accordingly to produce True/False value instead of character value that is associated with the Char2BlnColMap string value.

• PlexPostgresSQLBlnCharMap.Update.UpdateRow
  

Open UpdateRow function’s source code and locate for the “Update” SQL statement.
Update the SQL statement for the field Char2BlnColMap accordingly to produce True/False value instead of character value that is associated with the Char2BlnColMap string value.

PlexPostgresSQLBlnCharMap.Fetch, PlexPostgresSQLBlnCharMap.BlockFetch works as expected with the field mapped (no specific changes required).

Build the updated sources and verify the built application.  The database operations create/read/update/delete should work as expected with the built application.

Additional Information

Formal solution being worked on via CA Plex feature backlog.  Interested end users are encouraged to subscribe to this KB article for any updates.