file in a progress DB

Hi everyone,

I'm looking for a way to store files in my progress DB (oe 10.02B) (not the file path but the file himself).
Is it possible ?
If it is, is there some limitation about it ?

best regards,

- BobyIsProgress -
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
This is possible. Search for "binary large object" or "BLOB". It is a field type that can store large binary objects, e.g. arbitrary files.

The fact that it is possible does not mean that you should. Many people decide to use BLOB or CLOB columns because they have some files (XML, PDF, images, etc.) they think should go in a database instead of a file system, and later regret it because the LOBs become very large and difficult to manage.

I suggest you (or whoever is proposing this as a solution) construct a business case for adding a BLOB column to a table, as opposed to putting the files in the file system and storing their paths in the database. What is the problem you are trying to solve? Why do you think BLOBs are the appropriate solution? Do you know how this will affect: database maintenance, backups, disaster recovery, database repair? Are you aware of the bugs/limitations of working with BLOBs in 10.2B? Have you read all the relevant docs and KB articles?
 

TomBascom

Curmudgeon
10.02B does not exist. 10.2B does exist but it is ancient, obsolete and unsupported. You should upgrade. OpenEdge 12 has been the current release for almost a year.

Upgrading is easy, safe and effective.

Staying on old releases is painful and dangerous.
 

Cringer

ProgressTalk.com Moderator
Staff member
From my own experience (working on an inherited system in the past), storing files in the database is a very bad idea. The database bloats very quickly, even if you compress the files before storing. As a result, all other maintenance jobs (backups, dumps and loads, restores, Replication synch etc) all take a lot longer than they should.

This doesn't mean you can't do it with a bit of planning though. If I had to store them in the database though, I would consider adding them to a separate database that is only for BLOBs. That way only that database bares the brunt of the administration overheads.

I also second the comments above about ensuring you are on a modern release.
 
Hi,

First I know since I subscribed on this forum that we have to update to a more newer version of progress. I also want to do it but it's not my call to make.

Second about the main object of this topic.
We first thought of a filesystem solution. The main issue for us is the credential attribute. In fact we were thinking of making a share folder on our network and give everyone the reading power. But for some privacy issue it would allowed some smart thinking people to find out the folder location and so all the file that we doesn't him to find.
Thats why we were looking for a database way because it's more easyer to manage. And for us we would have done it by creating a dedicating database.

But I'm open to idea & solution. So if you have some use case I would like to hear about it.

Best Regards,

- Vivien -
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
We first thought of a filesystem solution. The main issue for us is the credential attribute.
I have been involved in discussions like this. In my experience it often comes down to someone saying, in not so many words, "I know more about how to secure a database than how to secure a file system."

If users should only have access to some of the files in question, or maybe none, then either (a) they shouldn't have shell or file-sharing access to that server at all, or (b) they shouldn't have shell or file-sharing access to the parent of the directories that contain the files in question, or anything below the parent. Preferably, as this is a back-end server, they will have no access at all. Then the only way they should be able to get at the data is via the application which should have its own authorization system and logic. And in that case, at least from the perspective of logical access, it doesn't matter to the user whether the file data is in the file system or database.

Either one can be as secure as you need it to be. All modern file systems have granular and robust access controls. To me the difference between secure and insecure is people, policy, and process, rather than the technology. Either a database or a file system can be secure or insecure, however you measure that, depending on how it is configured. So security shouldn't be the only factor that impacts the architecture decision of where to store this data.

You also need to know and factor in things like how much of this data there will be, how large it will grow, how long it must be retained (is there a retention policy?), how essential it is to the application (i.e. availability SLA), what your replication needs are, what your performance needs are, etc. For example, in a disaster, can you bring up the production database first and take time to bring up a LOB database that might take a lot longer to be restored or go through crash recovery? Or must the LOBs be available whenever the application is? From that perspective, having that data in the file system is advantageous. As long as the file system is not corrupted, you can access it. Whereas if it's in a database, we know there are a number of scenarios that can affect the availability of database data.

