I have a MFC CMap object, each object stores 160K~ entries of long data. I need to store it on Oracle SQL. We decided to save it as a blob. Since we do not want to make additional table, we also thought about saving it as local file, with the SQL column pointing to that file. We would prefer to just keep it as blob on the server and clear the table every couple of weeks.
The table has a sequential key as ID, and 2 column of time. I need to add the blob column in order to store on every row that CMap.
Can you recommend a guide to do this approach (read/write Map to blob or maybe a clob)? Thanks.
I do not think this is a great approach:
- For starters Oracle BLOB/CLOB handling in the ODBC and MFC classes is
not great and can be subject to some non-trivial rules (e.g. The LOB
columns must be last in the select statement, positional updates are
not always possible). - Secondly, by hiding it all in the blob you
lose ability to easily query content (e.g find only the records that
have the “sample value” in the map), reorganize into a different
form. - Thirdly, if the content of the BLOB references other DB
items, you can miss out on validation and ability to maintain the
integrity if the referenced data changes.
It is difficult to see a downside in just normalizing the data and storing it in the separate, referenced table. You can even assemble it into a BLOB later with a stored procedure, if that is a requirement.
You’re looking for serialization, which turns a structured object into a blob. MFC has explicit support for it.