Question Archive a file in a progress table

Hi,

Is there a way to archive/save (I don't know the exact term) a file like pdf, jpeg, excel, word, ... in a progress DB? We also need to have a credential management .

-> YES , how?

-> No, do you have a solution ?

Best Regards All
 

TomBascom

Curmudgeon
You could store it as a BLOB field and use COPY-LOB, RAW-TRANSFER and so forth to load and manipulate the data.

BLOB and CLOB fields should be stored in dedicated storage areas -- be careful not to allow them to default to the schema area or get dropped in with "normal" data. It is tempting to say that the "rows per block" for BLOB data ought to be 1 since your average BLOB is going to be larger than a db block. BUT unless they are all of a uniform size that just happens to be a multiple of the usable size of a db block that is incorrect because there will always be more or less random sized trailing fragments. So you actually probably want a fairly large rows per block.

There is also a pretty good argument that you want this sort of data in its own database rather than in your main transactional db. It tends to create major growth. You probably want to tune -B and other parameters differently for a BLOB db and you may have different backup requirements.

You can roll your own or many applications have "bolt-on" document management databases available for this purpose.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
We don't use LOB fields in our databases so I can't give you any hands-on tips for using them. But I am aware of people who used BLOBs/CLOBs to store large amounts of unstructured data in their databases because they could and, in the long term, regretted it.

I think you should ask yourself: are you planning to use a database as a proxy for a file system? If so, it will be more time and money and hassle to manage. Do you have a policy for the life cycle of the data? If not, it is difficult to determine the management cost up front.

Do you actually need to execute relational queries on this data? If not I would think twice about storing it in a database. For example, I wouldn't put it in an OpenEdge database just because it needs to be secure. A modern OS/file system should provide access controls at least as granular and robust as you will get from an OpenEdge database.

If you need a full-blown document-management system, then the scope of this discussion changes. You could possibly build that with an OpenEdge back end, but designing and building it would be non-trivial. Purchasing a purpose-built solution, if you can find one to meet your needs, might be more economical and provide faster time to market than building it yourself.
 

Cringer

ProgressTalk.com Moderator
Staff member
Lots of good advice here. Do not store such files in your database. It starts off as a good idea, but rapidly grows to be something you regret. I used to maintain a 400GB database, of which 250GB were PDFs. It was a badly designed quagmire that meant that even the simplest of maintenance jobs took over twice as long as they should.
Tom's comment about using a separate database is a good one. But remember, a database is NOT a file system.
 

andre42

Member
The document management system that is included in our product (ERP software) is implemented through an AppServer that stores the files on the file system of the server it is running on. All metadata of the DMS is still stored in the Progress DB.
Of course one reason for this decision is that the function was implemented long before OpenEdge 10 offered LOBs, but as Rob and Cringer wrote this might still be a good idea.
 

Cringer

ProgressTalk.com Moderator
Staff member
It's a very good idea. I designed a very similar solution, with the added security that the file system used was separate from the main server. Only one non-admin user had read/write access, and that was the user the AppServer connected with. File names were obfuscated as well to make it hard for a user to find the files even if they did get access. These were customer bills that were being stored so security had to be high.
The advantages of such an approach though, are that you can use your standard file system tools for things like backups and restores, such as using snapshots and incremental backups. Having it all in the database made even such trivial aspects of maintenance very hard.
 
Hi Thank you for your experience and advice.

Our need is as following:

We have an app for creating buying request, where the user had every line of is demand. But some of our user have a file with all the line in it and they ask to add it as attached file.

So we thought about a windows solution with security administration but we are limited.....

Our idea was to stored the file in an new progress DB only used for this function. And with a delete of file when the buying request line where all delivered . So like that we can limited the total size of the DB.

I will also explore the solution of an app server to do it (but for me it's an all new tech).
 
Top