If it were me, I'd put those large files in the file system. But if you decide to use BLOBs instead, I'll echo Cringer's comment: put them in their own database.
 

Cringer

ProgressTalk.com Moderator
Staff member
We looked at, and designed a file system solution. It wasn't implemented in my time at the company though, so I can't vouch for how well it works! Essentially the files are stored on a filesystem where only one user has access (obviously admins as well). The user would be owned by an AppServer. The user requests a file and the AppServer then gets that file and passes it back to the user. We added additional obfuscation by using non-human-readable file names and folder names which were then stored in the database. That way, even if someone got access to the file system, they couldn't find relevant documents easily.
 

Bounty

New Member
OpenEdge 12 has been the current release for almost a year.

That is strange my OpenEdge 12 installation files are dated 22-2-2019 (2/22/2019) which is slightly less than 6 months, but maybe at your age time is going twice as fast.
 

Bounty

New Member
... to putting the files in the file system and storing their paths in the database.
Another option is to use Amazon S3 (Simple Storage Service) or something similar. I have been looking into this myself but there is no high level API available for OpenEdge.
I know that putting files in the cloud can be read as "putting files on someone else's computer", but depending on how sensitive the data is, this can be a feasible solution.
 

TomBascom

Curmudgeon
That is strange my OpenEdge 12 installation files are dated 22-2-2019 (2/22/2019) which is slightly less than 6 months, but maybe at your age time is going twice as fast.

Zing!

I am rounding to the next integer for the benefit of future generations ;)
 

PatrickTingen

New Member
Although I support the idea of using a file-based solution, for completeness, I would like to add that one disadvantage of a file-based solution is that you will have to do some additional backing up. If you save the files in the database, the db will grow and backups will take longer, but at least your files are backed up without additional effort.
 
So we have evaluate the amount of data arround 5Gb per year. It's not that big.

I have been involved in discussions like this. In my experience it often comes down to someone saying, in not so many words, "I know more about how to secure a database than how to secure a file system."

If users should only have access to some of the files in question, or maybe none, then either (a) they shouldn't have shell or file-sharing access to that server at all, or (b) they shouldn't have shell or file-sharing access to the parent of the directories that contain the files in question, or anything below the parent. Preferably, as this is a back-end server, they will have no access at all. Then the only way they should be able to get at the data is via the application which should have its own authorization system and logic. And in that case, at least from the perspective of logical access, it doesn't matter to the user whether the file data is in the file system or database.

Either one can be as secure as you need it to be. All modern file systems have granular and robust access controls. To me the difference between secure and insecure is people, policy, and process, rather than the technology. Either a database or a file system can be secure or insecure, however you measure that, depending on how it is configured. So security shouldn't be the only factor that impacts the architecture decision of where to store this data.

You also need to know and factor in things like how much of this data there will be, how large it will grow, how long it must be retained (is there a retention policy?), how essential it is to the application (i.e. availability SLA), what your replication needs are, what your performance needs are, etc. For example, in a disaster, can you bring up the production database first and take time to bring up a LOB database that might take a lot longer to be restored or go through crash recovery? Or must the LOBs be available whenever the application is? From that perspective, having that data in the file system is advantageous. As long as the file system is not corrupted, you can access it. Whereas if it's in a database, we know there are a number of scenarios that can affect the availability of database data.

If it were me, I'd put those large files in the file system. But if you decide to use BLOBs instead, I'll echo Cringer's comment: put them in their own database.

I agree with that. Our idea is to allow the user to add some document to his purchase request. This document can be read by and the users that can allowed the purchase order.

Also we aren't using appServer so I'm not able to do it with progress.

Thank you for all you idea and observation about the different way to do it.

We will judge the pros and con and will make a choice.

Thank you everyone as always.

Best Regards,

- BobyIsProgress -
 
Top